How do I pass a CLOB value to a stored procedure

时间:2020-12-17 08:50:39
April 05, 2004 Cleo -- Thanks for the question regarding "How do I pass a CLOB value to a stored procedure?", version
You Asked
How do I pass a CLOB value to a stored procedure?
I am creating a stored procedure which appends a value to a CLOB 

datatype.  The procedure has 2 in parameter (one CLOB and one 
CLOB).  The procedure is compiled but I'm having problem 
executing it.  Below is a simplified version of the procedure 
and 
the error given when the procedure is executed.

SQL> CREATE OR REPLACE PROCEDURE prUpdateContent (
2     p_contentId IN NUMBER,
3     p_body IN CLOB)
4     IS
5   v_id      NUMBER;
6   v_orig     CLOB;
7   v_add       CLOB;
8  
9  BEGIN
10   v_id      := p_contentId;
11   v_add     := p_body;
12  
13   SELECT body INTO v_orig FROM test WHERE id=v_id FOR UPDATE;
14   
15   DBMS_LOB.APPEND(v_orig, v_add);
16   commit;
17  END;
18  /

Procedure created.

SQL> exec prUpdateContent (1, 'testing');
BEGIN prUpdateContent (1, 'testing'); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 
'PRUPDATECONTENT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored





and we said...
Looks like you are attempting to append a TEXT (varchar2) to an 
existing clob -- NOT appending a CLOB to a CLOB.

It'll just be:

ops$tkyte@dev8i> create table demo ( x int, y clob );

Table created.

ops$tkyte@dev8i> 
ops$tkyte@dev8i> insert into demo values ( 1, 'Hello' );

1 row created.

ops$tkyte@dev8i> 
ops$tkyte@dev8i> create or replace procedure lob_append( p_id in 
number, p_text in varchar2 )
  2  as
  3          l_clob  clob;
  4  begin
  5          select y into l_clob from demo where x = p_id for 
update;
  6  
  7          dbms_lob.writeappend( l_clob, length(p_text), 
p_text );
  8  end;
  9  /

Procedure created.

ops$tkyte@dev8i> 
ops$tkyte@dev8i> select * from demo
  2  /

         X Y
---------- 
-----------------------------------------------------------------
---------------
         1 Hello

ops$tkyte@dev8i> exec lob_append( 1, ' World' );

PL/SQL procedure successfully completed.

ops$tkyte@dev8i> select * from demo
  2  /

         X Y
---------- 
-----------------------------------------------------------------
---------------
         1 Hello World


to do that.

Review & Followup

Rating: 5
Succinct as usual, Mr. Kyte  October 25, 2001
Reviewer:  Harrison  from Haymarket ("Venice without the water") VA, USA

These things always look a lot harder in my mind
than on your terminal. As Winston quoted another
old man, "I've seen a lot of troubles in my life
and most of them never happened" (except the recent
rash of people writing to the data dictionary, but
he never saw that.  Obviously some people are not
aware that every ringing phone is not announcing
a raise). 


Rating: 3
  April 16, 2002
Reviewer:  Joshua Perry  from Las Vegas

When I do something similar in PLSQL (9i), I get an error!

Example:

BEGIN
  DECLARE l_clob             CLOB;
         vTEXT_PART    varchar2(20) := 'This is a test';
         vTEXT_ID       int          := 0;
  BEGIN
    IF( vTEXT_ID = 0 )
    THEN
    INSERT INTO TEXT_TMP VALUES 
          ( SEQ_TEXT_ID.NextVal, vTEXT_PART ) 
       RETURNING TEXT_ID INTO vTEXT_ID;
    ELSE 
    SELECT TEXT_DATA INTO l_clob 
           FROM TEXT_TMP WHERE XTEXT_ID = vTEXT_ID 
           FOR UPDATE ;
        
        dbms_lob.writeappend( l_clob, 
           length(vTEXT_TEXT_PART), vTEXT_PART );
    END IF;
  END;
END;

First, I run with vTEXT_ID = 0, then after a row is inserted I take the returned 
sequence and run again to try to append to that row and I get the error:

"The following error has occurred:

ORA-00600: internal error code, arguments: [ktsircinfo_num1], [2147483647], [0], 
[0], [], [], [], []
ORA-06512: at "SYS.DBMS_LOB", line 722
ORA-06512: at line 13"

What am I doing wrong here?

Thanks
 

Followup:
After changing your code significantly to make it even compile:

ops$tkyte@ORA9I.WORLD> create table text_tmp( text_id int, text_data clob );

Table created.

ops$tkyte@ORA9I.WORLD> create sequence seq_text_id;

Sequence created.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> begin
  2    DECLARE l_clob             CLOB;
  3           vTEXT_PART    varchar2(20) := 'This is a test';
  4           vTEXT_ID       int          := 0;
  5    BEGIN
  6      IF( vTEXT_ID = 0 )
  7      THEN
  8      INSERT INTO TEXT_TMP VALUES
  9            ( SEQ_TEXT_ID.NextVal, vTEXT_PART )
 10         RETURNING TEXT_ID INTO vTEXT_ID;
 11      ELSE
 12      SELECT TEXT_DATA INTO l_clob
 13             FROM TEXT_TMP WHERE TEXT_ID = vTEXT_ID
 14             FOR UPDATE ;
 15  
 16          dbms_lob.writeappend( l_clob,
 17             length(vTEXT_PART), vTEXT_PART );
 18      END IF;
 19    END;
 20  END;
 21  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> BEGIN
  2    DECLARE l_clob             CLOB;
  3           vTEXT_PART    varchar2(20) := 'This is a test';
  4           vTEXT_ID       int          := 1;
  5    BEGIN
  6      IF( vTEXT_ID = 0 )
  7      THEN
  8      INSERT INTO TEXT_TMP VALUES
  9            ( SEQ_TEXT_ID.NextVal, vTEXT_PART )
 10         RETURNING TEXT_ID INTO vTEXT_ID;
 11      ELSE
 12      SELECT TEXT_DATA INTO l_clob
 13             FROM TEXT_TMP WHERE TEXT_ID = vTEXT_ID
 14             FOR UPDATE ;
 15  
 16          dbms_lob.writeappend( l_clob,
 17             length(vTEXT_PART), vTEXT_PART );
 18      END IF;
 19    END;
 20  END;
 21  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> select * from text_tmp;

   TEXT_ID TEXT_DATA
---------- 
--------------------------------------------------------------------------------
         1 This is a testThis is a test


I cannot reproduce.

You need to show an ENTIRE case, everything from start to finish.  don't make me 
"guess" 


Rating: 3
  April 17, 2002
Reviewer:  Sagi  from India

I have a table LOGINFO. The Structure is:

LOGID           VARCHAR2(8)
CALLDESC        CLOB
CLOSEDESC       CLOB

When I tried the following:

declare
v_calldesc CLOB ;
v_closedesc CLOB ;
BEGIN
  SELECT calldesc, closedesc INTO v_calldesc, v_closedesc 
  from calllog where callid='00078349' for update ;

  dbms_lob.writeappend(v_calldesc,LENGTH('APPENDING TO  CALLDESC'), 'APPENDING 
TO CALLDESC' ) ;
  DBMS_LOB.writeappend(v_closedesc,LENGTH('APPENDING TO CLOSEDESC'), 'APPENDING 
TO CLOSEDES') ;
 
  DBMS_OUTPUT.PUT_LINE('Before commit') ;
  COMMIT ;
  DBMS_OUTPUT.PUT_LINE('After commit') ;
END ;

Encounted the below Error:
==========================
  declare
*
ERROR at line 1:
ORA-21560: argument 2 is null, invalid, or out of range
ORA-06512: at "SYS.DBMS_LOB", line 722
ORA-06512: at line 9

It is working if I use one column but gives error if using two CLOB Columns. Why 
is this? 

Thanx in advance.

Regards, 

Followup:
because argument 2 is out of range.

You have DIFFERENT strings passed to length then to dbms_lob.

.. TO  CALLDESC ...
.. TO CALLDESC ...

bug in your code. 


Rating: 3
Better example and understanding  April 17, 2002
Reviewer:  Joshua Perry  from Las Vegas

Sorry about the other example...

I have discovered that the problem lies in the temp table not the SQL.  What I 
failed to mention in the example is that I am using a Global Temporary table 
built off an Object Type, 

Example:
   CREATE TYPE "USERNAME".TEXT_ROW_TYPE as object     
     ("TEXT_ID" int, "TEXT_DATA" CLOB );        
   CREATE Global Temporary TABLE TEXT_TMP 
     of "USERNAME"."TEXT_ROW_TYPE"   
     ON COMMIT preserve ROWS;

The code that you provided works for the example:

   CREATE Global Temporary TABLE TEXT_TMP 
      ("TEXT_ID" int, "TEXT_DATA" CLOB)  
      ON COMMIT preserve ROWS;

but not when I use the Temp. Table derived from the Object "TEXT_ROW_TYPE," why?

Thanks for the help!
 

Followup:
How's about a full example -- from start to finish -- in the fashion I do them.

A simple, small, yet 100% complete example we can run to see the behaviour.

After a while I lose track of what problem exactly you are trying to solve. 


Rating: 3
Full Example  April 17, 2002
Reviewer:  Joshua Perry  from Las Vegas

I was responding the the earlier example/response on this question, and assumed 
you had that information available to to so.  Here is an example in full:

This is how you created the temp table (from your earlier response):
create table text_tmp( text_id int, text_data clob );

This is where the problem lies:
   CREATE TYPE "USERNAME".TEXT_ROW_TYPE as object     
     ("TEXT_ID" int, "TEXT_DATA" CLOB );        
   CREATE Global Temporary TABLE TEXT_TMP 
     of "USERNAME"."TEXT_ROW_TYPE"   
     ON COMMIT preserve ROWS;
/
create sequence seq_text_id;
/
begin
      DECLARE l_clob       CLOB;
             vTEXT_PART    varchar2(50) := 'This is a test';
             vTEXT_ID      int          := 0;
     BEGIN
        INSERT INTO TEXT_TMP 
            VALUES( SEQ_TEXT_ID.NextVal, vTEXT_PART )
            RETURNING TEXT_ID INTO vTEXT_ID;
          
     END;
END;
/
BEGIN
      DECLARE l_clob       CLOB;
             vTEXT_PART    varchar2(50) := 'This is a ANOTHER test';
             vTEXT_ID      int          := 1;
     BEGIN

       SELECT TEXT_DATA INTO l_clob
              FROM TEXT_TMP WHERE TEXT_ID = vTEXT_ID
              FOR UPDATE ;
   
          dbms_lob.writeappend( l_clob,
              length(vTEXT_PART), vTEXT_PART );
     END;
END;

It work fine with the first create statement, but fails on the second PLSQL 
block with the Temp table based on the row object.  

Thanks 

Followup:
Reproduced, looks like it might be related to an in progress bug #1310111.  My 
suggestion is to contact support and provide this 100% reproducible and as small 
as possible test case:

ops$tkyte@ORA817DEV.US.ORACLE.COM> CREATE or replace type TEXT_ROW_TYPE as 
object("TEXT_DATA" CLOB );
  2  /
Type created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> CREATE Global Temporary TABLE TEXT_TMP of 
"TEXT_ROW_TYPE" ON COMMIT preserve ROWS;
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> DECLARE
  2     l_clob       CLOB;
  3  BEGIN
  4     INSERT INTO TEXT_TMP VALUES( 'testing' );
  5     SELECT TEXT_DATA INTO l_clob FROM TEXT_TMP FOR UPDATE ;
  6     dbms_lob.writeappend( l_clob, length('x'), 'x' );
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsircinfo_num1], [2147483647], [0], 
[0], [], [], [], []
ORA-06512: at "SYS.DBMS_LOB", line 722
ORA-06512: at line 6


I could file it ( i believe it to be a dup of the above bug# ) but it'll be 
better if you do it. 


Rating: 5
Nice one  April 18, 2002
Reviewer:  Nsabasi Etteh  from England

I am new to this and i never knew that you could pass XML through a stored 
procedure. I did it and it worked. Keep it up guys 


Rating: 5
  June 12, 2002
Reviewer:  A reader

Hi Tom,

 I am passing 1-LOB from one procedure to another.
 Second procedure already has 2-CLOB. I want to append 1-LOB passed from 
procdedure to 2-LOB

 eg. 2-LOB := 2-LOB ||1-LOB;

How can i do that.

Thanks
  

   

Followup:
read about the dbms_lob package.

PROCEDURE APPEND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       BLOB                    IN/OUT
 SRC_LOB                        BLOB                    IN
PROCEDURE APPEND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       CLOB                    IN/OUT
 SRC_LOB                        CLOB                    IN

will do that. 


Rating: 5
  March 31, 2003
Reviewer:  SY  from AVO

hi,Tom, I face the same question.

1. I can append text to CLOB now, or use "writeAppend" raw data to BLOB.
or use " loadfromfile " to insert external text file into BLOB,
but it seems it can't write/update/append text string
into BLOB like the following :

procedure SP_update_textBLOB( p_id in number, p_text
in varchar2 )
as
        l_blob  blob;
begin
        select blob into l_blob from report where id = p_id for update;

        dbms_lob.writeappend( l_blob, length(p_text), p_text );

        commit;
end;

Something wrong with my code (run in oracle 8.1.7)?


2. in fact, I want to convert a select statement result
into csv format, and insert this to a BLOB or CLOB column.
If I use dump_csv SP to output to OS, and load a real file from OS,
I can do it great. But if I direct write from select result
to BLOB, it seems I only can append one line once like 
this:

    for i in 1 .. 255 loop
          begin

      dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
              
              l_colCnt := i;

              l_v2:=l_separator || l_descTbl(i).col_name;

              SP_updateBLOB(2, utl_raw.cast_to_raw(l_v2) );

              l_separator := p_separator;
              
     end;
       end loop;
          
Can I write it all once ? instead write line one by one?


3. How could write a line end charater to BLOB/CLOB? 


Thank you very much!:)
 

Followup:
1) blobs take raw.  varchar2 is not raw.  a varchar2 passed to a routine that 
expects a raw expects that the varchar2 is in fact HEX codes that represent raw 
data.


use utl_raw.cast_to_raw(p_text) instead of just p_text.

2)) well, just replace the || with dbms_lob.writeAppend

this routine would have the blob, you could:

    loop
        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        l_separator := '';
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, 
                                   l_columnValue );
            dbms_lob.writeAppend( l_blob, length(l_separator||l_columnValue),
                          utl_raw.cast_to_raw(l_separator||l_columnValue) );
            l_separator := p_separator;
        end loop;
        dbms_lob.writeAppend( l_blob, 1, '0A' );  /* newline */
    end loop;
    dbms_sql.close_cursor(l_theCursor);


but -- i have a feeling you would find that building a line and then appending 
the line would be more efficient -- but DON'T select the blob out each time -- 
just do that once at the top of the loop.


3) see above, look for /* newline */ 


Rating: 5
  March 31, 2003
Reviewer:  SY  from AVO

I checked the question "XML Generation/Manipulation using SQL", and found the 
following question is exactly what I want, if change XML to CSV. :)

declare
  2    l_ctx  dbms_xmlquery.ctxHandle;
  3    l_clob clob;
  4  begin
5    l_ctx := dbms_xmlquery.newContext('select * from scott.emp');
  6    insert into xmldocs values ('EMP TABLE', empty_clob())
  7    returning xmldoc into l_clob;
  8    dbms_xmlquery.getXml(l_ctx, l_clob);
  9  end;
 10  /

But, it seems there's no dbms_csvquery exist. :(
 


Rating: 4
  April 01, 2003
Reviewer:  SY  from AVO

when I keep digging asktom.oracle.com & google , I find many treasure.

I can answer my 3 question now, everybody who have better advice don't hesitate 
to tell me.

1.As blob stores binary data, to update/append it must through raw , clob can 
update/append with text .

2. insert chr(13)||chr(10) can end the line.

3. I update Tom's dump_csv code to a sp to do as this xml function:
xml :

  l_ctx := dbms_xmlquery.newContext('select * from scott.emp');
      insert into xmldocs values ('EMP TABLE', empty_clob())
      returning xmldoc into l_clob;
      dbms_xmlquery.getXml(l_ctx, l_clob);

-------------------------------
csv:

CREATE OR REPLACE procedure  dump_csv_clob
( p_id in number, p_query in varchar2, p_separator in varchar2 default ',' )
  is
      l_output        utl_file.file_type;
      l_theCursor     integer default dbms_sql.open_cursor;
      l_columnValue   varchar2(2000);
      l_status        integer;
      l_colCnt        number default 0;
      l_separator     varchar2(10) default '';
      l_cnt           number default 0;
      l_descTbl       dbms_sql.desc_tab;
      l_v2 varchar2(4000);


  begin
     --l_output := utl_file.fopen( p_dir, p_filename, 'w', 32000 );

      dbms_sql.parse(  l_theCursor,  p_query,
                                           dbms_sql.native );


      dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
      
      l_v2:=chr(13)||chr(10);
      clob_update(p_id, l_v2 );

      for i in 1 .. 255 loop
          begin

              dbms_sql.define_column( l_theCursor, i,
                                      l_columnValue, 2000 );
              l_colCnt := i;


              l_v2:=l_separator || l_descTbl(i).col_name;

              clob_append(p_id, l_v2 );

              l_separator := p_separator;

          exception
             when others then
                if ( sqlcode = -1007 ) then exit;
                 else
                      raise;
               end if;

          end;
      end loop;

               l_v2:=chr(13)||chr(10);
               clob_append(p_id, l_v2 );

      dbms_sql.define_column( l_theCursor, 1,
                              l_columnValue, 2000 );

      l_status := dbms_sql.execute(l_theCursor);

      loop
          exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
          l_separator := '';
          for i in 1 .. l_colCnt loop
              dbms_sql.column_value( l_theCursor, i,
                                     l_columnValue );
     
                l_v2:=l_separator || l_columnValue  ;

               clob_append(p_id, l_v2 );


              l_separator := p_separator;

               end loop;
               
               l_v2:=chr(13)||chr(10);
               clob_append(p_id, l_v2 );
     
          l_cnt := l_cnt+1;
     
      end loop;
      dbms_sql.close_cursor(l_theCursor);

      commit;
     
  end dump_csv_clob;
/


procedure clob_update( p_id in number, p_text
in varchar2 )
as
        l_clob  clob;
begin


        UPDATE report_doc SET docclob = EMPTY_CLOB()
     where sid=p_id
     RETURNING docclob into l_clob;

        dbms_lob.writeappend( l_clob, length(p_text), p_text );
end;

procedure clob_append( p_id in number, p_text
in varchar2 )
as
        l_clob  clob;
begin
        select docclob into l_clob from report_doc where sid = p_id for update;

        dbms_lob.writeappend( l_clob, length(p_text), p_text );
end;

e..... it's still much room to improve....
could anyone throw some light here? :) thanx in adv. 
 


Rating: 5
  April 01, 2003
Reviewer:  SY

Wow, Tom's response much faster than I expected ! I even don't know you have 
replied . Thank you, Tom. 

With your suggestion, my code now look like :
...
l_v2:=l_v2 || chr(13)||chr(10);
dbms_lob.writeappend( l_clob, length(l_v2), l_v2 );
...

it's fast !

PS: Will it be faster if I write the lines to a temp CLOB in ram and in the end 
write the temp CLOB to the real CLOB ?  

Followup:
no, it would not be (faster)...  but hey -- as Im fond of saying -- benchmark 
it! 


Rating: 5
Thanks !  April 02, 2003
Reviewer:  SY

Thank tom, your suggestion is alway the best. :)
I benchmark it and got surprise result, one is ten times faster than the other, 
so I think maybe I didn't make the best code, and maybe set temp clob 'cache' 
parameter to 'true' is really helpful.... I paste my code here ... it would be a 
bit lengthiness. 

I use 'set timing on' in sqlplus to test. 
the faster one, use temp clob ,  cost 1 second . : 
/*--------------------------------------------------*/
CREATE OR REPLACE procedure dump_csv_clob3 
( p_id in number, p_query in varchar2 )
  is            
      l_theCursor     integer default dbms_sql.open_cursor;
      l_columnValue   varchar2(2000);
      l_status        integer; 
      l_colCnt        number default 0; 
      l_separator     varchar2(10) default ''; 
      l_cnt           number default 0; 
      l_descTbl       dbms_sql.desc_tab; 
        p_separator       varchar2(10) default ',';  
      l_v2 varchar2(4000) default ''; 
      l_clob           clob;  
      l4t_clob           clob;  
 
  begin 
      dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native ); 
       dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl ); 
   
      DBMS_LOB.CREATETEMPORARY(l_clob, TRUE); 
 
      for i in 1 .. 255 loop  
          begin 
              dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 ); 
              l_colCnt := i; 
              l_v2:=l_v2 || l_separator || l_descTbl(i).col_name;
                l_separator := p_separator;  
            exception   
             when others then   
                if ( sqlcode = -1007 ) then exit;  
                 else       
                      raise;   
               end if;   
           end; 
      end loop; 
               l_v2:=l_v2 || chr(13)||chr(10);  
               dbms_lob.writeappend (l_clob, length(l_v2), l_v2);
               l_v2:='';  
              
      dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );
       
      l_status := dbms_sql.execute(l_theCursor); 
      
      loop 
          exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );  
          l_separator := '';  
          for i in 1 .. l_colCnt loop 
              dbms_sql.column_value( l_theCursor, i, l_columnValue ); 
                  l_v2:=l_v2 || l_separator || l_columnValue  ;
                    l_separator := p_separator; 
          end loop; 
                          l_v2:=l_v2 || chr(13)||chr(10); 
                 dbms_lob.writeappend (l_clob, length(l_v2), l_v2);
                  l_v2:='';   
                           l_cnt := l_cnt+1;                                     
             
      end loop;  
      dbms_sql.close_cursor(l_theCursor);  
     
      UPDATE table1 SET clob_colume = EMPTY_CLOB() 
                    where id=p_id   
                 RETURNING clob_colume into l4t_clob; 
    
        DBMS_LOB.APPEND(l4t_clob, l_clob); 
      commit;        
  end dump_csv_clob3;  
/                                                           


2nd: direct write, slower                 
/*--------------------------------------------------*/
CREATE OR REPLACE procedure dump_csv_clob2 
( p_id in number, p_query in varchar2 )
  is            
      l_theCursor     integer default dbms_sql.open_cursor;
      l_columnValue   varchar2(2000);
      l_status        integer; 
      l_colCnt        number default 0; 
      l_separator     varchar2(10) default ''; 
      l_cnt           number default 0; 
      l_descTbl       dbms_sql.desc_tab; 
        p_separator       varchar2(10) default ',';  
      l_v2 varchar2(4000) default ''; 
      l_clob           clob;  

 
  begin 
      dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native ); 
       dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl ); 
   
    
 UPDATE table1 SET clob_column = EMPTY_CLOB()
            where id=p_id
     RETURNING clob_column into l_clob;

      for i in 1 .. 255 loop  
          begin 
              dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 ); 
              l_colCnt := i; 
              l_v2:=l_v2 || l_separator || l_descTbl(i).col_name;
                l_separator := p_separator;  
            exception   
             when others then   
                if ( sqlcode = -1007 ) then exit;  
                 else       
                      raise;   
               end if;   
           end; 
      end loop; 
               l_v2:=l_v2 || chr(13)||chr(10);  
               dbms_lob.writeappend (l_clob, length(l_v2), l_v2);
               l_v2:='';  
              
      dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );
       
      l_status := dbms_sql.execute(l_theCursor); 
      
      loop 
          exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );  
          l_separator := '';  
          for i in 1 .. l_colCnt loop 
              dbms_sql.column_value( l_theCursor, i, l_columnValue ); 
                  l_v2:=l_v2 || l_separator || l_columnValue  ;
                    l_separator := p_separator; 
          end loop; 
                          l_v2:=l_v2 || chr(13)||chr(10); 
                 dbms_lob.writeappend (l_clob, length(l_v2), l_v2);
                  l_v2:='';   
                          l_cnt := l_cnt+1;                                      
            
      end loop;  
      dbms_sql.close_cursor(l_theCursor);  
     
      commit;        
  end dump_csv_clob2;  
/                                                                             


Rating: 3
SY, I got different result  April 08, 2003
Reviewer:  A reader

In my case, writing a real clob is slightly faster than
using temp clob , Oracle9iR2.
Maybe you should check the relative settings. 


Rating: 4
  April 20, 2003
Reviewer:  A reader

hi ,all , how to ' create a temp CLOB in ram ' ? It seems that temp CLOB is not 
stored in ram ..... 

Followup:
PROCEDURE CREATETEMPORARY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
 CACHE                          BOOLEAN                 IN
 DUR                            BINARY_INTEGER          IN     DEFAULT

it'll cache what it can/should.   


Rating: 4
Copying Varchar2 to CLOB  February 12, 2004
Reviewer:  Yogesh  from Pune, India

I have a table with a VARCHAR2 column. I want to change its datatype to CLOB, 
But as this is not possible I added one new column as CLOB. How can I copy data 
from varchar column to CLOB ?  

Followup:
update set clob_column = varchar2_column;

ops$tkyte@ORA9IR2> alter table emp add ename_clob clob;
 
Table altered.
 
ops$tkyte@ORA9IR2> update emp set ename_clob = ename;
 
14 rows updated.
 
ops$tkyte@ORA9IR2> alter table emp drop column ename;
 
Table altered.
 
ops$tkyte@ORA9IR2> alter table emp rename column ename_clob to ename;
 
Table altered.
 
ops$tkyte@ORA9IR2> set linesize 60
ops$tkyte@ORA9IR2> desc emp
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 EMPNO                         NOT NULL NUMBER(4)
 JOB                                    VARCHAR2(9)
 MGR                                    NUMBER(4)
 HIREDATE                               DATE
 SAL                                    NUMBER(7,2)
 COMM                                   NUMBER(7,2)
 DEPTNO                                 NUMBER(2)
 ENAME                                  CLOB
 


Rating: 4
ORA-03120  February 13, 2004
Reviewer:  Yogesh  from Pune, India

Thanks Tom, I did the same thing. But when I use to select rows from that table 
I was getting 

SQL> select * from test;
ERROR:
ORA-03120: two-task conversion routine: integer overflow

My Server is sun Solaris and I was trying this from my XP client.  When I saw 
the explanation, it was saying something about heterogeneous system. So I logged 
in to my Solaris server using telnet and did the same thing. It worked. 

I could not understand, if something is working from server, why it should fail 
from client. 

Your comments Please.
 

Followup:
what version of plus do you have on windoze and which version of the database do 
you have on sun?   


Rating: 3
ORA-03120  February 16, 2004
Reviewer:  Yogesh  from Pune, India

SQL*Plus: Release 8.1.7.0.0
Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production

 

Followup:
are you utf8 in the server. 


Rating: 4
UTF8  February 17, 2004
Reviewer:  Yogesh  from Pune, India

Yes you are right,  NLS_CHARACTERSET is set to UTF8
 


Rating: 4
Clob retrival and keyword search  February 24, 2004
Reviewer:  djecal  from Richmond,VA

Hi Tom, is it a way to run a keyword search in Clob file data type? After 
writing a procedure to load a clob file can we load more than one file? I failed 
to retrieve file using dbms_lob.read (). These are my statements:

select dbms_lob.read (state_code,seq_num,resp) from narrative_tb;
ERROR at line 1:
ORA-00904: invalid column name

SQL> select dbms_lob.read(resp,4000,1) from narrative_tb;
select dbms_lob.read(resp,4000,1) from narrative_tb;
                *
ERROR at line 1:
ORA-00904: invalid column name

SQL> select dbms_lob.substr (resp,4000,1) from narrative_tb;
The last query was successful. Can you help me with query in case the file is 
over 4000? Also, how would search for keyword?
Thank you 

Followup:
No idea what you are trying to do with read there..


select clob_column from table;

will work or select dbms_lob.substr( col_column, for_char, from_char ) from t; 
will get a substring of it.


if the file is over 4000, just select clob_column from t; in sqlplus use "set 
long 500000" to set the max that plus will display (only applies to sqlplus) 


Rating: 5
keyword search on multiple flat files in oracle  February 24, 2004
Reviewer:  Dams  from Orlando,FL

Hello,
we have 1600 files stored on a directory of our server. What will be the easiest 
way to do a keyword search through all thes files?
Thanks 

Followup:
Oracle Text, just index them.  Then you can use SQL.

http://otn.oracle.com/pls/db92/db92.docindex?remark=homepage#index-TEX
 


Rating: 5
keyword search  February 25, 2004
Reviewer:  Dams  from Orlando, FL

Hi Tom,
can you help me out with this please?
I wanted to create this index in Oracle InterMedia and got this error message.
I can I solve it?

CREATE INDEX MASTERDB.MASTERDB_IDX 
    ON MASTERDB.NARRATIVE_TB(RESP) INDEXTYPE IS CTXSYS.CONTEXT 
    PARAMETERS (' DATASTORE CTXSYS.FILE_DATASTORE')
/
                *
ERROR at line 2:
ORA-04045: errors during recompilation/revalidation of CTXSYS.CONTEXT
ORA-29835: ODCIGETINTERFACES routine does not return required interface(s) 

Followup:
looks like an upgrade or install issue -- please contact support for assistance 
on that one. 


Rating: 3
combining file_datastore with user_datastore  March 26, 2004
Reviewer:  Parag  from Munich, Germany

Hi Tom,

We are running full text search based on a file_datastore for the files residing 
on the filesystem. Moreover, we have some simple OR conditions with main file 
content clause,for search of other attributes like:

select .. from text_files
where contains(file, 'plan') > 0
or filename like 'plan'
or extension = 'plan'

As now the search requirenments grows, we have to add lot of other attributes to 
the search criteria(owner, description, categories, keywords ...)

So on a trial basis, I have created a User_datastore for all the other 
attributes. New query would look like:

select .. 
from text_files, file_attributes
where text_files.objectid = file_attributes.objectid
and (contains(file, 'plan') > 0
    or 
    contains(fileAttr, 'plan') > 0
    )

Now the question is, is it possible to combine these two datastores together so 
that I can issue only one contains clause?

(I tried appending the File_datastores's 'file' column(on which the index is 
created) in the procedure created for User_datastore. Procedure creation went 
fine but select statement did not returned any result.
I can provide my test case as well if you want to see, but before that, I just 
want to know wheather such thing is possible at all or not?)
)

thanks a lot! 

Followup:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:440419921146



Rating: 2
No results by combining two datastores!  March 30, 2004
Reviewer:  parag  from Germany

Hi Tom,

Here is my test code for creating a File_datastore and User_datastore.

conn test/test

drop table file_content;
create table file_content(id int primary key, filePath varchar2(255));

insert into file_content values(1, 'C:\data\abc.html');

-- abc.html is result of conversion from 
-- abc.doc to abc.html by ctxhx 
-- stored in C:\data directory

begin
 ctx_ddl.drop_preference('FILE_PREFERENCE');
end;
/

begin
 ctx_ddl.create_preference('FILE_PREFERENCE','FILE_DATASTORE');
end;
/

CREATE INDEX IDX_FILE_CONTENT ON file_content(filePath)
INDEXTYPE IS CTXSYS.CONTEXT
parameters ('datastore FILE_PREFERENCE filter ctxsys.null_filter');


drop table tt;

create table tt (id number(10), varchar2_col varchar2(50), dummy_col char(1) );

insert into tt values(1,'jungle', '');
insert into tt values(2, 'forest', '');

commit;

grant select any table to ctxsys;


conn ctxsys/ctxsys

create or replace procedure pr_one(rid in rowid, tlob in out clob) is
begin
     for c1 in ( select varchar2_col, nvl(dummy_col,' ') dummy_col, filepath
               from test.tt, test.file_content
               where test.tt.id = test.file_content.id
               and test.tt.rowid = rid)
     loop
          dbms_lob.writeappend(tlob, length(c1.varchar2_col)+1, 
c1.varchar2_col||' ');
          dbms_lob.writeappend(tlob, length(c1.filepath)+1, c1.filepath||' ');
          dbms_lob.writeappend(tlob, length(c1.dummy_col), c1.dummy_col);
     end loop;
end;
/


grant execute on pr_one to test;

conn test/test@parag2

exec ctx_ddl.drop_preference('one_user_datastore');
exec ctx_ddl.drop_preference('one_lexer');

begin
     ctx_ddl.create_preference( 'one_user_datastore', 'user_datastore' );
     ctx_ddl.set_attribute( 'one_user_datastore', 'procedure','pr_one' );
  end;
/

begin
     ctx_ddl.create_preference( 'one_lexer', 'BASIC_LEXER' );
     ctx_ddl.set_attribute( 'one_lexer', 'base_letter', 'YES');
     ctx_ddl.set_attribute( 'one_lexer', 'mixed_case', 'NO' );
end;
/

create index idx_tt on tt(dummy_col)
  indextype is ctxsys.context
  parameters( 'datastore one_user_datastore lexer one_lexer');

-- test File_datastore index individually
-- 'training' is a word appearing in abc.html file
Prompt 'Testing FILE DATASTORE with contains(filepath,'training')'
Select * from file_content
where contains(filepath,'training') > 0;

/*
Result I get
ID FILEPATH 
-- -----------------
1  C:\data\abc.html
*/

prompt 'Testing USER_DATASTORE'
Select * from tt
where contains(dummy_col,'jungle') > 0;

/*
  ID VARCHAR2_COL  D
---- ------------- -
   1 jungle         
*/

prompt 'Testing Combined FILE_DATASTORE AND USER_DATASTORE'
Select * from tt
where contains(dummy_col,'training') > 0;

/*
no rows selected
*/


So if I try to search a by putting the real path which is in filepath column 
then I get the result:

Select * from tt
where contains(dummy_col,'abc') > 0;

   ID VARCHAR2_COL                                       D
----- -------------------------------------------------- -
    1 jungle

Index on File_datastore is not getting used at all!

How can solve this problem?

 

Followup:
of course not?  why would it?  

You indexed the return value of a function.  that function indexed:

          dbms_lob.writeappend(tlob, length(c1.varchar2_col)+1, 
c1.varchar2_col||' ');
          dbms_lob.writeappend(tlob, length(c1.filepath)+1, c1.filepath||' ');
          dbms_lob.writeappend(tlob, length(c1.dummy_col), c1.dummy_col);


so, when did the word 'training' ever get returned by that function?  you 
indexed the NAME of a file.  You did not index the CONTENTS of the file.

Perhaps, you meant to load the file into the blob for indexing?  like a 
dbms_lob.loadfromfile call would do?? 


Rating: 3
Combined datastore  April 02, 2004
Reviewer:  Parag  from Germany

well, as I see from dbms_lob.loadfromfile which copy the content of the file 
into our temp clob. And the content will be returned by the function to check 
against contains clasue. This will surely give my results.

but how is the index working then? If I have million number of files then 
function will load all the files, concat the content to other attributes and 
return the resulting string to check whether it contains the specified term. 
This is too expensive, and I guess that's why FILE_DATASTORE is presented so as 
to keep the file content-data as indexed one.Any query against this index will 
not check the whole content of file but just a indexed data.

What I need is to join the indexed data from file contents to the other 
attributes!!



 

Followup:
it'll be the same

the document is in the file system -- the contents of the document in the index.

whether you do it yourself in the function OR you let text read the document -- 
the same stuff ends up in the index!! the document -- it is always in the file 
system, the index -- that is always in the database.

 


Rating: 4
combined datastore  April 02, 2004
Reviewer:  Parag  from Germany

ok, just forget the previous comment. I got it what you mean by load the file 
before indexing.(function based index..)

But I see one problem here. Normally file is a static data on our platform, that 
means once it is uploaded it is used by all the users on the platform. It is not 
changed. BUT, metadata of a file(which is also part of index), like categories, 
keywords, permissions they change all the time. This means, this function based 
index will always be synchronized whenever there is a change in metadata, right?

Do you see any other way of implementing the same? 

Followup:
correct -- the index would be syncronized.


and the problem with that would be what exactly? (other than you get the right 
answer..... when you query....) 


Rating: 3
Datastore  April 05, 2004
Reviewer:  Parag  from Germany

(Wish you a nice holydays!!)
In case you see this,what do you mean by:
(other than you get the right 
answer..... when you query....) ??

If the file is too big, synchronization of index could be expensive in that 
case. Moreover as I said, we do not expect the file to be changed, but the 
metadata.
So my question was that is there a way to index the file content only once and 
use it with other metadata in a function?

 

Followup:
what i mean is

- if you update the data
- and we DID NOT sync the index
- then you get the wrong answer (as text would not find documents it should and 
it would find documents it shouldnt)


You are indexing the "row", either the entire row or nothing, in your case. 


Rating: 4
Datastore  April 05, 2004
Reviewer:  Parag  from Germany

Thanks a lot Tom. I see now there is no way I can avoid synchronizing of index 
every time the metadata changed. But as I already have documents parsed (to 
convert them to htmls and that was also a expenssive task) on my hard disk, 
hopefully, synchronizing of index should be fast enough.
(for a really big file, god help my users!!)

thanks again! 



April 05, 2004 -- Thanks for the question regarding "Performance poor after analyzeing table", version 8.1.7