DSXchange

Hi All,

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


-Nripendra

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

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

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

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...

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#')

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

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,

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

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

Regards
Saravanan