Home » SQL & PL/SQL » SQL & PL/SQL » UTL_TCP returning blank line? (Oracle 11g, PC Miler version 23)
UTL_TCP returning blank line? [message #442510] |
Mon, 08 February 2010 14:19  |
gjcjan
Messages: 5 Registered: February 2010 Location: Canada
|
Junior Member |
|
|
Hi all,
I did a search in the forum and found no similar subject. Hopefully someone will be able to help me out on this.
I am trying to use utl_tcp to connect, through TCP/IP, to a 3rd party program PC Miler. When I use telnet, PC Miler works without any issue. However, when I use utl_tcp, beginning from the second PC Miler API call, when I do a utl_tcp.get_line to get the result, the first line that return is always blank (with 1 white space). Here is my program.
---------------------------------------------------
DECLARE
c utl_tcp.connection;
ret_val PLS_INTEGER;
v_data VARCHAR2(4000);
BEGIN
c := utl_tcp.open_connection('mars', 8145);
LOOP
v_data := utl_tcp.get_line(c, TRUE);
dbms_output.put_line(v_data);
EXIT WHEN substr(v_data, length(v_data) - 4) = 'READY';
END LOOP;
-- First API call
ret_val := utl_tcp.write_line(c,
'pcmscalcdistance3("Chicago, IL", "New York, NY", 1)');
LOOP
v_data := utl_tcp.get_line(c, TRUE);
dbms_output.put_line(v_data);
EXIT WHEN substr(v_data, length(v_data) - 4) = 'READY';
END LOOP;
-- Second API call
ret_val := utl_tcp.write_line(c,
'pcmscalcdistance("Chicago, IL", "New York, NY")');
LOOP
v_data := utl_tcp.get_line(c, TRUE);
dbms_output.put_line(v_data);
EXIT WHEN substr(v_data, length(v_data) - 4) = 'READY';
END LOOP;
-- Third API call
ret_val := utl_tcp.write_line(c,
'pcmscalcdistance3("Chicago, IL", "New York, NY", 1)');
LOOP
v_data := utl_tcp.get_line(c, TRUE);
dbms_output.put_line(v_data);
EXIT WHEN substr(v_data, length(v_data) - 4) = 'READY';
END LOOP;
utl_tcp.close_connection(c);
END;
--------------------------------------------------------
Here is the result:
---------------------------------------------------------
ALK PCMILER SERVER READY
7939
826
READY
READY
826
READY
----------------------------------------------------------
Note that the 1st and 3rd API call is the same and should give me the same results. The 2nd API is supposed to give me 1 line back. At the end of each output, the server will end will the word "Ready". I use that to indicate when to exit the loop.
If I disconnect and reconnect after each API call, each output result will be correct because each API call will become the "1st" call since connection - like this program:
----------------------------------------------
DECLARE
c utl_tcp.connection;
ret_val PLS_INTEGER;
v_data VARCHAR2(4000);
BEGIN
c := utl_tcp.open_connection('mars', 8145);
LOOP
v_data := utl_tcp.get_line(c, TRUE);
dbms_output.put_line(v_data);
EXIT WHEN substr(v_data, length(v_data) - 4) = 'READY';
END LOOP;
-- First API call
ret_val := utl_tcp.write_line(c,
'pcmscalcdistance3("Chicago, IL", "New York, NY", 1)');
LOOP
v_data := utl_tcp.get_line(c, TRUE);
dbms_output.put_line(v_data);
EXIT WHEN substr(v_data, length(v_data) - 4) = 'READY';
END LOOP;
-- Close and reopen connection
utl_tcp.close_connection(c);
c := utl_tcp.open_connection('mars', 8145);
LOOP
v_data := utl_tcp.get_line(c, TRUE);
dbms_output.put_line(v_data);
EXIT WHEN substr(v_data, length(v_data) - 4) = 'READY';
END LOOP;
-- Second API call
ret_val := utl_tcp.write_line(c,
'pcmscalcdistance("Chicago, IL", "New York, NY")');
LOOP
v_data := utl_tcp.get_line(c, TRUE);
dbms_output.put_line(v_data);
EXIT WHEN substr(v_data, length(v_data) - 4) = 'READY';
END LOOP;
-- Close and reopen connection
utl_tcp.close_connection(c);
c := utl_tcp.open_connection('mars', 8145);
LOOP
v_data := utl_tcp.get_line(c, TRUE);
dbms_output.put_line(v_data);
EXIT WHEN substr(v_data, length(v_data) - 4) = 'READY';
END LOOP;
-- Third API call
ret_val := utl_tcp.write_line(c,
'pcmscalcdistance3("Chicago, IL", "New York, NY", 1)');
LOOP
v_data := utl_tcp.get_line(c, TRUE);
dbms_output.put_line(v_data);
EXIT WHEN substr(v_data, length(v_data) - 4) = 'READY';
END LOOP;
utl_tcp.close_connection(c);
END;
-----------------------------------------------------
And this gives me the expected result:
--------------------------------------------------
ALK PCMILER SERVER READY
7939
826
READY
ALK PCMILER SERVER READY
7994
READY
ALK PCMILER SERVER READY
7939
826
READY
---------------------------------------------------
If there is something wrong with PC Miler, why would it work when I telnet through a Windows Command prompt? If it is not PC Miler and utl_tcp, what else can be wrong?
Hope I am not breaking any forum rules as its my first time posting here. Thanks in advance for any help!
Gary
|
|
|
Re: UTL_TCP returning blank line? [message #442511 is a reply to message #442510] |
Mon, 08 February 2010 14:49   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Well, especially in this case where "formatting" is important, and line ending chars might be a problem, it would be nice if you could post what is happening FORMATTED (with code tags) exactly as you get it back when you run it in SQL*Plus.
(Like explained in the Forum Guide)
Since, especially in this case, the first thing that should be done is rule out any influence possible development tools could have on the output that is happening.
Also, when the code is formatted properly one can spot the loop constructs better.
For debugging it would also help if you also output what you SEND to the server. So that the output will become something like
-> Send Sting....
<- Received String....
<- Received String....
-> Send String....
<- Received String....
|
|
|
Re: UTL_TCP returning blank line? [message #442512 is a reply to message #442511] |
Mon, 08 February 2010 15:25   |
gjcjan
Messages: 5 Registered: February 2010 Location: Canada
|
Junior Member |
|
|
Thanks for the advice.
The program with better formatting
DECLARE
c utl_tcp.connection;
ret_val PLS_INTEGER;
v_data VARCHAR2(4000);
v_call VARCHAR2(1000);
BEGIN
c := utl_tcp.open_connection('mars', 8145);
LOOP
v_data := utl_tcp.get_line(c, TRUE);
dbms_output.put_line('Receive: ' || v_data || '^');
EXIT WHEN substr(v_data, length(v_data) - 4) = 'READY';
END LOOP;
-- First API call
v_call := 'pcmscalcdistance3("Chicago, IL", "New York, NY", 1)';
ret_val := utl_tcp.write_line(c, v_call);
dbms_output.put_line('Send: ' || v_call);
LOOP
v_data := utl_tcp.get_line(c, TRUE);
dbms_output.put_line('Receive: ' || v_data || '^');
EXIT WHEN substr(v_data, length(v_data) - 4) = 'READY';
END LOOP;
-- Second API call
v_call := 'pcmscalcdistance("Chicago, IL", "New York, NY")';
ret_val := utl_tcp.write_line(c, v_call);
dbms_output.put_line('Send: ' || v_call);
LOOP
v_data := utl_tcp.get_line(c, TRUE);
dbms_output.put_line('Receive: ' || v_data || '^');
EXIT WHEN substr(v_data, length(v_data) - 4) = 'READY';
END LOOP;
-- Third API call
v_call := 'pcmscalcdistance3("Chicago, IL", "New York, NY", 1)';
ret_val := utl_tcp.write_line(c, v_call);
dbms_output.put_line('Send: ' || v_call);
LOOP
v_data := utl_tcp.get_line(c, TRUE);
dbms_output.put_line('Receive: ' || v_data || '^');
EXIT WHEN substr(v_data, length(v_data) - 4) = 'READY';
END LOOP;
utl_tcp.close_connection(c);
END;
The result:
Receive: ALK PCMILER SERVER READY^
Send: pcmscalcdistance3("Chicago, IL", "New York, NY", 1)
Receive: 7939^
Receive: 826^
Receive: READY^
Send: pcmscalcdistance("Chicago, IL", "New York, NY")
Receive:
Receive: READY^
Send: pcmscalcdistance3("Chicago, IL", "New York, NY", 1)
Receive:
Receive: 826^
Receive: READY^
I have put a "^" to the end of each of the receiving lines.
[Updated on: Mon, 08 February 2010 15:26] Report message to a moderator
|
|
|
|
Re: UTL_TCP returning blank line? [message #442619 is a reply to message #442570] |
Tue, 09 February 2010 08:19   |
gjcjan
Messages: 5 Registered: February 2010 Location: Canada
|
Junior Member |
|
|
Below is how it looks when I run it in SQL Plus
SQL> DECLARE
2 c utl_tcp.connection;
3 ret_val PLS_INTEGER;
4 v_data VARCHAR2(4000);
5 v_call VARCHAR2(1000);
6 BEGIN
7 c := utl_tcp.open_connection('mars', 8145);
8 LOOP
9 v_data := utl_tcp.get_line(c, TRUE);
10 dbms_output.put_line('Receive: ' || v_data || '^');
11 EXIT WHEN substr(v_data, length(v_data) - 4) = 'READY';
12 END LOOP;
13
14 -- First API call
15 v_call := 'pcmscalcdistance3("Chicago, IL", "New York, NY", 1)';
16 ret_val := utl_tcp.write_line(c, v_call);
17 dbms_output.put_line('Send: ' || v_call);
18 LOOP
19 v_data := utl_tcp.get_line(c, TRUE);
20 dbms_output.put_line('Receive: ' || v_data || '^');
21 EXIT WHEN substr(v_data, length(v_data) - 4) = 'READY';
22 END LOOP;
23
24 -- Second API call
25 v_call := 'pcmscalcdistance("Chicago, IL", "New York, NY")';
26 ret_val := utl_tcp.write_line(c, v_call);
27 dbms_output.put_line('Send: ' || v_call);
28 LOOP
29 v_data := utl_tcp.get_line(c, TRUE);
30 dbms_output.put_line('Receive: ' || v_data || '^');
31 EXIT WHEN substr(v_data, length(v_data) - 4) = 'READY';
32 END LOOP;
33
34 -- Third API call
35 v_call := 'pcmscalcdistance3("Chicago, IL", "New York, NY", 1)';
36 ret_val := utl_tcp.write_line(c, v_call);
37 dbms_output.put_line('Send: ' || v_call);
38 LOOP
39 v_data := utl_tcp.get_line(c, TRUE);
40 dbms_output.put_line('Receive: ' || v_data || '^');
41 EXIT WHEN substr(v_data, length(v_data) - 4) = 'READY';
42 END LOOP;
43
44 utl_tcp.close_connection(c);
45 END;
46 /
Receive: ALK PCMILER SERVER READY^
Send: pcmscalcdistance3("Chicago, IL", "New York, NY", 1)
Receive: 7939^
Receive: 826^
Receive: READY^
Send: pcmscalcdistance("Chicago, IL", "New York, NY")
Receive:
Receive: READY^
Send: pcmscalcdistance3("Chicago, IL", "New York, NY", 1)
Receive:
Receive: 826^
Receive: READY^
PL/SQL procedure successfully completed.
I too am surprised to see that the "^" signs are missing in those blank lines.
I checked the PC Miler log file, and found the log has nothing unusual:
+PCMSCalcDistance3 ( 10000, "Chicago, IL", "New York, NY", 1, [0x004C63F0] )
-PCMSCalcDistance3 ( 7939, 826 )
+PCMSCalcDistance ( 10000, "Chicago, IL", "New York, NY" )
-PCMSCalcDistance ( 7994 )
+PCMSCalcDistance3 ( 10000, "Chicago, IL", "New York, NY", 1, [0x004C63D0] )
-PCMSCalcDistance3 ( 7939, 826 )
My co-worker suspects that maybe there is a bug in UTL_TCP. Since it looks like not many people are using that package, this bug was never discovered?
Should I be better off finding a java source to do telnet terminal and connect to PC Miler instead of using UTL_TCP? If so, any suggestions of good java program? Unfortunately, I know close to nothing about java.
|
|
|
Re: UTL_TCP returning blank line? [message #442646 is a reply to message #442619] |
Tue, 09 February 2010 13:09   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
I have used utl_tcp a couple of times and have never seen this behavior.
I would probably check the actual network traffic with wireshark next.
Or, one more thing you could try:
replace the
dbms_output.put_line('Receive: ' || v_data || '^');
lines with
dbms_output.put_line('Receive: ' || v_data || '^');
dbms_output.put_line('Size: ' || length(v_data) || '^');
So see if you actually get any data.
|
|
|
Re: UTL_TCP returning blank line? [message #443621 is a reply to message #442646] |
Tue, 16 February 2010 09:29   |
gjcjan
Messages: 5 Registered: February 2010 Location: Canada
|
Junior Member |
|
|
After doing a socket level trace, we have found the problem and a workaround.
First, after we send an API call to PC Miler, the last line of PC Miler data return is always "READY" to indicate that the PC Miler server is ready to receive another call. However, after close examination, the actual data is 'READY' || chr(10) || chr(0)
When using a utl_tcp.get_line, the function will grab data from the buffer until it sees a chr(10). In this case, it gets 'READY' || chr(10). And chr(0) is left in the buffer.
Now, if you issue another API call, and then do get_line, the chr(0) remained from the last API will throw-off the get_line function and return blank line as a result.
To get around this, we need to clear out the remaining chr(0) in the buffer before calling the next get_line. I use
to remove the chr(0) from the buffer. And the next get_line will work as normal.
And the code will be something like this:
v_call := 'pcmscalcdistance3("Chicago, IL", "New York, NY", 1)';
ret_val := utl_tcp.write_line(c, v_call);
dbms_output.put_line('Send: ' || v_call);
LOOP
v_data := utl_tcp.get_line(c, TRUE);
dbms_output.put_line('Receive: ' || v_data);
EXIT WHEN substr(v_data, length(v_data) - 4) = 'READY';
END LOOP;
v_data := utl_tcp.get_raw(c, 1);
And everything works.
ThomasG, thanks for all the suggestions.
[Updated on: Tue, 16 February 2010 09:30] Report message to a moderator
|
|
|
|
|
Goto Forum:
Current Time: Sun Feb 16 00:42:46 CST 2025
|