This discussion is archived
14 Replies Latest reply: Feb 18, 2013 10:33 AM by bencol RSS

Error in dynamic cursor::

983563 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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::
    ManguilibeKAO Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    in other words, first make work then make it fancy.
  • 6. Re: Error in dynamic cursor::
    BluShadow Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points