All Forums Database
Craig.Schweizer 4 posts Joined 04/06
12 Apr 2006
Write/Read external file from within Stored Procedure?

Is there a way to read and/or write to an external file from within a Stored Procedure. I have a requirement that after performing some processing logic to write the clean records to an extract file.From within Oracle, I would use the UTL_FILE package. Example:--declarefile_handle UTL_FILE.FILE_TYPE;master_rec VARCHAR(1000);--open file for 'W'rite file_handle := utl_file.fopen(V_DIRECTORY,v_file,'W',1000);LOOP master_rec:= col1||col2|col3||etc; UTL_FILE.put_line(file_handle, master_rec);END LOOP--close file utl_file.fclose(file_handle);Does Teradata have something equivalent? Thanks!

Craig.Schweizer 4 posts Joined 04/06
19 Apr 2006

Does anyone have an update on this? Should I post in another forum? Please let me know if clarification is needed. Thanks.

Jim Chapman 449 posts Joined 09/04
19 Apr 2006

There is no good way to do that with Teradata. Even if methods for accessing the host OS file system were available, it probably would not be useful on MPP configurations, since you don't have control over which node your SP will run on.Have you considered writing your "clean records" to a table instead of a file?

Craig.Schweizer 4 posts Joined 04/06
20 Apr 2006

Thank you for the update. Unfortunately a table won't work since the data needs to be sent out of house via flat file.

stami27-2406 22 posts Joined 02/06
20 Apr 2006

Hi,had a similar problem.My stored procedure write in a table and then whith a simple bteq (on Unix) exported the table to a file.Example of bteq:-------------------------------------------------------------.logon dbc/stami27,psw;.set errorout stdout.SET WIDTH 254--------------------------------------------------------------- PARAMETER 1: Set source databasedatabase stami27;-------------------------------------------------------------.OS rm file1.out .EXPORT DATA FILE=file1.outselect emp_id||';'||emp_name||';'||emp_abt||';'||emp_sal||';' from stami27.emp_table;.EXPORT RESET.logoff;.quit;---------------In Unix line mod: bteqselect_bteq.ouththgreetingsstami27

stami27-2406 22 posts Joined 02/06
20 Apr 2006

Hi Craig, sorry for the html mistake above.The rihgt notation of the unix Command for the bteq above (say its name is select_test.bteq) were:bteq &lt select_test.bteq &gt select_test.outthanks stami27

Craig.Schweizer 4 posts Joined 04/06
20 Apr 2006

Thanks Stami!

BBR2 96 posts Joined 12/04
20 Apr 2006

Craig,Why don't you consider populating a volatile or global temporary table instead of permtable. You can export out with same bteq session.Again if the record set is very high, you would need a large TEMP SPACE allocation. BTEQ is not the best size for high volumn exports.Hope this helps.Vinay

You must sign in to leave a comment.