Getting error in the package body execution [message #218119] |
Tue, 06 February 2007 22:28 |
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 |
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 |
Frank
Messages: 7901 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 |
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,
|
|
|