Home » SQL & PL/SQL » SQL & PL/SQL » Declaring Variable in Basic PL/SQL...
icon6.gif  Declaring Variable in Basic PL/SQL... [message #392982] Thu, 19 March 2009 21:34 Go to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Hi.

I have this script..
declare
cursor c_updt_tabl
is
select acct_num from cpc.account@CPC.link where not exists
( select account_num from customer_accounts where
account_num=acct_num);
begin

For r_updt_tabl in c_updt_tabl LOOP

begin
insert into customer_accounts (ACCOUNT_NUM , ACCOUNT_NAME , ACCOUNT_STATUS , ACCOUNT_CATEGORY , ACCOUNT_TYPE ,
ACTIVE_DATE , CLOSED_DATE , CREATION_DATE , BILL_PERIOD , PRICING_PLAN_IND , FLOOR_LEVEL_CODE , APARTMENT_NUM, 
LOT_NUM , STREET_NUM_CODE , PO_BOX_NUM , BUILDING_NAME , STREET_TYPE , STREET_NAME ,
POSTAL_CODE , SECTION_NAME , CITY_NAME , STATE_NAME , COUNTRY_NAME , STATE_CODE ,
COUNTRY_CODE , CUSTOMER_ID , COST_CENTER_CODE , PAYMENT_MODE , external_cust_id , external_system , AE_AM,
TIMESTAMP )
  select /*+ index(cpc.account) index(cpc.acct_frmtaddr_link) index(cpc.address) index(cpc.building) index(cpc.street_code)
   index(cpc.addr_section) index(cpc.city) index(cpc.state) */ account.acct_num, account.acct_name, account.acct_status_code,
   account.acct_cat_code, account.acct_type_code, account.start_date, account.end_date, account.create_date, account.bill_prd_code,
   account.pp_ind, address.flr_lvl_code, address.apt_num, address.hse_lot_num, address.street_num , address.po_box_num, building.bldg_name,
   street_code.street_type, street_code.street_name, address.postal_code, addr_section.addr_section_short_desc, city.city_short_desc,
   state.state_short_desc, country.country_short_name, state.state_code, address.country_code, account.univers_party_id, account.cost_ctr_code,
   account.payment_mode, account.extrn_party_id, account.extrn_syst_id, account.AE_AM, account.DATESTAMP
   from 
cpc.account@CPC.link, cpc.acct_frmtaddr_link@CPC.link, cpc.address@CPC.link, cpc.building@CPC.link, cpc.street_code@CPC.link,
cpc.addr_section@CPC.link, cpc.city@CPC.link, cpc.state@CPC.link, cpc.country@CPC.link
   where 
account.acct_num = acct_frmtaddr_link.acct_num(+) and account.acct_num = r_updt_tabl.acct_num and 
acct_frmtaddr_link.addr_seq_num = address.addr_seq_num(+) and address.bldg_code = building.bldg_code(+) and 
address.street_code = street_code.street_code(+) and address.addr_section_code = addr_section.addr_section_code(+) and 
address.city_id = city.city_id(+) and address.state_id = state.state_id(+) and address.country_code = country.country_code(+) ;

 exception
  when OTHERS then
   NULL;
   end;
   
end loop;
commit;    

end;
/

which I want to extend to include these 4 variables:

proc_name VARCHAR2(36) := 'cust_prof.sql';
table_name VARCHAR2(36) := 'customer_profile';
start_time date:= sysdate;
v_ErrorCode number;
v_ErrorText varchar2(200);


so that I could enable the script tracking log by this at the end of the script:
insert into PROCEDURE_TRACKING_LOG
(procedure_name, table_name, rows_inserted, rows_updated, rows_deleted, rows_inserted_of_deletion, rows_inserted_after_deletion, 
rows_rejected, start_time, end_time, elapse_time, remarks, subject_area)
values
(proc_name,table_name,row_insert,NULL,NULL,NULL,NULL,NULL,start_time,sysdate,(sysdate-start_time)* 86400,
v_ErrorText,'CUSTOMER PROFILE');


But I just don't know how to declare those variables using that script. Sad

Thank you very much.

[Updated on: Thu, 19 March 2009 21:36]

Report message to a moderator

Re: Declaring Variable in Basic PL/SQL... [message #392983 is a reply to message #392982] Thu, 19 March 2009 21:58 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm


Re: Declaring Variable in Basic PL/SQL... [message #393013 is a reply to message #392983] Fri, 20 March 2009 00:34 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
exception
  when OTHERS then
   NULL;
   end;



Why do you have such a line in your code?
Re: Declaring Variable in Basic PL/SQL... [message #393046 is a reply to message #393013] Fri, 20 March 2009 02:28 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
bonker wrote on Fri, 20 March 2009 13:34
exception
  when OTHERS then
   NULL;
   end;



Why do you have such a line in your code?

I don't know.

It's my senior's coding actually.

The script is working fine at the moment.

I've tried declaring the variables using the normal stored PL/SQL convention but it failed.

Thank you.
Re: Declaring Variable in Basic PL/SQL... [message #393053 is a reply to message #393046] Fri, 20 March 2009 02:54 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
The script is working fine at the moment.


It just appears to be working fine because if there were any error occured while inserting it is just silently ignoring it because of when others then null condition.

SCOTT@XE>create table t(x int,constraint check_x_not_zero check(x > 0));

SCOTT@XE>insert into t values(0);
insert into t values(0)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHECK_X_NOT_ZERO) violated

SCOTT@XE>select * from t;

no rows selected

SCOTT@XE>declare
  2  cursor c1 is select 1 r from dual union all select 0 from dual;
  3  begin
  4  for each in c1
  5  loop
  6     begin
  7      insert into t values(each.r);
  8    exception when others then null;
  9    end;
 10  end loop;
 11  commit;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SCOTT@XE>select * from t;

         X
----------
         1

1 row selected.

SCOTT@XE>truncate table t;

Table truncated.

SCOTT@XE> declare
  2    cursor c1 is select 1 r from dual union all select 0 from dual;
  3    begin
  4    for each in c1
  5    loop
  6      -- begin
  7        insert into t values(each.r);
  8    --  exception when others then null;
  9    --  end;
 10    end loop;
 11    commit;
 12    end;
 13  /
 declare
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHECK_X_NOT_ZERO) violated
ORA-06512: at line 7

SCOTT@XE>select * from t;

no rows selected

SCOTT@XE>




I hope you could understand the difference now

Updated: I know that I have not answered your problem but thought of pointing out the current issues in the script itself

[Updated on: Fri, 20 March 2009 02:59]

Report message to a moderator

Re: Declaring Variable in Basic PL/SQL... [message #393056 is a reply to message #393053] Fri, 20 March 2009 03:21 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Ok thanks.

It's clear.

I guess that's the objective i.e. to ignore all erros which is most possibly because of the primary keys, check constraint and so on.

Am I rite?

So, how do I declare the variables? Cool
Re: Declaring Variable in Basic PL/SQL... [message #393060 is a reply to message #393056] Fri, 20 March 2009 03:41 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Put variables into the DECLARE section.
Re: Declaring Variable in Basic PL/SQL... [message #393077 is a reply to message #393056] Fri, 20 March 2009 04:50 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
aimy wrote on Fri, 20 March 2009 08:21
I guess that's the objective i.e. to ignore all erros which is most possibly because of the primary keys, check constraint and so on.

Am I rite?



It'll ignore those.
It'll also ignore errors caused by:
Running out of tablespace.
Running out of undo.
The DB link being down.
Somebody writing a trigger on the table to stop you inserting.
Database corruption.
meteor strike.

Just lose it - one day it'll fail to insert data and you won't know why.
Re: Declaring Variable in Basic PL/SQL... [message #393079 is a reply to message #393077] Fri, 20 March 2009 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
one day it'll fail to insert data and you won't know why.

You even won't know it failed. Good luck!

Regards
Michel
Re: Declaring Variable in Basic PL/SQL... [message #393152 is a reply to message #392982] Fri, 20 March 2009 09:36 Go to previous message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
>but it failed.
Not a valid Oracle error message!

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Previous Topic: queston on view (merged)
Next Topic: Coalesce multiple columns (merged)
Goto Forum:
  


Current Time: Fri Dec 09 11:44:01 CST 2016

Total time taken to generate the page: 0.08472 seconds