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    SQLORA - unable to insert all records.

Read-Only Read-Only Topic
Go
Search
Notify
Tools
SQLORA - unable to insert all records.
 Login/Join
 
Kar
Silver Member
posted
Hi,

I am running following fex file
-----------------------
-SET &ECHO=ALL;
ENGINE SQLORA SET DEFAULT_CONNECTION oraprod
SQL SQLORA

insert into target01 values ('5665');
COMMIT;
insert into target01 values ('6656');
COMMIT;

-RUN

-*

----------------------

When I query my target table target01, it only shows one row inserted.
It has one record with value 5655.
Target table is in Oracle 10g and DM is 7.6.4 on HpUX.

What is missing in my fex which is preventing it to insert the second row?

Thanks

kat
 
Posts: 43 | Registered: August 09, 2007Report This Post
jgelona
Master
posted Hide Post
Try this:
ENGINE SQLORA SET DEFAULT_CONNECTION oraprod
SQL SQLORA
SQL insert into target01 values ('5665');
SQL COMMIT;
SQL insert into target01 values ('6656');
SQL COMMIT;
END
-RUN


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Kar
Silver Member
posted Hide Post
Hi,
Thanks for help.
The solution provided by you was helpful.
Q. If I have 10 sql insert statements, I will have to copy it in the fex file you have created
or
can i put these statemetns in a file and refer to it in my fex?

kat
 
Posts: 43 | Registered: August 09, 2007Report This Post
jgelona
Master
posted Hide Post
You can put almost anything in its own .fex file and just use -INCLUDE programname in the fex that is going to run. Is that what you mean?


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Kar
Silver Member
posted Hide Post
Hi,
1. I tried your solution i.e
SQL SQLORA
SQL insert into target01 values ('5665');
SQL COMMIT;
SQL insert into target01 values ('6656');
SQL COMMIT;
END
-RUN

It gives an error message -
SQL insert into target01 values ('5665');
SQL COMMIT;
SQL insert into target01 values ('6656');
SQL COMMIT;
END
-RUN
(FOC1400) SQLCODE IS 900 (HEX: 00000384)
: ORA-00900: invalid SQL statement
(FOC1414) EXECUTE IMMEDIATE ERROR.

Thanks

Kat
 
Posts: 43 | Registered: August 09, 2007Report This Post
jgelona
Master
posted Hide Post
Kat,

This is the syntax we use and we are now on 10g. The only difference I see is that we list the column names associated with the VALUES clause.

Have you tried doing this in SQLPlus?

If it works there, you can always put the commands in a file and call SQLPlus from WebFOCUS like this:
-UNIX sqlplus userid@database/password @path/program.sql


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
<murali swarna>
posted
Hi Try this,

SQL SQLORA SET SERVER XYZ
SQL SQLORA SET ERRORTYPE DBMS

SQL SQLORA
insert into t_country values ( 'AE','UNITED ARAB EMIRATES','AE','35')
END

SQL SQLORA
insert into t_country values ( 'AR','ARGENTINA','AR','01')
END
 
Report This Post
Kar
Silver Member
posted Hide Post
Hi,

Thanks for help and I was able to insert records using your technique.
Now my question is, if I have 100 insert statement or more, I will need to put
SQLORA and END statement between each statement.
Is that correct? Is there any other workaround to void this tedious process.

THanks
Kat
 
Posts: 43 | Registered: August 09, 2007Report This Post
jgelona
Master
posted Hide Post
What I would do is put the 100 values in a file. Then do the following:
FILEDEF CODES DISK path/codes.txt ( LRECL 4 RECFM V
-RUN
-REPEAT ENDLOOP 100 TIMES
-READ CODES NOCLOSE &CODE.4.
 SQL SQLORA
 SQL insert into target01 values ('&CODE.EVAL');
 SQL COMMIT;
-RUN
-ENDLOOP


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
PBrightwell
Master
posted Hide Post
Are you required to do this with SQL or can you use MODIFY? Is your data in a file or do you have to manually enter the data?

If you are going to be doing much SQL code, I would recommend "SQL in a Nutshell" . It is about $45 and should be available from any of the major online booksellers.


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
Kar
Silver Member
posted Hide Post
I have file with SQL commands like
Insert into customer ....;
delete from vendor ....;
update client ......;
insert into vendor ......;
Insert into customer ....;
delete from vendor ....;

I want to know how can I user SQl passthru feature to usethe above file as source and apply it to target Oracle database.

Kat
 
Posts: 43 | Registered: August 09, 2007Report This Post
Jessica Bottone
Platinum Member
posted Hide Post
Read back up a few posts to what Murali said. That gives the precise syntax you need.


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
  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    SQLORA - unable to insert all records.

Copyright © 1996-2020 Information Builders