How to execute oracle procedure from Windows command line?

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

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.

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;

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

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

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?

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.

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?

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.

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.

Thank you, Sdstuber.

Let me try.