loading a CLOB from a sql plus script

[Thargy]

I am building a static data loading script for a database.

One table contains clob information which is >2499 characters.

Is there any way to script this in sql plus, so that I can make it part of the general script, and not do anything special for just one table?

Regards

Tharg.

Grinding away at things Oracular

 

[SantaMufasa]

John,

Consider the following:

SQL> create table tharg (x clob);

Table created.

insert into tharg values (rep('y',4000));

1 row created.

select length(x) from tharg;

 LENGTH(X)
----------
      4000

"rep" is a user-defined function of mine to replicate arg-1 arg-2 times. In Oracle 9i, the maximum buffer size in SQL*Plus is 4000 for a column.

So, given all of this, what follow-up would you like to pursue?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Thargy]

Santa,

I cringe with embarassment. A badly posted question, gracefully answered - arrrgh!

OK, here goes. I was attempting to load a CLOB using an "insert into <tablename> values (1, <description>,<clob text>);

three records went in just fine, but a fourth came to a shuddering halt, and sql plus moaned that the string was too long. I googled and found that the limit is 2,499 characters in SQL plus. Where have I gone wrong Dave?

Apologies for the delay in responding, it's my bedtime, and the sun has set in the West long since

Have a good 'un and don't stare at that monitor for too long.

John

Grinding away at things Oracular

 

[SantaMufasa]

John,

The problem comes with some (silly and arbitrary) limitation2 in SQL*Plus:

1) Physical number of (syntax) characters that make up a single SQL command cannot exceed 2499.
2) Physical number of data characters that SQL*Plus can process for a single expression from within a single SQL*Plus statement cannot exceed 4000.

As you can see in this example (below), however, SQL*Plus can manipulate far more than 4000 characters (up to 2,147,483,647) in a SQL statement:

SQL> insert into tharg values (rep('y',4000));

1 row created.

SQL> update tharg set x = x||rep('z',4000);

1 row updated.

SQL> update tharg set x = x||rep('z',4000);

1 row updated.

SQL> update tharg set x = x||x;

1 row updated.

SQL> /

1 row updated.

SQL> /

1 row updated.

SQL> /

1 row updated.

SQL> /

1 row updated.

SQL> /

1 row updated.

SQL> /

1 row updated.

SQL> /

1 row updated.

SQL> select length(x) from tharg;

 LENGTH(X)
----------
   3072000 <-- 3MB !

So, provided you can successively concatenate your data in sub-4001-chr chunks, you can even insert them in SQL*Plus.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Thargy]

Santa,

thanks for the clarification. I would likely never have found the difference between the syntax and data limit.

It's great to have a solution to roll out tomorrow at work. I believe that the reason for my error is that the CLOB data string contains loads of syntax characters, which is why SQL Plus was throwing a wobbly.

Much appreciated.

Regards

T

Grinding away at things Oracular

 

[Thargy]

Santa,

below is my version of rep.

Would you mind giving me a critique and your version of how it should look. Mine was bashed out in haste, so I suspect it has a few flaws.

CREATE OR REPLACE FUNCTION Rep
     (character_to_repeat VARCHAR2,
	  number_required     PLS_INTEGER)
RETURN VARCHAR2
IS

/***************************************************************
This function created 27 Feb 2006 to assist in loading
CLOB's with > 4000 characters in them (which is beyond the sql plus
limit)
This should be deleted once the insertions are done.
*****************************************************************/
working_string VARCHAR2(32767) DEFAULT '';
BEGIN

FOR i IN 1 .. number_required
LOOP
    working_string := working_string||character_to_repeat;
END LOOP;

RETURN working_string;

END Rep;

Regards

T

Grinding away at things Oracular

 

[SantaMufasa]

John,

Part of my response depends upon HOW you plan to use this function and WHAT you expect it to do. If you plan to use the function from within SQL*Plus, you will still run into the 4000-character limitation per expression. Tell us what you want the function to do.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Thargy]

Santa,

I intend to create the function, from within sql plus,
and then invoke it with the appropriate number of repetitions. One of the strings that I'm handling contains 4,438 white spaces, so I'll add the first 2,000 chars directly, and then call the function with

update tharg set x = x||rep(' ',4438);

Then I'll add some non-white space characters at the end, and then drop the function.

Regards

T

Grinding away at things Oracular

 

[SantaMufasa]

If it will be helpful, you are welcome to my "rep" function that is much faster that the one above:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
create or replace function rep
   (string_to_rep in varchar2, reps in number)
   return varchar2
is
begin
   return lpad(string_to_rep,reps*length(string_to_rep),string_to_rep);
end;
/

...then (if I understand correctly what you are trying to do), you can (from within SQL*Plus) use the "rep" function on successive updates of your target rows.

...but I could also be "in the stands instead of on the pitch" in this case if I am not understanding properly your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Thargy]

Santa,

No, as usual you're right on the money.
I just knew you'd have an absolute belter of a function up your sleeve.

I just did the quick and dirty solution above, whereas you've gone the 'elegant simplicity' route, which is why I asked.

I wanted to demonstrate that I'd had a go myself first, and wasn't freeloading.

T

Grinding away at things Oracular

 

[SantaMufasa]

You da' Man, John! I sincerely appreciate your attitude of "try it myself first"...you'll always be ahead of the pack with that approach. That's also why you are a "Good Citizen of Tek-Tips", as well.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.