How to specify the key in schema file

[kamalshil]

Hi,

I am reading from the sequential file and need to load into oracle table doing an upsert mode.

So how can i specify the key columns.

I check forum searched and found the method in below link to specify the key

viewtopic.php?t=133949&highlight=schema+file

But i am getting below error for it

Code: Select all

Sequential_File_2: Import validation failed.
Sequential_File_2: At field "CREATION_DATE": Unrecognized type-specific format properties: {Key}
main_program: Could not check all operators because of previous error(s)

And i have given schema file as below

Code: Select all

record
  {final_delim=end, record_delim='\n', delim=';', null_field='', quote=double}
(
  CREATION_USER:string[max=80] ;
  CREATION_DATE:timestamp {Key} ;
)

Is it the correct way or i need to change it

[kamalshil]

I also tried printing the schema for each operator using $OSH_PRINT_SCHEMAS

But was not able to find any difference how key is specified.

[ray.wurlod]

Read ALL of that other thread. Generally the key is not specified at all in the record schema (you can use a comment if you must).

[kamalshil]

How can i comment it.
Because if i am not importing the meta-data form where it will pick up the internal format.

How can i specify the comments in schema file?
Because i need to do upserts?

[ray.wurlod]

Schema file can use

Code: Select all

 /* C-style comments */

[sureshreddy2009]

Hi,
We implemented the same job reading from sequential file and loading into oracle table with a generic approach
for this we used schema files, here is the procedure.
source schema file format will be like
record
{final_delim=none,delim='|',null_field='',quote=none,record_delim_string='{BEG}'
{
ALT_CD:string[max=4] {quote=none};
LAN_CD:string[max=2] {quote=none};
LAST_TIMESTAMP:timestamp {quote=none};
}

In the above schema file ALT_CD is a key but dont need to mention any column as key as it is reading from a sequential file stage.

[kamalshil]

Yes reading from source we don't require key.
what about target?

I need to write into oracle table with upsert mode.

how to do it through upsert should be implemented,

[sureshreddy2009]

In target also use same type of schema file but you have to change format which is first line in schema files and column names according to target database table, dont need to mention any key here also and we loaded using load method not a upsert option.Try with load option there is nothing wrong using that. Its a happy path to load into table when you are aware of all table structure

[kamalshil]

In target also use same type of schema file but you have to change format which is first line in schema files and column names according to target database table, dont need to mention any key here also and we loaded using load method not a upsert option.Try with load option there is nothing wrong using that. Its a happy path to load into table when you are aware of all table structure

And through which property can we specify the schema file in oracle stage?

[sureshreddy2009]

Basically if columns present in source and oracle are same then dont need to mention any schema file any where while loading.Enable RCP.It will take care otherwise use column export after sequential file stage to convert into string and column import before oracle stage to convert it into desired structure , for transformations use tranformer in between that. there is option schema file option in both column export and column import

[kamalshil]

Basically if columns present in source and oracle are same then dont need to mention any schema file any where while loading.Enable RCP.It will take care otherwise use column export after sequential file stage to convert into string and column import before oracle stage to convert it into desired structure , for transformations use tranformer in between that. there is option schema file option in both column export and column import

For load its working fine now thanks for help.

Now i need to find a method to implement upsert.
Because i have requirement where rows might be there i need to update the value fields as table has primary key and i cannot insert same records.

Any idea on this how to do?

[sureshreddy2009]

Yes ofcourse
It will be done
use upsert method in oracle and use parameterized sql.
Update #table_name# #sql#

pass complete query like set col1=orchestrate.col1, col2=orchestrate.col2 ...where col5=orchestrate.col5 in #sql#

[kamalshil]

Yes ofcourse
It will be done
use upsert method in oracle and use parameterized sql.
Update #table_name# #sql#

pass complete query like set col1=orchestrate.col1, col2=orchestrate.col2 ...where col5=orchestrate.col5 in #sql#

This will make me write the query for all the tables
I have to implement this more generic way how it can be implemented

[sureshreddy2009]

Generic job is some thing you have to use parameters
I dont think there is another way with out parameters

[Sreenivasulu]

It would be 'wonderful' if we are able to define primary keys in a schema file.

Regards
Sreeni