Home » SQL & PL/SQL » SQL & PL/SQL » Getting error in the package body execution
Getting error in the package body execution [message #218119] Tue, 06 February 2007 22:28 Go to next message
world.apps
Messages: 70
Registered: January 2007
Location: Hyderabad
Member

SQL> CREATE OR REPLACE PACKAGE emp_package IS
  2    TYPE emp_table_type IS TABLE OF emp%ROWTYPE
  3      INDEX BY BINARY_INTEGER;
  4  PROCEDURE read_emp_table
  5                     (p_emp_table OUT emp_table_type);
  6  END emp_package;
  7  /
SQL> package created

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE PACKAGE BODY emp_package IS
  2    PROCEDURE read_emp_table(p_emp_table OUT emp_table_type)
  3    IS
  4    I BINARY_INTEGER:=0;
  5    BEGIN
  6       FOR emp_record IN (SELECT * FROM EMP) LOOP
  7       emp_table(i):=emp_record;
  8       i:=i+1;
  9       END LOOP;
 10    END read_emp_table;
 11* END emp_package;
 12  /

Warning: Package Body created with compilation errors.

SQL> show err
Errors for PACKAGE BODY EMP_PACKAGE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/6      PLS-00201: identifier 'EMP_TABLE' must be declared
7/6      PL/SQL: Statement ignored



Thanks in advance

[Updated on: Tue, 06 February 2007 22:32]

Report message to a moderator

Re: Getting error in the package body execution [message #218140 is a reply to message #218119] Tue, 06 February 2007 23:55 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

the error itself is self explanatory that you haven't declare any variable as emp_table.

 CREATE OR REPLACE PACKAGE emp_package IS
TYPE emp_table_type IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
emp_table emp_table_type;--you are missing this
PROCEDURE read_emp_table
(p_emp_table OUT emp_table_type);
END emp_package;



regards,
Re: Getting error in the package body execution [message #218154 is a reply to message #218140] Wed, 07 February 2007 00:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:

CREATE OR REPLACE PACKAGE emp_package
IS
TYPE emp_table_type IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
emp_table emp_table_type;--you are missing this

PROCEDURE read_emp_table
( p_emp_table OUT emp_table_type
);
END emp_package;


This is NOT the right way to do it. First of all, it is clear that the OP meant p_emp_table instead of emp_table (how else would the out-parameter be filled?), and secondly you would NOT want to declare this variable as a public package variable!
Re: Getting error in the package body execution [message #218168 is a reply to message #218154] Wed, 07 February 2007 01:26 Go to previous message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

thanks Frank for correcting me.can the below code be the correct way of doing it.

CREATE OR REPLACE PACKAGE EMP_PACKAGE
IS
  TYPE EMP_TABLE_TYPE IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER ;
  
  PROCEDURE READ_EMP_TABLE(P_EMP_TABLE  OUT EMP_TABLE_TYPE);
END;
/

CREATE OR REPLACE PACKAGE BODY EMP_PACKAGE
IS
  PROCEDURE READ_EMP_TABLE
       (P_EMP_TABLE  OUT EMP_TABLE_TYPE)
  IS
  BEGIN
    SELECT *
    BULK COLLECT INTO P_EMP_TABLE
    FROM   EMP;
    
    FOR I IN P_EMP_TABLE.FIRST.. P_EMP_TABLE.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(P_EMP_TABLE(I).ENAME);
    END LOOP;
  END;
END;



regards,
Previous Topic: no output
Next Topic: View Compressed Packages
Goto Forum:
  


Current Time: Mon Dec 05 09:11:38 CST 2016

Total time taken to generate the page: 0.19795 seconds