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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #442570 is a reply to message #442512] Tue, 09 February 2010 02:39 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Hmm.. Now that is strange. In the "empty" lines there is a "Receive:", but no "^".

Maybe PCMILER sends some sort of non-printable control character that is messing up the output. Did you run it in SQL*Plus?

For example this here:

SQL>  set serverout on;
SQL> DECLARE
  2
  3   v_text VARCHAR2(10);
  4
  5  BEGIN
  6
  7   v_text := 'Foo';
  8   Dbms_Output.put_line('Received: ' || v_text || '^');
  9
 10   v_text := 'Foo' || Chr(13);
 11   Dbms_Output.put_line('Received: ' || v_text || '^');
 12
 13   v_text := Chr(13) || 'Foo' || Chr(13);
 14   Dbms_Output.put_line('Received: ' || v_text || '^');
 15
 16  END;
 17  /
Received: Foo^
^eceived: Foo
^ooeived:

PL/SQL procedure successfully completed.

SQL>


is recognizable as messed up output in SQL*Plus, but results in the "Blank lines" you are seeing in the GUI tool I use.

/forum/fa/7429/0/
  • Attachment: blank.png
    (Size: 2.75KB, Downloaded 2064 times)
Re: UTL_TCP returning blank line? [message #442619 is a reply to message #442570] Tue, 09 February 2010 08:19 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
utl_tcp.get_raw(c, 1) 

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

Re: UTL_TCP returning blank line? [message #443622 is a reply to message #443621] Tue, 16 February 2010 09:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
THANKS for the feedback!
Re: UTL_TCP returning blank line? [message #443635 is a reply to message #443622] Tue, 16 February 2010 10:55 Go to previous message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yep. Many thanks for letting us know the mystery is solved. Smile
Previous Topic: Executing script files in a directory
Next Topic: Spooling data in pipe delimitted file
Goto Forum:
  


Current Time: Sun Feb 16 00:42:46 CST 2025