Home » SQL & PL/SQL » SQL & PL/SQL » Bind variables with DBMS_XMLGEN
Bind variables with DBMS_XMLGEN [message #264027] Fri, 31 August 2007 09:37 Go to next message
Art Trifonov
Messages: 11
Registered: June 2007
Location: Boston
Junior Member
I am getting "ORA-01008: not all variables bound" whenever any bind variable evaluates to NULL.

SQL> DECLARE
  2   
  3    xml_result   CLOB;
  4    v1           VARCHAR2(10);
  5    qrycontext   DBMS_XMLGEN.ctxHandle;
  6    
  7  BEGIN
  8    
  9    V1 := NULL;
 10   
 11    qrycontext :=
 12    DBMS_XMLGEN.newcontext
 13   ('SELECT SYSDATE FROM DUAL WHERE USER = :1 ') ;
 14    DBMS_XMLGEN.setbindvalue (qrycontext, '1', V1);
 15    xml_result := DBMS_XMLGEN.getxml (qrycontext);
 16   
 17  END;
 18  /
DECLARE
*
ERROR at line 1:
ORA-19202: Error occurred in XML processing
ORA-01008: not all variables bound
ORA-06512: at "SYS.DBMS_XMLGEN", line 7
ORA-06512: at "SYS.DBMS_XMLGEN", line 147
ORA-06512: at line 15


The same block executes fine when I assign any not null value to V1.

Any suggestions?

Thanks
-Art
Re: Bind variables with DBMS_XMLGEN [message #264031 is a reply to message #264027] Fri, 31 August 2007 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>The same block executes fine when I assign any not null value to V1.

>Any suggestions?
Assign NOT NULL value to V1 or provide default value via NVL function.
Re: Bind variables with DBMS_XMLGEN [message #264046 is a reply to message #264027] Fri, 31 August 2007 10:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sounds like a bug to me.
Why would it not be possible to use NULL for a bind?
Re: Bind variables with DBMS_XMLGEN [message #264055 is a reply to message #264046] Fri, 31 August 2007 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Clearly a bug, in the Oracle meaning not in standard one (NULL and '' difference):
SQL> DECLARE
  2    xml_result   CLOB;
  3    v1           VARCHAR2(10);
  4    qrycontext   DBMS_XMLGEN.ctxHandle;
  5  BEGIN
  6    V1 := NULL;
  7    qrycontext :=
  8      DBMS_XMLGEN.newcontext
  9        ('SELECT SYSDATE FROM DUAL WHERE USER = :1 ') ;
 10    DBMS_XMLGEN.setbindvalue (qrycontext, '1', V1);
 11    xml_result := DBMS_XMLGEN.getxml (qrycontext);
 12  END;
 13  /
DECLARE
*
ERROR at line 1:
ORA-19202: Error occurred in XML processing
ORA-01008: not all variables bound
ORA-06512: at "SYS.DBMS_XMLGEN", line 7
ORA-06512: at "SYS.DBMS_XMLGEN", line 147
ORA-06512: at line 11


SQL> 6
  6*   V1 := NULL;
SQL> c:NULL:''
  6*   V1 := '';
SQL> l
  1  DECLARE
  2    xml_result   CLOB;
  3    v1           VARCHAR2(10);
  4    qrycontext   DBMS_XMLGEN.ctxHandle;
  5  BEGIN
  6    V1 := '';
  7    qrycontext :=
  8      DBMS_XMLGEN.newcontext
  9        ('SELECT SYSDATE FROM DUAL WHERE USER = :1 ') ;
 10    DBMS_XMLGEN.setbindvalue (qrycontext, '1', V1);
 11    xml_result := DBMS_XMLGEN.getxml (qrycontext);
 12* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> @v

Version Oracle : 10.2.0.3.0

Regards
Michel
Re: Bind variables with DBMS_XMLGEN [message #264061 is a reply to message #264055] Fri, 31 August 2007 10:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Execute immediate can handle null binds, so one would think it should be possible.
SQL> declare
  2    v1 varchar2(1);
  3    l_result varchar2(1);
  4  begin
  5    v1 := null;
  6    execute immediate 'select * from dual where :1 = ''X''' into l_result using v1;
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 6


SQL> declare
  2    v1 varchar2(1);
  3    l_result varchar2(1);
  4  begin
  5    v1 := 'X';
  6    execute immediate 'select * from dual where :1 = ''X''' into l_result using v1;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> declare
  2    v1 varchar2(1);
  3    l_result varchar2(1);
  4  begin
  5    v1 := null;
  6    execute immediate 'select * from dual where :1 = ''X''' into l_result;
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at line 6


[Edit: added the not all variables bound example]

[Updated on: Fri, 31 August 2007 10:34]

Report message to a moderator

Re: Bind variables with DBMS_XMLGEN [message #264066 is a reply to message #264061] Fri, 31 August 2007 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The problem/bug here is that Oracle makes a difference between NULL and empty string.

Regards
Michel
Re: Bind variables with DBMS_XMLGEN [message #264080 is a reply to message #264027] Fri, 31 August 2007 12:25 Go to previous messageGo to next message
Art Trifonov
Messages: 11
Registered: June 2007
Location: Boston
Junior Member
Thanks Michel for the simple workaround.

-Art
Re: Bind variables with DBMS_XMLGEN [message #264083 is a reply to message #264066] Fri, 31 August 2007 12:47 Go to previous messageGo to next message
Art Trifonov
Messages: 11
Registered: June 2007
Location: Boston
Junior Member
Quote:

The problem/bug here is that Oracle makes a difference between NULL and empty string.

Regards
Michel



There is a difference.
V1 := NULL means V1 is the string of unknown length.
V1 := '' means V1 is a string of 0 length.

-Art
Re: Bind variables with DBMS_XMLGEN [message #264084 is a reply to message #264083] Fri, 31 August 2007 13:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Art Trifonov wrote on Fri, 31 August 2007 19:47
There is a difference.
V1 := NULL means V1 is the string of unknown length.
V1 := '' means V1 is a string of 0 length.
-Art

No, there is no difference for Oracle.
NULL is no value.
Oracle assumes that an empty string is "NO VALUE", NULL.
This is its policy. NULL is empty string, empty string is NULL, both have NULL length.
SQL> declare
  2    v1 varchar2(10) := '';
  3  begin
  4    if v1 is null then 
  5      dbms_output.put_line ('Empty string IS NULL');
  6    else
  7      dbms_output.put_line ('Empty string IS NOT NULL');
  8    end if;
  9    dbms_output.put_line ('Length='||length(v1));
 10  end;
 11  /
Empty string IS NULL
Length=

PL/SQL procedure successfully completed.

I don't say this is right but this is Oracle policy.

Regards
Michel


Re: Bind variables with DBMS_XMLGEN [message #264100 is a reply to message #264084] Fri, 31 August 2007 15:29 Go to previous messageGo to next message
Art Trifonov
Messages: 11
Registered: June 2007
Location: Boston
Junior Member
Just found another intersting thing.

SQL> declare
  2    v1 varchar2(10);
  3  begin
  4    select extractValue(XMLTYPE('<aaa>1</aaa>'),'/zzz') 
  5    into v1 from dual;
  6  end;
  7  /

PL/SQL procedure successfully completed.

It does not raise "No data found".

I don't thing there is one "correct" way to treat NULLs, but it has to be consistent.

-Art
Re: Bind variables with DBMS_XMLGEN [message #264135 is a reply to message #264100] Sat, 01 September 2007 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I don't thing there is one "correct" way to treat NULLs, but it has to be consistent.

I fully agree.

Regards
Michel
Re: Bind variables with DBMS_XMLGEN [message #266295 is a reply to message #264100] Mon, 10 September 2007 07:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why do you think that this
SQL> declare
  v1 varchar2(10);
begin
  select extractValue(XMLTYPE('<aaa>1</aaa>'),'/zzz') 
  into v1 from dual;
end;
/
should raise a No Data Found?

You are performing a SELECT against Dual, so you will get a single row back. For this one row, you execute the ExtractValue function, which returns a null.

Which of these steps should raise the exception?
Re: Bind variables with DBMS_XMLGEN [message #266780 is a reply to message #266295] Tue, 11 September 2007 11:21 Go to previous message
Art Trifonov
Messages: 11
Registered: June 2007
Location: Boston
Junior Member
You are right.
My mistake.
Previous Topic: Identify non-numeric
Next Topic: May be Cartesian Join
Goto Forum:
  


Current Time: Fri Dec 02 16:51:26 CST 2016

Total time taken to generate the page: 0.30977 seconds