Re: How to create file in oracle using sqlplus

From: <pete_lucuk_at_my-dejanews.com>
Date: Mon, 05 Apr 1999 15:43:33 GMT
Message-ID: <7ealn3$scg$1_at_nnrp1.dejanews.com>

Here is an example in UNIX. It also does some other stuff too that you can cut out.

rm /var/opt/idwif/work/export_files/1agg_fact_mm.dat

mknod /var/opt/idwif/work/export_files/1agg_fact_mm.dat p
gzip -c < /var/opt/idwif/work/export_files/1agg_fact_mm.dat >
/var/opt/idwif/work/export_files/1agg_fact_mm.dat.gz &

sqlplus -s / <<!
set ver off;
set feed off;
set echo off;
set hea off;
set newpage 0;
set pagesize 0;
ttitle off;
set linesize 400
spool 1agg_fact_mm.dat

select /*+ use_hash(a,b) */
a.CAPTL_ID||
','||a.CUST_ID||
','||a.FUNC_ID||
','||a.GENRC_DMNSN_1_ID||
','||a.GENRC_DMNSN_1_TYPE_ID||
','||a.GENRC_DMNSN_2_ID||
','||a.GENRC_DMNSN_2_TYPE_ID||
','||a.GENRC_DMNSN_3_ID||
','||a.GENRC_DMNSN_3_TYPE_ID||
','||a.GEO_ID||
','||a.LEGAL_ENT_ID||
','||a.MEASR_ID||
','||a.MM_HYBRD_ID||
','||a.ORG_ID||
','||a.PROD_ID||
','||a.PROFT_CTR_ID||
','||a.SITE_ID||
','||a.TIME_PERD_ID||
','||a.TRADE_CHANL_ID||
','||'202'||
','||'VM'||
','||a.ISO_CRNCY_CODE_CHAR||
','||a.SRCE_SYS_ID||
','||'202'||
','||a.FACT_QLTY_CODE||
','||a.MKT_CLASS_CODE||
','||a.DEMND_PLAN_CUST_GRP_CODE||
','||a.FACT_AGG_FLAG||
','||a.TIME_PERD_TYPE_CODE||
','||a.TIME_PERD_END_DATE||
','||a.DUE_PERD||
','||a.FACT_AMT_1||
','||a.FACT_AMT_3||
','||a.FACT_AMT_2||
','||a.FACT_AMT_4||
','||a.FACT_AMT_5||
','||a.FACT_AMT_6||
','||a.FACT_AMT_7||
','||a.FACT_AMT_8||
','||a.FACT_AMT_9||
','||a.FACT_AMT_10||
','||a.FACT_AMT_11||
','||a.FACT_AMT_12||
','||a.FACT_AMT_13||
','||a.FACT_AMT_14||
','||a.FACT_AMT_15||
','||b.FACT_AMT_1||
','||b.FACT_AMT_2||','

from agg_fact_mm a, agg_fact_mm b

where a.prod_id=b.prod_id
and   a.geo_id=b.geo_id
and   a.time_perd_id=b.time_perd_id
and   a.fact_type_code='PM'
and   b.fact_type_code='PZ'

/
spool off
exit
!

#uncompress the spool file into the named pipe gzip -cd < /var/opt/idwif/work/export_files/1agg_fact_mm.dat.gz > /var/opt/idwif/work/export_files/1agg_fact_mm.dat & #create another named pipiie
mknod /var/opt/idwif/work/export_files/1agg_fact_mm.dat.tmp p #compress from the new named pipe
gzip -c < /var/opt/idwif/work/export_files/1agg_fact_mm.dat.tmp > /var/opt/idwif/work/export_files/1agg_fact_mm.dat.tmp.gz & #edit out multiple spaces and send output to named pipe cat /var/opt/idwif/work/export_files/1agg_fact_mm.dat|sed 's/ *//g' > /var/opt/idwif/work/export_files/1agg_fact_mm.dat.tmp #move new tmp file into original compressed spool file mv /var/opt/idwif/work/export_files/1agg_fact_mm.dat.tmp.gz /var/opt/idwif/work/export_files/1agg_fact_mm.dat.gz

In article <19990330204311.17904.00000315_at_ng34.aol.com>,   lakshne_at_aol.com (LAKSHNE) wrote:

> Hello Everyone,
>
> I have a question that " How to create file in oracle using sqlplus". Could

you

> please respond me as soon as possible.
>
> Thanks for help.
> Vijaya.
>

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own    

Received on Mon Apr 05 1999 - 17:43:33 CEST