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

Thread: DBMS_SQL in Reports

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi there,

    I want to truncate the table in Pre-report trigger at Report level.

    I used DBMS_SQL package for parsing and executing Truncate command.

    But, it gives error at dbms_sql.native parameter.

    Following is the code.

    PROCEDURE ode_truncate_table IS

    CURSOR_NAME INTEGER;
    ROWS_PROCESSED INTEGER;
    stmt VARCHAR2(100);

    begin

    CURSOR_NAME:=DBMS_SQL.OPEN_CURSOR;

    DBMS_SQL.PARSE(CURSOR_NAME,'TRUNCATE TABLE xyz' ,DBMS_SQL.NATIVE);

    ROWS_PROCESSED:=DBMS_SQL.EXECUTE(CURSOR_NAME);

    DBMS_SQL.close_cursor(CURSOR_NAME);

    exception

    WHEN OTHERS THEN

    DBMS_SQL.CLOSE_CURSOR(CURSOR_NAME);

    end;

    If I run this procedure from the Reports 2.5, it does not recognize the dbms_sql.native in dbms_sql.parse statement. But, if I create the stored procedure at database level, it works fine. I want to use it in Reports.

    Any idea/Fix?

    Thanks in Adv.

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Try :

    CURSOR_NAME:=DBMS_SQL.OPEN_CURSOR;

    DBMS_SQL.PARSE(CURSOR_NAME,'TRUNCATE TABLE xyz' ,1);

    ROWS_PROCESSED:=DBMS_SQL.EXECUTE(CURSOR_NAME);

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