PL/SQL error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: PL/SQL error

  1. #1
    Join Date
    Aug 2000
    Posts
    87

    PL/SQL error

    Hi Guys,
    Could you pls correct me where i am wrong.The below procedure is used to delete large number of rows from a table.I am delete the rows based on date field now.Priviously it was working very well for non date columns.I am tired & i know there is just small correction.

    Below is the script.
    ====================
    create or replace procedure delete_commit
    ( p_statement in varchar2,
    p_commit_batch_size in number default 10000)
    is
    cid integer;
    changed_statement varchar2(2000);
    finished boolean;
    nofrows integer;
    lrowid rowid;
    rowcnt integer;
    errpsn integer;
    sqlfcd integer;
    errc integer;
    errm varchar2(2000);
    begin
    /* If the actual statement contains a WHERE clause, then append a
    rownum < n clause after that using AND, else use WHERE
    rownum < n clause */
    if ( upper(p_statement) like '% WHERE %') then
    changed_statement := p_statement||' AND rownum < ' ||to_char(p_commit_batch_size + 1);
    else
    changed_statement := p_statement||' WHERE rownum < '||to_char(p_commit_batch_size + 1);
    end if;
    begin
    cid := dbms_sql.open_cursor; -- Open a cursor for the task
    dbms_sql.parse(cid,changed_statement, dbms_sql.native);
    -- parse the cursor. Pleae note that in Oracle 7.2.2
    -- parsing does a execute too. But that does not
    -- pose a problem here as we want that.
    rowcnt := dbms_sql.last_row_count;
    -- store for some future reporting
    exception
    when others then
    errpsn := dbms_sql.last_error_position;
    -- gives the error position in the changed sql
    -- delete statement if anything happens
    sqlfcd := dbms_sql.last_sql_function_code;
    -- function code can be found in the OCI manual
    lrowid := dbms_sql.last_row_id;
    -- store all these values for error reporting. However
    -- all these are really useful in a stand-alone proc
    -- execution for dbms_output to be successful, not
    -- possible when called from a form or front-end tool.
    errc := SQLCODE;
    errm := SQLERRM;
    dbms_output.put_line('Error '||to_char(errc)||
    ' Posn '||to_char(errpsn)||
    ' SQL fCode '||to_char(sqlfcd)||
    ' rowid '||rowidtochar(lrowid));
    raise_application_error(-20000,errm);
    -- this will ensure the display of atleast the error
    -- message if someething happens, even in a frontend
    -- tool.
    end;
    finished := FALSE;
    while not (finished)
    loop -- keep on executing the cursor till there is no more to process.
    begin
    nofrows := dbms_sql.execute(cid);
    rowcnt := dbms_sql.last_row_count;
    exception
    when others then
    errpsn := dbms_sql.last_error_position;
    sqlfcd := dbms_sql.last_sql_function_code;
    lrowid := dbms_sql.last_row_id;
    errc := SQLCODE;
    errm := SQLERRM;
    dbms_output.put_line('Error '||to_char(errc)||
    ' Posn '||to_char(errpsn)||
    ' SQL fCode '||to_char(sqlfcd)||
    ' rowid '||rowidtochar(lrowid));
    raise_application_error(-20000,errm);
    end;
    if nofrows = 0 then
    finished := TRUE;
    else
    finished := FALSE;
    end if;
    commit;
    end loop;
    begin
    dbms_sql.close_cursor(cid);
    -- close the cursor for a clean finish
    exception
    when others then
    errpsn := dbms_sql.last_error_position;
    sqlfcd := dbms_sql.last_sql_function_code;
    lrowid := dbms_sql.last_row_id;
    errc := SQLCODE;
    errm := SQLERRM;
    dbms_output.put_line('Error '||to_char(errc)||
    ' Posn '||to_char(errpsn)||
    ' SQL fCode '||to_char(sqlfcd)||
    ' rowid '||rowidtochar(lrowid));
    raise_application_error(-20000,errm);
    end;
    end;
    /
    USAGE :
    execute delete_commit('delete mytable where mycol = ''AAAA''',500)

    Now the probs when i use like this:
    execute delete_commit('delete mytable where date_log <= ''TO_DATE('31-DEC-2002','dd-mon-yyyy')''',1000)

    Can you guys fig where am i wrong??

    Thanks in advance.
    Cheers,
    Vinod

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Code:
    execute delete_commit('delete from mytable where date_log <= TO_DATE(''31-DEC-2002'',''dd-mon-yyyy'')',1000)
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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