PL/SQL function not working while passing table name as parameter

Question

Following is my function,

create or replace 
FUNCTION checkXML
 (idx in number , tblname in varchar2) 
 return xmltype 
is 
required_xml XMLTYPE;
saved_hash_value raw(50);
current_hash_value raw(50);
xml_not_equal EXCEPTION;

begin
execute immediate 'select checkfield , my_hash(extract(xmlcol,'/')) , xmlcol into saved_hash_value , 
                   current_hash_value , required_xml  from ' || tblname || '  where indexid =' || idx ;


if saved_hash_value = current_hash_value then
  return required_xml;
else
  RAISE xml_not_equal;  
end if;

end;

I want to know where am going wrong.

Error message which am receiving is,

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "SYSTEM.CHECKXML", line 11
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    
*Action:

Alex Poole · Accepted Answer · 2014-06-10 09:24:41Z

You have unescaped single quotes in your SQL statement, so the slash / is being interpreted as a division symbol, not as part of a string. You need to add escapes:

my_hash(extract(xmlcol, ''/''))

You should also use a bind variable for idx really, and your into is in the wrong place for dynamic SQL:

execute immediate 'select checkfield , my_hash(extract(xmlcol, ''/'')) ,
  xmlcol from ' || tblname || ' where indexed = :idx'
into saved_hash_value , current_hash_value , required_xml
using idx;

Also not sure what you're trying to achieve with the exception. You've declared it locally and then try to raise it, but that will just generate an unhandled user-defined exception error, I think. You might just want to raise an application error, with your own error number and message, something like:

  ...
  if saved_hash_value != current_hash_value then
    raise_application_error(-20001, 'XML hash is not correct');  
  end if;
  return required_xml;
end;

Collectives™ on Stack Overflow

PL/SQL function not working while passing table name as parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related