Avatar of GoodName
GoodName asked on

How to execute oracle procedure from Windows command line?

Hi,

How to execute oracle procedure from Windows command line?

Thanks.
Oracle Database

Avatar of undefined
Last Comment
GoodName

8/22/2022 - Mon
Sean Stuber

you can't directly

you'll have to invoke something else like sqlplus first

C:\>sqlplus -s /nolog
connect username/password@db_name
set serveroutput on
set feedback off
spool "test_output.txt"
exec dbms_output.put_line(sys_context('userenv','db_name'));
exit
ASKER
GoodName

Then I have to find the way to run Windows batch file from sqlplus or batter from Oracle procedure.

My task is
1. import data from different sources (Sybase) into Oracle using sqlldr
2. execute an Oracle package to process imported data. I need to invoke a package only after successful data import completion.



Sean Stuber

Can you use external tables?  It's still sqlldr but embedded within the db.

Then you can wrap your import and processing inside a single procedure.

your process would be something as simple as....

begin
    insert into db_table(a,b,c)  (select a,b,c from external_table);
    insert into db_table2(d,e,f) (select d,e,f from external_table2);
    process_imported_data;
    commit;
end;
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
GoodName

No I cannot use external tables because the import files are located on another server.
Sean Stuber

ok, then you can put your sqlldr command in a batch file followed by the command line syntax I used above.  should work just fine.

good luck
ASKER
GoodName

Would you please clarify what statement I should use.

Like here is a part of my batch file:

@isql -Uuser -Ppass -Solestage -s -b -istore.sql -ostore.dat

@IF EXIST output.dat sqlldr userid=posint/posint@eisuat DIRECT=TRUE errors=30000 control=store.CTL log=store.LOG bad=store.BAD

What is next?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
GoodName

Brief summary:

1. Is it possible to call OS batch file from pl/sql?

2. Is it possible to call Oracle 9i stored procedure from Windows batch file?

Thanks.
Sean Stuber

1 - yes, but you have to use java or external procedures to invoke a shell.
2- yes, but you have to use sql*plus or some other tool that will login to Oracle and execute the procedure.

I demonstrated 2 above,  did that example not work for you?  If not, what error did you get?
ASKER
GoodName

Thank you, Sdstuber.

The fist sample you gave should work in unix but in dos it stops after first command (sqlplus -s /nolog) inside of sqlplus environment. Maybe I need to play with dos settings to make it run without interruptions?

I slightly adjusted your code and it works this way for now:

                      IF EXIST test.sql sqlplus -s user/pass@server @test.sql

where test.sql contains the import processing procedure call. If you have any further suggestions here please let me know.

External tables sample is very good but I cannot use them in my prod environment. I am still trying to find the  similar way maybe using java or external procedures as you mentioned.

Would you please give me some small samples with java or external procedures?

thanks.

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sean Stuber

external procedures are similar in nature, you write the procedure and compile it then wrap it in a pl/sql procedure like the java.  You must have your listener set up to handler the external procedure calls too.  It's more pieces and not all inside the db so it's a little harder to keep track of.  I prefer the java approach.
ASKER
GoodName

Thank you, Sdstuber.

Let me try.