4 replies to this topic

[madhulika]

Hi all,
I am new to dollar universe and I want to know whether we can schedule
oracle store procedures by using $U.


Thanks in advance,
Madhulika.

[men]

Hello,

I know it is possible to do it - but I never did it myself.

From what I heard, you need a template uproc to run stored procedures using command osql (or isql depending on the version).

You should also be aware that running a sql stored procedure is not synchronous - unlike running a SQL statement.

Hope someone can provide more information.

Michel

[madhulika]

men, on May 19 2010, 04:33 AM, said:

Hello,

I know it is possible to do it - but I never did it myself.

From what I heard, you need a template uproc to run stored procedures using command osql (or isql depending on the version).

You should also be aware that running a sql stored procedure is not synchronous - unlike running a SQL statement.

Hope someone can provide more information.

Michel

Thanks for the reply Michel....
Can someone provide a better solution for this....

[shaft]

madhulika, on May 19 2010, 04:15 PM, said:

Thanks for the reply Michel....
Can someone provide a better solution for this....

Hi,

You can try this syntax for your script:

sqlplus << __EOF__
USERNAME/PASSWORD
set serveroutput on
DECLARE
...
BEGIN
...
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQLCODE='||SQLCODE);
DBMS_OUTPUT.PUT_LINE('SQLERRM='||SUBSTR(SQLERRM, 1, 100));
END;
/
quit
__EOF__

[men]

Hello,

This is a solution used by one of our consultants - big thank you to him!

This will enable an Oracle stored procedure to be executed without the need to create a SQL script for each execution, it will trap any error codes during execution as well as trap for valid connection to the database.

There are 2 attached files.

Sample_procedure.sql

This is a sample Oracle stored procedure, it takes two parameters, a string and a number. It is provided so that you can test your implementation without any concerns (it does nothing offensive).

To load the procedure to the database

- Store the file on the machine (i.e. /tmp/sample_procedure.sql)
- Execute the command: Sqlplus username/password @/tmp/sample_procedure.sql
where username and password is changed for your account details. It will say procedure created on the screen if it worked.

ORACLE_PROC.ksh.txt

This is the sample code for DUAS to execute stored procedures, there are many choices on how to implement, either copy the code into Internal CL code for the UPROC, or put this file in a directory within DUAS installation and then execute it from the CL code. Later is a better scenario as only one copy of the code will exist – however that means it has to be deployed to the DUAS installations.

To use:-

Create two prompts on the UPROC
ORA_PROCEDURE String Holds the name of the stored procedure (packagename.procedurename or just procedurename if not in a package)
ORA_PARAMETERS String Any parameters you wish to pass to the stored procedure

Have two other operating system variables setup (from UPROC or U_ANTE_UPROC)

ORA_USERNAME String Username to connect to database
ORA_PASSWORD String Password of this user
ORACLE_SID String Oracle SID of database to connect, this is optional if already set in environment then it is not required, also TWO_TASK can be used as well if database is remote

Important:-

This script does not have code to set the Oracle environment – it could be added if required as long as client has onfigured “oraenv”

Parameters are not passed cleanly, if you need variables in each then script will need to be changed to parse from environment space.

Michel

Attached Files

•  ORACLE_PROC.ksh.txt   1.21K   10 downloads
•  sample_procedure.sql   350bytes   1 downloads