Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    17

    Unanswered: how to create a dynamic cursor like this??!!

    I want to create a cursor using a string,the code as below.
    but it can't compiled successfully.who can tell me the solution.
    thanks in advance!!

    StringSql := '
    select
    Right.SEQ_NUM,
    Right.PAY_SYS_CD,
    HD.TORI_CD
    from
    ' || HdTableName || ' HD,
    ' || RightTableName || ' Right
    where Right.DENPYO_NUM = DenpyoNum
    and HD.DENPYO_NUM = Right.DENPYO_NUM
    ';

    declare cursor CUR_PAYINTERNALCODE(DenpyoNum in AATCHANGEHD.DENPYO_NUM%TYPE)
    is
    StringSql;--error occur here

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You will need to use a REF CURSOR. In 9i:
    Code:
    DECLARE
      rc SYS_REFCURSOR;
      StringSQL varchar2(1000);
      l_Denpyo_Num integer := 12345;
    BEGIN
      StringSql := '
    select
    Right.SEQ_NUM,
    Right.PAY_SYS_CD,
    HD.TORI_CD
    from 
    ' || HdTableName || ' HD,
    ' || RightTableName || ' Right 
    where Right.DENPYO_NUM = :DenpyoNum
    and HD.DENPYO_NUM = Right.DENPYO_NUM
    ';
    
      open rc for StringSql using l_DenpyoNum;
      ...
    In 8i there is no SYS_REFCURSOR type so you have to create your own like this:
    Code:
    DECLARE
      TYPE rc_type IS REF CURSOR;
      rc rc_type;
    ..

  3. #3
    Join Date
    Sep 2003
    Posts
    17

    Wink

    thanks for your greate help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •