I am facing an issue in reading (-READ command) LONG oracle database column in the FEX.
I have a requirement to generate report output using SQL pass-through method . All my queries will be stored in a Oracle table REPORT_TABLE with ID as key and REP_SQL (LONG datatype) to store SQLS,We have more than 100 reports in the table now.I am planing to use report caster to distribute each report by scheduling a single template file passing report id as a parameter.
my template code look like as given below.

-DEFAULT RPTID = '11'
TABLE FILE REPORT_TABLE
PRINT
NAME
DESCRIPTION
REP_SQL
WHERE ID EQ &RPTID
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
-READ HOLD1 &DUMMY1.A6. &VNAME.A100. DUMMY1.A6. &VDESC.A500. &DUMMY1.A6. &VSQL.A4000.
-TYPE VNAME &VNAME
-TYPE VDESC &VDESC
-TYPE VSQL &VSQL
-* If I try to print the value of &VSQL I am not getting anything, even if , value is available in the database table. Except that all other are coming correctly
-* if I use a TABLE FILE HOLD1 PRINT * END command , It is correctly printing even LONG value
-* Issue 2 is in the next step . I chnaged the REP_SQL as varchar2(4000) and now I am able to read the SQL, but , in the SQL if I reference any LONG columns of any tables , it will give strange errors not for LONG columns but the columns next to that.
-*
ENGINE SQLORA SET DEFAULT_CONNECTION myconn
SQL SQLORA PREPARE SQLOUT FOR
&VSQL.EVAL
END
TABLE FILE SQLOUT
HEADING
"&VNAME"
"Run date: <+0>&DATEtrMDYY <+0>"
"&VDESC"
PRINT
*
END

We have some reports which use LONG database fields from Oracle. We have them defined as TX fields in the master file desription.

For example:

FIELD=LONGDESC ,ALIAS=LONGDESC ,TX250 ,TX ,MISSING=ON , TITLE='Description' ,DESC='Description',$

We need to do some additional processing to the contents of the fields so we also create a HOLD file using these fields. Using the TX250 format gets all the contents of the LONG database field into the HOLD file but breaks it into multiple lines with a max of 250 characters per line. We then use -READ to massage the data.

This is the only way we were able to use DM to do -READs on the data. 4000 characters was just way too long.

I hope this helps.

Hi Mickey,
I have changed my master file desc as TX250 and -READ variable format is A4000 (Tried TX250 but gave me some error as UNRECOGNIZED FORMAT OF AMPER VARIABLE IN -READ: -READ HOLD1 OPEN
&VSQL.TX250.).While keeping the VSQL variable as A4000 I am able to retrive only the first line of the query.
Current master file setting is as below,
FIELD=REP_SQL ,REP_SQL,
TX250 ,TX ,MISSING=OFF,$

New sample code as below,

TABLE FILE REPORT_TABLE
PRINT
REP_SQL
WHERE ID=1
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
-READ HOLD1 OPEN &VSQL.A4000.
-TYPE VSQL &VSQL
-EXIT

quote:

FORMAT OF AMPER VARIABLE IN -READ: -READ HOLD1 OPEN
&VSQL.TX250.).

Johney,

Try changing your variable to

 &VSQL.A250. 

when you use the TX field.

Fernando

Johney,

What exactly do you want as the final output for the value of REP_SQL?

In order to get ALL parts of the REP_SQL from the HOLD1 file, you need to use A250 for the format of your variable and then use a loop around the -READ statement to read each line of the hold file until there are no more lines in the HOLD1 file. This is because using TX250 will break the REP_SQL value into multiple lines in the HOLD1 file. Edit the HOLD1 file and you will see what I mean.

Try this:

TABLE FILE REPORT_TABLE
PRINT
REP_SQL
WHERE ID=1
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN

ENGINE SQLORA SET DEFAULT_CONNECTION myconn
SQL SQLORA PREPARE SQLOUT FOR
-BEGLOOP
-READ &VSQL.A250.
-IF (&IORETURN NE 0) GOTO ENDLOOP;
&VSQL
-GOTO BEGLOOP
-ENDLOOP
END
TABLE FILE SQLOUT
HEADING
"&VNAME"
"Run date: <+0>&DATEtrMDYY <+0>"
"&VDESC"
PRINT
*
END

Thanks Fernando and Mickey . I tried your method of changing the format to A250 but it was not giving me the full text.Then I tried following but still I have the formating issues.

I have changed my master entry as ,

FIELD=REP_SQL, ALIAS=REP_SQL, USAGE=A3968, ACTUAL=A4000V, MISSING=ON, $

FEX Code as below,

TABLE FILE REPORT_TABLE
PRINT
REP_SQL
WHERE ID=1
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
-READ HOLD1 &VSQL.A3968.
-TYPE &VSQL
-EXIT
ENGINE SQLORA SET DEFAULT_CONNECTION myconn
SQL SQLORA
&VSQL;
TABLE FILE SQLOUT
PRINT
*
END

It is working for me if the REP_SQL was stored in the table after removing newline and tab characters.

Any suggestions ?

Johney,
You might want to check the contents of the hold1 file afer it gets created.
So, after you did:

TABLE FILE REPORT_TABLE
PRINT 
REP_SQL
WHERE ID=1
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN

Do a !cat hold1.ftm to see what's in the hold file.
My guess is that everything you heed is in this file.
If that's the case I wonder if the following code would work:

TABLE FILE REPORT_TABLE
PRINT 
REP_SQL
WHERE ID=1
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
ENGINE SQLORA SET DEFAULT_CONNECTION myconn
SQL SQLORA
-INCLUDE hold1.ftm
;
TABLE FILE SQLOUT
PRINT
*
END

And, if you have other LONG or TX fields in the final request, make sure there is only 1 such field in the request and also make sure it is the very last field in the resulting output. Reason for this is that a hold file may containno more than 1 tx-field in the output and is has to be the last (rightmost) column. And the result of the sql query is always a hold file (the sqlout file).

Hi GamP,

I made it my master file defenition as below,

FIELD=REP_SQL, ALIAS=REP_SQL, USAGE=TX256, ACTUAL=TX, $

And now I am able to print the entire SQLs (I tried upto 19000 characters ,and obviosly it is more than that of my requirement.Thanks a lot)

There is one %$ in the include result , which gives me an error,
ORA-00911: invalid character : Erroneous character: % SQLPREPARE error.

Could you please help me to correct this also ?

Thanks a lot....!!!

I'm curious as to why using TX256, TX worked for you as opposed to TX250, TX. Either one should have given you all the values.

In order to eliminate the one record with the %$ value, you could use the LOOP with the -READ and do an -IF test on the record to decide whether or not to include it in the SQL statements. We use a different method to parse the HOLD file but we have to deal with the %$ as well. We simply exclude the record that has this since it appears on a line by itself.

Hi Mickey,
The master file defenition I used is the default when I created a synonym against the database table with a LONG datatype.

My only issue now is to remove the unwanted %$ when using the -INCLUDE ftm statements to process the SQL regest.

Any suggestions ?

You cannot exclude that line by simply using the -INCLUDE of the HOLD file. You need to read through the HOLD file either with a Dialogue Manager loop or use TABLE FILE to process the HOLD file.

Thanks everybody ,

I achieved what I was looking for .I am sharing it with you.

My requirement was to store an SQL query which can grow upto 15000 and execute this SQLs using sqlpassthru.

My database column is still LONG and I changed my master file defenition only, as ACTUAL=15000 , USAGE=15000

Now I used the same steps which I described earlier

TABLE FILE REPORT_TABLE
PRINT
REP_SQL
WHERE ID=1
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
ENGINE SQLORA SET DEFAULT_CONNECTION myconn
SQL SQLORA
-INCLUDE hold1.ftm
;
TABLE FILE SQLOUT
PRINT
*
END

It is working for me now ... Thanks a lot once again to all whoever involved in the discussion...

Regards,
Johney