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).

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

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" 

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,

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!

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

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. 

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

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

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!:)

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. :(

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.

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 ?

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;
/

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

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

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 ?

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
 

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.
 

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

Yes you are right, NLS_CHARACTERSET is set to UTF8

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 

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

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)

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!

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?

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!!

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?

(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?

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!

Hi to All,

I have seen the question asked by some one at the top.
in that he has tried to make SP with type of CLOB and tried to pass args to it.
but in the respective solution the stored procedure is created with the type Varchar.
is Varchar and Clob are same..?
expected answer is no.
so Mr. Expert can u tell me how to pass an args to Sp whose type is Clob...?
waiting for your reply,
hope u won't change ans for my question..

bye,
Hardik Mehta.

you would pass it a CLOB.

see how they called it:


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

that was a string, 'testing' is a string.


As I said in the original answer:

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

sooooo, if you have a clob, you can pass a clob.  they did not.


Note that in the period of time since they first asked -- many things have changed.  In 9i, clobs are much more "string like" so this works:

ops$tkyte@ORA9IR2> create or replace procedure p( p_clob in clob )
  2  as
  3  begin
  4          dbms_output.put_line( p_clob );
  5  end;
  6  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec p( 'testing' )
testing
 
PL/SQL procedure successfully completed.
 

Whereas it would not compile in 8i:


tkyte@ORA8I> create or replace procedure p( p_clob in clob )
  2  as
  3  begin
  4          dbms_output.put_line( p_clob );
  5  end;
  6  /

Warning: Procedure created with compilation errors.


because of the dbms_output (not expecting a clob) and if we take out the dbms_output, the call would fail:


tkyte@ORA8I> create or replace procedure p( p_clob in clob )
  2  as
  3  begin
  4          null;
  5          -- dbms_output.put_line( p_clob );
  6  end;
  7  /

Procedure created.

tkyte@ORA8I>
tkyte@ORA8I> exec p( 'testing' )
BEGIN p( 'testing' ); END;

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



Sooo, in 8i and before, to pass a clob -- TAKES A CLOB.  In 9i and later, a string will be automagically converted into a clob for you if you try to pass it (and a clob to a string if you try to use it that way) 

Hi Tom,

thanks for your follow-up..

1.
In the above example , what happens when we assign some string which is greater than 32k to parameter: p_clob

2.
is it advisable to use Varchar2 parameters for passing in CLOBs to stored procedures ?


Thanks in advance.

Hi Tom,

I have a table and stored procedure and don't know how to test it:

create table chen_test (x CLOB);

CREATE OR REPLACE PROCEDURE add_chen (p_x chen_test.x%TYPE)
AS
v_x CLOB;
BEGIN
INSERT INTO alias.chen_test
(x
)
VALUES (EMPTY_CLOB ()
)
RETURNING x
INTO v_x;

DBMS_LOB.WRITE (v_x, DBMS_LOB.getlength (p_x), 1, p_x);
END add_chen_test;

My clob data is about 60KB with 1000 lines like this:

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxx
......
.....
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxx

How do I test this SP?

Thanks in advance for your help.

Hi Tom,

You were correct. I remembered in early version of Oracle, I had to initial clob field by using EMPTY_CLOB () function. It works as you pointed out now.

SQL> CREATE OR REPLACE PROCEDURE add_chen_test (p_clob_data chen_test.clob_data%TYPE)
  2  AS
  3     v_clob_data   CLOB;
  4  BEGIN
  5     INSERT INTO alias.chen_test
  6                 (clob_data
  7                 )
  8          VALUES (p_clob_data
  9                 );
 10  END add_chen_test;
 11  /

Procedure created.

SQL> exec add_chen_test('ahahrahetajeajwej');

PL/SQL procedure successfully completed.

I have developers using c++ to invoke this code, but data gets truncated. Is this because 32KB limitation in PL/SQL? In a case, they got ORA-01460: unimplemented or unreasonable conversion requested. So, I logged a TAR in metalink, the analyst ask me a test case, which includes how to test this SP. I wrote a sp to try to insert this piece of CLOB into the table, but it inserts 903 records instead of one record because there is caracter returns in the clob data:

CREATE OR REPLACE PROCEDURE test_add_chen_test (
   p_file_dir    IN   VARCHAR2,
   p_file_name   IN   VARCHAR2
)
AS
   v_datafilehandle   UTL_FILE.file_type;
   v_logfilehandle    UTL_FILE.file_type;
   v_newline          CLOB;
   v_sqlstmt          VARCHAR2 (100);
BEGIN
   v_sqlstmt :=
      'CREATE OR REPLACE DIRECTORY fbi_dispo_dir AS ''' || p_file_dir || '''';

   EXECUTE IMMEDIATE v_sqlstmt;

   -- Open the CLOB data file for reading.
   v_datafilehandle := UTL_FILE.fopen ('FBI_DISPO_DIR', p_file_name, 'R');

   -- Loop over the data file, reading in each line.
   -- GET_LINE will raise NO_DATA_FOUND when it is done.
   LOOP
      BEGIN
         UTL_FILE.get_line (v_datafilehandle, v_newline);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            EXIT;
      END;

      IF v_newline IS NOT NULL
      THEN
         INSERT INTO alias.chen_test
              VALUES (v_newline);
      END IF;
   END LOOP;

   COMMIT;
   -- Close the data file.
   UTL_FILE.fclose (v_datafilehandle);
END test_add_chen_test;
/

Can you please help?

Thanks. 

Hi Tom,

Yes, column will contains more than 4000 characters. That's why we use CLOB datatype instead of VARCHAR2 datatype. Did you mean that they can't invoke PL/SQL to populate the columns? Do you have some sample codes how Java or c++ handle this? Or can you please point me some doc/notes to learn how to deal with this requirement?

Thanks very much.

for java -- yuo can create a temporary clob for example -- fill it up and pass it in.

What would probably be "best" here -- would be for the plsql to insert an empty_clob() and use returning it get it back out and RETURN THAT EMPTY_CLOB() to java and let it stream into it directly (less copy from here to there going one)


ProC and OCI have similar documentation:

Hi Tom,

"In Java or ProC or OCI you can create a client clob, populate it and pass it."

What about in PL/SQL? Can we create a client side CLOB in PL/SQL and pass that to a procedure? An example would help. Thanks!

Kashif

Hey Tom,

Thanks, I guess what threw me off was the documentation (9i LOB Guide, Page 3-7), which says:

"Client-side PL/SQL procedures cannot call DBMS_LOB package routines.

However, you can use server-side PL/SQL procedures or anonymous blocks in Pro*C/C++ to call DBMS_LOB package routines."

I assumed they were referring to Oracle Forms and such when they say client-side procedures, though that's not what you said. Any ideas what they were referring to? Thanks!

Kashif

I actually AM trying to pass a CLOB into a stored procedure. Previously this worked just fine passing in a varchar but now we have a scenario where the data is just to long for that. I changed the IN var datatype to CLOB, and also the statement to set the length but am still getting an error on my dbms_lob.write statement. As follows:

CREATE OR REPLACE PROCEDURE CLOB_INSERT
( PRIMARY_NBR_IN IN VARCHAR2,
CLOB_DATA_IN IN CLOB

) IS
lob_loc CLOB;
pos CONSTANT INTEGER := 1;
len BINARY_INTEGER;
v_SQLErrCd NUMBER := 1;
v_SQLErrMsg VARCHAR2(256) := 'Initial';
BEGIN
-- len := Length(CLOB_DATA_IN);
len := dbms_lob.getlength(CLOB_DATA_IN);


Insert into CLOB_TAB
(PRIMARY_NBR,
CLOB_DATA
)
values
(PRIMARY_NBR_IN,
empty_clob()
);
COMMIT;


SELECT CLOB_DATA
INTO lob_loc
FROM CLOB_TAB
WHERE PRIMARY_NBR = PRIMARY_NBR_IN
FOR UPDATE;
dbms_lob.write (lob_loc, len, pos, CLOB_DATA_IN);
COMMIT;

END CLOB_INSERT;
/

Error:
PLS-00306: wrong number or types of arguments in call to 'WRITE'

What do I need to do to change this statement (oracle 8i).
Thanks!

Well Well...Sometimes the most obvious answer is something that we overlook. Since our input data was being changed from a varchar to a clob I thought it would be more difficult. Thanks again for the input!

Hi,

I am attaching a piece of code from my proc as shown below:

SELECT IMG INTO TIMG
FROM tab1
WHERE ID = PID ;
PLEN := DBMS_LOB.GETLENGTH(TIMG);
DBMS_LOB.READ(TIMAGE,plen,1,PIMAGE1);

I am getting the foll error
ORA-21560: argument 2 is null, invalid, or out of range

Pls help.

the dbms_lob.read should be as follows

DBMS_LOB.READ(TIMG,plen,1,PIMAGE1);

where pimage1 is RAW and timg is blob.

Thanks.

ops$tkyte@ORA9IR2> create table tab1 ( img blob, id int );
 
Table created.
 
ops$tkyte@ORA9IR2> insert into tab1 values ( empty_blob(), 1 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          pid number := 1;
  3          plen number;
  4          pimage1 long raw;
  5          timg blob;
  6  begin
  7      SELECT IMG INTO TIMG
  8        FROM tab1
  9       WHERE ID = PID ;
 10
 11     PLEN := DBMS_LOB.GETLENGTH(TIMG);
 12     DBMS_LOB.READ(TIMG,plen,1,PIMAGE1);
 13  end;
 14  /
declare
*
ERROR at line 1:
ORA-21560: argument 2 is null, invalid, or out of range
ORA-06512: at "SYS.DBMS_LOB", line 707
ORA-06512: at line 12
 
 

perhaps argument 2 really is null, invalid, or out of range.  print it out and see what you see.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          pid number := 1;
  3          plen number;
  4          pimage1 long raw;
  5          timg blob;
  6  begin
  7      SELECT IMG INTO TIMG
  8        FROM tab1
  9       WHERE ID = PID ;
 10
 11     PLEN := DBMS_LOB.GETLENGTH(TIMG);
 12     dbms_output.put_line( 'plan = "' || plen || '"' );
 13     DBMS_LOB.READ(TIMG,plen,1,PIMAGE1);
 14  end;
 15  /
plan = "0"
declare
*
ERROR at line 1:
ORA-21560: argument 2 is null, invalid, or out of range
ORA-06512: at "SYS.DBMS_LOB", line 707
ORA-06512: at line 13
 

<b>maybe it is ZERO</b>

 

I have a CLOB variable that contains dynamic SQL statement. The reason of using a CLOB is the SQL may be greater than 32K.

I have to pass the contents out of the SQL through a ref cursor.

How does ref cursor handles with a clob variable?

Thanks,

If you have an example for this, could you please share it with us?

Thanks,

Hi Tom,
I am getting this error in my procedures,i just want to know what type of errror it is?? i am using 10gdatabase and .Net as a front end.in one environment it is not giving any error but in other environment it is giving error.
Can you tell us,is this a any enviornmental error or any programming issue.

Vishal

Below procedure contains XMLDATA as varchar2 how can we execute into clob as the label quantity gets higher pls help.

create or replace PROCEDURE XXACC_LBL_IN_OUT_QUICK
(ErrMsg OUT VARCHAR2
, ErrCode OUT VARCHAR2
, p_label_name IN VARCHAR2
, p_label_size IN VARCHAR2
, p_organization_id IN NUMBER
, p_Delivery IN VARCHAR2
, P_SO_NUMBER IN VARCHAR2
, P_SO_LINE IN VARCHAR2
, P_LOT_NUM IN VARCHAR2
, P_QTY IN NUMBER
, P_CONTAINER_QTY IN NUMBER
, P_COMMENTS IN VARCHAR2
, P_include_address IN VARCHAR2 )
AS

XMLDATA                     VARCHAR2 (32767);

JOBSTATUS VARCHAR2 (300);
PRINTERSTATUS VARCHAR2 (300);
STATUSTYPE NUMBER;
LINEPOS NUMBER;
LINELEN NUMBER;
L_PRINTER VARCHAR2 (50);
l_copies NUMBER;
l_printer_type VARCHAR2 (50);

l_head1 VARCHAR2 (200);
l_head2 VARCHAR2 (200);
l_head3 VARCHAR2 (200);
l_head4 VARCHAR2 (200);
l_head5 VARCHAR2 (200);

-- Local Variables
l_SHIPPED_QUANTITY NUMBER;
l_order_quantity_uom VARCHAR2(10);
l_ordered_item VARCHAR2(40);
l_container_qty NUMBER;
l_cust_po_number VARCHAR2(50);
l_segment1 VARCHAR2(40);
l_REVISION VARCHAR2(10);
l_description VARCHAR2(100);
l_organization_id NUMBER;
l_customer_name VARCHAR2(50);


CURSOR Line_info(l_so_number VARCHAR2, l_so_line VARCHAR2) IS
SELECT l.SHIPPED_QUANTITY
, l.order_quantity_uom
, REPLACE(l.ordered_item, '&','&'||'amp;') ordered_item
, l.cust_po_number
, REPLACE(msi.segment1, '&','&'||'amp;') segment1
, REV.REVISION
, REPLACE(msi.description, '&','&'||'amp;') description
, msi.organization_id
, REPLACE(c.customer_name, '&','&'||'amp;') customer_name
FROM oe_order_lines_all l
, oe_order_headers_all h
, mtl_system_items msi
, mtl_item_revisions_b rev
, ar_customers c --remediate
WHERE l.header_id = h.header_id
AND l.inventory_item_id = msi.inventory_item_id
AND l.ship_from_org_id = msi.organization_id
AND MSI.INVENTORY_ITEM_ID = REV.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = REV.ORGANIZATION_ID
AND l.sold_to_org_id = c.customer_id
AND h.order_number = l_so_number
AND l.line_number||'.'||l.shipment_number = l_so_line;

CURSOR Cust_address(l_so_number VARCHAR2, l_so_line VARCHAR2) IS
SELECT arp_addr_label_pkg.format_address(
address_style,
address1, address2, address3, address4,
city, county, state, province, postal_code,
NULL, country, customer_name,NULL,
NULL, NULL, NULL, NULL, NULL, NULL, 'N',
100, 100, 100 ) address
FROM oe_order_lines_all l
, oe_order_headers_all h
, ar_customers c
, HZ_CUST_SITE_USES_ALL s --remediate for r11 to r12
-- , ar_addresses_v A
, hz_cust_acct_sites_all hcas
, hz_party_sites hps
, hz_locations hl
WHERE l.header_id = h.header_id
AND l.sold_to_org_id = c.customer_id
AND l.ship_to_org_id = s.site_use_id
--AND s.CUST_ACCT_SITE_ID = A.address_id
AND s.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND h.order_number = l_so_number
AND l.line_number || '.' || l.shipment_number = l_so_line;

BEGIN
--l_printer := 'S600_DEV';
--l_copies := 1;
--l_printer_type := 'STRIPE600';

SELECT PRINTER, NUMBER_OF_COPIES
INTO L_PRINTER, l_copies
FROM FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID;

SELECT printer_type
INTO l_printer_type
FROM fnd_printer
WHERE printer_name = l_printer;


FND_FILE.put_line( FND_FILE.LOG, 'L_PRINTER: ' ||L_PRINTER );
FND_FILE.put_line( FND_FILE.LOG, 'L_COPIES: ' ||L_COPIES );
FND_FILE.put_line( FND_FILE.LOG, 'L_PRINTER_TYPE: ' ||L_PRINTER_TYPE );
FND_FILE.put_line( FND_FILE.LOG, 'L_PRINTER: ' ||L_PRINTER );

FND_FILE.put_line( FND_FILE.LOG, 'p_label_name: ' ||p_label_name );
FND_FILE.put_line( FND_FILE.LOG, 'p_label_size: ' ||p_label_size );
FND_FILE.put_line( FND_FILE.LOG, 'p_organization_id: ' ||p_organization_id );
FND_FILE.put_line( FND_FILE.LOG, 'P_SO_NUMBER: ' ||P_SO_NUMBER );
FND_FILE.put_line( FND_FILE.LOG, 'P_SO_LINE: ' ||P_SO_LINE );

FND_FILE.put_line( FND_FILE.LOG, 'P_LOT_NUM: ' ||P_LOT_NUM );
FND_FILE.put_line( FND_FILE.LOG, 'P_QTY: ' ||P_QTY );
FND_FILE.put_line( FND_FILE.LOG, 'P_CONTAINER_QTY: ' ||P_CONTAINER_QTY );


--
-- Building Shipping Label
--
xmldata := '<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE labels SYSTEM "C:\Program Files\Loftware Labeling\Batch\label.dtd">';
-- xmldata := xmldata || '<labels _FORMAT="' || p_label_name||'_'||l_printer_type||'.lwl';
-- ********************************************************************************************************
xmldata := xmldata || '<labels _FORMAT="' || p_label_name||'_'||p_label_size||'_'||l_printer_type||'.lwl';
FND_FILE.put_line( FND_FILE.LOG, 'FILE: ' || p_label_name||'_'||p_label_size||'_'||l_printer_type||'.lwl' );
-- ********************************************************************************************************
xmldata := xmldata || '" _JOBNAME="' || p_label_name||'_'||l_printer_type;
xmldata := xmldata || '" _PRINTERNAME="' ||l_printer;
xmldata := xmldata || '"><label _QUANTITY="' || l_copies;
xmldata := xmldata || '"><variable name="LOT_NUM">' || p_lot_num;
xmldata := xmldata || '</variable><variable name="COMMENTS">' || p_comments;
FOR C1 IN line_INFO (P_SO_NUMBER, P_SO_LINE)
LOOP

-- FND_FILE.put_line( FND_FILE.LOG, 'C1: ' || C1 );
xmldata := xmldata || '</variable><variable name="QUANTITY_UOM">' || NVL(p_qty, p_container_qty)||' '||c1.order_quantity_uom;
xmldata := xmldata || '</variable><variable name="CUSTOMER_ITEM">' || c1.ordered_item;
xmldata := xmldata || '</variable><variable name="ITEM_DESC">' || c1.description;
xmldata := xmldata || '</variable><variable name="ORDER_LINE">' || p_so_number||'/'||p_so_line;
xmldata := xmldata || '</variable><variable name="CUSTOMER">' || c1.customer_name;
xmldata := xmldata || '</variable><variable name="ITEM">' || c1.segment1||' REV '||c1.revision;
xmldata := xmldata || '</variable><variable name="CUSTOMER_PO">' || c1.cust_po_number;
xmldata := xmldata || '</variable></label>';

IF p_qty IS NOT NULL AND p_container_qty IS NOT NULL AND p_qty>=p_container_qty THEN
l_container_qty := p_qty;

WHILE l_container_qty > 0
LOOP
--FND_FILE.put_line( FND_FILE.LOG, 'i: ' || i );
FND_FILE.put_line( FND_FILE.LOG, 'p_qty: ' || p_qty );
FND_FILE.put_line( FND_FILE.LOG, 'FLOOR(p_qty/p_container_qty): ' || FLOOR(p_qty/p_container_qty) );
FND_FILE.put_line( FND_FILE.LOG, 'p_container_qty: ' || p_container_qty );



xmldata := xmldata || '<label _FORMAT="' || p_label_name||'_'||p_label_size||'_'||l_printer_type||'.lwl';
xmldata := xmldata || '"><variable name="LOT_NUM">' || p_lot_num;
xmldata := xmldata || '</variable><variable name="COMMENTS">' || p_comments;
IF l_container_qty>=p_container_qty THEN
-- l_container_qty := p_container_qty + MOD(p_qty,p_container_qty);
xmldata := xmldata || '</variable><variable name="QUANTITY_UOM">' || p_container_qty||' '||c1.order_quantity_uom;
ELSE
xmldata := xmldata || '</variable><variable name="QUANTITY_UOM">' || l_container_qty||' '||c1.order_quantity_uom;
END IF;
l_container_qty := l_container_qty - p_container_qty;
xmldata := xmldata || '</variable><variable name="CUSTOMER_ITEM">' || c1.ordered_item;
xmldata := xmldata || '</variable><variable name="ITEM_DESC">' || c1.description;
xmldata := xmldata || '</variable><variable name="ORDER_LINE">' || p_so_number||'/'||p_so_line;
xmldata := xmldata || '</variable><variable name="CUSTOMER">' || c1.customer_name;
xmldata := xmldata || '</variable><variable name="ITEM">' || c1.segment1||' REV '||c1.revision;
xmldata := xmldata || '</variable><variable name="CUSTOMER_PO">' || c1.cust_po_number;
xmldata := xmldata || '</variable></label>';
END LOOP;
END IF;
END LOOP;
--
-- Bulid Address Label if requested
--

IF p_include_address = 'Y' THEN
xmldata := xmldata || '<label _FORMAT="' || 'XXACC_CUSTADD_'||p_label_size||'_'||l_printer_type||'.lwl';

FND_FILE.put_line( FND_FILE.LOG, 'FILE: ' || 'XXACC_CUSTADD_'||p_label_size||'_'||l_printer_type||'.lwl' );
xmldata := xmldata || '" _QUANTITY="' || l_copies;
FOR C2 IN Cust_address (P_SO_NUMBER, P_SO_LINE)
LOOP
SELECT SUBSTR(c2.address, 1, INSTR(c2.address, CHR(10), 1, 1))
, DECODE(INSTR(c2.address, CHR(10), 1, 1), 0, NULL, SUBSTR(c2.address, INSTR(c2.address, CHR(10), 1, 1)+1 ,DECODE(INSTR(c2.address, CHR(10), 1, 2), 0, 9999, INSTR(c2.address, CHR(10), 1,2)- INSTR(c2.address, CHR(10), 1, 1) -1 )))
, DECODE(INSTR(c2.address, CHR(10), 1, 2), 0, NULL, SUBSTR(c2.address, INSTR(c2.address, CHR(10), 1, 2)+1 ,DECODE(INSTR(c2.address, CHR(10), 1, 3), 0, 9999, INSTR(c2.address, CHR(10), 1,3)- INSTR(c2.address, CHR(10), 1, 2) -1 )))
, DECODE(INSTR(c2.address, CHR(10), 1, 3), 0, NULL, SUBSTR(c2.address, INSTR(c2.address, CHR(10), 1, 3)+1 ,DECODE(INSTR(c2.address, CHR(10), 1, 4), 0, 9999, INSTR(c2.address, CHR(10), 1,4)- INSTR(c2.address, CHR(10), 1, 3) -1 )))
, DECODE(INSTR(c2.address, CHR(10), 1, 4), 0, NULL, SUBSTR(c2.address, INSTR(c2.address, CHR(10), 1, 4)+1 ,DECODE(INSTR(c2.address, CHR(10), 1, 5), 0, 9999, INSTR(c2.address, CHR(10), 1,5)- INSTR(c2.address, CHR(10), 1, 4) -1 )))
INTO l_head1
, l_head2
, l_head3
, l_head4
, l_head5
FROM DUAL;

END LOOP;
xmldata := xmldata || '"><variable name="HEAD1">' || l_head1;
xmldata := xmldata || '</variable><variable name="HEAD2">' || l_head2;
xmldata := xmldata || '</variable><variable name="HEAD3">' || l_head3;
xmldata := xmldata || '</variable><variable name="HEAD4">' || l_head4;
xmldata := xmldata || '</variable><variable name="HEAD5">' || l_head5;
xmldata := xmldata || '</variable></label>';
END IF;
xmldata := xmldata || '</labels>';

INV_SYNC_PRINT_REQUEST.SYNC_PRINT_REQUEST (XMLDATA, JOBSTATUS, PRINTERSTATUS, STATUSTYPE);

LINEPOS := 0;
fnd_file.put_line (fnd_file.output, 'jobstatus:');
DBMS_OUTPUT.PUT_LINE ('jobstatus:');

WHILE LENGTH (JOBSTATUS) > LINEPOS
LOOP
IF LENGTH (JOBSTATUS) - LINEPOS > 254
THEN
LINELEN := 254;
ELSE
LINELEN := LENGTH (JOBSTATUS) - LINEPOS;
END IF;

DBMS_OUTPUT.PUT_LINE (SUBSTR (JOBSTATUS, LINEPOS, LINELEN));
fnd_file.put_line (fnd_file.output, SUBSTR (JOBSTATUS, LINEPOS, LINELEN));
LINEPOS := LINEPOS + LINELEN;
END LOOP;

DBMS_OUTPUT.PUT_LINE (' ');
fnd_file.put_line (fnd_file.output, ' ');
LINEPOS := 0;
fnd_file.put_line (fnd_file.output, 'printerstatus:');
DBMS_OUTPUT.PUT_LINE ('printerstatus:');

WHILE LENGTH (PRINTERSTATUS) > LINEPOS
LOOP
IF LENGTH (PRINTERSTATUS) - LINEPOS > 254
THEN
LINELEN := 254;
ELSE
LINELEN := LENGTH (PRINTERSTATUS) - LINEPOS;
END IF;

DBMS_OUTPUT.PUT_LINE (SUBSTR (PRINTERSTATUS, LINEPOS, LINELEN));
fnd_file.put_line (fnd_file.output, SUBSTR (PRINTERSTATUS, LINEPOS, LINELEN));
LINEPOS := LINEPOS + LINELEN;
END LOOP;

DBMS_OUTPUT.PUT_LINE (' ');
fnd_file.put_line (fnd_file.output, ' ');
DBMS_OUTPUT.PUT_LINE ('statustype: ' || TO_NUMBER (STATUSTYPE));
fnd_file.put_line (fnd_file.output, 'statustype: ' || TO_NUMBER (STATUSTYPE));
END XXACC_LBL_IN_OUT_QUICK;

Thanks for your followup,

My request is XMLDATA variable type was VARCHAR2 and size 32767 but I need it to get as CLOB because the data gets more than 32767 to XMLDATA variable. So I need to handle CLOB in above given Procedure XXACC_LBL_IN_OUT_QUICK. Please help to fix to get normal running in concurrent program when the parameter of P_Qty was more than 10000.

xmldata := xmldata || l_string

dbms_lob.writeappend(xmldata,length(l_string),l_string);