14 Replies Latest reply: Feb 18, 2013 12:33 PM by bencol RSS

    Error in dynamic cursor::

    983563
      hi experts

      please help me for this error
      In this code the cursor conditions are not appending to the query
      so when i am executing the result it is displaying the error
      can you help me on this
      SQL> CREATE OR REPLACE PROCEDURE test_dynamic_detailed_rpt (
        2     v_typ             IN       VARCHAR2,
        3     v_initiator   IN       VARCHAR2,
        4     p_rc                  OUT      sys_refcursor
        5  )
        6  IS
        7     v_sql   VARCHAR2(32767);
        8  BEGIN
        9     v_sql := 'SELECT  COUNT(*) FROM  txnlg tl WHERE ';
       10
       11      v_sql := v_sql||'tl.typ = ''1''';
       12
       13
       14           FOR j IN (SELECT tc.tgid, tc.tranid, tc.reqtype
       15                       FROM txnccde tc
       16                      WHERE tc.actstatus = 'Y'
       17                        AND tc.txndesc = v_typ)
       18           LOOP
       19              IF LENGTH (j.tgid) < 4
       20              THEN
       21                 v_sql := v_sql || ' OR ''UP''||J.tgid';
       22                 --'''|| v_typ|| ''')';
       23              ELSE
       24                 v_sql := v_sql || ' OR   J.tgid';
       25              END IF;
       26           END LOOP;
       27
       28     DBMS_OUTPUT.PUT_LINE(v_sql);
       29  END;
       30  /
      
      Procedure created.
      
      SQL>
      SQL> set serveroutput on;
      SQL>
      SQL> Var  p_rc refcursor;
      SQL>
      SQL> BEGIN
        2  test_dynamic_detailed_rpt('ALL','C',:p_rc);
        3  END;
        4  /
      SELECT  COUNT(*) FROM  txnlg tl WHERE tl.typ = '1'
      
      PL/SQL procedure successfully completed.
      
      SQL>
      SQL> print p_rc;
      ERROR:
      ORA-24338: statement handle not executed
      
      
      SP2-0625: Error printing variable "p_rc"
      SQL>
        • 1. Re: Error in dynamic cursor::
          ranit B
          FOR j IN (SELECT tc.tgid, tc.tranid, tc.reqtype
          FROM txnccde tc
          WHERE tc.actstatus = 'Y'
          AND tc.txndesc = v_typ)
          Is the cursor query proper? I mean does it give any row as o/p with ur inputs

          Please check the o/p of this query with inputs tc.txndesc = 'ALL' coz if this gives no result then the loop will not run even once.

          Let us know the output.
          • 2. Re: Error in dynamic cursor::
            Manguilibe KAO
            Hi,

            In your procedure, just add this, after the line where you wrote END LOOP;
             open p_rc for v_sql;
            and it should now work ( you shoud now see the result of the query associated to v_sql)
            • 3. Re: Error in dynamic cursor::
              983563
              But it is constructing the query like this .
              It is not appending the cursor result is directly taking the cursor and appending to it
              how can i avoid this


              result :
              SELECT  COUNT(*) FROM  txnlg tl WHERE tl.txntype = '1' OR 'AP'||J.tgid
              
              OR 'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR
              'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR
              'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR
              'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR
              'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR
              'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR
              'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR
              'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR
              'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR
              'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR
              'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR
              'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR
              'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR
              'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR
              'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR
              'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid OR
              'AP'||J.tgid OR 'AP'||J.tgid OR 'AP'||J.tgid
              BEGIN
              *
              ERROR at line 1:
              ORA-00920: invalid relational operator
              ORA-06512: at "MPAYBOB.TEST_DYNAMIC_DETAILED_RPT", line 29
              ORA-06512: at line 2
              • 4. Re: Error in dynamic cursor::
                rp0428
                >
                But it is constructing the query like this .
                It is not appending the cursor result is directly taking the cursor and appending to it
                >
                That is what happens when you start writing code before you define the requirements.

                Do NOT try to automate what you cannot do manually.

                The first step in constructing queries dynamically is to create a query manually to use as a template for what you want to create dynamically.

                Since you never did that we have no idea what the query should even look like when you get it built.

                Post the actual query that should be created. Then we can help you figure out how to create it dynamically.
                • 5. Re: Error in dynamic cursor::
                  sb92075
                  in other words, first make work then make it fancy.
                  • 6. Re: Error in dynamic cursor::
                    BluShadow
                    980560 wrote:
                    But it is constructing the query like this .
                    It is not appending the cursor result is directly taking the cursor and appending to it
                    how can i avoid this
                    Your cursor doesn't store any "result". cursors are not result sets.
                    All you're doing in your code is building up an SQL string and then (now you've opened the cursor) you're passing that SQL string to the SQL engine to be processed as a cursor. That parses the query and would then execute it, waiting for you to fetch the data back through the cursor variable, however you're not even getting that far, because the SQL statement itself fails to be parsed correctly as it is syntactically incorrect.

                    As others have said, if there really is a need for dynamic SQL, then you would first write the SQL itself so you know you have a working SQL statement, and then you would convert that into a dynamically generated string.
                    However, I very much doubt you can justify the need for dynamic SQL anyway. Perhaps if you posted some examples of your tables and data, and explained what output you are trying to get, we could help you to write it as a static SQL instead.
                    • 7. Re: Error in dynamic cursor::
                      983563
                      I have coded that cursor . but the following error is occuring while
                      executing the cursor .please help me to resolve this .



                      CREATE OR REPLACE PROCEDURE test_dynamic_detailed_rpt (
                      v_typ IN VARCHAR2,
                      v_initiator IN VARCHAR2,
                      p_rc OUT sys_refcursor
                      )
                      IS
                      v_sql VARCHAR2 (32767);
                      BEGIN
                      v_sql := 'SELECT COUNT(*) FROM txnlg tl WHERE ';
                      v_sql := v_sql || 'tl.typ = ''1''';

                      FOR j IN (SELECT tc.tgid, tc.tranid, tc.reqtype
                      FROM txnccde tc
                      WHERE tc.actstatus = 'Y')
                      LOOP
                      IF LENGTH (j.tagid) < 4
                      THEN
                      v_sql :=
                      v_sql || ' OR ' || 'tl.txntype =' ||''''|| 'AP' ||j.tgid
                      || '''';
                      ELSE
                      v_sql := v_sql || 'OR ' || 'tl.txntype =' ||''''||j.tgid||'''';
                      END IF;
                      END LOOP;

                      DBMS_OUTPUT.put_line (v_sql);

                      OPEN p_rc FOR v_sql;
                      END;
                      /

                      Procedure created.

                      SQL>
                      SQL> set serveroutput on;
                      SQL>
                      SQL> Var p_rc refcursor;
                      SQL>
                      SQL> BEGIN
                      2 test_dynamic_detailed_rpt('ALL','C',:p_rc);
                      3 END;
                      4 /
                      ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                      ORA-06512: at P_DETAILED_REPRT, line 226
                      ORA-06512: at line 2
                      • 8. Re: Error in dynamic cursor::
                        BluShadow
                        980560 wrote:
                        I have coded that cursor . but the following error is occuring while
                        executing the cursor .please help me to resolve this .



                        CREATE OR REPLACE PROCEDURE test_dynamic_detailed_rpt (
                        v_typ IN VARCHAR2,
                        v_initiator IN VARCHAR2,
                        p_rc OUT sys_refcursor
                        )
                        IS
                        v_sql VARCHAR2 (32767);
                        BEGIN
                        v_sql := 'SELECT COUNT(*) FROM txnlg tl WHERE ';
                        v_sql := v_sql || 'tl.typ = ''1''';

                        FOR j IN (SELECT tc.tgid, tc.tranid, tc.reqtype
                        FROM txnccde tc
                        WHERE tc.actstatus = 'Y')
                        LOOP
                        IF LENGTH (j.tagid) < 4
                        THEN
                        v_sql :=
                        v_sql || ' OR ' || 'tl.txntype =' ||''''|| 'AP' ||j.tgid
                        || '''';
                        ELSE
                        v_sql := v_sql || 'OR ' || 'tl.txntype =' ||''''||j.tgid||'''';
                        END IF;
                        END LOOP;

                        DBMS_OUTPUT.put_line (v_sql);

                        OPEN p_rc FOR v_sql;
                        END;
                        /

                        Procedure created.

                        SQL>
                        SQL> set serveroutput on;
                        SQL>
                        SQL> Var p_rc refcursor;
                        SQL>
                        SQL> BEGIN
                        2 test_dynamic_detailed_rpt('ALL','C',:p_rc);
                        3 END;
                        4 /
                        ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                        ORA-06512: at P_DETAILED_REPRT, line 226
                        ORA-06512: at line 2
                        That error does not relate to the code you've posted. P_DETAILED_REPORT is not the name of your procedure.

                        Please post the error you get from running the code shown. I suspect you are trying to build an SQL statement that is too large for the v_sql variable, but we don't have your data to be able to tell.

                        I'll repeat what I said earlier:
                        Perhaps if you posted some examples of your tables and data, and explained what output you are trying to get, we could help you to write it as a static SQL instead.
                        Read this: {message:id=9360002}

                        You'll find that the people who get the quickest answers around here are the ones who ask their questions properly with sufficient information and example data. If you keep people guessing what you want, then they often tend to get bored and go elsewhere.... so the choice is yours... help us to help you.
                        • 9. Re: Error in dynamic cursor::
                          bencol
                          It looks like you want to join txnlg to txncode, using two separate join conditions, so why not something like the following untested query:
                          SELECT COUNT(*)
                          FROM   txnlg tl
                          JOIN   txncode tc
                            ON   (tl.txntype = 'AP'||tc.tgid
                               AND length(tc.jagid) <4
                                 )
                              OR (tl.txntype = tc.tgid
                               AND (length(tc.jagid) >=4 OR tc.jagid IS NULL)
                                 )
                          WHERE  tl.Typ = '1'
                          AND    tc.Status = 'Y'
                          --AND    tc.txndesc = v_typ do you want this? it was in you first example
                          /
                          Dynamic SQL not required.

                          If you provided create table and insert statements, then I and other posters would be able to test our suggestions
                          • 10. Re: Error in dynamic cursor::
                            983563
                            Sry, I am new for the oracle .I am learing so many things from blueshadow
                            If i do any wrong means please excuse me .please help me for this procedure
                            as i want this one to implement this procedure dynamically please
                            help me to implent in dynamic query fashion .
                            suggest me where i am going wrong.

                            1)As this is going wrong can i implement this one in the CLOB replacing with the varchar2 .
                            • 11. Re: Error in dynamic cursor::
                              bencol
                              980560 wrote:
                              ... as i want this one to implement this procedure dynamically ...
                              Why? Which one of the criteria here, http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS011 under "When You Need Dynamic SQL" Does your problem meet? From what I understand about your code you are generating a huge SQL statement in order to return a single integer, COUNT(*), and maintaining a variable holding the query is causing problems for you.

                              You know the SQL text at compile time, the tables accessed and joins used are not dependent on the input parameters, so use static SQL if possible and store it only in you package.

                              There are many reasons to use dynamic SQL, "because I want to" doesn't usually count :)

                              If my supplied query is wrong, please tell us all why. It will help if you provide:

                              Create table txnlg
                              (<All (and no more) columns needed to test your requirements>
                              /
                              Create table txntype
                              (<All (and no more) columns needed to test your requirements>
                              /

                              Insert statements for txnlg and txncode enough rows (but not more) to fully test your requirements.

                              Required rows from txnlg and txncode for each set of input parameters that you want to count.

                              Help us help you,

                              Ben
                              • 12. Re: Error in dynamic cursor::
                                983563
                                Hi Bencol,

                                First Thanks for replying me .Here for checking the result In testing environment
                                i want using count(*) in generally i will use select * from txnlog table based up on the
                                given inputs i have to append the conditions and get the reporting result.So here i am
                                using the dynamic procedure.Here what i have supplied to you is a sample procedure.
                                In general i will add so many conditions dynamically to it .So please suggest me how to
                                avoid ths error in the above proceudure.

                                Here according to blueshadow i am constructing huge sql in v_sql so that i am getting this error how can i avoid this .Can i use CLOB for it.Please instruct me.Here the procedure name which i have used is different but the logic is same .I have posted the sample logic

                                ORA-06502: PL/SQL: numeric or value error: character string buffer too small

                                Edited by: 980560 on Feb 18, 2013 9:31 AM

                                Edited by: 980560 on Feb 18, 2013 9:35 AM

                                Edited by: 980560 on Feb 18, 2013 9:43 AM

                                Edited by: 980560 on Feb 18, 2013 9:59 AM
                                • 13. Re: Error in dynamic cursor::
                                  sb92075
                                  How do I ask a question on the forums?
                                  SQL and PL/SQL FAQ


                                  which line is #226?
                                  • 14. Re: Error in dynamic cursor::
                                    bencol
                                    If you think you must use dynamic SQL to apply further conditions (though you have not made this clear) do not so it like this you code looks like it is generating:
                                    select count(*)
                                    from tab1
                                    where col1 = (1st value from tab2)
                                    or col1 = (2nd value from tab2)
                                    .
                                    .
                                    .
                                    or col1 = (nth value from tab2)
                                    /
                                    whereas I'd say you are better off doing:
                                    select count(*)
                                    from tab1
                                    , tab2
                                    where (tab1.col1 = tab2.col1 and length tab2.col2 <4)
                                      or (tab1.col1 = 'xx'||tab2.col1 and length tab2.col2 >=4)
                                    /
                                    As the second query is only this long, not n (plus a few) lines long. It doens't matter if you are writing static or dynamic SQL: You need to write a proper, concise SQL query and not a massive, sprawling one. Then you will no have to worry about the limitation of the VARCHAR2 data type holding the full text of of your query.

                                    If after writing a nice concise sql query and it is still >32k long, then you might want to look at using the VARCHAR2S data type and DBMS_SQL.TO_REFCURSOR

                                    Example tables and data and required results will make usefule advice much easier to give.

                                    Ben