Home » Developer & Programmer » JDeveloper, Java & XML » Xml data to be passed
Xml data to be passed [message #577160] Tue, 12 February 2013 23:57 Go to next message
rajiv.v
Messages: 16
Registered: February 2013
Location: Bangalore
Junior Member
Hi Guys,this is my function for xml(am new to xml).How do i test this,when tested i get the errors pls-00306 and ora-06550.I need to overcome this.Help needed with advance thanks.



create or replace
FUNCTION check_length11(
ip_attr_xmltype IN XMLTYPE,
op_error_code OUT NUMBER)
return NUMBER
Is
test_ex exception;
l_xml_attr_length number(10);
cursor cur_c is select mandatory_v,length_n,api_error_code_n from xml_tab102;
begin
for i in cur_c
loop
SELECT LENGTH(TRIM(EXTRACTVALUE(ip_attr_xmltype,'//i.parent_xml/@||i.attribute_name')))
INTO l_xml_attr_length from dual;
if (i.mandatory_v='Y') and (l_xml_attr_length<i.length_n) then
return 0;
else op_error_code:=i.api_error_code_n;
raise test_ex;
end if;
if (i.mandatory_v='N') and (l_xml_attr_length<i.length_n) then
return 0;
else
raise test_ex;
end if;
end loop;
exception
when test_ex then
return 1;
end;

[Updated on: Wed, 13 February 2013 00:54]

Report message to a moderator

Re: Xml data to be passed [message #577168 is a reply to message #577160] Wed, 13 February 2013 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Use SQL*Plus and copy and paste your session, the WHOLE session.
Use "show error" after CREATE FUNCTION.

Regards
Michel

[Updated on: Wed, 13 February 2013 01:02]

Report message to a moderator

Re: Xml data to be passed [message #577174 is a reply to message #577168] Wed, 13 February 2013 01:33 Go to previous messageGo to next message
rajiv.v
Messages: 16
Registered: February 2013
Location: Bangalore
Junior Member
Thank you Michel.I have just copied and pasted from sqlplus. My code goes on like this.
create or replace
FUNCTION check_length10001(ip_attr_xmltype IN XMLTYPE,
op_error_code OUT NUMBER)
return NUMBER
Is
test_ex exception;
l_xml_attr_length number(10);




cursor cur_c is select mandatory_v,length_n from xml_tab102;
begin
for i in cur_c
loop
SELECT LENGTH(TRIM(EXTRACTVALUE(ip_attr_xmltype,'//i.parent_xml/@||i.attribute_name')))
INTO
l_xml_attr_length
from dual;
if (i.mandatory_v='Y') and (l_xml_attr_length<i.length_n) then
return 0;
else
raise test_ex;
end if;
if (i.mandatory_v='N') and (l_xml_attr_length<i.length_n) then
return 0;
else
raise test_ex;
end if;
end loop;

exception
when test_ex then
return 1;
end;
I wish to know how to test this as am encountering the error pls-00306 and ora-06550.With Advance thanks to Michel
Re: Xml data to be passed [message #577178 is a reply to message #577174] Wed, 13 February 2013 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 13 February 2013 08:00
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Use SQL*Plus and copy and paste your session, the WHOLE session.
Use "show error" after CREATE FUNCTION.

Regards
Michel

Re: Xml data to be passed [message #577185 is a reply to message #577178] Wed, 13 February 2013 02:47 Go to previous messageGo to next message
rajiv.v
Messages: 16
Registered: February 2013
Location: Bangalore
Junior Member
create or replace 
FUNCTION check_length10001(ip_attr_xmltype IN XMLTYPE,
op_error_code OUT NUMBER)
return NUMBER
Is
test_ex exception;
l_xml_attr_length number(10);

  cursor cur_c is select mandatory_v,length_n from xml_tab102;
begin
for i in cur_c
loop 
           SELECT LENGTH(TRIM(EXTRACTVALUE(ip_attr_xmltype,'//i.parent_xml/@||i.attribute_name')))
                INTO 
                l_xml_attr_length
                from dual;
   if (i.mandatory_v='Y') and (l_xml_attr_length<i.length_n)  then 
             return 0;
    else
        raise test_ex;
  end if;
   if (i.mandatory_v='N') and  (l_xml_attr_length<i.length_n)  then
             return 0;
  else
       raise test_ex;
   end if;
end loop;

exception
when test_ex then 
return 1;
end;


[Edit: Frank added code tags to improve readability]

[Updated on: Sat, 09 March 2013 07:59] by Moderator

Report message to a moderator

Re: Xml data to be passed [message #577186 is a reply to message #577185] Wed, 13 February 2013 02:53 Go to previous messageGo to next message
rajiv.v
Messages: 16
Registered: February 2013
Location: Bangalore
Junior Member
Hey Michel on submission the alignments are not reflected.I have no error while compiling the function,i have a run time error while passing the xml data on to this function
Re: Xml data to be passed [message #577187 is a reply to message #577186] Wed, 13 February 2013 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read the link I posted you, it explains how to do it in 10 seconds.

Regards
Michel
Re: Xml data to be passed [message #577188 is a reply to message #577187] Wed, 13 February 2013 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i have a run time error while passing the xml data on to this function


1/ You do not show us any error
2/ We cannot run your code to see what it may be
3/ We can't help.

Read again what I posted and COMPLETLY fulfil what he asked.

Regards
Michel

[Updated on: Mon, 13 May 2013 04:50]

Report message to a moderator

Re: Xml data to be passed [message #584345 is a reply to message #577188] Mon, 13 May 2013 04:24 Go to previous messageGo to next message
rajiv.v
Messages: 16
Registered: February 2013
Location: Bangalore
Junior Member
FUNCTION Check_length10001(ip_attr_xmltype IN XMLTYPE,
op_error_code OUT NUMBER)
RETURN NUMBER
IS
test_ex EXCEPTION;
l_xml_attr_length NUMBER(10);
CURSOR cur_c IS
SELECT mandatory_v,
length_n
FROM xml_tab102;
BEGIN
FOR i IN cur_c LOOP
SELECT Length(Trim(Extractvalue(ip_attr_xmltype,
'//i.parent_xml/@||i.attribute_name')
)
)
INTO l_xml_attr_length
FROM dual;

IF ( i.mandatory_v = 'Y' )
AND ( l_xml_attr_length < i.length_n ) THEN
RETURN 0;
ELSE
RAISE test_ex;
END IF;

IF ( i.mandatory_v = 'N' )
AND ( l_xml_attr_length < i.length_n ) THEN
RETURN 0;
ELSE
RAISE test_ex;
END IF;
END LOOP;
EXCEPTION
WHEN test_ex THEN
RETURN 1;
END;

[Updated on: Mon, 13 May 2013 04:27]

Report message to a moderator

Re: Xml data to be passed [message #584350 is a reply to message #584345] Mon, 13 May 2013 04:50 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 13 February 2013 08:55
Michel Cadot wrote on Wed, 13 February 2013 08:00
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Use SQL*Plus and copy and paste your session, the WHOLE session.
Use "show error" after CREATE FUNCTION.

Regards
Michel



Michel Cadot wrote on Wed, 13 February 2013 10:12
Quote:
i have a run time error while passing the xml data on to this function


1/ You do not show us any error
2/ We cannot run your code to see what it may be
3/ We can't help.

Read again what I posted and COMPLETLY fulfil what he asked.

Regards
Michel

[Updated on: Mon, 13 May 2013 04:51]

Report message to a moderator

Previous Topic: Dynamically build xml SQL statement
Next Topic: ORA-30951: Element or attribute at Xpath exceeds maximum length ORA-06512: at line 11
Goto Forum:
  


Current Time: Thu Mar 28 07:53:12 CDT 2024