1 2 3 Previous Next 30 Replies Latest reply on Jan 5, 2009 6:53 PM by Satyaki_De Go to original post
      • 15. Re: ora-00902 invalid data type
        247890
        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;
        /
        • 16. Re: ora-00902 invalid data type
          Satyaki_De
          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.
          • 17. Re: ora-00902 invalid data type
            Satyaki_De
            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.
            • 18. Re: ora-00902 invalid data type
              isotope
              What's the version of your database ?
              select * from v$version;
              isotope
              • 19. Re: ora-00902 invalid data type
                247890
                It is 8.1.6. Thanks,
                • 20. Re: ora-00902 invalid data type
                  Satyaki_De
                  Oh! Goodness.

                  This is almost primitive DB.

                  Did you check my solution? Is it working?

                  Regards.

                  Satyaki De.
                  • 21. Re: ora-00902 invalid data type
                    247890
                    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,
                    • 22. Re: ora-00902 invalid data type
                      Satyaki_De
                      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.
                      • 23. Re: ora-00902 invalid data type
                        247890
                        :(

                        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.
                        • 24. Re: ora-00902 invalid data type
                          Satyaki_De
                          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.
                          • 25. Re: ora-00902 invalid data type
                            isotope
                            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
                            • 26. Re: ora-00902 invalid data type
                              Satyaki_De
                              Oh! dear.

                              I was completely overlooked it. It is a function and op is returning in form of ref cursor. ;)

                              Good point.

                              Regards.

                              Satyaki De.
                              • 27. Re: ora-00902 invalid data type
                                247890
                                It works from sqlplus this way. But, we need to call this function from java code. I am not sure if creating a variable and printing it would work from java call. Will it work?
                                • 28. Re: ora-00902 invalid data type
                                  isotope
                                  user6773 wrote:
                                  It works from sqlplus this way. But, we need to call this function from java code. I am not sure if creating a variable and printing it would work from java call. Will it work?
                                  Of course not, as has been mentioned by SomeoneElse earlier in this thread. You have been asking for a SQL*Plus solution since the get-go:
                                  user6773 wrote:
                                  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.
                                  and all the suggestions over here are for SQL*Plus.

                                  This is not a java forum. This is a SQL and PL/SQL forum. If you are expecting someone to post a Java/JDBC program to help you out, then you are barking up the wrong tree.

                                  You may want to check out the appropriate forum for a java solution.

                                  isotope
                                  • 29. Re: ora-00902 invalid data type
                                    Satyaki_De
                                    Here is another way to print the value from ref cursor without using the print statement and that might be helpful for you in your Java ->
                                    satyaki>
                                    satyaki>select * from v$version;
                                    
                                    BANNER
                                    ----------------------------------------------------------------
                                    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
                                    PL/SQL Release 10.2.0.3.0 - Production
                                    CORE    10.2.0.3.0      Production
                                    TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
                                    NLSRTL Version 10.2.0.3.0 - Production
                                    
                                    Elapsed: 00:00:01.56
                                    satyaki>
                                    satyaki>
                                    satyaki>create or replace procedure gen_cur(str in varchar2,rc in out sys_refcursor) 
                                      2  is   
                                      3    str1  varchar2(500); 
                                      4  begin   
                                      5    str1 := 'select empno,ename,hiredate,sal            
                                      6             from emp            
                                      7             where '||str;               
                                      8             
                                      9    open rc for str1;   
                                     10  exception   
                                     11    when others then     
                                     12      dbms_output.put_line(sqlerrm); 
                                     13  end; 
                                     14  /
                                    
                                    Procedure created.
                                    
                                    Elapsed: 00:00:01.18
                                    satyaki>
                                    satyaki>
                                    satyaki>declare 
                                      2   type a is record
                                      3     (                    
                                      4        empno    number(4),                    
                                      5        ename    varchar2(30),                    
                                      6        hiredate date,                    
                                      7        sal      number(10,2)                  
                                      8     );  
                                      9   rec a;    
                                     10   b sys_refcursor;  
                                     11   src varchar2(300); 
                                     12  begin   
                                     13    src:= 'sal between 2000 and 7000'; 
                                     14    gen_cur(src,b);   
                                     15    loop     
                                     16      fetch b into rec;      
                                     17       exit when b%notfound;            
                                     18         dbms_output.put_line('Employee No:'||rec.empno||' - '||                      
                                     19                              'Name:'||rec.ename||' - '||                           
                                     20                              'Hire Date:'||rec.hiredate||' - '||                     
                                     21                              'Salary:'||rec.sal);   
                                     22     end loop;   
                                     23     close b; 
                                     24  exception   
                                     25    when others then     
                                     26      dbms_output.put_line(sqlerrm); 
                                     27  end; 
                                     28  /
                                    Employee No:7566 - Name:JONES - Hire Date:02-APR-81 - Salary:2975
                                    Employee No:7698 - Name:BLAKE - Hire Date:01-MAY-81 - Salary:2850
                                    Employee No:7782 - Name:CLARK - Hire Date:09-JUN-81 - Salary:4450
                                    Employee No:7788 - Name:SCOTT - Hire Date:19-APR-87 - Salary:3000
                                    Employee No:7839 - Name:KING - Hire Date:17-NOV-81 - Salary:7000
                                    Employee No:7902 - Name:FORD - Hire Date:03-DEC-81 - Salary:3000
                                    
                                    PL/SQL procedure successfully completed.
                                    
                                    Elapsed: 00:00:01.02
                                    Regards.

                                    Satyaki De.