Basic Oracle 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
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
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.
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.