Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL suggestion (Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production)
PL/SQL suggestion [message #348286] Tue, 16 September 2008 06:09 Go to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi All,

Good day.

I have two tables it is different schema's, i have created the view for select access to one schema.

Now i have access both the tables in one schema one table one view.

below process i need to do. Could you please give me some input on this.


DECLARE

begin

truncate table mecube.ctdr026_lk_gsdb;

insert into  mecube.ctdr026_lk_gsdb
  (cd_gsdb,cd_type,na_gsdb,ad_street,ad_city,ad_state,ad_country,ad_zip,NO_LATITUDE,NO_LONGITUDE,ts_last_update)
  SELECT cd_supplr_site,'CMMS3',substr(na_site_name,1,25),substr(ad_sp_street,1,30),ad_sp_city,cd_sp_state,
  cd_sp_country_code,ad_sp_postal_code,
  'Inst','Inst',sysdate FROM mecube.tahp271_site
  WHERE cd_supplr_site not in (select distinct cd_gsdb from mecube.ctdr026_lk_gsdb);

 commit;           

exception
   when others then
       dbms_output.put_line (sqlerrm);
END;


*** above procedure just for example***

Setp 1: I need to truncate all the old data.
Setp 2: I need to insert into new details to truncated table.

While doing mannualy it is working fine but i need to fix this like weekly job. Automatically run throu UNIX cron job.

Let me know if you need any more details.

Thanx & Reds
Thangam.
Re: PL/SQL suggestion [message #348296 is a reply to message #348286] Tue, 16 September 2008 06:35 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So what is the question, actually? How to run a PL/SQL procedure weekly? If so, DBMS_JOB might be your choice.

Quote:
mannualy it is working fine
What is working fine? This script? I wouldn't say so:
SQL> begin
  2    truncate table test;
  3
  4    insert into test (col) values ('abc');
  5  end;
  6  /
  truncate table test;
           *
ERROR at line 2:
ORA-06550: line 2, column 12:
PLS-00103: Encountered the symbol "TABLE" when expecting one of the following:
:= . ( @ % ;
The symbol ":= was inserted before "TABLE" to continue.
i.e. you can't use DDL in PL/SQL block (other than using the EXECUTE IMMEDIATE):
SQL> begin
  2    execute immediate ('truncate table test');
  3
  4    insert into test (col) values ('abc');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>

Finally, get rid of that silly WHEN OTHERS exception handler as it does nothing.
Re: PL/SQL suggestion [message #348297 is a reply to message #348296] Tue, 16 September 2008 06:38 Go to previous messageGo to next message
monikabhakuni
Messages: 110
Registered: September 2008
Location: India
Senior Member
Hi

i am new to unix. can you tell me the steps that how can we set it on a weekly bases from unix
Re: PL/SQL suggestion [message #348298 is a reply to message #348296] Tue, 16 September 2008 06:39 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi Littlefoot,

I agree your point of view.

I told like manually in SQL * PULS.

i did one by one.

First setp Truncate and insert.

Let me know if you need any more details.

Thanks & Regards
Thangam.
Re: PL/SQL suggestion [message #348299 is a reply to message #348298] Tue, 16 September 2008 06:44 Go to previous messageGo to next message
monikabhakuni
Messages: 110
Registered: September 2008
Location: India
Senior Member
i want to know that how can we set the execution of a particular query from unix on weekly basis
Re: PL/SQL suggestion [message #348306 is a reply to message #348299] Tue, 16 September 2008 06:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Don't hijack other's topic
2/ Don't post non Oracle question
3/ If it is an Oracle question, post in the correct forum

Regards
Michel
Re: PL/SQL suggestion [message #348308 is a reply to message #348299] Tue, 16 September 2008 06:57 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi,

This is the way i am calling the SQL file from shell script.

Shell script File name: run_tdr102gsdb

  sqlplus -s $ORACLEID/$PASSWD << END
  set feedback off
  set verify off
  set pagesize 0
  set serveroutput on size 100000
  spool $LOGFILE_GSDB
  start $HOME/cubing/sql/cubing.sql
  spool off 
  exit


And this shell script i have fixed as a weekly job(CRON JOB).

50 17 * * 3   $HOME/cubing/batch/run_tdr102gsdb> /dev/null


let me know if you need any more details.

Thanks & Regards
Thangam.
Re: PL/SQL suggestion [message #348311 is a reply to message #348306] Tue, 16 September 2008 07:00 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi Michel,

Good day,

I don't want to anything about other then oracle. Just i need some suggestions about the delete and insert new data.

Between above mentiooned two tables.

Let me know if you need any more details.

Thanks & Regards
Thangam.
Re: PL/SQL suggestion [message #348314 is a reply to message #348311] Tue, 16 September 2008 07:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
iamdurai,

I was not replying to you but to monikabhakuni that is hijacking your topic.

Regards
Michel
Re: PL/SQL suggestion [message #348317 is a reply to message #348314] Tue, 16 September 2008 07:09 Go to previous messageGo to next message
monikabhakuni
Messages: 110
Registered: September 2008
Location: India
Senior Member
Hi Michel

Sorry i was not trying to hijak ny topic i jst asked a question out of curiousity as i was nt knowing nythng in unix and i m new to this field
Re: PL/SQL suggestion [message #348318 is a reply to message #348317] Tue, 16 September 2008 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You must not:
- post question is not Oracle related
- use IM speak

You should read OraFAQ Forum Guide and create your own topic when and where appropriate.

Regards
Michel
Re: PL/SQL suggestion [message #348407 is a reply to message #348318] Tue, 16 September 2008 11:46 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi Michel,

Could you please give me some tips on this.

Thanks & Regards
Thangam
Re: PL/SQL suggestion [message #348415 is a reply to message #348407] Tue, 16 September 2008 12:08 Go to previous messageGo to next message
gkbiswal
Messages: 5
Registered: August 2008
Location: Mumbai
Junior Member
What I understant from your post is that you requirement is get rid of rows where cd_supplr_site is equal to cd_gsdb.

Then in this case you can simply do this by a DELETE statement on mecube.ctdr026_lk_gsdb.

Regards,
GK


Re: PL/SQL suggestion [message #348425 is a reply to message #348407] Tue, 16 September 2008 13:35 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Please explain what is left unclear from Littlefoot's reply?

1/ Format a procedure with your requirement.
2/ Schedule the procedure using DBMS_JOB package.

For Scheduling From Oracle try the following links:

1/ http://www.oradev.com/dbms_scheduler.jsp
2/ http://download-uk.oracle.com/docs/cd/B28359_01/server.111/b28310/appendix_a004.htm
3/ http://www.orafaq.com/node/871 (For pre-10g releases)

If you insist in using a cron job try the following link:
http://forums11.itrc.hp.com/service/forums/questionanswer.do?threadId=947212&admit=109447626+1221589033785+28353475

By the way if you already do a truncate of your table mecube.ctdr026_lk_gsdb
iamdurai wrote on Tue, 16 September 2008 16:39

truncate table mecube.ctdr026_lk_gsdb;



what good is this condition in your insert statement? Confused
Quote:

WHERE cd_supplr_site not in (select distinct cd_gsdb from mecube.ctdr026_lk_gsdb);



Hope these helps. Smile

Regards,
Jo
Re: PL/SQL suggestion [message #348921 is a reply to message #348425] Thu, 18 September 2008 05:33 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi Jo,

I need delete all the rows weekly once also at the same time i need to insert new details from this table to mecube.ctdr026_lk_gsdb to mecube.ctdr026_lk_gsdb. Even i do not need to where condition also.

Delete / truncate table mecube.ctdr026_lk_gsdb;

and 

insert into  mecube.ctdr026_lk_gsdb
  (cd_gsdb,cd_type,na_gsdb,ad_street,ad_city,ad_state,ad_country,ad_zip,NO_LATITUDE,NO_LONGITUDE,ts_last_update)
  SELECT cd_supplr_site,'CMMS3',substr(na_site_name,1,25),substr(ad_sp_street,1,30),ad_sp_city,cd_sp_state,
  cd_sp_country_code,ad_sp_postal_code,
  'Inst','Inst',sysdate FROM mecube.tahp271_site);



Thanks & Regards
Thanga.
Re: PL/SQL suggestion [message #348930 is a reply to message #348921] Thu, 18 September 2008 05:51 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
write your procedure properly.(if u want to fire DDL(ie truncate) in pl/sql block use execute immediate) and
submit a job in oracle job queue using dbms_job package.

Regards,
Navneet
Re: PL/SQL suggestion [message #348946 is a reply to message #348425] Thu, 18 September 2008 06:23 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@iamdurai,

I can understand your requirement. My answer remains the same. But I wonder what you didn't understand in my previous reply.

joicejohn wrote on Wed, 17 September 2008 00:05
Please explain what is left unclear from Littlefoot's reply?

1/ Format a procedure with your requirement.
2/ Schedule the procedure using DBMS_JOB package.

For Scheduling From Oracle try the following links:

1/ http://www.oradev.com/dbms_scheduler.jsp
2/ http://download-uk.oracle.com/docs/cd/B28359_01/server.111/b28310/appendix_a004.htm
3/ http://www.orafaq.com/node/871 (For pre-10g releases)

If you insist in using a cron job try the following link:
http://forums11.itrc.hp.com/service/forums/questionanswer.do?threadId=947212&admit=109447626+1221589033785+28353475



Did you face problems formatting the procedure or did you have problems scheduling them? Confused

Please Explain...

Regards,
Jo
Previous Topic: ERROR Handling in Multi Table Inserts
Next Topic: Time taken by query
Goto Forum:
  


Current Time: Sat Dec 10 18:26:43 CST 2016

Total time taken to generate the page: 0.05782 seconds