Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need to create file in unix (oracle directory path)

594436May 12 2010 — edited May 12 2010
Is it possible to create a file in oracle directory path (in unix folder).

I am using a dynamic procedure to create the External Tables, when the given pattern file is present the procedure will create the External table, if the file is not present then it should create an empty file so that the corresponding External table would return zero(0) records. It should not fail.
It has to be through plsql not using unix script.

..............

UTL_FILE.FGETATTR(V_DIR_NAME, V_FILE_NAME, V_EXISTS, V_FILE_LENGTH, V_BLOCKSIZE);

IF v_exists=FALSE THEN
--------create an empty file
v_file_name:=empty_file;
end if;

--Create table script which will dynamically create the external table

v_sql := 'CREATE TABLE '||V_RX_NAME||' ( '||RTRIM(v_cols,', ')||' )' ||
' ORGANIZATION EXTERNAL '||
' ( TYPE ORACLE_LOADER '||
' DEFAULT DIRECTORY "'|| v_dir_name || '"'||
' ACCESS PARAMETERS '||
' ( RECORDS DELIMITED BY NEWLINE '||
' LOAD WHEN ( '||RTRIM(v_null_cols,'and ')||')'||
' BADFILE '''||rec_mkt_name.SUBJECT_AREA_NAME||'_'||rec_mkt_name.PARAM_NAME||'.BAD''' ||
' DISCARDFILE '''||rec_mkt_name.SUBJECT_AREA_NAME||'_'||rec_mkt_name.PARAM_NAME||'.DISCARD''' ||
' LOGFILE '''||rec_mkt_name.SUBJECT_AREA_NAME||'_'||rec_mkt_name.PARAM_NAME||'.LOG'''||
' FIELDS TERMINATED BY ''~'' OPTIONALLY ENCLOSED BY ''"'' '||
' MISSING FIELD VALUES ARE NULL ' ||
' ) LOCATION ('''||V_FILE_NAME||''') ) REJECT LIMIT UNLIMITED';
GV_CURR_STEP := v_sql;
dbms_output.put_line(V_FILE_NAME) ;

EXECUTE IMMEDIATE v_sql;
...........
This post has been answered by Saubhik Banerjee on May 12 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 9 2010
Added on May 12 2010
2 comments
562 views