Stored proc using Dynamic sql

, ,
1

hi guys,
I am using jdbc adapter in my integration and I need to run the stored procedure on the Oracle Db. First I used the Stored Procedure template to run but I was unable to run ,webMethods was giving me some kid of Oracle error .but when that stored procedure
Run through Toad and SQL Plus Its running fine.

Then I planned to use Dynamic JDBC adapter service Template to run the same stored procedure but this time i am just giving statement

exec Procedure name

and this time I am getting different error…

[wm-cjdbc33-0009][Oracle JDBC Driver][Oracle]ORA-00900: invalid SQL statement

does any one was successful in running stored Procedure though this Dynamic adapter service?
I will appreciate your response.

Thanks,

2

Kitt,

If you are getting “invalid SQL statement” error then issue might be the query used in the editor may be something is missing(typo) or else make sure the query is not ended with semicolon(;)like that.just remove this incase.

HTH,
RMG.

3

thanks for the reply RMG
yes I am not keeping “:” in the Query

just typing
exec procedurename

regards,

4

thanks for the reply RMG
yes I am not keeping “;” in the Query

just typing
exec procedurename

and one more thing i just need to run the Stored Procedure ,
so there are no parameters for input or output.

regards,

5

Kitt,

I checked here after a long testing still i am not sure if we cannot execute a SP using a Dynamic SQL template to get desired results.

Curious to know why you want to use Dynamic SQL for just executing SP instead you can use SP template which will work?

And i will definetely let you if i make it working SP with Dynamic SQL.

HTH,
RMg.

6

I was successful in running stored procedure using Dynamic service
for SQL databasefor different interface.
but on this interface i am using ORACLE,I am not sure weather stored procedure will run or not using Dynamic service on Oracle.

regarding Stored procedure service, I did tried to used Stored procedure template to run . but i am getting one Oracle error. I tried to talk with Oracle guys on this issue they saying that that stored procedure is running fine on there side. So I tried to use Dynamic Service,

We run lot of Stored procedure on SQL db with no problem. But this one stored procedure is on Oracle database that my main concern.

I got one more issue with As400 .I need to FTP file to AS400 system
Do we need to have AS400 adapter for that. ?
I tried using pub.client.ftp and I can run my service but I cant see the file in the
FTP server .all my input parameters are correct , I need to PUT the file in AS400 system,
I really don’t have any idea about AS400 File system.

Regards,

7

Kitt,

For Oracle DB you can just use the SP template instead of Dynamic SQL template,it will be fast than doing R&D.

If you are integrating with AS400(DB2) system either you can use JDBC Adapter or else you can ftp PUT the files(pub.client:ftp)in certain FTP location where AS400 system should or will pick that file for process futher.

HTH,
RMG.

8

One reason that we want to call a stored procedure using the dynamic template is that we can pass in the database schema instead of selecting it from the drop-down. Our schema might change from different environments.

Did anyone figure out how to execute a SP from a dynamic adapter service template?

Thanks
Cort

9

Cortney,

Try the following SQL instead of EXEC PROCEDURE:
BEGIN
SCHEMA.PROCEDURE;
END;

  • Percio
10

Hi,
I guess there is 2 requirements here-
a) calling a oracle store proc
b) it may belong to different schema.

I would create a adapter service -> jdbc adapter-> storedprocwithsignature and use current schema [ hard code no schema]
and use it.

11

Shashwata,

That would only work if the current schema was the schema that she wanted. I would imagine that she probably needs to invoke a procedure on a different schema altogether, hence the need for Dyanmic SQL. We have a very similar scenario where I work and we ended up using Dynamic SQL as well to solve the problem.

  • Percio
12

Thanks for the responses.

The following example should work for us:
BEGIN
${dbSchema}.PROCEDURE;
END;

Thanks!

13

BTW, using a dynamic sql template to call a stored proc is limited in that you can not get a return variable from the stored proc. You can execute a stored proc but nothing is returned.

We contacted wM support to verify our findings and they agree. Something to do with using a prepared statement versus a callable statement.

FYI,
Cort