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.
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:
Post a Comment