Reading SQL from file to extract data from Oracle Tables

[parameswar]

Hi All,

I have to extract data from oracle tables. SQL is given in a .txt file. How can I read that file from Oracle stage instead of copying the SQL to extract data from tables? Is there any other stage supports to extract data from table by reading the sql file ?


Thanks,
Parameswar

[Maveric]

Pass the SQL as a parameter. Read the statement from the file in the sequence and pass it to the job.

[parameswar]

Pass the SQL as a parameter. Read the statement from the file in the sequence and pass it to the job.

Hi Maveric,

In job how the file can be read which contains SQL in database stages ?

Could you please give an idea of job design to raed SQL file from datastage.

Thanks,
Parameswar

[ray.wurlod]

Use a job parameter for the entire SQL statement. Mark the SQL type "user defined".

Use an Execute Command activity to read the file and pass the output of that command, stripped of line terminators (or equivalents) as the value for the job parameter.

[parameswar]

Use a job parameter for the entire SQL statement. Mark the SQL type "user defined".

Use an Execute Command activity to read the file and pass the output of that command, stripped of line terminators (or equivalents) as the value for the job parameter.

Hi Ray,

Thanks for your input.
I am passing output of the Execute Commannd activity to the parameter defined in job. But I am not getting how to strip line terminators (or equivalents) as the value for the job parameter .

I am getting correct output ( SQL query) from Execute Commannd activity . After taht Sequence is aborting with the below warning:
Controller problem: Error calling DSSetParam(sql), code=-4 ParamValue/Limitvalue is not appropriate]

Sequence is aborting, control is not going to Job. Please help me in solving this problem.

Thanks,
Parameswar

[kool78]

Use a job parameter for the entire SQL statement. Mark the SQL type "user defined".

Use an Execute Command activity to read the file and pass the output of that command, stripped of line terminators (or equivalents) as the value for the job parameter.

Hi Ray,

Thanks for your input.
I am passing output of the Execute Commannd activity to the parameter defined in job. But I am not getting how to strip line terminators (or equivalents) as the value for the job parameter .

I am getting correct output ( SQL query) from Execute Commannd activity . After taht Sequence is aborting with the below warning:
Controller problem: Error calling DSSetParam(sql), code=-4 ParamValue/Limitvalue is not appropriate]

Sequence is aborting, control is not going to Job. Please help me in solving this problem.

Thanks,
Parameswar

Can you provide more details on the command that you used in Execute Command Activity.

[ray.wurlod]

Is your job parameter of type String?

Post your actual expression used when setting the job parameter. You may need to remove @FM characters from the command output.

[parameswar]

Is your job parameter of type String?

Post your actual expression used when setting the job parameter. You may need to remove @FM characters from the command output.

Hi,

My Job Parameter is of string type.
In execute command activity I have given the command cat and the parameter is the file path which contains the sql statement.
From director log Iam getting below output from execute command activity which I need to pass to my job parameter:
SELECT
RPAD(NVL(LFCFL00.FC0_ACCOUNT_NUMBER,' '),15,' ') ACCOUNT_NUMBER,
RPAD(NVL(LFCFL00.FC0_FACILITY_ID,' '),13,' ' ) FACILITY_ID,
RPAD( NVL(LFCFL00.FC0_RELATED_TO_FAC,' '),13,' ') RELATED_TO_FAC,
RPAD( DECODE(GREATEST(NVL(LFCFL00.FC0_CREATE_DATE,0),0),0, '00000000',
DECODE(SUBSTR(TRIM(TO_CHAR(LFCFL00.FC0_CREATE_DATE, '0000000')), 1, 1), '1', '20','19')||
SUBSTR(TRIM(TO_CHAR(LFCFL00.FC0_CREATE_DATE, '0000000')), 2, 6)
) ,8,' ') CREATE_DATE,
RPAD( DECODE(GREATEST(NVL(LFCFL00.FC0_EXPIRY_DATE,0),0),0, '00000000',
DECODE(SUBSTR(TRIM(TO_CHAR(LFCFL00.FC0_EXPIRY_DATE, '0000000')), 1, 1), '1', '20','19')||
SUBSTR(TRIM(TO_CHAR(LFCFL00.FC0_EXPIRY_DATE, '0000000')), 2, 6)
),8,' ') EXPIRY_DATE,
RPAD( DECODE(NVL(LFCFL00.FC0_REVOLVING_IND,' '),'Y','Y','N'),1,' ') REVOLVING_IND,
RPAD( DECODE(NVL(LFCFL00.FC0_APPROVE_IND,' '),'Y','Y','N'),1,' ') APPROVE_IND,
RPAD( DECODE(NVL(LFCFL00.FC0_BLOCKED_IND,' '),'Y','Y','N'),1,' ') BLOCKED_IND,
RPAD( NVL(LFCFL00.XFC_REASON_BLOCK,' '),13,' ') REASON_BLOCK,
RPAD( NVL(LFCFL00.FC0_PENDING_IND,' ') ,1,' ') PENDING_IND,
RPAD( NVL(LFCFL00.FC0_OBL_CODE_01,' ') ,3,' ') OBL_CODE_01,
RPAD( TRIM(TO_CHAR(GREATEST(NVL(LACDF01.AD1_MARGIN_PCT,0),0),'00000.00')),8,' ') AD1_MARGIN_PCT,
RPAD( TRIM(TO_CHAR(GREATEST(NVL(LFCFL00.FC0_CR_AMT_LNE_BSE,0),0),'0000000000000.00')),16,' ') CR_AMT_LNE_BSE,
RPAD( TRIM(TO_CHAR(GREATEST(NVL(LFC00.XF00_USD_AMT_LN_FR,0),0),'0000000000000.00')),16,' ') USD_AMT_LN_FR,
RPAD( TRIM(TO_CHAR(GREATEST(NVL(LFC00.XF00_USD_AMT_LN_BS,0),0),'0000000000000.00')),16,' ') USD_AMT_LN_BS
FROM
LFCFL00 LFCFL00 ,LACTM00 LACTM00 ,LFC00 LFC00 ,LACDF01 LACDF01
WHERE
HEXTORAW(RAWTOHEX(LFCFL00.FC0_ACCOUNT_NUMBER||'00'||' ')) = LACTM00.XLACTM00_KEY
AND LFCFL00.FC0_LOCATION_CODE = LACTM00.AM0_WRKR_LOCATION
AND XLFCFL00_KEY = XLFC00_KEY (+)
AND HEXTORAW(RAWTOHEX(LFCFL00.FC0_ACCOUNT_NUMBER||'01'||LFCFL00.FC0_OBL_CODE_01||LFCFL00.FC0_FACILITY_ID))
= LACDF01.XLACDF01_KEY (+)

Even when I am passing the above sql statement directly in Job parameter, It is aborting with fatal error SrcLfcfloo: Unrecognized argument: ),15, . But this select statement is working fine when I am using directly in oracle stage.


Thanks,
Parameswar

[kool78]

Is your job parameter of type String?

Post your actual expression used when setting the job parameter. You may need to remove @FM characters from the command output.

Hi,

My Job Parameter is of string type.
In execute command activity I have given the command cat and the parameter is the file path which contains the sql statement.
From director log Iam getting below output from execute command activity which I need to pass to my job parameter:
SELECT
RPAD(NVL(LFCFL00.FC0_ACCOUNT_NUMBER,' '),15,' ') ACCOUNT_NUMBER,
RPAD(NVL(LFCFL00.FC0_FACILITY_ID,' '),13,' ' ) FACILITY_ID,
RPAD( NVL(LFCFL00.FC0_RELATED_TO_FAC,' '),13,' ') RELATED_TO_FAC,
RPAD( DECODE(GREATEST(NVL(LFCFL00.FC0_CREATE_DATE,0),0),0, '00000000',
DECODE(SUBSTR(TRIM(TO_CHAR(LFCFL00.FC0_CREATE_DATE, '0000000')), 1, 1), '1', '20','19')||
SUBSTR(TRIM(TO_CHAR(LFCFL00.FC0_CREATE_DATE, '0000000')), 2, 6)
) ,8,' ') CREATE_DATE,
RPAD( DECODE(GREATEST(NVL(LFCFL00.FC0_EXPIRY_DATE,0),0),0, '00000000',
DECODE(SUBSTR(TRIM(TO_CHAR(LFCFL00.FC0_EXPIRY_DATE, '0000000')), 1, 1), '1', '20','19')||
SUBSTR(TRIM(TO_CHAR(LFCFL00.FC0_EXPIRY_DATE, '0000000')), 2, 6)
),8,' ') EXPIRY_DATE,
RPAD( DECODE(NVL(LFCFL00.FC0_REVOLVING_IND,' '),'Y','Y','N'),1,' ') REVOLVING_IND,
RPAD( DECODE(NVL(LFCFL00.FC0_APPROVE_IND,' '),'Y','Y','N'),1,' ') APPROVE_IND,
RPAD( DECODE(NVL(LFCFL00.FC0_BLOCKED_IND,' '),'Y','Y','N'),1,' ') BLOCKED_IND,
RPAD( NVL(LFCFL00.XFC_REASON_BLOCK,' '),13,' ') REASON_BLOCK,
RPAD( NVL(LFCFL00.FC0_PENDING_IND,' ') ,1,' ') PENDING_IND,
RPAD( NVL(LFCFL00.FC0_OBL_CODE_01,' ') ,3,' ') OBL_CODE_01,
RPAD( TRIM(TO_CHAR(GREATEST(NVL(LACDF01.AD1_MARGIN_PCT,0),0),'00000.00')),8,' ') AD1_MARGIN_PCT,
RPAD( TRIM(TO_CHAR(GREATEST(NVL(LFCFL00.FC0_CR_AMT_LNE_BSE,0),0),'0000000000000.00')),16,' ') CR_AMT_LNE_BSE,
RPAD( TRIM(TO_CHAR(GREATEST(NVL(LFC00.XF00_USD_AMT_LN_FR,0),0),'0000000000000.00')),16,' ') USD_AMT_LN_FR,
RPAD( TRIM(TO_CHAR(GREATEST(NVL(LFC00.XF00_USD_AMT_LN_BS,0),0),'0000000000000.00')),16,' ') USD_AMT_LN_BS
FROM
LFCFL00 LFCFL00 ,LACTM00 LACTM00 ,LFC00 LFC00 ,LACDF01 LACDF01
WHERE
HEXTORAW(RAWTOHEX(LFCFL00.FC0_ACCOUNT_NUMBER||'00'||' ')) = LACTM00.XLACTM00_KEY
AND LFCFL00.FC0_LOCATION_CODE = LACTM00.AM0_WRKR_LOCATION
AND XLFCFL00_KEY = XLFC00_KEY (+)
AND HEXTORAW(RAWTOHEX(LFCFL00.FC0_ACCOUNT_NUMBER||'01'||LFCFL00.FC0_OBL_CODE_01||LFCFL00.FC0_FACILITY_ID))
= LACDF01.XLACDF01_KEY (+)

Even when I am passing the above sql statement directly in Job parameter, It is aborting with fatal error SrcLfcfloo: Unrecognized argument: ),15, . But this select statement is working fine when I am using directly in oracle stage.


Thanks,
Parameswar

when passing the parameter value to the job in sequence , as ray said do the following to remove the @FM characters.

Convert(@FM," ",Execute_Command.$CommandOutput

let us know if this works for u.

[parameswar]

Is your job parameter of type String?

Post your actual expression used when setting the job parameter. You may need to remove @FM characters from the command output.

Hi,

My Job Parameter is of string type.
In execute command activity I have given the command cat and the parameter is the file path which contains the sql statement.
From director log Iam getting below output from execute command activity which I need to pass to my job parameter:
SELECT
RPAD(NVL(LFCFL00.FC0_ACCOUNT_NUMBER,' '),15,' ') ACCOUNT_NUMBER,
RPAD(NVL(LFCFL00.FC0_FACILITY_ID,' '),13,' ' ) FACILITY_ID,
RPAD( NVL(LFCFL00.FC0_RELATED_TO_FAC,' '),13,' ') RELATED_TO_FAC,
RPAD( DECODE(GREATEST(NVL(LFCFL00.FC0_CREATE_DATE,0),0),0, '00000000',
DECODE(SUBSTR(TRIM(TO_CHAR(LFCFL00.FC0_CREATE_DATE, '0000000')), 1, 1), '1', '20','19')||
SUBSTR(TRIM(TO_CHAR(LFCFL00.FC0_CREATE_DATE, '0000000')), 2, 6)
) ,8,' ') CREATE_DATE,
RPAD( DECODE(GREATEST(NVL(LFCFL00.FC0_EXPIRY_DATE,0),0),0, '00000000',
DECODE(SUBSTR(TRIM(TO_CHAR(LFCFL00.FC0_EXPIRY_DATE, '0000000')), 1, 1), '1', '20','19')||
SUBSTR(TRIM(TO_CHAR(LFCFL00.FC0_EXPIRY_DATE, '0000000')), 2, 6)
),8,' ') EXPIRY_DATE,
RPAD( DECODE(NVL(LFCFL00.FC0_REVOLVING_IND,' '),'Y','Y','N'),1,' ') REVOLVING_IND,
RPAD( DECODE(NVL(LFCFL00.FC0_APPROVE_IND,' '),'Y','Y','N'),1,' ') APPROVE_IND,
RPAD( DECODE(NVL(LFCFL00.FC0_BLOCKED_IND,' '),'Y','Y','N'),1,' ') BLOCKED_IND,
RPAD( NVL(LFCFL00.XFC_REASON_BLOCK,' '),13,' ') REASON_BLOCK,
RPAD( NVL(LFCFL00.FC0_PENDING_IND,' ') ,1,' ') PENDING_IND,
RPAD( NVL(LFCFL00.FC0_OBL_CODE_01,' ') ,3,' ') OBL_CODE_01,
RPAD( TRIM(TO_CHAR(GREATEST(NVL(LACDF01.AD1_MARGIN_PCT,0),0),'00000.00')),8,' ') AD1_MARGIN_PCT,
RPAD( TRIM(TO_CHAR(GREATEST(NVL(LFCFL00.FC0_CR_AMT_LNE_BSE,0),0),'0000000000000.00')),16,' ') CR_AMT_LNE_BSE,
RPAD( TRIM(TO_CHAR(GREATEST(NVL(LFC00.XF00_USD_AMT_LN_FR,0),0),'0000000000000.00')),16,' ') USD_AMT_LN_FR,
RPAD( TRIM(TO_CHAR(GREATEST(NVL(LFC00.XF00_USD_AMT_LN_BS,0),0),'0000000000000.00')),16,' ') USD_AMT_LN_BS
FROM
LFCFL00 LFCFL00 ,LACTM00 LACTM00 ,LFC00 LFC00 ,LACDF01 LACDF01
WHERE
HEXTORAW(RAWTOHEX(LFCFL00.FC0_ACCOUNT_NUMBER||'00'||' ')) = LACTM00.XLACTM00_KEY
AND LFCFL00.FC0_LOCATION_CODE = LACTM00.AM0_WRKR_LOCATION
AND XLFCFL00_KEY = XLFC00_KEY (+)
AND HEXTORAW(RAWTOHEX(LFCFL00.FC0_ACCOUNT_NUMBER||'01'||LFCFL00.FC0_OBL_CODE_01||LFCFL00.FC0_FACILITY_ID))
= LACDF01.XLACDF01_KEY (+)

Even when I am passing the above sql statement directly in Job parameter, It is aborting with fatal error SrcLfcfloo: Unrecognized argument: ),15, . But this select statement is working fine when I am using directly in oracle stage.


Thanks,
Parameswar

when passing the parameter value to the job in sequence , as ray said do the following to remove the @FM characters.

Convert(@FM," ",Execute_Command.$CommandOutput

let us know if this works for u.

Hi,

I am getting the same fatal error. This query is working fine in oracle stage itself, but when I am passing it through job parameter job is aborting with the same fata error SrcLfcfloo: Unrecognized argument: ),15.

Any suggestion to execute the sql query through job parameter ?

Thanks,
Parameswar

[ray.wurlod]

Can you please post the "job started" event from the job log (or status) view so that the actual value passed to the parameter may be recognized?

And, when you do, please verify that the SQL is both complete and as you intended.

[parameswar]

Can you please post the "job started" event from the job log (or status) view so that the actual value passed to the parameter may be recognized?

And, when you do, please verify that the SQL is both complete and as you intended.

Hi Roy,
In Job started event in job log, I am getting the value of SQL parameter as given below:

SQL = SELECT RPAD(NVL(LFCFL00.FC0_ACCOUNT_NUMBER,' '),15,' ') FC0_ACCOUNT_NUMBER, RPAD(NVL(LFCFL00.FC0_FACILITY_ID,' '),13,' ' ) FC0_FACILITY_ID, RPAD( NVL(LFCFL00.FC0_RELATED_TO_FAC,' '),13,' ') FC0_RELATED_TO_FAC, RPAD( DECODE(GREATEST(NVL(LFCFL00.FC0_CREATE_DATE,0),0),0, '00000000', DECODE(SUBSTR(TRIM(TO_CHAR(LFCFL00.FC0_CREATE_DATE, '0000000')), 1, 1), '1', '20','19')|| SUBSTR(TRIM(TO_CHAR(LFCFL00.FC0_CREATE_DATE, '0000000')), 2, 6) ) ,8,' ') FC0_CREATE_DATE, RPAD( DECODE(GREATEST(NVL(LFCFL00.FC0_EXPIRY_DATE,0),0),0, '00000000', DECODE(SUBSTR(TRIM(TO_CHAR(LFCFL00.FC0_EXPIRY_DATE, '0000000')), 1, 1), '1', '20','19')|| SUBSTR(TRIM(TO_CHAR(LFCFL00.FC0_EXPIRY_DATE, '0000000')), 2, 6) ),8,' ') FC0_EXPIRY_DATE, RPAD( DECODE(NVL(LFCFL00.FC0_REVOLVING_IND,' '),'Y','Y','N'),1,' ') FC0_REVOLVING_IND, RPAD( DECODE(NVL(LFCFL00.FC0_APPROVE_IND,' '),'Y','Y','N'),1,' ') FC0_APPROVE_IND, RPAD( DECODE(NVL(LFCFL00.FC0_BLOCKED_IND,' '),'Y','Y','N'),1,' ') FC0_BLOCKED_IND, RPAD( NVL(LFCFL00.XFC_REASON_BLOCK,' '),13,' ') XFC_REASON_BLOCK, RPAD( NVL(LFCFL00.FC0_PENDING_IND,' ') ,1,' ') FC0_PENDING_IND, RPAD( NVL(LFCFL00.FC0_OBL_CODE_01,' ') ,3,' ') FC0_OBL_CODE_01, RPAD( TRIM(TO_CHAR(GREATEST(NVL(LACDF01.AD1_MARGIN_PCT,0),0),'00000.00')),8,' ') AD1_MARGIN_PCT, RPAD( TRIM(TO_CHAR(GREATEST(NVL(LFCFL00.FC0_CR_AMT_LNE_BSE,0),0),'0000000000000.00')),16,' ') FC0_CR_AMT_LNE_BSE, RPAD( TRIM(TO_CHAR(GREATEST(NVL(LFC00.XF00_USD_AMT_LN_FR,0),0),'0000000000000.00')),16,' ') XF00_USD_AMT_LN_FR, RPAD( TRIM(TO_CHAR(GREATEST(NVL(LFC00.XF00_USD_AMT_LN_BS,0),0),'0000000000000.00')),16,' ') XF00_USD_AMT_LN_BS FROM LFCFL00 LFCFL00 ,LACTM00 LACTM00 ,LFC00 LFC00 ,LACDF01 LACDF01 WHERE HEXTORAW(RAWTOHEX(LFCFL00.FC0_ACCOUNT_NUMBER||'00'||' ')) = LACTM00.XLACTM00_KEY AND LFCFL00.FC0_LOCATION_CODE = LACTM00.AM0_WRKR_LOCATION AND XLFCFL00_KEY = XLFC00_KEY (+) AND HEXTORAW(RAWTOHEX(LFCFL00.FC0_ACCOUNT_NUMBER||'01'||LFCFL00.FC0_OBL_CODE_01||LFCFL00.FC0_FACILITY_ID)) = LACDF01.XLACDF01_KEY (+)

I surprised that this query is working in oracle stage, but not when passing through parameter.

Thanks,
Parameswar

[ray.wurlod]

OK, we've proven that the SQL is being passed to the job successfully. The next step is to establish that the SQL is being passed from the job to Oracle. You will need to enlist the help of your DBA to monitor the connection and record the SQL that was actually received by the Oracle database server. Then compare that against what you think it ought to be.

[parameswar]

OK, we've proven that the SQL is being passed to the job successfully. The next step is to establish that the SQL is being passed from the job to Oracle. You will need to enlist the help of your DBA to monitor the connection and record the SQL that was actually received by the Oracle database server. Then compare that against what you think it ought to be.

Hi,

Passing a sql file as parameter is working fine if file contains very simple statement like " select ID, NAME, TR_DATE,AMT from Table1 ". Job is running successfully without any warning. But when I am passing any complex query it is throwing error . Anyway I will try with our DBA and if solved will post the steps.

Thanks you all for inputs.

Regards,
Parameswar

[kool78]

OK, we've proven that the SQL is being passed to the job successfully. The next step is to establish that the SQL is being passed from the job to Oracle. You will need to enlist the help of your DBA to monitor the connection and record the SQL that was actually received by the Oracle database server. Then compare that against what you think it ought to be.

Hi,

Passing a sql file as parameter is working fine if file contains very simple statement like " select ID, NAME, TR_DATE,AMT from Table1 ". Job is running successfully without any warning. But when I am passing any complex query it is throwing error . Anyway I will try with our DBA and if solved will post the steps.

Thanks you all for inputs.

Regards,
Parameswar

try the above simple query with a WHERE condition in it. let us know how its treating the where condition when passed from a seq file.