Focal Point
[SOLVED] Calling an Oracle procedure in Data Migrator

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/576105022

August 25, 2009, 08:09 PM
dballest
[SOLVED] Calling an Oracle procedure in Data Migrator
Hi All,

I would like to know if it's possible to call/execute an Oracle procedure within a flow in Data Migrator. I tried the DBMS SQL Flow but it only requires a SQL statement.

Thanks,
Dan

This message has been edited. Last edited by: dballest,


Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE
August 26, 2009, 08:24 AM
Tobar
My Oracle is a little rusty, but I think the command is "exec procedure" or something to that akin. If the flow is saying that that string is not valid sql maybe you need to use the "pass through" option. Sorry I can't give a definite answer, but I do not have access to Oracle.


------
Livin' down on the cube farm. Left, right, right.

iWay 5.6
Windows
Excel, Tab-delimited, XML
August 26, 2009, 08:41 AM
Tobar
OK. I tried this with SQL Server and it worked. I would think it or something like it would work for Oracle.

In the statement I had:
exec usp_donothing

I had the "evaluate" propery set to "False", which I think is the default. Are you getting an error? What has you say "it only requires a SQL statement"?

This message has been edited. Last edited by: Tobar,


------
Livin' down on the cube farm. Left, right, right.

iWay 5.6
Windows
Excel, Tab-delimited, XML
August 26, 2009, 09:53 AM
Clif
There are a couple of different ways to call an ORACLE stored procedure from DataMigrator. Which approach you use depends on what you want to accomplish. If you want to call a stored procedure to DO something then use exec as Tobar suggested.

However if you want to call a stored procedure that returns an answer set to use in a Data Flow then you should create a synonym for the stored procedure. This is documented in the DMUG and onlilne help in the section "How to Create a Synonym for a Relational Stored Procedure."


N/A
August 26, 2009, 12:29 PM
dballest
Hi Tobar,

You're suggestion worked. The procedure does something in the database.

Thanks also for the information Clif. I'll keep note of it.

Dan


Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE
August 26, 2009, 12:42 PM
dballest
Hi Tobar,

When I run the DBMS SQL flow, it does not seem to sql statement the I put in:

EXEC rvdw.getlast3thenavg

However, if I view the SQL statement and click on the icon on the right hand side with a SQL text and a green check mark, it executes the statement. It takes about 15 secs to process the procedure.

Dan


Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE
August 26, 2009, 12:54 PM
Tobar
Hi Dan,

That seems odd doesn't it. Are there any traces files you can check to see exactly what the SQL Flow Object is doing. I must admit, I did not realize that you were using Data Migrator when I first responded. I have been using iWay. I do not have any experience with Data Migrator. So, other then the suggestioins to check logs I am not much help.


------
Livin' down on the cube farm. Left, right, right.

iWay 5.6
Windows
Excel, Tab-delimited, XML
August 26, 2009, 01:18 PM
dballest
There were no error messages on the log.

When I right click on the DBMS SQL icon and select "Toggle", under the Test SQL tab, there were a few messages:

(FOC1400) SQLCODE IS 900 (HEX: 00000384)
: ORA-00900: invalid SQL statement
(FOC1414) EXECUTE IMMEDIATE ERROR.


Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE
August 26, 2009, 02:13 PM
Tobar
Are you using any qouting in your statement. You might need to do some escaping if that is the case. Can you show me your "statement"?


------
Livin' down on the cube farm. Left, right, right.

iWay 5.6
Windows
Excel, Tab-delimited, XML
August 26, 2009, 04:56 PM
dballest
The statement is just a one-liner:

EXEC rvdw.getlast3thenavg


Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE
August 26, 2009, 05:15 PM
Tobar
I'm sorry. You had shown that earlier. So if you start up a sql session (SQLPlus or Toad) and connect to your database, can you run EXEC rvdw.getlast3thenavg successfully? First though, in the world of simple things, try putting a semi-colon ; at the end of the statement. Also, did you check if you are running in "SQL pass through"? I am thinking you are as you got an oracle error back (ORA-00900) so it does appear it is Oracle complaining and not DM.


------
Livin' down on the cube farm. Left, right, right.

iWay 5.6
Windows
Excel, Tab-delimited, XML
August 26, 2009, 06:37 PM
dballest
The statement failed in SQL Navigator. To run the procedure, I have a SQL:

BEGIN
getlast3thenavg;
END;

But when I put this in the statement, I get an error when I run it:

(FOC1400) SQLCODE IS 6550 (HEX: 00001996)
: ORA-06550: line 1, column 21:
: PLS-00103: Encountered the symbol "end-of-file" when expecting one of
: the following:
: := . ( @ % ;
(FOC1414) EXECUTE IMMEDIATE ERROR.


Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE
August 27, 2009, 08:44 AM
Tobar
Alrighty then. Now we have something to go on. It is not DM that has the issue, you have an error in the procedure itself. Oracle is the one complaining, not DM. How long is your code? Is it something you could post? It might help to reference the Oracle documentation to make sure you are creating your procedure correctly. Not sure what version you are using, and they are all the same in this case, but here is the doc for 10g: http://download.oracle.com/doc...7/08_subs.htm#i14202


FYI:

EXEC PROCEDURE

and

BEGIN
PROCEDURE
END

are equivalent


------
Livin' down on the cube farm. Left, right, right.

iWay 5.6
Windows
Excel, Tab-delimited, XML
August 27, 2009, 12:05 PM
dballest
I found a way to do it.

1. Create a new stored procedure.
-*Call the Oracle Stored Procedure
SET SQLENGINE=SQLORA
-RUN

SQL SET SERVER RVDW_Dev_Dev145
-RUN

SQL SQLORA EX rvdw.getlast3thenavg
END
-RUN

2. Create a process flow with this new procedure.

Thanks for all your help.

Dan


Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE
September 26, 2012, 07:48 AM
janaki_senthil
Hi

I tried the same with sql stored procedure into iway,I get the out which isnt alligned.When I try sqlout,it isnt working as well saying there is no description for it.Will hold file help or is there any other option?

Please help.