Home » SQL & PL/SQL » SQL & PL/SQL » Compare a varchar2 variable to a String using PL/SQL function (Oracle 11g, Windows XP)
Compare a varchar2 variable to a String using PL/SQL function [message #328318] Thu, 19 June 2008 13:59 Go to next message
Monk27
Messages: 10
Registered: June 2008
Junior Member
Hi,

I am a newbie to PL/SQL functions so sorry if my question is too basic. My doubt is as follows :

socket UTL_TCP.connection;
line VARCHAR2 (30000);
BYTES INTEGER;

BYTES := UTL_TCP.read_line (socket, line);

Now I want to check if the contents of variable "line" is equal to some String say "This is a test message". How can I do this using PL/SQL functions ?
Re: Compare a varchar2 variable to a String using PL/SQL function [message #328320 is a reply to message #328318] Thu, 19 June 2008 14:08 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

The whole Oracle Doc. set can be found at http://tahiti.oracle
which includes the PL/SQL Reference Manual.


FUNCTION READ_LINE RETURNS BINARY_INTEGER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 C                              RECORD                  IN/OUT
   REMOTE_HOST                  VARCHAR2(255)           IN/OUT
   REMOTE_PORT                  BINARY_INTEGER          IN/OUT
   LOCAL_HOST                   VARCHAR2(255)           IN/OUT
   LOCAL_PORT                   BINARY_INTEGER          IN/OUT
   CHARSET                      VARCHAR2(30)            IN/OUT
   NEWLINE                      VARCHAR2(2)             IN/OUT
   TX_TIMEOUT                   BINARY_INTEGER          IN/OUT
   PRIVATE_SD                   BINARY_INTEGER          IN/OUT
 DATA                           VARCHAR2                IN/OUT
 REMOVE_CRLF                    BOOLEAN                 IN     DEFAULT
 PEEK                           BOOLEAN                 IN     DEFAULT

Re: Compare a varchar2 variable to a String using PL/SQL function [message #328370 is a reply to message #328318] Thu, 19 June 2008 23:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Now I want to check if the contents of variable "line" is equal to some String say "This is a test message". How can I do this using PL/SQL functions ?

if line = 'mystring' then ...

Regards
Michel
Re: Compare a varchar2 variable to a String using PL/SQL function [message #328541 is a reply to message #328320] Fri, 20 June 2008 07:43 Go to previous messageGo to next message
Monk27
Messages: 10
Registered: June 2008
Junior Member
Hi,

Thanks for the reply. Now I want to check if the value of varchar2 variable "line" is empty String (empty line) and therefore I tried:

line = '';

And it does not work. Can anyone suggest me some solution for this ?
Re: Compare a varchar2 variable to a String using PL/SQL function [message #328542 is a reply to message #328541] Fri, 20 June 2008 07:45 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
line is null;
Re: Compare a varchar2 variable to a String using PL/SQL function [message #328547 is a reply to message #328542] Fri, 20 June 2008 07:58 Go to previous messageGo to next message
Monk27
Messages: 10
Registered: June 2008
Junior Member
Hi,

line is null;

is not working for me. What I am trying to do is reading an email attachment which is in base64 format. So firstly, I decode it using :
line2 := UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(line)));

And then I look for a blank line (which marks the end of attachment) so as to stop with application of the above mentioned decoding function and that is where I need to check if "line" which is of type varchar2 an empty line.

[Updated on: Fri, 20 June 2008 08:03]

Report message to a moderator

Re: Compare a varchar2 variable to a String using PL/SQL function [message #328550 is a reply to message #328547] Fri, 20 June 2008 08:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What do you mean by 'Is not working'?

If 'line is null' does not catch a line, then by definition, the line is not empty.

Re: Compare a varchar2 variable to a String using PL/SQL function [message #328551 is a reply to message #328547] Fri, 20 June 2008 08:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Monk27 wrote on Fri, 20 June 2008 14:58
Hi,

line is null;

is not working for me. Any other suggestion ?

Yes: grab a book on PL/SQL and study that.
This is so very very basic, that I am afraid you will be back within 15 minutes with a question about the next line in your program.
Asking here and simply copy-paste the answer is not going to help you, since you clearly don't understand what you are doing (this is not meant to offend, just an observation).
Re: Compare a varchar2 variable to a String using PL/SQL function [message #328552 is a reply to message #328318] Fri, 20 June 2008 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL User's Guide and Reference

Regards
Michel
Re: Compare a varchar2 variable to a String using PL/SQL function [message #328558 is a reply to message #328551] Fri, 20 June 2008 08:21 Go to previous messageGo to next message
Monk27
Messages: 10
Registered: June 2008
Junior Member
Hi Frank,

Sorry for not putting forward my question properly. As i mentioned in my previous post, I am trying to read an email attachment from a POP server which is encoded in base64. I am able to decode the attachment properly, but the decoding function has to stop at the last line of the attachment which happens to be a blank line (as shown on with telnet command RETR). This is where I got stuck and asked for help.

Regarding my original question, I did get the syntax correctly and had already implemented the suggested solution but was not able to get the correct answer, so just wanted to verify if what I was doing was right and finally I realized that there was a problem with the decoding function that I was implementing. I am sorry for taking up your valuable time.

Thanks again.
Re: Compare a varchar2 variable to a String using PL/SQL function [message #328561 is a reply to message #328558] Fri, 20 June 2008 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
so just wanted to verify if what I was doing was right

Next time, if you want to know this, at least post the code.

Regards
Michel
Re: Compare a varchar2 variable to a String using PL/SQL function [message #328563 is a reply to message #328558] Fri, 20 June 2008 08:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Monk27 wrote on Fri, 20 June 2008 15:21
I am sorry for taking up your valuable time.

That's not the point.
My point was that if you don't know how to check the contents of a variable, and you can't interpret an "line is null;" answer, you know too little about PL/SQL to code a program.

Now you updated your post; added a lot of info.

Since you still don't seem to be able how to recognize an empty string, my previous point still stands.
Show us your real code, show how you used Michel's hint.
And please, do not change your posts so rigorously after people have commented on it.
Re: Compare a varchar2 variable to a String using PL/SQL function [message #328568 is a reply to message #328561] Fri, 20 June 2008 08:48 Go to previous messageGo to next message
Monk27
Messages: 10
Registered: June 2008
Junior Member
I have uploaded my code file with appropriate comments. Please do let me know in case you need any more clarifications. Also, here I am pasting the encoded attachment as received through the Telnet command RETR

------=_Part_1171_24578785.1213817664875
Content-Type: text/plain; name=PrinterSetupInfo.txt
Content-Transfer-Encoding: base64
X-Attachment-Id: f_fhmbp2rp0
Content-Disposition: attachment; filename=PrinterSetupInfo.txt

PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PQ0KU2V0dXAgUmVzdWx0
cw0KPT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PQ0KDQpNb2RlbCA6
DQoJQ2Fub24gTVg3MDAgc2VyaWVzDQoNCiAgTmV0d29yayBTZXR1cCBvZiB0aGUgUHJpbnRlciA6
DQoJQ29tcGxldGVkDQogICAgTmFtZSA6DQoJQ2Fub24gTVg3MDAgc2VyaWVzDQogICAgUG9ydCBO
YW1lIDoNCglDTkJKTlBfMDAwMDg1QzQ5NDUwDQoNCiAgTmV0d29yayBTZXR1cCBvZiB0aGUgU2Nh
bm5lciA6DQoJQ29tcGxldGVkDQogICAgTmFtZSA6DQoJQ2Fub24gTVg3MDAgc2VyXzAwMDA4NUM0
OTQ1MA0KDQogIE5ldHdvcmsgU2V0dXAgb2YgdGhlIEZheCA6DQoJQ29tcGxldGVkDQogICAgTmFt
ZSA6DQoJQ2Fub24gTVg3MDAgc2VyaWVzIEZBWA0KICAgIFBvcnQgTmFtZSA6DQoJQ05CSk5QRkFY
XzAwMDA4NUM0OTQ1MA0KDQogIE5ldHdvcmsgU2V0dXAgb2YgdGhlIENhcmQgU2xvdCA6DQoJQ29t
cGxldGVkDQogICAgRHJpdmUgTmFtZSA6DQoJY2Fub25fbWVtb3J5IG9uICcwMDAwMDBjNDk0NTAn
IChaOikNCg0KDQo9PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09DQo=

------=_Part_1171_24578785.1213817664875

As you can see, I am trying to read the blank line above "------=_Part_1171_24578785.1213817664875" to stop decoding the attachment.

The above encoded attachment corresponds to:
===========================================
Setup Results
===========================================

Model :
Canon MX700 series

Network Setup of the Printer :
Completed
Name :
Canon MX700 series
Port Name :
CNBJNP_000085C49450

Network Setup of the Scanner :
Completed
Name :
Canon MX700 ser_000085C49450

Network Setup of the Fax :
Completed
Name :
Canon MX700 series FAX
Port Name :
CNBJNPFAX_000085C49450

Network Setup of the Card Slot :
Completed
Drive Name :
canon_memory on '000000c49450' (Z:)


===========================================
Re: Compare a varchar2 variable to a String using PL/SQL function [message #328570 is a reply to message #328568] Fri, 20 June 2008 09:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
try
IF rtrim(line) IS NULL


If the line is just space padded, then that should do the job.
Re: Compare a varchar2 variable to a String using PL/SQL function [message #328573 is a reply to message #328570] Fri, 20 June 2008 09:24 Go to previous messageGo to next message
Monk27
Messages: 10
Registered: June 2008
Junior Member
Thanks for the reply JRowBottom,

rtrim(line) does not help me. But I tried testing with a few more attachments and there is an "=" sign to mark the end of attachment. So now I will read the contents of the attachment line by line and at the same time try to find out "=" sign (at the end of line) which marks the end of attachment.

Re: Compare a varchar2 variable to a String using PL/SQL function [message #328580 is a reply to message #328573] Fri, 20 June 2008 09:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The only other thing I can think of is that you might be getting passed a CHR(0) as a blank line.

Try IF LINE = chr(0)
Re: Compare a varchar2 variable to a String using PL/SQL function [message #328583 is a reply to message #328573] Fri, 20 June 2008 09:39 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You could also stop trying to use Oracle as a mail client which interacts with a mail server on the network socket level.

There are dozens of language like Perl, Java, VBScript that can connect both to Oracle and a Mail server and do that much more easily.

Also, the format of the mail itself might be slightly different depending on the mail client that sent it, so you would forever be catching up with trying to correctly decode new formats. (One example : The attachments could be either inlined or attached in the mail.

Also, the Content-Transfer-Encoding might change from client to client or from mail to mail.


Re: Compare a varchar2 variable to a String using PL/SQL function [message #328585 is a reply to message #328583] Fri, 20 June 2008 09:47 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
PS : The "=" Character(s) at the end of the attachment are not always there. They are used for output padding and might also vary from mail to mail.

If you really want to write the MIME decoder yourself, have a look at RFC2045, RFC2046, RFC2047, RFC4288, RFC4289 and RFC2077 for the format descriptions.
Re: Compare a varchar2 variable to a String using PL/SQL function [message #328601 is a reply to message #328585] Fri, 20 June 2008 11:50 Go to previous message
Monk27
Messages: 10
Registered: June 2008
Junior Member
Hi Thomas,

Thanks for pointing it out to me. We did have a discussion about what you suggested right before I started with this work. The company wants me to use PL/SQL functions to fulfill email client functionality.

Anyways good news is that I am able to retrieve an attachment and stop decoding at the right instant using :

IF line LIKE '%='
THEN
.....

I know this condition will work only for attachment encoded to base64. Since we are developing a Host application, for now we are concerned with serving just one of our clients' requirements and then as the client base widens, I will have to integrate more decoding schemes Smile.

Thanks for all the help Thomas, Frank and JRowBottom. I really appreciate it.

[Updated on: Fri, 20 June 2008 11:51]

Report message to a moderator

Previous Topic: Query Help
Next Topic: Alter table
Goto Forum:
  


Current Time: Sat Dec 10 06:42:15 CST 2016

Total time taken to generate the page: 0.22468 seconds