Select sysdate from dual into variable or rebluid the procedure to have in variable and give the date in the execute command of the procedure.
-----Mensagem original----- De: Malik, Fawzia [mailto:[EMAIL PROTECTED] Enviada: quinta-feira, 2 de Outubro de 2003 12:50 Para: Multiple recipients of list ORACLE-L Assunto: Help with procedure. Hi Guys, I have the following delete-commit procedure (see below). The way it is called is as follows : exec delete_commit('delete from xyz where timestamp <= to_date(''24/05/03'',''dd/mm/yy'')',1000) Is there a way I can pass the date (timestamp)as a variable so that the script doesnt have to be physically modified each time this procedure is executed?? Any help/advice would be greatly appreciated!!! Rgds Fawzia Procedure: 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; / ********************************************************************** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ********************************************************************** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Malik, Fawzia INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paulo Gomes INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
