Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Logic (10g)
PL/SQL Logic [message #444950] Wed, 24 February 2010 22:45 Go to next message
astik.mondal
Messages: 14
Registered: March 2008
Location: Kolkata
Junior Member
Hi All,

I have written the below function:

CREATE OR REPLACE FUNCTION check_xml
RETURN VARCHAR2
AS

v_xml clob;
exit_from_loop EXCEPTION;

BEGIN

FOR gethost IN (SELECT host FROM t_host_tab)
LOOP
v_host := gethost.host;

get_xml(v_host, v_xml);

IF v_xml IS NOT NULL THEN
RETURN v_host;
RAISE exit_from_loop;
ELSE
----I need to check for all the hosts.if get_xml returns null for all the hosts, we will return null from the function.

END IF;

END LOOP;

EXCEPTION
WHEN exit_from_loop THEN
RETURN v_host;

WHEN OTHERS THEN
return null;

END check_xml;

we are fetching the host ids from t_host_tab table and passing to the get_xml function which will return either xml value or null.
Now we need to implement the below logic:
if the get_xml returns xml (not null) then
return host id from main function and exit.
otherwise check for all hosts until all the hosts are checked.
if it returns null for all the hosts,then return null from the main function.

It would be appreciated if anybody can help how to implement this.


Thanks,
Astik
Re: PL/SQL Logic [message #444959 is a reply to message #444950] Wed, 24 February 2010 23:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
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" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: PL/SQL Logic [message #445092 is a reply to message #444950] Thu, 25 February 2010 21:06 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I think this is an example of doing too much work. Consider this fact: You go to a lot of trouble to retrieve a value that you never use. You fetch a clob value but you never return it anywhere.

Why not use a smarter query approach. Something like

where length(someclob) = 0
and rownum = 1;

Do not bother to actually retrieve your clob value. You never use it for anything.

Kevin

[Updated on: Thu, 25 February 2010 21:07]

Report message to a moderator

Previous Topic: UTL_SMTP Encoding Issue
Next Topic: examination seating plan
Goto Forum:
  


Current Time: Fri Dec 09 15:11:36 CST 2016

Total time taken to generate the page: 0.50191 seconds