| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Help with procedure.
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:Fawzia.Malik_at_bskyb.com]
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;
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Malik, Fawzia INET: Fawzia.Malik_at_bskyb.com 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: ListGuru_at_fatcity.com (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: PGomes_at_Datinfor.pt 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: ListGuru_at_fatcity.com (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).Received on Thu Oct 02 2003 - 07:27:38 CDT
 |
 |