Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] Calling an Oracle procedure in Data Migrator

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Calling an Oracle procedure in Data Migrator
 Login/Join
 
dballest
Platinum Member
posted
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
 
Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005Report This Post
Tobar
Member
posted Hide Post
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
 
Posts: 23 | Location: Minnesota | Registered: July 27, 2009Report This Post
Tobar
Member
posted Hide Post
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
 
Posts: 23 | Location: Minnesota | Registered: July 27, 2009Report This Post
Clif
Guru
posted Hide Post
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
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
dballest
Platinum Member
posted Hide Post
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
 
Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005Report This Post
dballest
Platinum Member
posted Hide Post
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
 
Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005Report This Post
Tobar
Member
posted Hide Post
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
 
Posts: 23 | Location: Minnesota | Registered: July 27, 2009Report This Post
dballest
Platinum Member
posted Hide Post
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
 
Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005Report This Post
Tobar
Member
posted Hide Post
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
 
Posts: 23 | Location: Minnesota | Registered: July 27, 2009Report This Post
dballest
Platinum Member
posted Hide Post
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
 
Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005Report This Post
Tobar
Member
posted Hide Post
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
 
Posts: 23 | Location: Minnesota | Registered: July 27, 2009Report This Post
dballest
Platinum Member
posted Hide Post
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
 
Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005Report This Post
Tobar
Member
posted Hide Post
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
 
Posts: 23 | Location: Minnesota | Registered: July 27, 2009Report This Post
dballest
Platinum Member
posted Hide Post
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
 
Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005Report This Post
janaki_senthil
Member
posted Hide Post
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.
 
Posts: 2 | Registered: September 20, 2012Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] Calling an Oracle procedure in Data Migrator

Copyright © 1996-2020 Information Builders