DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Error handling with 'OPEN FOR'

  1. #1
    Join Date
    Jan 2001
    Posts
    63

    Error handling with 'OPEN FOR'

    I am trying to add some error handling to this packaged procedure written by someone else. How can I check for an Oracle error in the query in m_sql_string? Or how can I check the results for an error so I can write the error to an error log table?

    CREATE OR REPLACE PACAKGE P1
    AS

    TYPE CHCUR IS REF CURSOR;
    PROCEDURE QRY_WR_STATUS_CHANGES (tsAfter IN VARCHAR2, rsResult OUT CHCUR);
    END P1;
    /

    CREATE OR REPLACE PACAKGE BODY P1
    AS

    PROCEDURE QRY_WR_STATUS_CHANGES(tsAfter IN VARCHAR2, rsResult OUT CHCUR)
    IS
    m_sql_string VARCHAR2(30000);
    BEGIN
    m_sql_string := 'SELECT TS_STATUS, CD_STATUS, CD_WR, RowId
    FROM TABLE_A
    WHERE
    (
    NOT EXISTS (SELECT ''X'' FROM TABLE_B where TABLE_B.USER_NAME =TABLE_A.ID_OPER)
    ) AND
    (
    NOT EXISTS (SELECT ''X'' FROM TABLE_C where TABLE_C.wr = TABLE_A.CD_WR and
    TABLE_C.dist = TABLE_A.CD_DIST)
    )
    AND
    TABLE_A.TS_STATUS >
    TO_DATE('''||tsAfter||''', '||'''MM/DD/YYYY HH24:MI:SS'')
    AND CD_STATUS Like ''%X''';
    OPEN rsResult FOR m_sql_string;
    END QRY_WR_STATUS_CHANGES;

    END P1;
    /

    Thanks in advance.

  2. #2
    Join Date
    Feb 2001
    Posts
    180
    Shouldn't it be something like:
    PROCEDURE QRY_WR_STATUS_CHANGES
    (tsAfter IN VARCHAR2, rsResult OUT CHCUR)
    IS
    ...
    my_error varchar2(2000);
    begin
    ...
    exception
    when others
    then
    my_error := sqlerrm;
    rollback;
    insert into your_log_table
    (message
    ,log_date
    ,prog_source
    )
    values
    (my_error
    ,sysdate
    ,'QRY_WR_STATUS_CHANGES'
    )
    ;
    commit;
    raise;
    end;
    Regards
    Ben de Boer

  3. #3
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    hi,
    use:
    CREATE OR REPLACE PACKAGE P1
    and not
    CREATE OR REPLACE PACAKGE P1

    Cheers,
    R.
    It is better to ask and appear ignorant, than to remain silent and remain ignorant.

    Oracle OCP DBA 9i,
    C++, Java developer

  4. #4
    Join Date
    Jan 2001
    Posts
    63
    Thanks Bensr for your suggestion (and also for over-looking the typo). If I restructure it that way, will I be able to SELECT INTO the cursor variable rsResult, so that the results will be returned in that out parameter as they are now?
    I was hoping to add error checking without restructuring, but couldn't figure out how.

  5. #5
    Join Date
    Feb 2001
    Posts
    180
    I don't understand what you would like to return.
    If it's the cursor var then maybe:
    exception
    when others
    then
    my_error := sqlerrm;
    rollback;
    insert into your_log_table
    (message
    ,log_date
    ,prog_source
    )
    values
    (my_error
    ,sysdate
    ,'QRY_WR_STATUS_CHANGES'
    )
    ;
    commit;
    rsResult := null;
    end;

    or do you want to return the query results?
    Regards
    Ben de Boer

  6. #6
    Join Date
    Jan 2001
    Posts
    63
    Yes, I want to return the query results, but also check those results to see if there is an error.
    Unfortunatley, the WHEN OTHERS exception is not trapping errors that are being returned in the query.

Posting Permissions

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


Click Here to Expand Forum to Full Width