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" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED] 10/02/2003 04:49 AM
|
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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;
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).
