Executing Stored prcedure using OBDC stage

neeraj · Post by **neeraj** » Wed Oct 19, 2005 5:57 am

Hi,

I have created a simple stored procdure having one input parameter and using sys_refcursor as an out variable...

here is the procedure:
CREATE OR REPLACE PROCEDURE test_procedure (
Test_Parameter IN DEPT.dname%TYPE,
Test_Cursor OUT sys_refcursor
)

AS

--TYPE Test_Type IS REF CURSOR RETURN DEPT%ROWTYPE;

BEGIN

OPEN Test_Cursor FOR
SELECT DEPT.DEPTNO DD, DNAME, LOC, test, DDATE,
EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, EMP.deptno ED
FROM DEPT,EMP
WHERE DEPT.dname = Test_Parameter
AND DEPT.deptno = EMP.deptno;

END test_procedure;
/

When I am using this stored procedure in ODBC stage, I am getting the follwoing error:

proc_test..Transformer_2: |proc_test..ODBC_5.DSLink3: DSD.BCIOpenR call to SQLExecDirect failed.
Statement was:{call SCOTT.TEST_PROCEDURE('1')}
SQLSTATE=37000, DBMS.CODE=6550
[DataStage][SQL Client][ODBC][Microsoft][ODBC driver for Oracle][Oracle]ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TEST_PROCEDURE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored|

Please tell me what needs to be done to run that stored procedure

Regards
Neeraj Mahajan

I_Server_Whale · Post by **I_Server_Whale** » Wed Oct 19, 2005 8:23 am

Hi Neeraj,

If you are using 7.5, then I would suggest you use the stored procedure stage for executing your SP.

The stored procedure stage supports Oracle, DB2 and Sybase. It doesn't support MS_SQL Server.

Naveen.

lstsaur · Post by **lstsaur** » Wed Oct 19, 2005 11:18 am

Hi Neeraj,

Even if you are going to use 7.5 STP stage, it does not support the Type of Ref Cursor. I have already complained to the Ascential.

via · Post by **via** » Thu Oct 20, 2005 9:31 am

Hello Every one,
I have a similar problem with Stored procedure,
I have a Stored procedure that need to be run between jobs. Is it possible to execute the SP with out any inputs or outputs with in the Datastage Sequence. I am using Ds7.5x2 and win2003,SQL server.
Please help me in running the sp with out input and output arguments.
Thankyou.

kcbland · Post by **kcbland** » Thu Oct 20, 2005 9:50 am

Write a script to connect to your database of choice and execute the "do something" stored procedure. You'll have full capability to audit, check, log messages, whatever. The best part of it is that you can run these on Windoze using the Command stage. You also can use the Batch job to call the DS APIs yourself and have even more capabilities.

Come on folks, do just a wee little tiny bit of programming.

lstsaur · Post by **lstsaur** » Thu Oct 20, 2005 10:27 am

Hi Via,
The following is the example code that you can use to put in a batch file to execute your stored procedure:

SET sp_home="c:\oracle\ora10g\bin"
cd %sp_home%
call c:\oracle\ora10g\bin\sqlplus scott/tiger1@ora10g @c:\exestp.sql
exit

Let me know if you are still having problems. Good luck.

gateleys · Post by **gateleys** » Thu Oct 20, 2005 10:32 am

Hey Neeraj,
An easy solution would be to ask your DBA to store the procedure in the proper schema in Oracle. Use ODBC stage, import the metadata for the stored procedure in the ouput tab. This stage will the make a call to the procedure which is executed by Oracle.
However, I am uncertain about the %ROWTYPE. Please check the number of fields in your columns tab. If it matches the fields in the Stored proc, then it has to be the %ROWTYPE that is causing the problem. You migtht want to tweak the procedure such that it does not use that type.

via · Post by **via** » Thu Oct 20, 2005 11:13 am

Hi lstsaur ,
I am not sure abt the sql server homes. I am new to sql server,Do you have any idea of how they present in the SQL server directory structure.
Thankyou

lstsaur · Post by **lstsaur** » Thu Oct 20, 2005 12:24 pm

Hi Via,
Sorry, I gave you the example code which works for Oracle's stored procedure. I though that you and Neeraj were having the same Oracle stored procedure problems.
However, you can use the following example ISQL command to put in a batch file to execute your SQL server stroed procedure:

cd Program Files
cd Microsoft SQL Server
cd 80
cd Tools
cd Binn
isql -U reader -P test -S sql15 -d Enterprise_Staging -q "execute Enterprise_Staging.dbo.usp_RunBCDVW_ClosedView"
exit

via · Post by **via** » Thu Oct 20, 2005 4:03 pm

Thanks you,
Can i use this in a jobcontrol of a serverjob. I am on win2003, is it possible to use batch jobs to invoke storedprocedures(MS SQL 2000) through jobcontrol.
I am trying to run a do some thing stored procedure in the odbc with a dummy column sending to a seqfile.
I am able to run the SP's like this before but now when i run it says
Can some one help me out in this.
[Finalups_Entity_HierarchySP..dummytrans: |SQL statement has incorrect number of result columns.|
]

Thank you for you help[/code]

via · Post by **via** » Thu Oct 20, 2005 4:30 pm

The SP i am calling inturn calls other stored procedures. Can't we use a SP which inturn calls other stored procedure with the odbcstage?
If i call a single SP with out any SP in it,it is working fine.
The error message is
"SQL statement has incorrect number of result columns"
Did any one got the same problem?
Any help is appreciated,
Thankyou

ray.wurlod · Post by **ray.wurlod** » Thu Oct 20, 2005 5:11 pm

ANY stored procedure invoked from DataStage must work with a record set. You can find this in the documentation. Any other kind of stored procedure will return that - or a similar - message, often when you're importing the SP definition. And you must import the SP definition before attempting to use it, because DataStage wants to check that the number of columns in your job design matches the number of arguments used by the SP.

via · Post by **via** » Thu Oct 20, 2005 6:48 pm

Ray,
I am able to run a 'do some thing' stored procedure with out importing SP definition in an ODBC stage, i mapped a dummy coloum to seq file, it is working fine,it too don't have input or output parameters to pass into SP.

This problem arrises only when i am using a Do some thing SP that inturn calls other stored procedure in it.

Could you suggest a method of invoking this type of SP's in a windows environment for SQL server.
As suggested by 'lstsaur' in the previous post i tryed to run in a jobcontrol of a server job, but it is not recognising all those operating sytem commands.
Please suggest,
Thanks again.

via · Post by **via** » Fri Oct 21, 2005 9:04 am

Hello,
I wrote a batch job to execute the stored procedure as suggested by lstsaur and kcbland .
I am unable to run it and job is aborting with a message "Attempting to Cleanup after ABORT raised in stage Batch::Batch_SP..JobControl"

The code i wrote is

Code: Select all

OpenPath "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\isql -U user -P password -S server -q" to pat
else
PRINT "Path not found"
ABORT
    Command= "Execute [i]storedprocedure[/i]"
    Shell = "NT"
 Call DSExecute(Shell, Command, Output, ExitStatus)

   If ExitStatus <> 0
      Then
         Msg = "Error executing command " : Quote(Command)
         Call DSLogWarn(Msg, "Stored procedure executed")
      End

Could some one tell me were i am going wrong,
Thanks for your help

lstsaur · Post by **lstsaur** » Fri Oct 21, 2005 12:14 pm

Hi Via,
Say you have a batch (dsbat1) file which contains the following statements under the C:\Ascential directory:
cd Program Files
cd Microsoft SQL Server
cd 80
cd Tools
cd Binn
isql -U reader -P test -S sql15 -d Enterprise_Staging -q "execute Enterprise_Staging.dbo.usp_RunBCDVW_ClosedView"
exit

All you need to do is to make sure in your job control's cmd variable which is built as a string of C:\Ascential\bat1; then your next statement CALL DSExecute('NT', cmd, Output, ExitStatus) will execute your batch file bat1.
Hopefully, this will give you an idea. Good luck