Write/Read external file from within Stored Procedure?

Database

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:

--declare
file_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!
7 REPLIES

Re: Write/Read external file from within Stored Procedure?

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

Re: Write/Read external file from within Stored Procedure?

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?

Re: Write/Read external file from within Stored Procedure?

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

Re: Write/Read external file from within Stored Procedure?

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 database
database stami27;
-------------------------------------------------------------
.OS rm file1.out
.EXPORT DATA FILE=file1.out
select emp_id||';'||emp_name||';'||emp_abt||';'||emp_sal||';' from stami27.emp_table
;
.EXPORT RESET
.logoff;
.quit;
---------------
In Unix line mod: bteqselect_bteq.out

hth
greetings
stami27

Re: Write/Read external file from within Stored Procedure?

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 < select_test.bteq > select_test.out

thanks
stami27

Re: Write/Read external file from within Stored Procedure?

Thanks Stami!
N/A

Re: Write/Read external file from within Stored Procedure?

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