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

Answers


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.


Need Your Help

R Batch mode - Can't execute script

r csv batch-processing

To run R non-interactively I've decided to use Batch processing (using Windows 7).

CSS convert table columns into full-width stacked elements

css table html

I've found many ways to create a 'tableless' table layout using only DIVs, but very little about the opposite.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.