Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Help with procedure.

RE: Help with procedure.

From: Paulo Gomes <PGomes_at_Datinfor.pt>
Date: Thu, 02 Oct 2003 04:27:38 -0800
Message-ID: <F001.005D1CF9.20031002042738@fatcity.com>


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;

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: 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US