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

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

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

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?

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

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

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

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

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

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.

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

Read back up a few posts to what Murali said. That gives the precise syntax you need.