Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Erik .

Asked: May 02, 2000 - 1:20 pm UTC

Last updated: May 19, 2005 - 8:04 am UTC

Version:

Viewed 1000+ times

You Asked

Good morning!

I represent a major sofware house here in Norway and we have
supported Oracle databases since 97. A major frustration among
several of our customers relates to case sensitivity when
retrieving data. Is there an easy way we can instruct Oracle NOT
to be case sensitve?



and Tom said...



In Oracle8i, release 8.1 -- this is pretty much solved.

In Oracle7, the standard solution for this is programatic. Create a 'mirror' column of the data you want to search in a case insensitive fashion. eg: alter table t add ename_upper varchar2(25);. Then create a trigger to maintain that column:

create or replace trigger t before insert or update on t for each row
begin
:new.ename_upper := upper(:new.ename);
end;

Then, in the search application, your developers would know to code:

select ename from T where ename_upper = upper(:search_criteria);


In Oracle8i, release 8.1, a new feature (perhaps reason enough to upgrade for you) was added. This is called function based indexes. See </code> http://asktom.oracle.com/~tkyte/article1/index.html <code>for a paper on this feature and how it works. It solves this issue much more elegantly.



Rating

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Case-insensitive Search & Replace on CLOB<32KB in Orcale 9iR2

Shimon Tourgeman, March 24, 2005 - 2:43 am UTC

Dear Tom,

I'm using DBMS_METADATA.GET_DDL to extract a DDL for VIEWS and other database objects. I also use
dictionary table USER_DEPENDENCIES to spot all the objects referenced by a view, and replace their
names by a new naming the view DDL (according to a new naming conventions schema which our
application should obey).

Using replace() I can do only case -sensitive search and replace. So, if I have in my database a
function called GET_SOMETHING(), and my view call this function as "Get_Something()", a simple
search & replace won't help to find this mixed case names. The same applies of course to table
names, other views names, etc.

My question is: How to do an efficient case-insensitive Search & Replace on CLOB's?

Here's an example:

I use this function I wrote - which fails in spotting the exact location of names in a string:

The goal:
We want a search & replace function that will get :
old_object_name - varchar2(30),
object_type - varchar2(30), --> function.. for test purposes only
new_object_name - varchar2(30),

and return a CLOB which will contain the function DDL, with its new_object_name replacing ANY occurrence of old_object_name.

for example, If our function is

CREATE OR REPLACE FUNCTION FROMSFDATE
(dateIn in NUMBER) RETURN DATE
DETERMINISTIC is
BEGIN
Return(dateIn/86400000 + date'1970-01-01');
REM FROMSFDATE
END FROMSFDate;

Then we want to output to be a CLOB like this:

CREATE OR REPLACE FUNCTION NEW_FROM_SF_DATE
(dateIn in NUMBER) RETURN DATE
DETERMINISTIC is
BEGIN
Return(dateIn/86400000 + date'1970-01-01');
REM NEW_FROM_SF_DATE;
END NEW_FROM_SF_DATE;


Steps to reproduce the problem:

-- First, install this small test function

CREATE OR REPLACE FUNCTION FROMSFDATE
(dateIn in NUMBER) RETURN DATE
DETERMINISTIC is
BEGIN
Return(dateIn/86400000 + date'1970-01-01');
REM FROMSFDATE
END FROMSFDate;


-- Then, install my function to test my Search & Replace function:

CREATE OR REPLACE FUNCTION Replace_Old_Names
(i_old_object_name in varchar2,
i_object_type in varchar2,
i_new_object_name in varchar2)
return CLOB
IS

l_new_object_DDL varchar2(32760);
l_UPPERCASE_DDL varchar2(32760);
l_old_object_DDL varchar2(32760);
l_position number:=0;
l_occurrences number:=0;

BEGIN
l_old_object_DDL:=DBMS_METADATA.GET_DDL(upper(i_object_type),upper(i_old_object_name));
l_new_object_DDL:=l_old_object_DDL;
l_UPPERCASE_DDL:=upper(l_old_object_DDL);

l_occurrences:=0;
loop
-- l_position:=dbms_lob.instr(l_UPPERCASE_DDL,upper(i_old_object_name),1,l_occurrences+1);
l_position := instr(l_UPPERCASE_DDL,upper(i_old_object_name),1,l_occurrences+1);
exit when l_position=0;
if l_position > 0 then
-- l_new_object_DDL:=dbms_lob.substr(l_new_object_DDL,1,l_position-1)||i_new_object_name||dbms_lob.substr(l_new_object_DDL,l_position+length(i_old_object_name));
l_new_object_DDL:=substr(l_new_object_DDL,1,l_position-1)||i_new_object_name||substr(l_new_object_DDL,l_position+length(i_old_object_name));
-- l_UPPERCASE_DDL:=upper(l_new_object_DDL);
l_occurrences:=l_occurrences+1;
end if;
end loop;

return(l_new_object_ddl);

end Replace_Old_Names ;

--**************************************************************************************

-- Now test it

select Replace_Old_Names ('FROMSFDATE','FUNCTION','NEW_FROM_SF_DATE') from dual

which returns:


CREATE OR REPLACE FUNCTION "SCOTT"."NEW_FROM_SF_DATE"
(dateIn in NUMBER) RETURN DATE
DETERMINISTIC is
BEGIN
Return(dateIn/86400000 + date'1970-01-01')NEW_FROM_SF_DATNEW_FROM_SF_DATED FROMSFDate;

--**************************************************************************************


The problem is - as far as I can see - a mal-function of the INSTR function:

select instr(upper(DBMS_METADATA.GET_DDL('FUNCTION','FROMSFDATE')),'FROMSFDATE',1,1) from dual

-- returns: 40
-- should return 28 - the location of the first character of the first "FROMSFDATE" searched word.

-- and if we look for the 2nd occurrence:

select instr(upper(DBMS_METADATA.GET_DDL('FUNCTION','FROMSFDATE')),'FROMSFDATE',1,2) from dual

-- returns: 166

It seems that INSTR, when it is operating on CLOB<32KB, does not calculate correctly the location of a string.
Could it be a matter of CR/LF ? and if it is - how can I remove/ignore them?


Thanks in advance !

Shimon Tourgeman.


thanks,
shimon.



Tom Kyte
March 24, 2005 - 8:46 am UTC

in 9i, you would have to write your own

in 10g, you can use regex_replace

Thanks Tom. Just for the protocol...here's the solution I found

Shimon Tourgeman, March 28, 2005 - 11:24 am UTC

-- the following function demostrates search & replace
-- on a CLOB, returning a new CLOB with i_old_object_name
-- replaced with i_new_object_name.

CREATE OR REPLACE FUNCTION Replace_Old_Name (i_old_object_name in varchar2, i_object_type in varchar2, i_new_object_name in varchar2)
return CLOB
IS

l_new_object_DDL varchar2(32760);
l_UPPERCASE_DDL varchar2(32760);
l_old_object_DDL varchar2(32760);
l_position number:=0;
l_occurrences number:=0;
l_start_from number;
l_last_position number;

BEGIN
l_old_object_DDL:=replace(DBMS_METADATA.GET_DDL(upper(i_object_type),upper(i_old_object_name)),chr(10),'<BR>');
l_UPPERCASE_DDL:=upper(l_old_object_DDL);

l_new_object_DDL:='';
l_occurrences:=1;
l_start_from:=1;
l_last_position:=1;

loop
l_position:=instr(l_UPPERCASE_DDL,upper(i_old_object_name),1,l_occurrences);
exit when l_position=0;
if l_position > 0 then

-- build the new CLOB while preserving old string case
l_new_object_DDL:=l_new_object_DDL||substr(l_old_object_DDL,l_start_from,l_position-l_start_from)||i_new_object_name;
l_start_from:=l_position+length(i_old_object_name) ;
l_occurrences:=l_occurrences+1;
l_last_position:=l_position;
end if;
end loop;
-- attach the reminder
l_new_object_DDL:=l_new_object_DDL||substr(l_old_object_DDL,l_start_from,length(l_old_object_DDL)-l_start_from+1) ;

return(replace(l_new_object_ddl,'<BR>',chr(10)));

end Replace_Old_Name ;

--***************************************

-- testing it

Assume we have a function called FROMSFDATE and we want to extract its CREATE DDL, and recreate the function as NEW_FROM_SF_DATE.

The function Original DDL:
CREATE OR REPLACE FUNCTION FROMSFDATE
(dateIn in NUMBER) RETURN DATE
DETERMINISTIC is
BEGIN
Return(dateIn/86400000 + date'1970-01-01');
-- remark FROMSFDATE
END FROMSFDate;

The wanted DDL:
CREATE OR REPLACE FUNCTION NEW_FROM_SF_DATE
(dateIn in NUMBER) RETURN DATE
DETERMINISTIC is
BEGIN
Return(dateIn/86400000 + date'1970-01-01');
-- remark NEW_FROM_SF_DATE
END NEW_FROM_SF_DATE;

Using the above function, we can say:

select replace_old_name ('FROMSFDATE','FUNCTION','NEW_FROM_SF_DATE') from dual ;

and also we can say:

declare
l_ddl varchar2(32760);
begin
select replace_old_name ('FROMSFDATE','FUNCTION','NEW_FROM_SF_DATE')
into l_ddl
from dual;

execute immediate l_ddl;
execute immediate 'drop function FROMSFDATE' ;
end
/

(-:

Thanks again !
Shimon.

replace on clob in oracle 10g

Bipin Ganar, April 22, 2005 - 12:21 am UTC

Hi Tom,

Till 9i Release 2 version I was able to play with replace function for CLOB. But Now, we are trying to test same PL/SQL in 10g Release

Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bi
PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for Solaris: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production

I'm getting the error "ORA-22922".
I Tried the same things in 9i Release 2, there it works.

Please let me know if any problem with replace on clob in 10g.

Tom Kyte
April 22, 2005 - 10:25 am UTC

example please.

Continuation of query related with ORA-22922

Bipin Ganar, April 25, 2005 - 4:42 am UTC

Please find the following pl/sql block and procedure.

Declare
v_XML_clob clob;
v_RowSetTag VARCHAR2(20):= 'Booking';
v_queryCtx DBMS_XMLQuery.ctxType;
v_resultout BOOLEAN;
v_error_msg VARCHAR2(1000);
Begin
v_queryCtx := DBMS_XMLQuery.newContext('select * from emp where empno in (select max(empno) from emp)');

Generate_XML(v_queryCtx,v_RowSetTag,v_XML_clob,v_resultout,v_error_msg );

v_XML_clob:=REPLACE(v_XML_clob,'<BookingLeg>'||CHR(10),NULL); /* Line which is giving error ORA-22922 at the time of execution*/

End;

***************************
Generate_XML(

CREATE OR REPLACE PROCEDURE GENERATE_XML
(P_QUERYCTX IN VARCHAR2
,P_ROWSETTAG IN VARCHAR2
,P_XML_CLOB OUT CLOB
,P_RESULTOUT OUT BOOLEAN
,P_ERRORMSG IN OUT VARCHAR2
)
IS
/* Purpose: To generate an XML message with the input as a XML Query context. */

/*Variable declarations*/
v_errorNum NUMBER;
v_errorMsg VARCHAR2(512);
v_xml_version VARCHAR2(500);
v_xml_attribute VARCHAR2(500);

BEGIN

DBMS_XMLQuery.setRowSetTag(p_queryCtx,p_RowSetTag);
DBMS_XMLQuery.setRowTag(p_queryCtx,Null);

p_XML_clob := DBMS_XMLQuery.getXML(p_queryCtx);

SELECT value INTO v_xml_version
FROM system_parameters
WHERE param_name='XML_VERSION';

SELECT value INTO v_xml_attribute
FROM system_parameters
WHERE param_name='XML_ATTRIBUTE';

p_XML_clob:=REPLACE(p_XML_clob,'<?xml version = ''1.0''?>',v_xml_version);
p_XML_clob:=REPLACE(p_XML_clob,'<'||p_RowSetTag||'>','<'||p_RowSetTag||' '||v_xml_attribute||'>');

DBMS_XMLQuery.closeContext(p_queryCtx);

p_resultout:=TRUE;
p_errormsg:=NULL;

EXCEPTION
WHEN Others THEN
p_resultout:=FALSE;
p_errormsg:='Max_Generate_XML ERROR: '||sqlerrm;
DBMS_XMLQuery.getExceptionContent(p_queryCtx,v_errorNum,v_errorMsg);
dbms_output.put_line('Max_Generate_XML: Exception XMLQuery '||v_errorNum||'-'||v_errorMsg);
dbms_output.put_line('Max_Generate_XML: Exception Others '||sqlerrm);
END Generate_XML;
/


In the above case i'm getting the error at PL/SQL block.

ORA-22922: nonexistent LOB value -22922

Tom Kyte
April 25, 2005 - 9:05 am UTC

I asked Sean Dillon, my XML Technologist to take a look at this here's what he had to say:
--

Bipin,

First, I can't reproduce your problem on my 9.2.0.1 database, so I'm not sure how you're getting it what version are you on.

Second, I suggest you look into SQLX or SQLXML (you can search AskTom for the many examples of SQLX here or look in the Oracle doc for more of the same) instead of using DBMS_XMLQUERY. SQLXML is much easier to use when it comes to creating XML documents, uses the native XMLType data type, and can give you a CLOB value which you could then operate after the fact if you so desired.



Clob Out in 10g

BIPIN GANAR, April 26, 2005 - 1:02 am UTC 

Hi Tom,

Still I'm not able to find the solution. As soon as out parameter is added and procedure is called in PL/SQL Block it starts giving me error. This is very urgent and critical problem for me. Please help me to resolve the problem. If you have any queries, you can contact me on biping@hexaware.com or Phone No. 0091 22 55919669 (9.00 to 6.00 PM IST (Indian Time).

SQL> select * from v$version;

BANNER
-----------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bi
PL/SQL Release 10.1.0.3.0 - Production
CORE    10.1.0.3.0      Production
TNS for Solaris: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production

SQL> CREATE OR REPLACE PROCEDURE GENERATE_XML(
  2  P_XML_CLOB OUT CLOB) IS
  3  
  4    queryCtx dbms_xmlquery.ctxType;
  5  
  6    v_Query Varchar2(1000) := 'SELECT * FROM Max_Bk_Booking Where rownum < 2';
  7  
  8    v_xml_version   VARCHAR2(1000);
  9    v_xml_attribute VARCHAR2(500);
 10    P_ROWSETTAG varchar2(100) := 'Booking';
 11  
 12  BEGIN
 13    -- set up the query context
 14    queryCtx := dbms_xmlquery.newContext(v_Query);
 15  
 16    dbms_xmlquery.setRowTag( queryCtx , 'Booking' );
 17    dbms_xmlquery.setRowSetTag( queryCtx , Null );
 18  
 19    P_XML_CLOB := dbms_xmlquery.getXml(queryCtx);
 20  
 21    SELECT value INTO v_xml_version
 22    FROM system_parameters
 23    WHERE param_name='XML_VERSION';
 24   
 25    SELECT value INTO v_xml_attribute
 26    FROM system_parameters
 27    WHERE param_name='XML_ATTRIBUTE';
 28  
 29    P_XML_CLOB :=REPLACE(P_XML_CLOB,'<?xml version = ''1.0''?>',v_xml_version);
 30    P_XML_CLOB :=REPLACE(P_XML_CLOB,'<'||p_RowSetTag||'>','<'||p_RowSetTag||' '||v_xml_attribute|
|'>');
 31   
 32    printclobout(P_XML_CLOB);
 33  
 34    dbms_xmlquery.closeContext(queryCtx);
 35  
 36     printclobout(P_XML_CLOB);
 37  END;
 38  /

Procedure created.

SQL> DECLARE
  2    v_result CLOB;
  3  BEGIN
  4    GENERATE_XML(v_result);
  5  printclobout(v_result);
  6  END;
  7  /
| <?xml version="1.0" encoding="UTF-8"?> ......
.
.
.
(this is 1st output of XML from called procedure )

| <?xml version="1.0" encoding="UTF-8"?>......
.
.
.
(this is 2nd output of XML from called procedure )

DECLARE
*
ERROR at line 1:
ORA-22922: nonexistent LOB value
ORA-06512: at "SYS.DBMS_LOB", line 783
ORA-06512: at "PHOENIX.PRINTCLOBOUT", line 5
ORA-06512: at line 5

Error in calling procedure....Please Note this is in 10g Release 1.

Tom Kyte
April 26, 2005 - 8:43 am UTC 

Sean here,

The reason I didn't hit the error is because I didn't recreate the printclobout procedure.  Once I built that, I hit your error.  It has to do with the OUT param on the query and then passing that to printclobout.  If I make the original a function that returns the CLOB instead of it being an OUT param, it works fine...

SQL> CREATE OR REPLACE FUNCTION GENERATE_XML RETURN CLOB IS
  2    queryCtx dbms_xmlquery.ctxType;
  3    v_Query Varchar2(1000) := 'SELECT * FROM EMP Where rownum < 2';
  4  
  5    v_xml_version   VARCHAR2(1000);
  6    v_xml_attribute VARCHAR2(500);
  7    P_ROWSETTAG varchar2(100) := 'Booking';
  8  
  9    l_clob clob;
 10  BEGIN
 11    -- set up the query context
 12    queryCtx := dbms_xmlquery.newContext(v_Query);
 13  
 14    dbms_xmlquery.setRowTag( queryCtx , 'Booking' );
 15    dbms_xmlquery.setRowSetTag( queryCtx , Null );
 16  
 17    l_clob := dbms_xmlquery.getXml(queryCtx);
 18  
 19  /*
 20    SELECT value INTO v_xml_version
 21    FROM system_parameters
 22    WHERE param_name='XML_VERSION';
 23  
 24    SELECT value INTO v_xml_attribute
 25    FROM system_parameters
 26    WHERE param_name='XML_ATTRIBUTE';
 27  */
 28  
 29    select '1.0' into v_xml_version from dual;
 30    select 'some_attribute_value' into v_xml_attribute from dual;
 31  
 32    l_clob := REPLACE(l_clob,'<?xml version = ''1.0''?>',v_xml_version);
 33    l_clob := REPLACE(l_clob,'<'||p_RowSetTag||'>','<'||p_RowSetTag||' '||v_xml_attribute||'>');
 34  
 35    --printclobout(l_clob);  -- this is printed out in the calling proc
 36    dbms_xmlquery.closeContext(queryCtx);
 37    --printclobout(l_clob);  -- this is printed out in the calling proc
 38    return l_clob;
 39  END;
 40  /
Function created.

SQL> DECLARE
  2    v_result CLOB;
  3  BEGIN
  4    v_result := GENERATE_XML;
  5    printclobout(v_result);
  6  END;
  7  /
1.0
<Booking some_attribute_value>
   <ENAME>SDILLON</ENAME>
   <EMPNO>1</EMPNO>
</Booking>
PL/SQL procedure successfully completed.

As you can see, once that problem is solved you've got a host of other issues surrounding the way you're doing your tag replacement, the xml version, et al... but at least the nonexistent LOB value is solved ;-).

XML To_Clob

Bipin Ganar, April 29, 2005 - 11:19 am UTC

Hi Sean / Tom,

I realized from the replace function that whenever you play with replace function it by default converts to character (base data type). So I used TO_CLOB(REPLACE...... in the inner procedure. It is working with out parameter and other functions as well as in main PL/SQL Block where we called the sub-routine.

Well thanks a lot for your help. But just put in ORAMAG, Questionnaire so that people will get a help from that.

xml bug

BIPIN GANAR, May 10, 2005 - 7:58 am UTC

Hi Tom,
All is okay,

But my code which is working well in 9i and suddenly stops in 10g. IS This a BUG with 10g features that comes?
Is that possible for oracle to resolve this with some release patch?

Tom Kyte
May 10, 2005 - 9:16 am UTC

is WHAT a bug.

my car won't start.
it started yesterday.
why?

Just read the previous Queries

Bipin Ganar, May 17, 2005 - 8:40 am UTC

Hi Tom,

I apreciate your knowledge in Oracle. I know you must be too much busy with your work. But I feel you are not reading previous questions./answers which you have answered on your page. I have not asked the question without any reference. My question was continued with previous queries and do not want to repeat the whole thing (to avoid replications of the story) if u want please go thro' the page and read the question and answers with on the same page.

I do not want whether your car is getting started or not. Please Read the questions properly or take the reference from previous question.

I'm again asking you to refer previous question before answering.

My code which was generating XML in the 9i suddenly stops working in 10g. Sean dillon has answered that if i use function instead of OUT parameter will work.

But then why it was working in 9i? I would like to know whether oracle has removed any functionality in 10g?

Please Note: Read previous 3-4 Questions before answering.

Tom Kyte
May 17, 2005 - 9:40 am UTC 

Bipin,

you said

<quote>
But my code which is working well in 9i and suddenly stops in 10g.
</quote>

I have absolutely *no context here*.  I see the last comment and that is it.  I do not re-read the entire page.  (how could I, there are too many -- you need to provide context)

Please Note:  Context is important, I cannot re-read every single page.  All I see is "But my code which is working well in 9i and suddenly stops in 10g."  

From your perspective, you are having a one on one conversation with me.  From my perspective, I'm seeing dozens of these snippets in rapid succession.

After figuring out what needed to be written in addition to what was provided (printoutclob, system_parameters), it does not reproduce for me in the current release of 10g


ops$tkyte@ORA10G> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod
PL/SQL Release 10.1.0.4.0 - Production
CORE    10.1.0.4.0      Production
TNS for Linux: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production
 
ops$tkyte@ORA10G> @test
ops$tkyte@ORA10G> create or replace procedure printclobout( p_xml_clob in clob )
  2  as
  3  begin
  4          dbms_output.put_line( dbms_lob.substr( p_xml_clob, 255, 1 ) );
  5  end;
  6  /
 
Procedure created.
 
ops$tkyte@ORA10G> drop table system_parameters;
 
Table dropped.
 
ops$tkyte@ORA10G> create table system_parameters ( param_name varchar2(30), value varchar2(30) );
 
Table created.
 
ops$tkyte@ORA10G> insert into system_parameters values ( 'XML_VERSION', 'xxx' );
 
1 row created.
 
ops$tkyte@ORA10G> insert into system_parameters values ( 'XML_ATTRIBUTE', 'yyy' );
 
1 row created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> CREATE OR REPLACE PROCEDURE GENERATE_XML( P_XML_CLOB OUT CLOB)
  2  IS
  3    queryCtx dbms_xmlquery.ctxType;
  4    v_Query Varchar2(1000) := 'SELECT * FROM scott.emp Where rownum < 2';
  5    v_xml_version   VARCHAR2(1000);
  6    v_xml_attribute VARCHAR2(500);
  7    P_ROWSETTAG varchar2(100) := 'Booking';
  8  BEGIN
  9    -- set up the query context
 10    queryCtx := dbms_xmlquery.newContext(v_Query);
 11    dbms_xmlquery.setRowTag( queryCtx , 'Booking' );
 12    dbms_xmlquery.setRowSetTag( queryCtx , Null );
 13    P_XML_CLOB := dbms_xmlquery.getXml(queryCtx);
 14    SELECT value INTO v_xml_version
 15    FROM system_parameters
 16    WHERE param_name='XML_VERSION';
 17    SELECT value INTO v_xml_attribute
 18    FROM system_parameters
 19    WHERE param_name='XML_ATTRIBUTE';
 20    P_XML_CLOB :=REPLACE(P_XML_CLOB,'<?xml version = ''1.0''?>',v_xml_version);
 21    P_XML_CLOB :=REPLACE(P_XML_CLOB,'<'||p_RowSetTag||'>','<'||p_RowSetTag||' '||v_xml_attribute||'>');
 22    printclobout(P_XML_CLOB);
 23    dbms_xmlquery.closeContext(queryCtx);
 24    printclobout(P_XML_CLOB);
 25  END;
 26  /
 
Procedure created.
 
ops$tkyte@ORA10G> show err
No errors.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> DECLARE
  2    v_result CLOB;
  3  BEGIN
  4    GENERATE_XML(v_result);
  5  printclobout(v_result);
  6  END;
  7  /
xxx
<Booking yyy>
   <EMPNO>7369</EMPNO>
   <ENAME>SMITH</ENAME>
 
<JOB>CLERK</JOB>
   <MGR>7902</MGR>
   <HIREDATE>12/17/1980 0:0:0</HIREDATE>
 
<SAL>800</SAL>
   <DEPTNO>20</DEPTNO>
</Booking>
 
xxx
<Booking yyy>
   <EMPNO>7369</EMPNO>
   <ENAME>SMITH</ENAME>
 
<JOB>CLERK</JOB>
   <MGR>7902</MGR>
   <HIREDATE>12/17/1980 0:0:0</HIREDATE>
 
<SAL>800</SAL>
   <DEPTNO>20</DEPTNO>
</Booking>
 
xxx
<Booking yyy>
   <EMPNO>7369</EMPNO>
   <ENAME>SMITH</ENAME>
 
<JOB>CLERK</JOB>
   <MGR>7902</MGR>
   <HIREDATE>12/17/1980 0:0:0</HIREDATE>
 
<SAL>800</SAL>
   <DEPTNO>20</DEPTNO>
</Booking>
 
 
PL/SQL procedure successfully completed.

Check with Oracle 10.1.0.3.0 It fails

BIPIN GANAR, May 19, 2005 - 5:05 am UTC 

Hi Tom,

PS: Reference previous Question

What I feel from the script that, It is related with version for the version i'm using is having the problem. So you are not able to replicate the same problem.

Whereas sean dhillon has tried the same case and got the same error and he suggested me to got for function.


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bi
PL/SQL Release 10.1.0.3.0 - Production
CORE    10.1.0.3.0      Production
TNS for Solaris: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production

SQL> select text from all_source where name = 'GENERATE_XML';

TEXT
--------------------------------------------------------------------------------
PROCEDURE GENERATE_XML(P_XML_CLOB OUT CLOB) IS
 queryCtx dbms_xmlquery.ctxType;
 v_Query Varchar2(1000) := 'SELECT * FROM system_parameters where rownum < 2';
 v_xml_version   VARCHAR2(1000);
 v_xml_attribute VARCHAR2(500);
        p_ROWSETTAG  varchar2(100) := 'Booking';
BEGIN
 -- set up the query context
 queryCtx := dbms_xmlquery.newContext(v_Query);
 dbms_output.put_line('1 st Execution');
 dbms_xmlquery.setRowTag( queryCtx , 'Booking' );
 dbms_xmlquery.setRowSetTag( queryCtx , Null );
 dbms_output.put_line('2 nd Execution');
 P_XML_CLOB := dbms_xmlquery.getXml(queryCtx);
 dbms_output.put_line('4 th Execution');
 SELECT value INTO v_xml_version
 FROM system_parameters
 WHERE param_name='XML_VERSION';
 SELECT value INTO v_xml_attribute
 FROM system_parameters
 WHERE param_name='XML_ATTRIBUTE';
 dbms_output.put_line('3 rd Execution');
 P_XML_CLOB := REPLACE(P_XML_CLOB,'<?xml version = ''1.0''?>',v_xml_version);
 P_XML_CLOB := REPLACE(P_XML_CLOB,'<'||p_RowSetTag||'>','<'||p_RowSetTag||' '||v
_xml_attribute||'>');

 dbms_xmlquery.closeContext(queryCtx);
 dbms_output.put_line('5 th Execution');
END;

27 rows selected.

SQL> DECLARE
  2  v_result CLOB;
  3  BEGIN
  4  GENERATE_XML(v_result);
  5  printclobout(v_result);
  6  END;
  7  /
1 st Execution
2 nd Execution
4 th Execution
3 rd Execution
5 th Execution
DECLARE
*
ERROR at line 1:
ORA-22922: nonexistent LOB value
ORA-06512: at "SYS.DBMS_LOB", line 783
ORA-06512: at "PHOENIX.PRINTCLOBOUT", line 5
ORA-06512: at line 5

Would you confirm and let me know whether it was a bug in the version. If so, I can convince the client to go for higher version only.

PS: Refer previous question.

Tom Kyte
May 19, 2005 - 8:04 am UTC

IF you run the code I ran
AND it fails
then it is something fixed in 10.1.0.4

if you want full confirmation, you'll need to work with support. It may never have been a filed/known bug ever as far as I know.

Why not Oracle include Case Constaint?

Tony, January 16, 2007 - 11:26 am UTC

Tom,

To store data in UPPPER or lower case in database, we need to use trigger or function based index. Can't we have better solution? Why don't Oracle add "Case Constaint" like check constraint in CREATE TABLE syntax to store data in UPPER/lower case? This way, we can avoid extra coding work. This approach is simple and stright forward.

Thanks

Why not Oracle include Case Constraint?

Tony, January 16, 2007 - 11:29 am UTC

Tom,

To store data in UPPPER or lower case in database, we need to use trigger or function based index. Can't we have better solution? Why don't Oracle add "Case Constraint" like check constraint in CREATE TABLE syntax to store data in UPPER/lower case? This way, we can avoid extra coding work. This approach is simple and stright forward.

Thanks

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library