Forum Stats

  • 3,740,990 Users
  • 2,248,355 Discussions
  • 7,861,563 Comments

Discussions

ora-00902 invalid data type

247890
247890 Member Posts: 420
edited Jan 5, 2009 1:53PM in SQL & PL/SQL
Hello all -

I have a function that returns a ref cursor.

When I test this function using PL/SQL developer, it works just fine and returns ref cursor with a set of price values for a set of products.

However, when I test the same function from SQL*Plus, it errors out.

select get_price('0123','p1,p2,p3') from dual;

Error at line 1:
ORA-00902: invalid datatype

Could you please help me out. Thanks in advance.
«1

Answers

  • OrionNet
    OrionNet Member Posts: 4,542
    Hello,
    Can you post your get_price procedure/function?

    Try using anonymous pl/sql block simple example,
    CREATE OR REPLACE FUNCTION getpmnorgrow (empid emp.id%TYPE)
       RETURN emp%ROWTYPE --
    IS
       result   emp%ROWTYPE;
    BEGIN
       SELECT *
       INTO result
       FROM emp
       WHERE id = empid;
    
       RETURN result;
    EXCEPTION
       WHEN NO_DATA_FOUND
       THEN
          raise_application_error (-20100, 'error');
          RETURN NULL;
    END;
    /
    
    DECLARE
       retval   emp%ROWTYPE;
       empid    NUMBER;
    BEGIN
       empid    := 1;
    
       retval   := getpmnorgrow (empid);
       DBMS_OUTPUT.put_line ('employee name=' || retval.name);
       COMMIT;
    END;
    Regards
  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    variable my_cursor refcursor;
    exec :my_cursor := get_price('0123','p1,p2,p3');

    print :my_cursor
  • OrionNet
    OrionNet Member Posts: 4,542
    Check this link , discussing the same error


    844608

    Regards
  • 247890
    247890 Member Posts: 420
    edited Jan 5, 2009 9:05AM
    Hi,

    Thanks for your response.
    -------------------------------------------------------------------------

    Edited by: user6773 on Jan 5, 2009 6:05 AM
  • 247890
    247890 Member Posts: 420
    Hi Tubby,

    I get an error msg when I try this. Not sure ..
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    Whats the error you are getting?
  • 247890
    247890 Member Posts: 420
    When I try to test it:

    select get_price('0123','p1,p2,p3') from dual;

    Error at line 1:
    ORA-00902: invalid datatype
  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    edited Jan 5, 2009 9:15AM
    select get_price('0123','p1,p2,p3') from dual;
    Why do you have p1,p2,p3 in quote marks?

    Is your function expecting a character string here? Or should they be separate parameters?
  • 247890
    247890 Member Posts: 420
    Yes, it is expecting a string of values for the second parameter (set of products).
  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    I have no idea what your function does, but try this:

    SQL> var results refcursor
    SQL> exec :results := get_price('0123','p1,p2,p3');
    SQL> print results
  • 247890
    247890 Member Posts: 420
    Thanks for your response. That works. But, I am not sure if this is useful when this function is called by a java application.
  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    But, I am not sure if this is useful when this function is called by a java application.
    Of course not. You asked why this didn't work in SQL Plus, so this is a SQL Plus solution.

    If you're using Java and having trouble, you can post another question.
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    It would be really, really helpful if you could post your function, as has already been requested!

    I could ask you "My car doesn't work start, unless I'm parked on a hill, why not?" but I bet you'd have trouble pinpointing the exact problem that my car has got...
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    edited Jan 5, 2009 10:32AM
    I have plenty of questions.

    Question:

    Why don't you post your actual script here?

    a. Your Company Restrict You.
    b. You don't want to post.
    c. You don't understand what our volunteers have repeatedly ask
    d. None of the above.

    Now,

    Solution - In case of ->

    a. Then post one dummy solution which also facing the same error prone scenario and ask your requirement clearly.

    b. Then hard luck. Our help will be up to this due to your response.

    c. Our volunteers are asking repeatedly to post the actual code here. And, again as i've already explained in point (a) - if you have any problem to post then follow solution a.

    d. Then try to read oracle documentation and specifically read how collection and ref cursor works and then try to implement that by yourself.

    I hope i've covered almost all the points for you.

    Regards.

    Satyaki De.
  • 247890
    247890 Member Posts: 420
    wow!

    Sorry, it took me a little time to check this. I am not sure if my company objects to this. So, I have posted this code earlier and then, I removed it again.

    I didn't mean to ask a random question and expect answer. After reading few similar threads on this forum, I thought this question is more independent of what code does. Sorry for any inconvenience caused...
    -------------------------
    create or replace package types as
    type cursor_type is ref cursor;
    end types;

    CREATE OR REPLACE FUNCTION GET_multi_PRICE(P_STORE IN VARCHAR2,
    --P_PRODCODE IN RPPRODPRICE.PRODCODE%TYPE,
    v_inStr IN VARCHAR2
    )
    RETURN types.cursor_type IS
    v_cursor types.cursor_type;
    V_PR_SH_CODE RPPRODPRICE.PRICESHEETCODE%TYPE;
    V_BASE_SH_CODE RPCOROLLARY.BASEPRICESHEETCODE%TYPE;
    V_SEQ RPPRODPRICE.SEQNUM%TYPE;
    v_fin_seq RPPRODPRICE.SEQNUM%TYPE;
    V_BASE_SEQ RPCOROLLARY.BASESEQNUM%TYPE;
    V_ADV_TYPE RPPRICESHEET.ADVTYPE%TYPE;
    V_PRICE RPPRODPRICE.PRICE%TYPE := 0;
    V_PR_SH_TYPE RPPRICESHEET.PRICESHEETTYPE%TYPE;
    v_fin_pr_sh_type RPPRICESHEET.PRICESHEETTYPE%TYPE;
    V_ADJ_TYPE RPCOROLLARY.ADJTYPE%TYPE;
    V_ADJ_AMT RPCOROLLARY.ADJAMT%TYPE;
    V_DISC_TYPE RPCOROLLARY.DISCTYPE%TYPE;
    V_END_PRICE RPCOROLLARY.PRICEENDING%TYPE := 0;
    V_FINAL_PRICE RPPRODPRICE.PRICE%TYPE := 0;
    V_RET_PRICE RPPRODPRICE.PRICE%TYPE := 0;
    MSG VARCHAR2(200) := NULL;
    v_sql VARCHAR2(4000) ;
    v_instr_rp VARCHAR2(200);
    -- 1. Check to see if there is a SALE PRICE for the selected product
    --=======(a) Get ADV price sheet for selected store from RPPRICESTORE that are in effect for the selected date
    --======= Check to ensure that the price sheet has been deployed ('D')
    --======= Get ADVTYPE field from RPPRICESHEET table to determine the type of ADV price sheet (E or R)
    --======= If multiple records found, select the one with the most recent effective date.
    BEGIN
    SELECT STR.PRICESHEETCODE, STR.SEQNUM, PS.ADVTYPE, PS.PRICESHEETTYPE
    INTO V_PR_SH_CODE, V_SEQ, V_ADV_TYPE, V_PR_SH_TYPE
    FROM RPPRICESTORE STR, RPPRICESHEET PS
    WHERE STR.PRICESHEETCODE = PS.PRICESHEETCODE
    AND STR.SEQNUM = PS.SEQNUM
    AND PS.STATUS = 'D'
    AND TO_DATE(STR.EFFECTIVEDATE, 'yyyymmdd') <= SUBSTR(SYSDATE, 1, 10)
    AND (STR.EXPDATE = '99999999' OR
    TO_DATE(STR.EXPDATE, 'yyyymmdd') >= SUBSTR(SYSDATE, 1, 10))
    AND STR.STORE = P_STORE
    AND STR.EFFECTIVEDATE =
    (SELECT MAX(S.EFFECTIVEDATE)
    FROM RPPRICESTORE S
    WHERE S.STORE = P_STORE
    AND TO_DATE(S.EFFECTIVEDATE, 'yyyymmdd') <= SUBSTR(SYSDATE, 1, 10)
    AND (S.EXPDATE = '99999999' OR
    TO_DATE(S.EXPDATE, 'yyyymmdd') >= SUBSTR(SYSDATE, 1, 10)));
    --=======(b) Get price from RPPRODPRICE. Check to make sure that ACDSTATUS is not D, or SD (Product deleted from price sheet)
    v_instr_rp := REPLACE(v_instr,chr(44),chr(39)||chr(44)||chr(39));

    IF V_PR_SH_TYPE = 'A' THEN
    IF V_ADV_TYPE = 'E' THEN


    v_sql := 'SELECT PRI.PRICE FROM RPPRODPRICE PRI
    WHERE PRI.PRICESHEETCODE = ' || '''' || V_PR_SH_CODE || '''' ||
    ' AND PRI.SEQNUM = ' || '''' || V_SEQ || '''' ||
    ' AND PRI.PRODCODE in (' || '''' || v_instr_rp || '''' ||
    ') AND (PRI.ACDSTATUS IS NULL OR PRI.ACDSTATUS NOT IN (''D'', ''SD''))';
    dbms_output.put_line(v_sql);

    OPEN v_cursor FOR v_sql;
    RETURN v_cursor;
    -- ADV Rule based
    END IF;

    END IF;
    exception
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('There is no data for the STORE and PRODUCT_CODE combination provided.');
    WHEN OTHERS THEN
    MSG := SUBSTR(SQLERRM, 1, 70);
    DBMS_OUTPUT.PUT_LINE(MSG);
    END;
    /
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    I'm posting your query in a formatted manner so that everyone can easily understand your coding ->
    create or replace package types as
      type cursor_type is ref cursor;
    end types;
    
    CREATE OR REPLACE FUNCTION GET_multi_PRICE
     (
       P_STORE IN VARCHAR2,
       --P_PRODCODE IN RPPRODPRICE.PRODCODE%TYPE,
       v_inStr IN VARCHAR2
     )
    RETURN types.cursor_type 
    IS
        v_cursor types.cursor_type;
        V_PR_SH_CODE RPPRODPRICE.PRICESHEETCODE%TYPE;
        V_BASE_SH_CODE RPCOROLLARY.BASEPRICESHEETCODE%TYPE;
        V_SEQ RPPRODPRICE.SEQNUM%TYPE;
        v_fin_seq RPPRODPRICE.SEQNUM%TYPE;
        V_BASE_SEQ RPCOROLLARY.BASESEQNUM%TYPE;
        V_ADV_TYPE RPPRICESHEET.ADVTYPE%TYPE;
        V_PRICE RPPRODPRICE.PRICE%TYPE := 0;
        V_PR_SH_TYPE RPPRICESHEET.PRICESHEETTYPE%TYPE;
        v_fin_pr_sh_type RPPRICESHEET.PRICESHEETTYPE%TYPE;
        V_ADJ_TYPE RPCOROLLARY.ADJTYPE%TYPE;
        V_ADJ_AMT RPCOROLLARY.ADJAMT%TYPE;
        V_DISC_TYPE RPCOROLLARY.DISCTYPE%TYPE;
        V_END_PRICE RPCOROLLARY.PRICEENDING%TYPE := 0;
        V_FINAL_PRICE RPPRODPRICE.PRICE%TYPE := 0;
        V_RET_PRICE RPPRODPRICE.PRICE%TYPE := 0;
        MSG VARCHAR2(200) := NULL;
        v_sql VARCHAR2(4000) ;
        v_instr_rp VARCHAR2(200);
    -- 1. Check to see if there is a SALE PRICE for the selected product
    --=======(a) Get ADV price sheet for selected store from RPPRICESTORE that are in effect for the selected date
    --======= Check to ensure that the price sheet has been deployed ('D')
    --======= Get ADVTYPE field from RPPRICESHEET table to determine the type of ADV price sheet (E or R)
    --======= If multiple records found, select the one with the most recent effective date.
    BEGIN
      SELECT STR.PRICESHEETCODE, STR.SEQNUM, PS.ADVTYPE, PS.PRICESHEETTYPE
      INTO V_PR_SH_CODE, V_SEQ, V_ADV_TYPE, V_PR_SH_TYPE
      FROM RPPRICESTORE STR, RPPRICESHEET PS
      WHERE STR.PRICESHEETCODE = PS.PRICESHEETCODE
      AND STR.SEQNUM = PS.SEQNUM
      AND PS.STATUS = 'D'
      AND TO_DATE(STR.EFFECTIVEDATE, 'yyyymmdd') <= SUBSTR(SYSDATE, 1, 10)
      AND (
             STR.EXPDATE = '99999999' 
             OR TO_DATE(STR.EXPDATE, 'yyyymmdd') >= SUBSTR(SYSDATE, 1, 10)
          )
      AND STR.STORE = P_STORE
      AND STR.EFFECTIVEDATE =(
                                SELECT MAX(S.EFFECTIVEDATE)
                                FROM RPPRICESTORE S
                                WHERE S.STORE = P_STORE
                                AND TO_DATE(S.EFFECTIVEDATE, 'yyyymmdd') <= SUBSTR(SYSDATE, 1, 10)
                                AND (
                                        S.EXPDATE = '99999999' 
                                        OR TO_DATE(S.EXPDATE, 'yyyymmdd') >= SUBSTR(SYSDATE, 1, 10)
                                    )
                             );
      --=======(b) Get price from RPPRODPRICE. Check to make sure that ACDSTATUS is not D, or SD (Product deleted from price sheet)
      v_instr_rp := REPLACE(v_instr,chr(44),chr(39)||chr(44)||chr(39));
    
      IF V_PR_SH_TYPE = 'A' THEN
        IF V_ADV_TYPE = 'E' THEN
          v_sql := 'SELECT PRI.PRICE FROM RPPRODPRICE PRI WHERE PRI.PRICESHEETCODE = ' || '''' || V_PR_SH_CODE || '''' ||
                   ' AND PRI.SEQNUM = ' || '''' || V_SEQ || '''' ||
                   ' AND PRI.PRODCODE in (' || '''' || v_instr_rp || '''' ||
                   ') AND (PRI.ACDSTATUS IS NULL OR PRI.ACDSTATUS NOT IN (''D'', ''SD''))';
          dbms_output.put_line(v_sql);
          
          OPEN v_cursor FOR v_sql;
          
          RETURN v_cursor;
        -- ADV Rule based
        END IF;
      END IF;
    exception
      WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('There is no data for the STORE and PRODUCT_CODE combination provided.');
      WHEN OTHERS THEN
        MSG := SUBSTR(SQLERRM, 1, 70);
        DBMS_OUTPUT.PUT_LINE(MSG);
    END;
    N.B.: This is your post - i just format it.

    Regards.

    Satyaki De.
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    I'm not so sure whether your previous solution is working or not.

    But, i've made some modification in your code and this might work for you ->
    CREATE OR REPLACE FUNCTION GET_multi_PRICE
     (
       P_STORE IN VARCHAR2,
       --P_PRODCODE IN RPPRODPRICE.PRODCODE%TYPE,
       v_inStr IN VARCHAR2
     )
    RETURN types.cursor_type 
    IS
        v_cursor types.cursor_type;
        V_PR_SH_CODE RPPRODPRICE.PRICESHEETCODE%TYPE;
        V_BASE_SH_CODE RPCOROLLARY.BASEPRICESHEETCODE%TYPE;
        V_SEQ RPPRODPRICE.SEQNUM%TYPE;
        v_fin_seq RPPRODPRICE.SEQNUM%TYPE;
        V_BASE_SEQ RPCOROLLARY.BASESEQNUM%TYPE;
        V_ADV_TYPE RPPRICESHEET.ADVTYPE%TYPE;
        V_PRICE RPPRODPRICE.PRICE%TYPE := 0;
        V_PR_SH_TYPE RPPRICESHEET.PRICESHEETTYPE%TYPE;
        v_fin_pr_sh_type RPPRICESHEET.PRICESHEETTYPE%TYPE;
        V_ADJ_TYPE RPCOROLLARY.ADJTYPE%TYPE;
        V_ADJ_AMT RPCOROLLARY.ADJAMT%TYPE;
        V_DISC_TYPE RPCOROLLARY.DISCTYPE%TYPE;
        V_END_PRICE RPCOROLLARY.PRICEENDING%TYPE := 0;
        V_FINAL_PRICE RPPRODPRICE.PRICE%TYPE := 0;
        V_RET_PRICE RPPRODPRICE.PRICE%TYPE := 0;
        MSG VARCHAR2(200) := NULL;
        v_sql VARCHAR2(4000) ;
        v_instr_rp VARCHAR2(200);
        
        cursor c1
        is
          SELECT  chr(39)||
                  SUBSTR(v_inStr,                    
                          INSTR(v_inStr,',',1,LEVEL) + 1,                    
                          NVL(
                              LEAD(INSTR(v_inStr,',',1,LEVEL)) OVER (ORDER BY INSTR(v_inStr,',',1,LEVEL)) - 1 , 
                              LENGTH(v_inStr) + 1 
                            ) - INSTR(v_inStr,',',1,LEVEL)                   
                        )||
                  chr(39) ils       
          FROM ee,DUAL 
          CONNECT BY LEVEL <= LENGTH(v_inStr) - LENGTH(REPLACE(v_inStr,',')) + 1;
          
        r1 c1%rowtype;
        
        v_instr_p  varchar2(2000);
    -- 1. Check to see if there is a SALE PRICE for the selected product
    --=======(a) Get ADV price sheet for selected store from RPPRICESTORE that are in effect for the selected date
    --======= Check to ensure that the price sheet has been deployed ('D')
    --======= Get ADVTYPE field from RPPRICESHEET table to determine the type of ADV price sheet (E or R)
    --======= If multiple records found, select the one with the most recent effective date.
    BEGIN
      SELECT STR.PRICESHEETCODE, STR.SEQNUM, PS.ADVTYPE, PS.PRICESHEETTYPE
      INTO V_PR_SH_CODE, V_SEQ, V_ADV_TYPE, V_PR_SH_TYPE
      FROM RPPRICESTORE STR, RPPRICESHEET PS
      WHERE STR.PRICESHEETCODE = PS.PRICESHEETCODE
      AND STR.SEQNUM = PS.SEQNUM
      AND PS.STATUS = 'D'
      AND TO_DATE(STR.EFFECTIVEDATE, 'yyyymmdd') <= SUBSTR(SYSDATE, 1, 10)
      AND (
             STR.EXPDATE = '99999999' 
             OR TO_DATE(STR.EXPDATE, 'yyyymmdd') >= SUBSTR(SYSDATE, 1, 10)
          )
      AND STR.STORE = P_STORE
      AND STR.EFFECTIVEDATE =(
                                SELECT MAX(S.EFFECTIVEDATE)
                                FROM RPPRICESTORE S
                                WHERE S.STORE = P_STORE
                                AND TO_DATE(S.EFFECTIVEDATE, 'yyyymmdd') <= SUBSTR(SYSDATE, 1, 10)
                                AND (
                                        S.EXPDATE = '99999999' 
                                        OR TO_DATE(S.EXPDATE, 'yyyymmdd') >= SUBSTR(SYSDATE, 1, 10)
                                    )
                             );
      --=======(b) Get price from RPPRODPRICE. Check to make sure that ACDSTATUS is not D, or SD (Product deleted from price sheet)
      v_instr_rp := REPLACE(v_instr,chr(44),chr(39)||chr(44)||chr(39));
      
      ---New Code for splitting the string
      
      for r1 in c1
      loop
        v_instr_p := v_instr_p || r1.ils ||',';
      end loop;
      
      v_instr_p := rtrim(v_instr_p,',')
      
      ---End Of the New Script
    
      IF V_PR_SH_TYPE = 'A' THEN
        IF V_ADV_TYPE = 'E' THEN
          v_sql := 'SELECT PRI.PRICE FROM RPPRODPRICE PRI WHERE PRI.PRICESHEETCODE = ' || '''' || V_PR_SH_CODE || '''' ||
                   ' AND PRI.SEQNUM = ' || '''' || V_SEQ || '''' ||
                   ' AND PRI.PRODCODE in (' || '''' || v_instr_p || '''' ||
                   ') AND (PRI.ACDSTATUS IS NULL OR PRI.ACDSTATUS NOT IN (''D'', ''SD''))';
          dbms_output.put_line(v_sql);
          
          OPEN v_cursor FOR v_sql;
          
          RETURN v_cursor;
        -- ADV Rule based
        END IF;
      END IF;
    exception
      WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('There is no data for the STORE and PRODUCT_CODE combination provided.');
      WHEN OTHERS THEN
        MSG := SUBSTR(SQLERRM, 1, 70);
        DBMS_OUTPUT.PUT_LINE(MSG);
    END;
    /
    And, now you should pass as you are trying to pass the value previously and see the result.

    N.B.: Not Tested...

    Regards.

    Satyaki De.
  • isotope
    isotope Member Posts: 748 Bronze Badge
    What's the version of your database ?
    select * from v$version;
    isotope
  • 247890
    247890 Member Posts: 420
    It is 8.1.6. Thanks,
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    Oh! Goodness.

    This is almost primitive DB.

    Did you check my solution? Is it working?

    Regards.

    Satyaki De.
  • 247890
    247890 Member Posts: 420
    Yes, I tried your solution. It is erroring out near OVER (line 34).

    True, it is a very old version. But, I can't help it. I have no control over it. I can execute this function just fine in Oracle 9i and above. But, not in in 8.1.6. I was wondering if there is any other way of achieving this. Thanks,
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    Oh! dear,

    Over isn't supported in Oracle 8.

    Really bad.

    So, we have to explore some different ares to achieve the same.

    It is working in Oracle 9i.

    Regards.

    Satyaki De.
  • 247890
    247890 Member Posts: 420
    :(

    There are all sorts of problems with this because of this version.

    I can't even use Oracle SQLDeveloper to connect. Other pl/sql developing tools don't work right either. Even if they do for couple of day, they eventually giveup.
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    First you can test this code ->
    create type np is table of number;
      2  / Type created. 
    SQL> ed
    Wrote file afiedt.buf   
    1     Create or Replace Procedure myProc(myArray np)
    2      is   
    3      i   number(10);   
    4      rec emp%rowtype;   
    5      Begin   
    6         for i in 1..myArray.count   
    7         loop   
    8           select *   
    9           into rec  
    10           from emp  
    11           where empno = myArray(i);  
    12        dbms_output.put_line('Employee No:'||rec.empno||' Name:'||rec.ename);  
    13        end loop;  
    14*   End myProc;  
    15  /
    Procedure created. 
    
    SQL> declare
      2      v np:=np(6666,7777);   
      3  begin   
      4    myProc(v);   
      5  end;   
      6  / 
      Employee No:6666 Name:prithwi 
      Employee No:7777 Name:Avik
    If collection is working in 8 then you can implement that in your code.

    Regards.

    Satyaki De.
  • isotope
    isotope Member Posts: 748 Bronze Badge
    user6773 wrote:
    It is 8.1.6. Thanks,
    SQL>
    SQL>
    SQL> @ver
    
    BANNER
    ----------------------------------------------------------------
    Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
    PL/SQL Release 8.1.7.4.0 - Production
    CORE    8.1.7.0.0       Production
    TNS for Solaris: Version 8.1.7.4.0 - Production
    NLSRTL Version 3.4.1.0.0 - Production
    
    SQL>
    SQL> --
    SQL> create or replace package types as
      2    type cursor_type is ref cursor;
      3  end types;
      4  /
    
    Package created.
    
    SQL>
    SQL> CREATE OR REPLACE FUNCTION get_multi_price
      2   (
      3     P_STORE IN VARCHAR2,
      4     v_inStr IN VARCHAR2
      5   )
      6  RETURN types.cursor_type
      7  IS
      8      v_cursor types.cursor_type;
      9      V_PR_SH_CODE varchar2(1);
     10      V_SEQ number;
     11      MSG VARCHAR2(200) := NULL;
     12      v_sql VARCHAR2(4000) ;
     13      v_instr_rp VARCHAR2(200);
     14      -- blah, ...
     15  BEGIN
     16   /*
     17     blah, blah, blah, ...
     18   */
     19   V_PR_SH_CODE := 'X';
     20   V_SEQ := 1;
     21   --
     22   v_instr_rp := REPLACE(v_instr,chr(44),chr(39)||chr(44)||chr(39));
     23
     24  -- returns multiple rows
     25   v_sql := 'SELECT 100*rownum FROM all_objects PRI WHERE ''X'' = ' || '''' || V_PR_SH_CODE || '''' ||
     26            ' AND ''1'' = ' || '''' || V_SEQ || '''' ||
     27            ' AND ''p2'' in (' || '''' || v_instr_rp || '''' ||
     28            ') AND (''X'' NOT IN (''D'', ''SD'')) and rownum <= 3';
     29  -- dbms_output.put_line(v_sql);
     30   OPEN v_cursor FOR v_sql;
     31   RETURN v_cursor;
     32  exception
     33    WHEN NO_DATA_FOUND THEN
     34      DBMS_OUTPUT.PUT_LINE('There is no data for the STORE and PRODUCT_CODE combination provided.');
     35    WHEN OTHERS THEN
     36      MSG := SUBSTR(SQLERRM, 1, 70);
     37      DBMS_OUTPUT.PUT_LINE(MSG);
     38  END;
     39  /
    
    Function created.
    
    SQL>
    SQL> -- this doesn't work in SQL*Plus in 8.1.7
    SQL> select get_multi_price('0123','p1,p2,p3') as rslt from dual;
    select get_multi_price('0123','p1,p2,p3') as rslt from dual
           *
    ERROR at line 1:
    ORA-00902: invalid datatype
    
    
    SQL>
    SQL> -- so use this method in SQL*Plus
    SQL> var results refcursor
    SQL> exec :results := get_multi_price('0123','p1,p2,p3');
    
    PL/SQL procedure successfully completed.
    
    SQL> print results
    
    100*ROWNUM
    ----------
           100
           200
           300
    
    SQL>
    SQL>
    What's the hold up ?

    isotope
This discussion has been closed.