Can Sqlplus read the contents of a file into a variable?

Question

I've been tinkering with sqlplus for awhile now, and I've managed to get sqlplus to read the contents of a file into a substitution variable as such:

exit | sqlplus -s login/pass@db @script.sql "`cat file.txt`"

This mostly works as my script requires... even newlines in the file are preserved. However, I was only using a sample file which was 50 or 60 bytes in size. The actual files I'll end up using will be at least a few kilobytes. So it was at this point I decided to check the max size of a substitution variable: 240 characters.

Is there a way within my sqlplus script to read a file's contents into a bind variable? The Oracle documentation seems to hint at this with the GET command, saying that typically you'll use this just to load a sql/sqlplus script.

http://docs.oracle.com/cd/B10501_01/server.920/a90842/ch13.htm#1009882

file_name[.ext] Represents the file you wish to load (typically a script).

Answer 1

Yes, there's a tricky way to do it. Put something into props.txt and run the script:

DECLARE
  -- the @@ expression must be in separate line as follows
  file_contents VARCHAR2(32767) := '
@@props.txt
';
BEGIN 
  dbms_output.put_line('===');
  dbms_output.put_line(file_contents);
  dbms_output.put_line('===');
END;
/

Note that the file props.txt can not contain an "@" or you'll get nested SQL*PLUS calls

Answer 2

No. Load would only store the file contents in Sql*Plus's own sql buffer. You can then run, edit and list the buffer.

A substitution variable is not the right place to load a file into. Use a bind variable of type clob for that and load the file contents using utl_file. But of course the file has to be located on the server in this case.

edit: if the data has to be located on the client, your option would be to load the clob using a pl/sql block and several calls to dbms_lob.writeappend

Your file would have to look like this (cannot test it ATM):

var l clob;

begin
  dbms_lob.createtemporary(l);
  dbms_lob.writeappend(l, 'abcdef...');
  dbms_lob.writeappend(l, 'ijkl...');
end;
/