Calling Oracle Stored Procedure in Stored Procedure Stage

[vuluvala]

I am trying to design a job that uses "Stored Procedure" Stage to call Oracle Stored Procedure. I went through the DataStage documentation but I am unable to complete the job design. Here's what happened so far.

- Added the Stored Procedure (STPPX) stage to my DataStage job.
- Got confused with the STPPX property window.
- Got confused with the Import Meta Data (Stored Procedures) window while trying to permform: Import ---> Table Definitions ---> Stored Procedure Definitions in order to import the Oracle Stored Procedure.

Thanks in advance, if any one could help me with any sort of documentation or samples.

Thanks

[lstsaur]

Read the stpstage.pdf file under the Doc folder.

[keshav0307]

Too much confusion, but what is your confusion?

[vuluvala]

1). STPPX property window has two main tabs "Stage" and "Input". The tabs under "Stage" tab with my questions are as follows.

- General ==> I picked "Oracle" for Database Vendor. Then I was expecting to see the Oracle Stage connection options (Username, Password, and Remote Server) as you see under "Input" tab in "Oracle Enterprise" Stage. Instead it has "Data source", "Username", "Password", "Database name", and "Transaction ISO". Do I have to define (configure) the data source for Oracle server. And I have project level parameters defined for Oracle Username and Password. Can I use these parameters here?

- Syntax ==> For procedure name, I clicked on the button to select the stored procedure from the repository. As I have not imported the definition for the procedure, I selected "Import => Stored Procedure Definitions..". Now I have the window with the fields "Seen from:", "DSN:", "User name:", "Password:", "Name contains", and "NLS map:". To show the Oracle Server name in DSN list, I have made the following entry in uvodbc.config file:
<ServerName>
DBMSTYPE = ODBC
Also I made any entry in /opt/odbc32v51/odbc.ini for the oracle server. Also I have an entry for the oracle server in /u01/oracle/product/10.2.0/network/admin/tnsnames.ora. When I click OK after entering the information, I am getting "invalid username/password" error. My confusion is with ODBC vs tnsnames. Usually we use either of them not both to establish a database connection. I know I am doing some thing wrong here but not sure what I should do.

- Parameters ==> For "Parameter name", does it have to exactly match the parameter name in the procedure?

Am I missing any configuration setup on the ETL server to make it work?

Thanks in advance.

[vuluvala]

Read the stpstage.pdf file under the Doc folder.

Though I hard coded Oracle database connection related info, I finally got it to work. I need to find out how to use project level parameters for Oracle database connection related info. Thank you for directing me to pdf document.

thanks

[srekant]

I am using stored procedure for the first time and have exactly same kind of question you raised below.Appreciate if you can provide some input to below propeties from your successful usage of STP stage:

STP---->Stage Tab:

DataBase Name :Oracle
DataSource Name : I have given the name as we would give in oracle enterprise stage for remote server.Is it correct?
StoredProcedure Name: Typed in the procedure name.Doing i this way is correct or i have to import the procedure to repository and then use it.If i have to import do i need to make entries in .odbc.ini and UVODBC.config.

How to pass the input and out parameters to the procedure(My STP will read 3 input parameter and returns 4 values)? Is it ok if i just define the input parameters as input columns and output parameters as output columns in STP stage?

Any help is appreciated?

1). STPPX property window has two main tabs "Stage" and "Input". The tabs under "Stage" tab with my questions are as follows.

- General ==> I picked "Oracle" for Database Vendor. Then I was expecting to see the Oracle Stage connection options (Username, Password, and Remote Server) as you see under "Input" tab in "Oracle Enterprise" Stage. Instead it has "Data source", "Username", "Password", "Database name", and "Transaction ISO". Do I have to define (configure) the data source for Oracle server. And I have project level parameters defined for Oracle Username and Password. Can I use these parameters here?

- Syntax ==> For procedure name, I clicked on the button to select the stored procedure from the repository. As I have not imported the definition for the procedure, I selected "Import => Stored Procedure Definitions..". Now I have the window with the fields "Seen from:", "DSN:", "User name:", "Password:", "Name contains", and "NLS map:". To show the Oracle Server name in DSN list, I have made the following entry in uvodbc.config file:
<ServerName>
DBMSTYPE = ODBC
Also I made any entry in /opt/odbc32v51/odbc.ini for the oracle server. Also I have an entry for the oracle server in /u01/oracle/product/10.2.0/network/admin/tnsnames.ora. When I click OK after entering the information, I am getting "invalid username/password" error. My confusion is with ODBC vs tnsnames. Usually we use either of them not both to establish a database connection. I know I am doing some thing wrong here but not sure what I should do.

- Parameters ==> For "Parameter name", does it have to exactly match the parameter name in the procedure?

Am I missing any configuration setup on the ETL server to make it work?

Thanks in advance.

[chulett]

This is well worth repeating:

Read the stpstage.pdf file under the Doc folder.

[srekant]

chulett,

Thanks for response.I scanned through the doc you mentioned and could not find some information about passing parameters. In STPSTAGE pdf 2.0 version stp stage has a seperate parameters tab but when i look into STP stage there is no seperate tab for parameters but it is sub propert under stage tab.

My concern is how do i pass the I/P output parameters to procedure.
If i had a parameters tab as mentioned in pdf then i can mentioned if it is an i/p or output parameter.

thanks in advance

This is well worth repeating:

Read the stpstage.pdf file under the Doc folder.

[srekant]

Also my job is abending since there are no parameters passes by the stage as part of the call:

BEGIN ICVMEXEC.BASE_OPTIONS_SPLIT(); END;

I modified the stage and gave the input columns(hard corded) as below

:Inputcol1,:Inputcol2

After the above change the error is

The parameter (:IN_ORDER_MODEL_DESIGNATOR,:IN_MODEL_YEAR,:IN_PARTNER_ID,:IN_RPO) is not associated with a column

chulett,

Thanks for response.I scanned through the doc you mentioned and could not find some information about passing parameters. In STPSTAGE pdf 2.0 version stp stage has a seperate parameters tab but when i look into STP stage there is no seperate tab for parameters but it is sub propert under stage tab.

My concern is how do i pass the I/P output parameters to procedure.
If i had a parameters tab as mentioned in pdf then i can mentioned if it is an i/p or output parameter.

thanks in advance

This is well worth repeating:

Read the stpstage.pdf file under the Doc folder.