How can i use stored procedures in PX.

Nripendra Chand · Post by **Nripendra Chand** » Fri Feb 18, 2005 2:33 am

Hi All,

How can i use stored procedures (For Oracle database) in PX? I also want to pass parameters to the procedure.

-Nripendra

sudharsanan · Post by **sudharsanan** » Fri Feb 18, 2005 3:04 am

I haven't used store procedure in my project.. This topic has been discussed already i am give that link...viewtopic.php?t=91200&highlight=Stored+Procedure

Nripendra Chand · Post by **Nripendra Chand** » Fri Feb 18, 2005 3:10 am

How can i solve this problem:

Country_Id is a job parameter. If it is blank then table should return all the records else values corresponding to country_id should be returned.
Country_Id is a column in the table.

-Nripendra

sudharsanan · Post by **sudharsanan** » Fri Feb 18, 2005 3:37 am

Are asking about how to write a Stored Procedure for your condition?...

sudharsanan · Post by **sudharsanan** » Fri Feb 18, 2005 3:46 am

I am giving a sample Store Procedure where you pass the Country_id
Open a cursor to store all the values there..

CREATE OR REPLACE PROCEDURE <Procedure_Name>(Country_id NUMBER) IS
BEGIN

IF Country_id IS NULL THEN

Cursor Country_out is Select * from table;
Else

Cursor Country_out is Select * from table where Country_id = <Country_id>

RETURN;
END IF;

Please modify the code according to your need... Just pass the country_id as the input and verify it in the procedure...

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Feb 18, 2005 5:57 am

It is so funny that I replied to an identical topic in the Server section a few minutes ago.

The answer was:

Use an OR clause as below

Select *
From Country
Where ('#CountryParameter#' = '')
Or (ContryCode = '#CountryParameter#')

bgs · Post by **bgs** » Fri Feb 18, 2005 3:56 pm

Either you can call the procedure in a select statement or you can write a shell script which calls the procedure.And you can call the shell script in external filter stage or you can create a wrapper stage

roy · Post by **roy** » Sun Feb 20, 2005 4:00 am

Hi,
you didn't mention if your on a 7.5 release of DS?
if so there is a new stored procedure stage that supports also output parameters.
though it seems to run via ODBC.

IHTH,

Madhav_M · Post by **Madhav_M** » Mon Jun 13, 2005 5:01 am

Is there is any ways to import stored procedure into a routine??

elavenil · Post by **elavenil** » Mon Jun 13, 2005 7:11 am

Any SQL statements including stored procedures can be executed from Before/After SQL of any DB stages.

Regards
Saravanan