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] SQLSERVER to ORACLE

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] SQLSERVER to ORACLE
 Login/Join
 
Cyril Joy
Platinum Member
posted
Hi,

I need to read data from a table in SQLSERVER and write to a table in Oracle database directly. Can I do that?

I dont want to PRINT and HOLD the records, instead of that I need to write that directly to the Oracle table.

SQL SQLMSS SET SERVER MASS
SQL SQLMSS
SELECT * FROM MASS_Application;
TABLE FILE SQLOUT
PRINT *
-****Is there any way to write directly to Oracle table?
END
-EXIT

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


Regards,
Cyril Joy.

WF Production 8008 on Linux.
 
Posts: 143 | Location: Rochester,NY. | Registered: August 20, 2004Report This Post
Kamesh
Master
posted Hide Post
I believe you can do this by writing a stored procedure in SQL Server. Check for OPENQUERY in Sqlserver help. You may get some idea.

Hope it helps, Good Luck.


WFConsultant

WF 8105M on Win7/Tomcat
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
Cyril Joy
Platinum Member
posted Hide Post
I got a solution. Thanks!


Regards,
Cyril Joy.

WF Production 8008 on Linux.
 
Posts: 143 | Location: Rochester,NY. | Registered: August 20, 2004Report This Post
Ingas
Gold member
posted Hide Post
Cyril,

1. If you just want to create table in Oracle with data, you just need:

TABLE FILE
PRINT ....

ON TABLE HOLD AS [your name of table] FORMAT ORACLE PERSISTENCE PERMANENT
END

2. If you already have table in Oracle - learn MODIFY.


WF 7.6.2, WinNT/2K, MSSQL 2K, MSOLAP 2K, BID
 
Posts: 79 | Location: Moscow | Registered: April 27, 2007Report This Post
Jessica Bottone
Platinum Member
posted Hide Post
Cyril, what was your solution?


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Report This Post
Cyril Joy
Platinum Member
posted Hide Post
Following is what I did:

-**********DEFINE THE ORACLE CONNECTION PARAMETERS
SET SQLENGINE=SQLORA
SQL SET SERVER connection
-RUN
SQL SET CONNECTION_ATTRIBUTES connection/userid,password
-RUN


-*******DEFINE AN ADAPTER FOR SQL SERVER
SQL SQLMSS SET SERVER XAP

-*********THE FOLLWING CODE WILL CREATE AN ORACLE TABLE NAMED STG1_ORG_LINKAGE
-*********FROM THE SQLSERVER TABLE NAMED ORG_LINKAGE
SQL SQLMSS
SELECT * FROM ORG_LINKAGE;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS STG1_ORG_LINKAGE FORMAT ORACLE
END


Regards,
Cyril Joy.

WF Production 8008 on Linux.
 
Posts: 143 | Location: Rochester,NY. | Registered: August 20, 2004Report This Post
Cyril Joy
Platinum Member
posted Hide Post
Hi Ingas,

Is it possible to modify/update and existing table?


Regards,
Cyril Joy.

WF Production 8008 on Linux.
 
Posts: 143 | Location: Rochester,NY. | Registered: August 20, 2004Report This Post
Waz
Expert
posted Hide Post
Cyril, you can do this using MODIFY or MAINTAIN


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Ingas
Gold member
posted Hide Post
quote:
Originally posted by Cyril Joy:
Hi Ingas,

Is it possible to modify/update and existing table?


Of course.

1. HOLD way (and "wrong" way)

Step 1. ON TABLE HOLD AS [my temporary table] FORMAT SQLORA
Step 2. run
SQL SQLORA
INSERT INTO [my existing table]
SELECT * FROM [my temporary table];
COMMIT; -- I'm not sure, but in Oracle you can COMMIT even if no modifications was done
END

2. MODIFY ( "right" way)
Cyril, you even do not have to learn MODIFY

Start DataManagement Console, draw your needed data flow, save, and:
a) grab an paste it in you report
b) EX this flow from your report
c) -INCLUDE this flow in your report


WF 7.6.2, WinNT/2K, MSSQL 2K, MSOLAP 2K, BID
 
Posts: 79 | Location: Moscow | Registered: April 27, 2007Report This Post
PBrightwell
Master
posted Hide Post
To modify an existing Oracle table you need to check your access file to be sure that the write permission is set to YES.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report 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] SQLSERVER to ORACLE

Copyright © 1996-2020 Information Builders