2017/06/04

How to load big text file into CLOB via SQL*Plus

From time to time a need to load a long text file with XML or even long PL/SQL script arises. And the only tool available is SQL*Plus.

As it appeared, it is very simply, if "include script" feature is used.
So, put your long text into some file, long_plsql.sql for example.
Here are steps to load it:

create table script_storage (
script_name varchar2(100),
script_content clob)
;

declare
  l_script clob := 
q'{
@@long_plsql.sql
}';
begin
  delete from script_storage where script_name='SCRIPT1';
  insert into script_storage (script_name,script_content) values
  ('SCRIPT1',l_script);
end;
/

and that's it!

Of course, if the file is longer than 32767 bytes, you will get an error:
PLS-00172: string literal too long.

But it can be easily overcome. Just divide the long text into parts shorter than 32767 bytes with the comment like this (usable for PL/SQL):
--}'||q'{
it will make Oracle concatenate big text from shorter parts. 

No comments: