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  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Conversion from Informix to Oracle

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Conversion from Informix to Oracle
 Login/Join
 
Member
posted
What will need to change when we convert our Lawson financial tables from Informix to Oracle? I've already run into reserved words for files and fields where Oracle will prefix with an r_. I've also seen my date formats change from YYMD to HYYMDS. How can I easily handle that in coding where I look for 20080403 in a WHERE or DEFINE? What other gems can I expect?

This message has been edited. Last edited by: FP Mod Chuck,
 
Posts: 20 | Location: Syracuse NY | Registered: August 26, 2005Report This Post
Expert
posted Hide Post
ENGINE SQLORA SET VARCHAR OFF

Make sure you specify this in edasprof.prf so that when you regen your synonyms, all of your alpha fiels don't have formats like this: AnV. Trust me, having variable formats will screw up a lot of programs.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Member
posted Hide Post
Thanks for the info - I definitely needed that. Here are other things I've found...
1. Dates are DD-MMM-YYYY in Oracle. I had to add
ENGINE SQLORA SET DATETIME OFF
to edasprof.prf to get the dates back to the Informix format of YYYYMMDD.
2. Lots of our code is written in SQL which produces HOLD files to focus code. SQL has lots of changes...
a) SQLINF to SQLORA
b) SQL SQLINF SET ISOLATION TO DIRTY READ; not recognized
c) SQL SQLINF SET PDQPRIORITY 24; not recognized
d) SELECT {+FULL(appayment), +FULL(apdistrib)} not recognized
e) 'today' becomes 'sysdate'
f) OUTER JOINS are totally different
g) substrings change from aroihdr.invoice[1,2] to substr(aroihdr.invoice,1,2)
h) I'm having trouble with stored procedures (mostly date routines)
i) fieldnames outputted from the SQL HOLD file were lower case; ORACLE puts out upper case and doesn't recognize lower case; therefore I've had to copy/paste all of my code into WORD, change the case and copy/paste it back.
j) referencing the file name is different - we used to just type the filename or have PROD:lawson.filename, now it MUST be lawprod.filename

I'm now investigating functions such as TRIM, UPPER, etc. to see if there is a difference.
 
Posts: 20 | Location: Syracuse NY | Registered: August 26, 2005Report This Post
Expert
posted Hide Post
How would we do a Dirty Read using a TABLE FILE request, not SQL Passthru?

... in 8201+

Thanks, Doug




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Virtuoso
posted Hide Post
quote:
Originally posted by sys1165a:
2. Lots of our code is written in SQL which produces HOLD files to focus code. SQL has lots of changes...
e) 'today' becomes 'sysdate'


You may be able to create an alias for that in the database.

quote:
f) OUTER JOINS are totally different


Of old, Oracle has had their own syntax for outer joins, but these days they understand standard SQL outer joins just fine. Or is Informix using something different?

quote:
h) I'm having trouble with stored procedures (mostly date routines)


There is no standard for stored procedures, so that's to be expected.

quote:
i) fieldnames outputted from the SQL HOLD file were lower case; ORACLE puts out upper case and doesn't recognize lower case; therefore I've had to copy/paste all of my code into WORD, change the case and copy/paste it back.


You can configure that in the adapter settings. SQL is case-insensitive (even in Oracle), the case-folding happens in the adapter.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Conversion from Informix to Oracle

Copyright © 1996-2020 Information Builders