I have a case where I create a dynamic Oracle table through a fex and the table name is dynamic to eliminate contention between users. The table name includes the project the user is working on the users name and a date time stamp.

The table needs to populated at the end of my fex and I am trying to do that through a modify statement. The issue is that I need a master file to have the same name as my Oracle table or the insert doesn’t work.

I have done the following and I am not getting an error but now rows are being updated in my table.

SQL SQLORA PREPARE SQLOUT FOR
SELECT TABLE_NAME
FROM USER_TABLES
WHERE TABLE_NAME LIKE 'BWL%'||'&PROJECT'||'%'||'&MOD_BY4'||'%';
END

-*

FILEDEF TAB_NM_VAR DISK TAB_NM_VAR.FTM
-*
TABLE FILE SQLOUT
PRINT
COMPUTE TABLE_NM/A30=TABLE_NAME;
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS TAB_NM_VAR FORMAT ALPHA
END
-RUN

-READ TAB_NM_VAR &TABLE_NM.A30.

-SET &TABLE_NM=TRUNCATE(&TABLE_NM);
-*-*

SQL SQLORA PREPARE SQLOUT FOR
SELECT PROJ_ID,
BUD_RVSN_ID,
ORG_ID,
ACCT_ID,
TOT_BUD_HRS,
TOT_BUD_AMT,
MODIFIED_BY
FROM &TABLE_NM
END


APP ENABLE
APP HOLD BUDGET_UPDATES
-RUN
DYNAM ALLOC FILE &TABLE_NM DS 'WEBFOCUS.APP.BASEAPP.&TABLE_NM.FOCUS' NEW REUSE
DYNAM ALLOC DD HOLDMAST DA 'WEBFOCUS.APP.BASEAPP.MASTER.DATA' SHR REUSE
-RUN
TABLE FILE SQLOUT
PRINT PROJ_ID
BUD_RVSN_ID
ORG_ID
ACCT_ID
TOT_BUD_HRS
TOT_BUD_AMT
MODIFIED_BY
ON TABLE HOLD AS &TABLE_NM FORMAT FOCUS
END
-RUN
APP HOLD

Steve

This message has been edited. Last edited by: <Kathryn Henning>,

APP HOLD XSUSANTEST
TABLE FILE IBISAMP/CAR
SUM DCOST BY COUNTRY BY CAR
ON TABLE HOLD AS CARDATA FORMAT FOCUS
END
-RUN
CMD DIR C:\IBI\APPS\XSUSANTEST\CARDATA.*
gives me this:
0 NUMBER OF RECORDS IN TABLE= 18 LINES= 10
Volume in drive C has no label.
Volume Serial Number is E4E2-4234
Directory of C:\IBI\APPS\XSUSANTEST
11/13/2013 03:25 PM 4,096 cardata.foc
11/13/2013 03:25 PM 214 cardata.mas
2 File(s) 4,310 bytes
0 Dir(s) 46,073,942,016 bytes free

and the master is right there.
The APP HOLD dirname
does all the work, holding both data and master, unless you say otherwise ;
i don't know from your [absent] signature what op sys you're on, but its not windows; i vaguely remember DYNAM ALLOCs from a distant former life.

Try something like this:

  
FI MYMAST DISK mymast.mas
-WRITE MYMAST FILE=PMYMAST, SUFFIX=FIX, SEGNAME=MYMAST, SEGTYPE=S0
-WRITE MYMAST FIELD=PROJ_ID,ALIAS=E01,ACTUAL=A05,USAGE=A05,  $
-WRITE THEMFD FIELD=BUD_RVSN_ID,ALIAS=E02,ACTUAL=A75,USAGE=A75,  $
-RUN

etc...

Fernando