Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    SQLORA - unable to insert all records.
Go
New
Search
Notify
Tools
Reply
  
SQLORA - unable to insert all records.
 Login/Join
 
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, 2007Reply With QuoteReport This Post
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: 924 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
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: 924 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
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: 924 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport 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
 
Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
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: 924 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    SQLORA - unable to insert all records.

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.