| 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.
Here's one way:
define delete_date_format = 'dd/mm/yy'
col old_nls_date_format noprint new_value old_nls_date_format
select value old_nls_date_format
from nls_session_parameters
where parameter = 'NLS_DATE_FORMAT'
/
alter session set nls_date_format = '&&delete_date_format';
accept delete_date date prompt "Please Enter the Delete-To Date in DD/MM/YY Format:"
begin
delete_commit('delete from xyz where timestamp <=
to_date(''&&delete_date'',''&&delete_date_format'')',1000);
end;
/
alter session set nls_date_format = '&&old_nls_date_format';
HTH Jared
"Malik, Fawzia" <Fawzia.Malik_at_bskyb.com>
Sent by: ml-errors_at_fatcity.com
10/02/2003 04:49 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: 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: INET: Jared.Still_at_radisys.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).Received on Thu Oct 02 2003 - 13:04:29 CDT
 |
 |