Hello,

Has anybody tried holding a file with an output format of SQLORA? The documentation states, "Creates an Oracle table, if you have the Oracle Data Adapter and permission to create tables."

For example, I would like to output the CAR file as an oracle table and make it available on the Oracle database.

How do you define which database, username, p/w and schema to use?

Thanks,
Dan

Try this:

SET SQLENGINE=SQLORA
SQL SET SERVER CBMLOL
-RUN

SQL SET CONNECTION_ATTRIBUTES CBMLOL/BMLIBI,BMLIBI
-RUN

In the SET CONNECTION_ATTRIBUTES command, one of the values is the User ID, one is the password. If the SET CONNECTION_ATTRIBUTES command is set in edasprof.prf, then I don't think you need it in the fex.

The UserID you're logging-in to the server with requires create table access for the hold file creation to work.

Thanks Francis. It worked.

How do you insert into an existing Oracle table the data that you get when you run the procedure?

with MODIFY!

-*XYZ is an input-file of any format
TABLE FILE XYZ
PRINT *
BY KEYXYZ
ON TABLE HOLD AS 'ORAIN'
END
-RUN
-*ORAXYZ is an existing Oracle table with existing AFD and MFD
MODIFY FILE ORAXYZ
FIXFORM FROM ORAIN
MATCH KEYXYZ
ON MATCH UPDATE FIELD1 FIELD2 etc.
ON NOMATCH INCLUDE
LOG DUPL MSG OFF
DATA ON ORAIN
END
-RUN

I hope this helps

Felix

If all you are doing is inserting rows, after the MODIFY FILE statement, insert:

SQL SET LOADONLY

This will dramatically improve the performace of the load as MODIFY will not do an initial read to see if the row exists. I remove the ON MATCH UPDATE and after the ON NOMATCH INCLUDE insert a ON MATCH/NOMATCH GOTO TOP.

The other option is to use a mixture of Dialogue Manager and SQL. This is also a lot faster than MODIFY if you have a lot of rows to update or want to update rows based on partial keys and other conditions. For example:

-UPDLOOP
-READ HOLD1 NOCLOSE &CASE.8. &RENEWDT.8.
-IF &IORETURN NE 0 THEN GOTO UPDEND;
-*
 SET SQLENGINE=SQLORA
 SQL SET SERVER &&KIDS_SERVER
 SQL UPDATE TCASE
        SET RENEWAL_DT = TO_DATE('&RENEWDT.EVAL','YYYYMMDD'),
            INTFC_TYP_CDE = 15671
      WHERE CAS_ID = &CASE;
 SQL COMMIT ;
 END
-*
 SET SQLENGINE=SQLORA
 SQL SET SERVER &&KIDS_SERVER
 SQL UPDATE TCL_ADPSUBSIDY
        SET RENEWAL_DT = TO_DATE('&RENEWDT.EVAL','YYYYMMDD'),
            INTFC_TYP_CDE = 15671
      WHERE (CAS_ID = &CASE)
        AND (STRT_DT <= TO_DATE('&RENEWDT.EVAL','YYYYMMDD'))
        AND (END_DT >= TO_DATE('&RENEWDT.EVAL','YYYYMMDD'))
        AND (SUBSDY_DETL_TYP_CD IN (2193,2195,7238))
        AND (APRV_DT IS NOT NULL);
 SQL COMMIT ;
 END
-RUN
-GOTO UPDLOOP
-*
-UPDEND
-CLOSE HOLD1

In the first update, the key is CAS_ID.

In the second, the key is CAS_ID, CL_ID and SEQ_NO. Here I needed to update renewal date for all rows for a given CAS_ID where the other conditions were met.

Just some other options.

Thanks for your suggestions. I'll give them a try.

How do I select which schema to use? We have a database with multiple schema. However, when you define a adapter, you only get to use 1 (depending on the usr/pwd). Currently, we ready have an adapter existing for that database for a particular schema.

I had the same problem to solve and found the following trick: per adapter you may define several connections but only one per TNS name (we are working with security on on the WebFOCUS server and explicit security for the Orcale adapter).
Now the trick works as follows: per schema I defined a TNS name for the Oracle client used by WebFOCUS in file tnsnames.ora (for simplicity I used the schema names)
and afterwards I created the respective connections per schema/TNS name for the adapter.
In the fex I select simply the the oracle schema to use by selecting the respective connection:

ENGINE SQLORA SET DEFAULT_CONNECTION schemaxyz

make a test!

best regards

Felix

Felix,

You can also explicitly get to the schema by the login username/password.

We defined an Oracle adapter using user1/pwd1 to connect to a DBNAME1/SCHEMA1.

Now on the FEX, we used:

SQL SET SERVER DBNAME1
-RUN
SQL SET CONNECTION_ATTRIBUTES DBNAME1/user2,pwd2
-RUN

User2 connects to the same DB but a different schema.

Thanks,
Dan