Hi

I have a code like ,
******************************************
SQL SQLORA
SELECT TO_CHAR(trunc((trunc(sysdate,'MM') -1),'MM'),'MM/DD/YYYY') LST_MONFRM,
TO_CHAR(trunc(sysdate,'MM')-1,'MM/DD/YYYY') LST_MONTO FROM DUAL;

TABLE FILE SQLOUT
PRINT
*
ON TABLE SAVE AS HLD
END

-RUN
-READ HLD &LST_MONFRM.A75. &LST_MONTO.A75.

-SET &TXT_FROM = &LST_MONFRM;
-SET &TXT_TO = &LST_MONTO;

***********************************************

here, i am getting an error as

A VALUE IS MISSING FOR: &LST_MONFRM

---------

Any suggestions?

Thanks
Naveen
WF 7.1.4

Hi

Initially the length of the variables in hold file were with the length of A75.

so we had a code like reading 75 characters in the READ command.

But we are having an issue like the length in the hold file got changed to 10 . . . so the read fails resulting in error .

Can any one suggests why it is getting changed so.


Thanks
Naveen.

after the RUN command you might ad the line

? HOLD HLD

you should see the fields and there length that would be available.

From what I see in the SQL code, the truncate function is used on a date field - the output can't be 75 characters for each field. When you SAVE, WebFOCUS tells you the names and formats of the fields in the saved file - adjust your -READ accordingly.

ya initially while fixing the length i printed the hold file and found the length and used it .

But all of a sudden today , its failing and the length is found to be as 10 . . .

Can you tell me wat will be the cause of this change . anything like server side configuration changes or DB level changes . . .

Thanks
Naveen.

It's a SAVE file, not a HOLD file.

Hi Naveen,

U are using the oracle database.If they change query plan,the variable length will vary ....

That is the isssue

Try this code,

SQL SQLORA SET VARCHAR OFF
SQL SQLORA
SELECT TO_CHAR(trunc((trunc(sysdate,'MM') -1),'MM'),'MM/DD/YYYY') LST_MONFRM,
TO_CHAR(trunc(sysdate,'MM')-1,'MM/DD/YYYY') LST_MONTO FROM DUAL;

TABLE FILE SQLOUT
PRINT
*
ON TABLE SAVE AS HLD
END

-RUN

-SET &LST_MONFRM=0;
-SET &LST_MONTO=0;
-READ HLD &LST_MONFRM.A75. &LST_MONTO.A75.

-SET &TXT_FROM = &LST_MONFRM;
-SET &TXT_TO = &LST_MONTO;


Hope this helps,

quote:

READ HLD &LST_MONFRM.A10. &LST_MONTO.A10.

Should sort it. You are writing 20bytes and if you read 150 it wont work.

quote:

READ HLD &LST_MONFRM.A75. &LST_MONTO.A75.

I'm not a dialog manager person, but seems to me the fex error thinks it wants a parameter value supplied for the read statement. Confused in Nebraska.

Navin,

You could always use the syntax that will get WebFOCUS to work out the lengths for you. This way you should not have another problem when someone changes any settings that affect varchar output -

ON TABLE SAVE AS savefilename
END
-RUN
-READ savefilename, &Lst_MonFrm &Lst_MonTo
-? &Lst

The important bit is the comma after the save file name.

T

Tony,

I keep forgetting about that magical comma, but I can't seem to get it to work. This is not giving me the expected results:

TABLE FILE CAR
PRINT 
COUNTRY
MODEL
ON TABLE SAVE AS SAVE1
END
-RUN

-READ SAVE1, &COUNTRY &MODEL

-REPEAT ENDREP1 WHILE &IORETURN EQ 0;
-TYPE COUNTRY: &COUNTRY MODEL: &MODEL
-READ SAVE1, &COUNTRY &MODEL

-ENDREP1

Hmmm, neither can I!

T

Sometimes when I am testing something like this, I do a HOLD FORMAT ALPHA and then a ? HOLD to help me infer something about the output.

Francis,

I ran your code and got:

0 NUMBER OF RECORDS IN TABLE= 18 LINES= 18
ALPHANUMERIC RECORD NAMED SAVE1
0 FIELDNAME ALIAS FORMAT LENGTH
COUNTRY COUNTRY A10 10
MODEL MODEL A24 24
TOTAL 34
COUNTRY: ENGLAND V12XKE AUTO MODEL: ENGLAND XJ12L AUTO
COUNTRY: ENGLAND INTERCEPTOR III MODEL: ENGLAND TR7
COUNTRY: JAPAN B210 2 DOOR AUTO MODEL: JAPAN COROLLA 4 DOOR DIX AUTO
COUNTRY: ITALY 2000 4 DOOR BERLINA MODEL: ITALY 2000 GT VELOCE
COUNTRY: ITALY 2000 SPIDER VELOCE MODEL: ITALY DORA 2 DOOR
COUNTRY: W GERMANY 100 LS 2 DOOR AUTO MODEL: W GERMANY 2002 2 DOOR
COUNTRY: W GERMANY 2002 2 DOOR AUTO MODEL: W GERMANY 3.0 SI 4 DOOR
COUNTRY: W GERMANY 3.0 SI 4 DOOR AUTO MODEL: W GERMANY 530I 4 DOOR
COUNTRY: W GERMANY 530I 4 DOOR AUTO MODEL: FRANCE 504 4 DOOR

What is it that you are not getting that you are expecting?

I ususally do the save part first to get the lengths and then my READ looks something like this:

-READ SAVE1 &COUNTRY.A10 &MODEL.A24

I then don't need a comma.

Hi Navin,
I would save the HOLD file in FORMAT ALPHA, before performing any read statements.

?FF HLD = will show the format of Data in Hold file.

"-RUN" SHOULD BE USED AFTER -READ STATEMENT. THIS -RUN WILL EXECUTE THE STACK & READS THE VALUES INTO &LST -VARIABLES.


Try this Code.

SQL SQLORA SET VARCHAR OFF
SQL SQLORA
SELECT TO_CHAR(trunc((trunc(sysdate,'MM') -1),'MM'),'MM/DD/YYYY') LST_MONFRM,
TO_CHAR(trunc(sysdate,'MM')-1,'MM/DD/YYYY') LST_MONTO FROM DUAL;

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS HLD FORMAT ALPHA
END
-RUN
-* This will show th format of HLD file.
?FF HLD
-RUN
-*
-READ HLD &LST_MONFRM.A75. &LST_MONTO.A75.
-RUN
-SET &TXT_FROM = &LST_MONFRM;
-SET &TXT_TO = &LST_MONTO;
-*
-TYPE &TXT_FROM ;
-TYPE &TXT_TO ;
-EXIT


TryFocus

I use SAVE format all the time for this kind of thing and don't have the hassle of a master. If you put the formats and lengths on the -READ, you won't have a problem.

Ginny,

The results are not what I was expecting.

COUNTRY: ENGLAND V12XKE AUTO MODEL: ENGLAND XJ12L AUTO
COUNTRY: ENGLAND INTERCEPTOR III MODEL: ENGLAND TR7
COUNTRY: JAPAN B210 2 DOOR AUTO MODEL: JAPAN COROLLA 4 DOOR DIX AUTO
COUNTRY: ITALY 2000 4 DOOR BERLINA MODEL: ITALY 2000 GT VELOCE
COUNTRY: ITALY 2000 SPIDER VELOCE MODEL: ITALY DORA 2 DOOR
COUNTRY: W GERMANY 100 LS 2 DOOR AUTO MODEL: W GERMANY 2002 2 DOOR
COUNTRY: W GERMANY 2002 2 DOOR AUTO MODEL: W GERMANY 3.0 SI 4 DOOR
COUNTRY: W GERMANY 3.0 SI 4 DOOR AUTO MODEL: W GERMANY 530I 4 DOOR
COUNTRY: W GERMANY 530I 4 DOOR AUTO MODEL: FRANCE 504 4 DOOR

COUNTRY should be just COUNTRY and MODEL should be just MODEL. It looks like two rows are read for every -READ statement.

According to the manual

quote:

-READ filename[,] [NOCLOSE] &name[.format.][,]...where:

filename[,] Is the name of an external file to read, which must be defined to the operating system. A space after filename denotes a fixed-format file, while a comma after filename denotes a free-format file.

NOCLOSE Keeps the external file open until the -READ operation is complete. Files kept open with NOCLOSE can be closed using the command -CLOSE filename. The option NOCLOSE is available only in OS/390.

&name[,]... Is a list of variables. For free-format files, you may but are not required to separate the variable names with commas.

.format. Is the format of the variable. For free-format files, you do not have to define the length of the variable, but you may.

In our understanding of the syntax, the magical comma after the -READ is supposed to allow you to exclude the format from the variable names in the -READ. We must be misunderstanding the syntax.

I guess the question is "What is a free-format file?".

Hi Naveen,

I hope your problem has got resolved by now.
The reason why you are facing the problem is that you are expecting 75 bytes of data from the columns returned by the SQl, whereas it is not actually returning that much data.

So you need to use RPAD to get this resolved.

Please try this

USE CLEAR
JOIN CLEAR *
SQL SQLORA
SELECT RPAD(TO_CHAR(trunc((trunc(sysdate,'MM') -1),'MM'),'MM/DD/YYYY'),75,' ') LST_MONFRM,
RPAD(TO_CHAR(trunc(sysdate,'MM')-1,'MM/DD/YYYY'),75,' ') LST_MONTO
FROM DUAL
;
TABLE
FILE SQLOUT
PRINT *
ON TABLE SAVE AS DATEVAL
END
-RUN

-READ DATEVAL &LST_MONFRM.A75. &LST_MONTO.A75.

-SET &TXT_FROM = &LST_MONFRM;
-SET &TXT_TO = &LST_MONTO;

-TYPE &TXT_FROM
-TYPE &TXT_TO

Hopefully your problem would get resolved. I tried the same in my machine and it worked.

Thanks, Ved

In the above case, you would be controlling the way you put the data in the file itself.
So you always know how many bytes you are putting in, and consequently, you can expect to read that many bytes while retrieving the data as well.

Here is some quick code to show how free and fix formats in -READ work:

FI WOW DISK wow.dat
-SET &HELLO = ' ';
-RUN
-WRITE WOW XTHIS,XIS,XA,XTEST
-RUN
-READ WOW, &THIS &IS &A &TEST
-TYPE &THIS &IS &A &TEST
-RUN
-READ WOW &HELLO
-TYPE &HELLO

All,

The issue was with the Oracle 10g parameter.
"Cursor Sharing" which was set to similar. Now it gor reset again and the issue got resolved.

But trying to do some coding to resolve this dependancy . . .

Edward,

Thanks for that example. So it appears that if commas separated the HOLD/SAVE file columns, then the -READ without format will work. This is interesting and good news - I will have to give this a try.

Cheers,

hmmm. very interesting indeed. thanks Edward.

Ed it has been 23 years since I worked with you at IBI. Great explanation.

Ira Frankel

HI Naveen

If the length of the variable changes.... Environmnet change only takes responsibility


So check with the DBA team