Forum OpenACS Q&A: Inserting a long string constant into a clob from plsql?

Collapse
1: Inserting a long string constant into a clob from plsql?
Posted by Dan Wickstrom on 02/18/03 02:50 AM
I want to load a long string constant into a oracle table when the package sql is sourced during installation, but looking through the oracle documentation, I don't see any easy way to do this.  In postgresql, I just created a text column and used a simple insert statement in the package create script to get the string into the table.
Collapse
2: Re: Inserting a long string constant into a clob from plsql? (response to 1)
Posted by Sebastiano Pilla on 02/18/03 10:14 AM
How long is the string? If < 32KB, it can be passed as parameter to any PL/SQL stored procedure, and the procedure can insert it into a CLOB with a simple insert statement. If its longer than 32KB, then you'll have to use the DBMS_LOB package.

Have a look at this page and at the parent chapter (both require an Oracle OTN account).
Collapse
3: Re: Inserting a long string constant into a clob from plsql? (response to 1)
Posted by Simos Gabrielidis on 02/18/03 02:38 PM
If you use the Oracle driver (most probably you do if you use OpenACS or ACS with Oracle) you can simply say: 
db_exec_plsql add_record {
begin
  1 := my_proc.new_record(
  description => empty_clob()
  ...
  );
end;
}

# Set long description (use CLOB)
db_dml set_description {
update my_table set
  description = empty_clob()
where id=:id returning description into :1
} -clobs [list $description]

This way, you don't have to worry about PL/SQL's limitation of 32Kbytes for a varchar2 variable (Oracle driver takes care of that).

Collapse
4: Re: Inserting a long string constant into a clob from plsql? (response to 1)
Posted by Dan Wickstrom on 02/18/03 02:40 PM
Thanks for the link.  I was assuming that varchars couldn't be longer than 4000 characters, and my string is around 6000 characters.  Assigning the string to a varchar and inserting it directly into the clob works.
Collapse
5: Re: Inserting a long string constant into a clob from plsql? (response to 4)
Posted by Simos Gabrielidis on 02/18/03 02:50 PM
Well, SQL varchar2 is limited to 4KBytes; PL/SQL varchar2 is limited to 32KBytes. (BTW, sorry for the small typo in my previous piece of code; it should be: 
:1 := my_proc.new_record(