SQL & PL/SQL

Hi,

we are calling a FTP function which internally calls the HOST function.
This HOST function inturn calls another overloaded function.

In the overloaded function we are calling the dbms_pipe.receive_message which is retuning 1 (meaning timed out) indicating that pipe deamon is getting timed out.

I wanted to know how can i get this pipe deamon running so that the receive message function will not be timed out.

Could you please suggest on this.

Please find the details below -

Database Version - 11.2.0.1.0

Host Function

FUNCTION HOST(text_in IN VARCHAR2,
result_out IN OUT VARCHAR2) RETURN BOOLEAN IS
ret boolean;
drun boolean;
hid number;
BEGIN

ret := HOST(text_in,result_out,hid,drun,1);
if not drun then
return false;
end if;
return ret;
END HOST;

Overloaded Host function

FUNCTION HOST(text_in IN VARCHAR2,
result_out IN OUT VARCHAR2,
hostid IN OUT NUMBER ,
demrunning IN OUT boolean,
maxlines IN NUMBER DEFAULT NULL) RETURN BOOLEAN IS

tempid number;
tempstat varchar2(10);
n number;
m number;
retry number;
mult number;
timestamp_date date;

BEGIN

demrunning := true;

select xxxxx.nextval
into tempid from dual;

hostid := tempid;

dbms_pipe.pack_message(tempid);
n := dbms_pipe.send_message('CMD');

dbms_pipe.pack_message(abs(maxlines));
dbms_pipe.pack_message(rtrim(text_in));
n := dbms_pipe.send_message ('CMD'||rtrim(to_char(tempid)),0);

--<<CHECK_STAT>>

/***
wait for 5 seconds to get back the command execution status
if timed out then the Pipe Deamon is NOT Running
***/

m := dbms_pipe.receive_message('CMDSTAT'||rtrim(to_char(tempid)),5);
if m = 1 then
demrunning := false;
result_out := 'ERROR : GLCC0005 presumed not running';
return FALSE;
elsif m = 0 then
dbms_pipe.unpack_message(tempstat);
end if;

if tempstat = 'ERROR' then
result_out := NULL;
return FALSE;
end if;


-- The dbms_pipe.receive_message call was originally inside an infinite
-- loop. Under normal circumstances that is not an issue, however, if
-- there is a problem with the availability of GLCC0006, then the loop
-- will contine ad infinitum. Use of dbms_lock.sleep is not an option
-- as this violates the HOST function restrict_references pragma.
--
-- The following (fairly clunky) work around has therefore been adopted.
-- Keep in mind, we are handling exceptions here, not general execution.
-- The loop will continue for one minute and then exit, if no response
-- is forthcoming from GLCC0006.
--
-- JNM 11.2000
/*
select
sysdate
into
timestamp_date
from dual;

retry := 0;
*/


<<CHECK_AGAIN>>

-- begin

-- m := 1; -- reset m so we enter following loop

-- while (sysdate < timestamp_date + ( 1 / (24*60))) AND
-- ( m <> 0 )
-- LOOP

-- retry := retry + 1;

m := dbms_pipe.receive_message('RSLT'||rtrim(to_char(tempid)),0);

-- if m <> 0 AND retry < 13 then


if m <> 0 then


-- dbms_lock.sleep(5);
goto CHECK_AGAIN;

end if;

-- end LOOP;

if m = 0 then
dbms_pipe.unpack_message(result_out);
dbms_pipe.purge('RSLT'||rtrim(to_char(tempid)));
return TRUE;
else
result_out := 'ERROR : Call to GLCC0006 failed';
return FALSE;
end if;

-- end;

END HOST;


In the above function the dbms_pipe.receieve_message is returning 1.

It would be of great help if anyone suggests what should be done for receiving 0 from dbms_pipe and also how can we get that pipe deamon running so that the recieve message returns '0'.



Thanks & Regards,
Sravanthi

Edited by: 906783 on Apr 9, 2012 3:13 AM