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    iDM - Invoking a function in Oracle DB

Read-Only Read-Only Topic
Go
Search
Notify
Tools
iDM - Invoking a function in Oracle DB
 Login/Join
 
AK
Member
posted
I have an Oracle function which returns the next value from a sequence.

1) How do I invoke an Oracle function using iDM Data Flow or using an iDM Stored Procedure (FEX)?
2) Can I execute the SQL directly i.e. select genvinsequence.nextval from dual from iDM?

Thanks.


iSM 616
iDM 7705
iDQC 802
WF 77
Windows 2008 Server
 
Posts: 29 | Registered: May 01, 2007Report This Post
Clif
Guru
posted Hide Post
(0) If all you need to do is use the SEQUENCE note that in Release 7.7 there is now support in the synonym for "autoincrementing" columns where for ORACLE the name of the sequence is identified, and as rows are inserted into the table the next value is automatically obtainsd.

(1) SQL SQLORA EX procedure name

(2) From New > Synonym select an ORACLE connection. In the "Restrict object to" pull-down select "stored procedures." Select the procedure you want, supply sample values and create the synonym.

Then you can use the synonym as source in a data flow. Pass input paramaters with a WHERE condition or a JOIN on each of the input parmaters, then select the output paramater.


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
Nan
Member
posted Hide Post
Clif,

U had mentioned that Oracle sequence will be listed as Stored procedures. I don't see them.

I have created a sequence in Oracle - SQ_ShadowTracitDM

After connecting to oracle in datamigrator,while creating synonym, if I do a restrict object to Stored procedures, it says no candidate objects found.

Please let me know. Thanks,
 
Posts: 23 | Registered: April 21, 2008Report This Post
AK
Member
posted Hide Post
Hi Clif, I tried the autoincrement field in the synonym and it didn't work for me.

In the synonym for the field(emp_id) I wanted to autoincrement based on an Oracle sequence I checked the autoincrement property and supplied the schema.sequencename to the sequence property. The synonym prompted me to selecte the field type to read only for autoincrement property so I made it read only. I added this synonym as my target in the data flow. In the target transformations I did NOT add the mapping/expression for this autoincrement field (emp_id). I also tried adding emp_id with a blank expression value. When I execute the flow emp_id is not getting populated.

Am I missing any other configuration? Is there any documentation available for this configuration?

Thanks.


iSM 616
iDM 7705
iDQC 802
WF 77
Windows 2008 Server
 
Posts: 29 | Registered: May 01, 2007Report This Post
AK
Member
posted Hide Post
quote:
Originally posted by Nan:
Clif,

U had mentioned that Oracle sequence will be listed as Stored procedures. I don't see them.

I have created a sequence in Oracle - SQ_ShadowTracitDM

After connecting to oracle in datamigrator,while creating synonym, if I do a restrict object to Stored procedures, it says no candidate objects found.

Please let me know. Thanks,


----
Nan, I don't think synonyms can be created for a sequence in Oracle. I ran into the same problem so I created an Oracle procedure/function which returns the nextval of a sequence, created a synonym for that procedure and used that in my data flow.


iSM 616
iDM 7705
iDQC 802
WF 77
Windows 2008 Server
 
Posts: 29 | Registered: May 01, 2007Report This Post
Clif
Guru
posted Hide Post
First, if I wwasn't clear, I described three different approaches you could use.

Nam: While can create a synonym for an ORACLE stored procedure (which you said you had) or package, you cannot create a synonym for a SEQUENCE.

AK: It sounds like the synonym and database are out of sync. If the target table is empty, then select the synonym from the browser, Data Management > Recreate DBMS table. For target transforms you had it right the first time, you don't need one for the autoincrement column because it will get assinged, well, automatically.


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
AK
Member
posted Hide Post
Cliff, you are right. Looks like the synonym and DBMS table are out of synch. After I recreated them the flow was able to autoincrement according to the Oracle sequence. Thanks for your help.

When recreating the table using the synonym DataMgmt-->Recreate DBMS table wizard I expected iDM to drop and create the DBMS only but it looks like iDM also dropped the sequence and recreated it again based on the properties in the synonym.


iSM 616
iDM 7705
iDQC 802
WF 77
Windows 2008 Server
 
Posts: 29 | Registered: May 01, 2007Report 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    iDM - Invoking a function in Oracle DB

Copyright © 1996-2020 Information Builders