Basic Oracle question

Question

I am new to oracle. When I create a stored procedure using:

CREATE OR REPLACE PROCEDURE PROCEDURE1 
AS
BEGIN

  SELECT FIRSTNAME,
         LASTNAME
    INTO FirstName,LastName
    FROM EMPLOYEE;

END PROCEDURE1;

i get the following errors:

PL/SQL Statement Ignored Identifier FIRSTNAME must be declared ORA-00904 Invalid identifier

score 7 · Answer 1 · answered Dec 02 '10 at 17:33

You need to declare variables before you attempt to populate them:

CREATE OR REPLACE PROCEDURE PROCEDURE1 
AS

 FirstName EMPLOYEE.FIRSTNAME%TYPE;
 LastName EMPLOYEE.LASTNAME%TYPE;

BEGIN

  SELECT FIRSTNAME,
         LASTNAME
    INTO FirstName,LastName
    FROM EMPLOYEE;

END PROCEDURE1;

The %TYPE notation is shorthand for data type declaration that matches the column data type. If that data type ever changes, you don't need to update the procedure.

It is also a good idea to not name your variables the same as the columns. — kurosch, Dec 02 '10 at 17:50

Sathyajith Bhat · Answer 2 · 2010-12-02T18:10:53.567

3

You need to declare the variables.

CREATE OR REPLACE
PROCEDURE PROCEDURE1 AS
 V_FIRSTNAME VARCHAR2(60);
 V_LASTNAME  VARCHAR2(60);
BEGIN
        SELECT FIRSTNAME,LASTNAME
         INTO V_FIRSTNAME ,V_LASTNAME  
         FROM EMPLOYEE;
END PROCEDURE1;

In reply to your comment, SQL statements in PL/SQL block can fetch only 1 record. If you need to fetch multiple records, you will need to store the records in a cursor and process them.

CREATE OR REPLACE
    PROCEDURE PROCEDURE1 AS

     CURSOR EMP_CUR IS
         SELECT FIRSTNAME,LASTNAME
         FROM EMPLOYEE;
    EMP_CUR_REC EMP_CUR%ROWTYPE;

    BEGIN
     FOR EMP_CUR_REC IN EMP_CUR LOOP
         -- do your processing
         DBMS_OUTPUT.PUT_LINE('Employee first name is ' || EMP_CUR_REC.FIRSTNAME);
         DBMS_OUTPUT.PUT_LINE('Employee last name is ' || EMP_CUR_REC.LASTNAME);
    END LOOP;
    END PROCEDURE1;

To explain: EMP_CUR holds the SQL statement to be executed. EMP_CUR_REC holds the records that will be fetched by the SQL statement. %ROWTYPE indicates that the Record will be of the same data type as the row which holds the data

The FOR LOOP will fetch each record, and you can do whatever processing that needs to be done.

edited Dec 02 '10 at 18:10

answered Dec 02 '10 at 17:31

Sathyajith Bhat

21,321
22
95
134

Can i have something like Select * from Tablename or select firstname,lastname from tablename , like we have in sql server – kayak Dec 02 '10 at 17:33
No, that's not possible in a PL/SQL block. – Sathyajith Bhat Dec 02 '10 at 17:33
Now i get this error,
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYSTEM.PROCEDURE1", line 5
ORA-06512: at line 2
– kayak Dec 02 '10 at 17:55
In PL/SQL you can fetch only 1 record - it cannot be zero or it cannot be more than one. Can you explain what exactly you're trying to do ? – Sathyajith Bhat Dec 02 '10 at 18:04
@sathya : by the above way can i read the result from .Net code – kayak Dec 02 '10 at 19:12
1

Not sure, I think you will have to return it as a ref cursor. Ref this: http://stackoverflow.com/questions/304600/how-to-make-oracle-procedure-return-result-sets – Sathyajith Bhat Dec 02 '10 at 19:25

Krishna Kumar N · Accepted Answer · 2010-12-03T10:31:18.910

2

I think the "AS" keyword won't work. If it doesn't work, then use "IS".
Rest are fine and very good tips.

If you need any help regarding PL/SQL, then you can have a look at this link. It is very simple and easy to understand;
http://plsql-tutorial.com/

This is my solution to the error which you are getting;

CREATE OR REPLACE PROCEDURE PROCEDURE1 IS
  v_FIRSTNAME EMPLOYEE.FIRSTNAME%TYPE;
  v_LASTNAME EMPLOYEE.LASTNAME%TYPE;
  CURSOR EMPCURSOR IS
  SELECT FIRSTNAME, LASTNAME FROM EMPLOYEE;      
  BEGIN   
     IF NOT EMPCURSOR%ISOPEN THEN
         OPEN EMPCURSOR;
     END IF;
     LOOP
        FETCH EMPCURSOR INTO V_FIRSTNAME,V_LASTNAME;
        EXIT WHEN EMPCURSOR%NOTFOUND;
     END LOOP;
     IF EMPCURSOR%ISOPEN THEN
        CLOSE EMPCURSOR;
     END;
  END PROCEDURE1;

You can also use DBMS_OUTPUT.PUT_LINE(V_FIRSTNAME || ','|| V_LASTNAME), inside the loop to display the output. but in order to do that, you first need to execute the command server output on

edited Dec 03 '10 at 10:31

answered Dec 02 '10 at 17:45

Krishna Kumar N

137
2
13

I'm adding my comment here for the previous question..... into statement works only if there is a single record or if you are trying to fetch a single record. You need to you cursors to loop through the records. – Krishna Kumar N Dec 02 '10 at 18:01
Actually, you can populate a `%ROWTYPE` variable, which provides similar to %TYPE -- don't have to worry about data type changes, besides row variable shorthand... – OMG Ponies Dec 02 '10 at 19:02
in a PL/SQL PROCEDURE (or FUNCTION) declaration, AS and IS are synonymous and either one may be used. – Bob Jarvis - Слава Україні Dec 03 '10 at 12:18

score 1 · Answer 4 · answered Dec 03 '10 at 12:17

In a response to @Sathya's answer above @kayak asked "Can i have something like Select * from Tablename or select firstname,lastname from tablename , like we have in sql server".

Yes, you can do that, but you'll either need to include a WHERE clause or use a cursor. If you include a WHERE clause which limits your results to a single row you could write something like

CREATE OR REPLACE PROCEDURE PROCEDURE1
IS
  rowEmployees EMPLOYEE%ROWTYPE;
BEGIN
  SELECT *
    INTO rowEmployees
    FROM EMPLOYEE
    WHERE EMPLOYEE_ID = 12345;
END PROCEDURE1;

On the other hand, if you either don't have a WHERE clause because you wish to process all rows in the table, or you have a WHERE clause which doesn't limit your results to a single row you could use a cursor in the following manner:

CREATE OR REPLACE PROCEDURE PROCEDURE1 IS
BEGIN
  FOR rowEmployees IN (SELECT *
                         FROM EMPLOYEE
                         WHERE EMPLOYEE_ID IN (12345, 67890, 111213, 141516))
  LOOP
    <do something with rowEmployees here>
  END LOOP;
END PROCEDURE1;

Share and enjoy.

Basic Oracle question

4 Answers4