Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

PLS-00382: expression is of wrong type

  • 23-11-2009 12:09PM
    #1
    irlrobins
    Registered Users, Registered Users 2 Posts: 7,541 ✭✭✭


    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?


Comments

  • #2
    DonnieBrasco
    Registered Users, Registered Users 2 Posts: 169 ✭✭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;


  • #3
    irlrobins
    Registered Users, Registered Users 2 Posts: 7,541 ✭✭✭irlrobins


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


  • #4
    DonnieBrasco
    Registered Users, Registered Users 2 Posts: 169 ✭✭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;


  • #5
    DonnieBrasco
    Registered Users, Registered Users 2 Posts: 169 ✭✭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;


  • #6
    irlrobins
    Registered Users, Registered Users 2 Posts: 7,541 ✭✭✭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)


  • Advertisement
  • #7
    DonnieBrasco
    Registered Users, Registered Users 2 Posts: 169 ✭✭DonnieBrasco


    yeap that looks like road to go down
    cheers


Advertisement