DsExecute for executing stored procedure.

[Aquilis]

Hi all,
I am trying to execute the stored procedure from the Jobcontrol code.
Currently i am using following DSExecute Command.

Cmd1 = 'sqlplus ':parmDBSchemaName:'/':parmDBPass:'@':parmDBName:' << EOF'
Cmd2 = 'EXECUTE ':Procedure_name :'(':'TABLE_Name_Value':')':';'
Cmd3 = 'Exit ;'
Cmd4 = 'EOF'
Cmd = Cmd1:Char(10):Cmd2:Char(10):Cmd3:Char(10):Cmd4
Call DSExecute('UNIX',Cmd,Output,ReturnCode)

Is this the valid one .Do suggest me.

My procedure seems to be like as;

CREATE OR REPLACE PROCEDURE PROC_NAME
(
p_table_nm IN truncate_control.table_nm%TYPE,
p_errmsg OUT VARCHAR2

Above truncate_control is my table name,where i am getting the parameter values from and passing those into procedure execution.
do suggest whether i need to fire 'execute' command or 'Call' command or where i am going wrong.


when try to execute above procedure throuh sqlplus directly i found following error.

ERROR at line 1:
ORA-06550: line 1, column 15:
PLS-00103: Encountered the symbol "Procedure_name " when expecting one of the
following:
:= . ( @ % ;
The symbol "." was substituted for "Procedure_name " to continue.

[saikir]

Hi,

You can call PL/SQLR or SQL statements in the job control as follows:

Call DSExecute('NT','SQLPLUS ' : ORACLE_UID : '/' : ORACLE_PWD : '@' : ORACLE_DSN : ' @' : Path : 'test.sql',Output, SystemReturnCode )

where NT indicates it is a OS command

:ORACLE_UID is the user id...

Path is the name of the path where your SQL or PL/SQL script is present.

Hope this answers your query

Sai

[Aquilis]

sai,
actually i need to supply the IN parameter into the procedure and then need to execute it.Your code matches pretty same with mine but you are calling script from a location, but in my case i need to execute it with the IN parameter supplied from DB as you see in my procedure syntax above.

Thanks,

[Ramona Reed]

Why don't you just use the Stored Procedure Stage? It works fine.