PLS-00382: expression is of wrong type

irlrobins

Hey all,

Stumped on this one so hope someone can help. I have the following package on a Oracle 10G datatbase.

CREATE OR REPLACE PACKAGE SCHEMA1.PENDING_ORDERS AS
TYPE type_onum_tab IS TABLE OF itr_orders.order_num%TYPE;
TYPE t_crs is ref cursor;
   
PROCEDURE dyn_sel(sqlString   in varchar2, crs in out t_crs);
   
   FUNCTION getIncludedApps(sqlString IN VARCHAR2) RETURN type_onum_tab;

END PENDING_ORDERS;

slqString is basically a select statement that is used within the function getIncludedApps.

I'm trying to test the function using the following code:

SET SERVEROUTPUT ON;
DECLARE

TYPE type_onum_tab IS TABLE OF itr_orders.order_num%TYPE;
tab_onum type_onum_tab :=type_onum_tab();

BEGIN
DBMS_OUTPUT.PUT_LINE('start');
tab_onum:= pending_orders.GETINCLUDEDAPPS('SELECT order from.... etc');
for i in 1 .. tab_onum.count
LOOP
    DBMS_OUTPUT.PUT_LINE('Row ' ||i||'. Value = ' || tab_onum(i));
END Loop;
END;

But when I try to run this I get the PLS-00382 error message. How can I assign the table that's return by the funtion to a table?

DonnieBrasco

has this package defo compiled ok?
looks like a small typo on the line "TYPEt_crs is ref cursor;"


CREATE OR REPLACE PACKAGE SCHEMA1.PENDING_ORDERS AS
TYPE type_onum_tab IS TABLE OF itr_orders.order_num%TYPE;
TYPEt_crs is ref cursor;

PROCEDURE dyn_sel(sqlString in varchar2, crs in out t_crs);

FUNCTION getIncludedApps(sqlString IN VARCHAR2) RETURN type_onum_tab;

END PENDING_ORDERS;

irlrobins

Sorry, that's a typo in my post, the package compiles fine.

DonnieBrasco

it might be necessary to see your body code. but try this out.


CREATE OR REPLACE PACKAGE SCHEMA1.PENDING_ORDERS AS
TYPE type_onum_tab IS TABLE OF itr_orders.order_num%TYPE;
TYPE t_crs is ref cursor;
--new code here
MeTable type_onum_tab;
--

PROCEDURE dyn_sel(sqlString in varchar2, crs in out t_crs);

FUNCTION getIncludedApps(sqlString IN VARCHAR2) RETURN type_onum_tab;

END PENDING_ORDERS;


AND THIS THEN THIS...

SET SERVEROUTPUT ON;
DECLARE

TYPE type_onum_tab IS TABLE OF itr_orders.order_num%TYPE;
tab_onum type_onum_tab :=type_onum_tab();

BEGIN
DBMS_OUTPUT.PUT_LINE('start');
PENDING_ORDERS.MeTable := pending_orders.GETINCLUDEDAPPS('SELECT order from.... etc');
for i in 1 .. tab_onum.count
LOOP
DBMS_OUTPUT.PUT_LINE('Row ' ||i||'. Value = ' || tab_onum(i));
END Loop;
END;

DonnieBrasco

DonnieBrasco wrote: »

it might be necessary to see your body code. but try this out.


CREATE OR REPLACE PACKAGE SCHEMA1.PENDING_ORDERS AS
TYPE type_onum_tab IS TABLE OF itr_orders.order_num%TYPE;
TYPE t_crs is ref cursor;
--new code here
MeTable type_onum_tab;
--

PROCEDURE dyn_sel(sqlString in varchar2, crs in out t_crs);

FUNCTION getIncludedApps(sqlString IN VARCHAR2) RETURN type_onum_tab;

END PENDING_ORDERS;


AND THIS THEN THIS...

SET SERVEROUTPUT ON;
DECLARE

TYPE type_onum_tab IS TABLE OF itr_orders.order_num%TYPE;
tab_onum type_onum_tab :=type_onum_tab();

BEGIN
DBMS_OUTPUT.PUT_LINE('start');
PENDING_ORDERS.MeTable := pending_orders.GETINCLUDEDAPPS('SELECT order from.... etc');
for i in 1 .. tab_onum.count
LOOP
DBMS_OUTPUT.PUT_LINE('Row ' ||i||'. Value = ' || tab_onum(i));
END Loop;
END;

correction

DECLARE

TYPE type_onum_tab IS TABLE OF itr_orders.order_num%TYPE;
tab_onum type_onum_tab :=type_onum_tab();

BEGIN
DBMS_OUTPUT.PUT_LINE('start');
PENDING_ORDERS.MeTable := pending_orders.GETINCLUDEDAPPS('SELECT order from.... etc');
for i in 1 .. PENDING_ORDERS.MeTable.count
LOOP
DBMS_OUTPUT.PUT_LINE('Row ' ||i||'. Value = ' || PENDING_ORDERS.MeTable(i));
END Loop;
END;

irlrobins

Thanks Donnie.

In the time between posting and your reply, some googling led me to this page.

This line in particular caught my attention: "The pipelined table function is the only way to read a table of record structures in a SQL statement"

So I changed my function to a pipelined table and now I can access the table that is returned with "select * from table(call to function)".

So looks like that will work for me.

I tried your suggestions for completeness and get PLS-00653 error. (Aggregate/table functions are not allowed in PL/SQL scope)

DonnieBrasco

yeap that looks like road to go down
cheers