Home » SQL & PL/SQL » SQL & PL/SQL » running Oracle Procedures as a DOS Batch file
running Oracle Procedures as a DOS Batch file [message #224868] Thu, 15 March 2007 16:06 Go to next message
boi-alien
Messages: 10
Registered: March 2007
Junior Member
Hi,

I have written several oracle procedures that I need to run every day. The DBA can't give me access to store the procedures as the DB I'm using is a production database. I've heard that you can run these scripts as a dos batch file. I have never done anything like that before. Can anybody give me some hints or sample code on how to do this?

Thanks.
Re: running Oracle Procedures as a DOS Batch file [message #224872 is a reply to message #224868] Thu, 15 March 2007 16:28 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Put your Oracle code into a file. For this example, let's call it 'test.sql':
begin
  insert into test values (my_seq.nextval);
end;
/

exit

Write DOS batch script (let's call it 'job.bat'), such as this one:
sqlplus -s scott/tiger@ora10 @test.sql

At the DOS prompt, you'd run it as
c:\> job

As you need to run it every day, use Windows Task scheduler, create a task and that should be all.
Re: running Oracle Procedures as a DOS Batch file [message #224880 is a reply to message #224868] Thu, 15 March 2007 17:14 Go to previous messageGo to next message
boi-alien
Messages: 10
Registered: March 2007
Junior Member
Hi Littlefoot,

I tried, that, I'm getting errors in the DOS prompt.

I just did a quick test for example, in my test.sql file I have the following:

begin
create table sasdev08.testing_dos_batch as
select * from sasdev08.class_loads_temp;

end;
/

when I run the batch file i get an error saying
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
begin declare exit for goto if loop mod null prgama raise return select update whil <an identifier>...etc..
Re: running Oracle Procedures as a DOS Batch file [message #224883 is a reply to message #224880] Thu, 15 March 2007 17:34 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why would you create a table in a PL/SQL procedure? It is, basically, bad and unnecessary decision. To do that, you'd need to use EXECUTE IMMEDIATE. But, if you really, really have to create the table, why wouldn't you do that in an SQL script? Simply put your CTAS statement into it and run it. No BEGIN-END.
Re: running Oracle Procedures as a DOS Batch file [message #224940 is a reply to message #224883] Fri, 16 March 2007 01:37 Go to previous messageGo to next message
boi-alien
Messages: 10
Registered: March 2007
Junior Member
Littlefoot wrote on Thu, 15 March 2007 15:34
Why would you create a table in a PL/SQL procedure? It is, basically, bad and unnecessary decision. To do that, you'd need to use EXECUTE IMMEDIATE. But, if you really, really have to create the table, why wouldn't you do that in an SQL script? Simply put your CTAS statement into it and run it. No BEGIN-END.


sorry, I was just doing the create to test to see if it works. What I'm actually doing is doing inserts, but since my data is time sensitive, I didn't want to insert it while I was testing.
Re: running Oracle Procedures as a DOS Batch file [message #225013 is a reply to message #224868] Fri, 16 March 2007 10:03 Go to previous messageGo to next message
boi-alien
Messages: 10
Registered: March 2007
Junior Member
would this work?

begin

INSERT INTO sasdev08.cfr_01OCT_31MAR@rsas
SELECT * FROM TLSAPPO.csm_future_request
WHERE CFR_ACTIVITY_CD = 'CAN'
AND cfr_create_date = SYSDATE-2;

end;
/
Re: running Oracle Procedures as a DOS Batch file [message #225014 is a reply to message #224868] Fri, 16 March 2007 10:06 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
#1 you do NOT need the BEGIN or END statements.
#2) you should really have a COMMIT; rather than rely on implicit behavior of SQL*Plus.
Re: running Oracle Procedures as a DOS Batch file [message #225031 is a reply to message #225013] Fri, 16 March 2007 11:54 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
boi-alien wrote on Fri, 16 March 2007 11:03
would this work?

begin

INSERT INTO sasdev08.cfr_01OCT_31MAR@rsas
SELECT * FROM TLSAPPO.csm_future_request
WHERE CFR_ACTIVITY_CD = 'CAN'
AND cfr_create_date = SYSDATE-2;

end;
/


Well, why don't YOU try it and tell us? Didn't that thought come to you?
Re: running Oracle Procedures as a DOS Batch file [message #225037 is a reply to message #225031] Fri, 16 March 2007 12:18 Go to previous messageGo to next message
boi-alien
Messages: 10
Registered: March 2007
Junior Member
joy_division wrote on Fri, 16 March 2007 09:54
Well, why don't [b
YOU[/b] try it and tell us? Didn't that thought come to you?


Thank you for being mean and condescending. As I stated earlier the data that I'm inserting is time sensitive and I didn't want to be randomly insterting it while I'm "testing" to see if this solution will work for me. But thanks again, it's very kind of you.
Re: running Oracle Procedures as a DOS Batch file [message #225040 is a reply to message #225037] Fri, 16 March 2007 12:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
So what WOULD you do? Trust us on our word? If we said 'go ahead', then you would do so?
Get yourself a development environment and get used to testing things yourself. joy_division was absolutely right.
Re: running Oracle Procedures as a DOS Batch file [message #225041 is a reply to message #224868] Fri, 16 March 2007 12:34 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
If you are implictly say that you do NOT have a test database or test schema; then shame on you!
I thought you were trying to show proof of concept initially which should NEVER touch anything Production.
Re: running Oracle Procedures as a DOS Batch file [message #225056 is a reply to message #225041] Fri, 16 March 2007 16:09 Go to previous messageGo to next message
boi-alien
Messages: 10
Registered: March 2007
Junior Member
anacedent wrote on Fri, 16 March 2007 10:34
If you are implictly say that you do NOT have a test database or test schema; then shame on you!
I thought you were trying to show proof of concept initially which should NEVER touch anything Production.


OK, I finally got ahold of a test environment.. the company I work for is... let's say really caught up in red tape.

now, I've followed littlefoot's advice and created a batch file that in turn calls my .sql file.

I have the following in my sql file (keep in mind this was a procedure at one point in time, but due to red tape, my group cannot create stored procedures):

Quote:
-- Delete LOADS existing in the TEMP table
DELETE CLASS_LOADS_TEMP;
COMMIT;

-- INSERT NEW LOADS FROM PREVIOUS DAY.
INSERT INTO sasdev08.class_loads_temp@rsas
(ban,subscriber_no,commit_start_date,commit_end_date,commit_length,
sub_status_last_act, sub_status_rsn_code, operator_id,init_activation_date,dealer_code,product_type,prv_ctn)

SELECT customer_id,subscriber_no,commit_start_date,commit_end_date,commit_orig_no_month,
sub_status_last_act, sub_status_rsn_code, operator_id,init_activation_date,dealer_code,product_type,prv_ctn
FROM tlsappo.subscriber@rpt1 a

WHERE(operator_id in (SELECT b.knowbility_id
FROM SASDEV08.knowbility_id_loy@RSAS b
WHERE b.group_name in ('VAS','BSS','MUM'))
OR SUBSTR(DEALER_CODE,1,10) IN ('1100032801','1100032802','1100034398'))

AND TRUNC(a.init_activation_date) = TRUNC(SYSDATE -1)


AND (a.sub_status_last_act ='NAC'
OR a.sub_status_last_act ='CCN'
AND trunc(init_activation_date)=trunc(prv_ctn_chg_date));

COMMIT;

-- REMOVE NUMBER CHNAGES WHICH HAVE ALREAD BEEN CREADITED.
BEGIN
FOR REC IN (SELECT a.ban
,a.prv_ctn
FROM sasdev08.class_loads_temp@rsas a
,sasdev08.class_new_loads@rsas b
WHERE a.ban = b.ban
AND a.prv_ctn = b.subscriber_no)

LOOP
DELETE sasdev08.class_loads_temp@rsas
WHERE ban = rec.ban
AND prv_ctn = rec.prv_ctn;
END LOOP;
END;
COMMIT;

-- Remove records where number change was not done on day of activation
BEGIN
FOR REC IN (SELECT a.ban,
a.subscriber_no
FROM sasdev08.class_loads_temp@rsas a
,subscriber@rpt1 b
WHERE a.prv_ctn IS NOT NULL
and a.ban = b.customer_ban
and a.prv_ctn = b.subscriber_no
and a.init_activation_date != b.init_activation_date)

LOOP
DELETE sasdev08.class_loads_temp@rsas
WHERE ban = rec.ban
AND subscriber_no = rec.subscriber_no;
END LOOP;
END;
COMMIT;

-- Remove records where 2 or more number changes where done on the same day.
BEGIN
FOR REC IN (SELECT a.ban,
a.subscriber_no
FROM sasdev08.class_loads_temp@rsas a
,subscriber_history@rpt1 b
WHERE a.prv_ctn IS NOT NULL
and a.ban = b.customer_id
and a.prv_ctn = b.subscriber_no
and b.prv_ctn is not null)

LOOP
DELETE sasdev08.class_loads_temp@rsas
WHERE ban = rec.ban
AND subscriber_no = rec.subscriber_no;
END LOOP;
END;
COMMIT;

EXECUTE IMMEDIATE 'ANALYZE TABLE sasdev08.class_loads_temp@rsas COMPUTE STATISTICS';
EXECUTE IMMEDIATE 'ANALYZE INDEX sasdev08.idx_class_loads_temp_01 COMPUTE STATISTICS';


UPDATE sasdev08.class_loads_temp@rsas

SET LOAD_DT = SYSDATE;

COMMIT;


When I try to run the batch file I get the following errors:

Quote:

SQL*Plus: Release 8.1.6.0.0 - Production on Fri Mar 16 15:06:25 2007

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production


0 rows deleted.


Commit complete.

SP2-0734: unknown command beginning "WHERE(oper..." - rest of line ignored.
SP2-0734: unknown command beginning "FROM SASDE..." - rest of line ignored.
SP2-0734: unknown command beginning "WHERE b.gr..." - rest of line ignored.
SP2-0734: unknown command beginning "OR SUBSTR(..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "AND TRUNC(..." - rest of line ignored.
SP2-0734: unknown command beginning "AND (a.sub..." - rest of line ignored.
SP2-0734: unknown command beginning "OR a.sub_s..." - rest of line ignored.
SP2-0734: unknown command beginning "AND trunc(..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.

Commit complete.

65


Then when I hit enter on the command prompt all I get is incrementing numbers from 65 and on. I have checked and run this script manually in TOAD before, so I know that it works.

Have I missed something or is there something else I need to add to the .SQL file in order to get this to work in SQL*PLUS?

Thanks for all your help. I do apologize for my snide remarks earlier. I was very stressed, and wasn't thinking straight.

Thanks
Re: running Oracle Procedures as a DOS Batch file [message #225063 is a reply to message #225056] Fri, 16 March 2007 18:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You need to get rid of the blank lines in the middle of your sql statement. Instead of:

select ...

where ...

you need:

select ...
where ...

Otherwise, Oracle is trying to figure out what command starts with the word "where" instead of "select".
Re: running Oracle Procedures as a DOS Batch file [message #225078 is a reply to message #225063] Sat, 17 March 2007 01:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And, in addition to Barbara's comment, you need a slash after each anonymous block to execute it.
This is a very 'SQL-Server/Sybase'-way of coding, by the way. In Oracle, you normally do not fill a temp table and then run all sorts of updates and deletes on it. Also, the cursor loops should be replaced by straight sql statements. The fact that your tables all seem to reside in different databases might be a reason to do it the way you did though. I would rewrite it using straight sql and compare the results.
Get rid of all the intermediate commits, they only slow things down.
Re: running Oracle Procedures as a DOS Batch file [message #225107 is a reply to message #225078] Sat, 17 March 2007 12:04 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Correct me if i am wrong.

In addition to frank's comments

a) If your sql is not having any restart logic then i will always suggest to do one commit after you have finished your processing rather than commiting here and there, because for some reason your script failed then you cannot revert back to the original position before you started your script.

d) If you delete any table unconditionally then use truncate (Remember Truncate is a DDL which means it has a implicit commit)

cheers

Re: running Oracle Procedures as a DOS Batch file [message #225182 is a reply to message #224868] Sun, 18 March 2007 22:29 Go to previous messageGo to next message
boi-alien
Messages: 10
Registered: March 2007
Junior Member
I will try this now, thank you all for your responses. I really appreciate all the help.
icon6.gif  Re: running Oracle Procedures as a DOS Batch file [message #246582 is a reply to message #224868] Thu, 21 June 2007 07:03 Go to previous messageGo to next message
samarendra_81
Messages: 10
Registered: April 2007
Location: india
Junior Member

hi little foot ,

i new to oracle ,i just tried to insert the file but as you tell
that.....

Write DOS batch script (let's call it 'job.bat'), such as this one:
sqlplus -s scott/tiger@ora10 @test.sql

can u tell me how to and where i can create the job batch file

. hey one more thing

that i have installed the oracle 8i and form 6i in e:/ drive .


as oracle 8i in --->e:/oracle/...
and form 6i in named as --->e:/ora6i/....

before i had installed the ora6i , when i type in sqlplus in command prompt of WINDOWS XP , itworks.

now its path may be changed to the sqlplus of e:/ora6i/....(which contain form6i) so icannot access direct from c:/of command. because it takes the sqlplus.exe of form 6i/bin folder


i have to go the directory in command prompt e:/oracle/ora90/bin and type sqlplus
then it works.

can you tell me how to direct acesses the sqlplus of oracle 8i (not sqlplus of form 6i) when i opened the command prompt.

from
sam

Re: running Oracle Procedures as a DOS Batch file [message #246590 is a reply to message #246582] Thu, 21 June 2007 07:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i have to go the directory in command prompt e:/oracle/ora90/bin and type sqlplus then it works.

Include ORACLE_HOME\bin in your PATH.

Regards
Michel
Re: running Oracle Procedures as a DOS Batch file [message #246671 is a reply to message #246590] Thu, 21 June 2007 11:03 Go to previous messageGo to next message
samarendra_81
Messages: 10
Registered: April 2007
Location: india
Junior Member

hi michel,

can you tell me how to include path in dos mode.

and also i also see by typing help command then set command that,like

showing path=oraclehome_of_form6i/bin/..; oraclehome_of_ora8i/bin.....; .......

that means sqlplus it already present in path , i think it may be due to in pesent in second position...

and can you plz tell me how to create a batch file as i mentioned

thanks michel
hoping for your answer..
from sam
Re: running Oracle Procedures as a DOS Batch file [message #246678 is a reply to message #246671] Thu, 21 June 2007 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set ORACLE_HOME=...
set PATH=%ORACLE_HOME%\bin;%PATH%

Regards
Michel
Re: running Oracle Procedures as a DOS Batch file [message #246699 is a reply to message #246678] Thu, 21 June 2007 11:39 Go to previous messageGo to next message
samarendra_81
Messages: 10
Registered: April 2007
Location: india
Junior Member

Michel Cadot wrote on Thu, 21 June 2007 21:47
set ORACLE_HOME=...
set PATH=%ORACLE_HOME%\bin;%PATH%

Regards
Michel


hi michele,
the first one i didnt got

oracle_home=.....

any way thanks michele

[Updated on: Thu, 21 June 2007 11:40] by Moderator

Report message to a moderator

Re: running Oracle Procedures as a DOS Batch file [message #246702 is a reply to message #246678] Thu, 21 June 2007 11:46 Go to previous messageGo to next message
samarendra_81
Messages: 10
Registered: April 2007
Location: india
Junior Member

Michel Cadot wrote on Thu, 21 June 2007 21:47
set ORACLE_HOME=...
set PATH=%ORACLE_HOME%\bin;%PATH%

Regards
Michel



and also michele ,

if i have to give that set path=%oracle_home%\bin; only
then any problem will there because its a very long path is there i cant see total path.

if it will not affect any

thanks
sam
Re: running Oracle Procedures as a DOS Batch file [message #246712 is a reply to message #246702] Thu, 21 June 2007 11:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand.
Don't read it, just set it. Exactly as I post it (replacing ... by your real home of course).

Regards
Michel
Re: running Oracle Procedures as a DOS Batch file [message #246718 is a reply to message #246712] Thu, 21 June 2007 12:10 Go to previous messageGo to next message
samarendra_81
Messages: 10
Registered: April 2007
Location: india
Junior Member

Michel Cadot wrote on Thu, 21 June 2007 22:22

Don't read it, just set it. Exactly as I post it (replacing ... by your real home of course).

Regards
Michel



i do as u say michele .

as in dos prompt

c:\> set ORACLE_HOME=e:\oracle\ora90
c:\> set PATH=%ORACLE_HOME%\bin;%PATH%

but not workin
the result is same
every time i typed the sqlplus in
command prompt

it open another dos screen for enter to user name but that is e:\ora6i\\bin\plus80.exe (written in top of screen) , which is the path of form 6i.

from sam





Re: running Oracle Procedures as a DOS Batch file [message #246784 is a reply to message #246718] Thu, 21 June 2007 23:55 Go to previous message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

What is the operating system you are using?. If Windows XP, Try to set your path in the environment variables of system properties and try.
Previous Topic: Index on aTable
Next Topic: autonomous transaction
Goto Forum:
  


Current Time: Sun Dec 11 06:31:46 CST 2016

Total time taken to generate the page: 0.15299 seconds