Home » SQL & PL/SQL » SQL & PL/SQL » checking for blank line (PL/SQL)
checking for blank line [message #376395] Wed, 17 December 2008 03:47 Go to next message
Supertd
Messages: 15
Registered: December 2008
Location: Africa
Junior Member
Hi,

I am new to pl/sql and i am reading, converting and writing a txt file. I am facing the following problem: When reading the input file, i need to check for a blank line. If a blank line is found, then I can skip that line and create a footer record. My code to do this is as follows :

if ((fileinput = CHR(10)) or (fileinput = CHR(13)) or (fileinput = '\n') or (trim(fileinput) is null) ) then 'skip and create a footer and write to file' ELSE do X

-fileinput is the line being read via utl_file.get_line

This works fine if the line contains no spaces (ie. just the enter key was pressed) but if the line contains spaces, it just goes to the do X part.

sample input:
12346 --data
--blank line
123 --old footer line


Please assist. any help would be greatly appreciated.
Thanks.

Re: checking for blank line [message #376397 is a reply to message #376395] Wed, 17 December 2008 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, "(fileinput = '\n')" is meaningless in PL/SQL.

Then it should work:
SQL> begin
  2     if trim('        ') is null 
  3     then dbms_output.put_line('Empty');
  4     else dbms_output.put_line('NOT empty');
  5     end if;
  6  end;
  7  /
Empty

PL/SQL procedure successfully completed.

Investigate if you have no other character like tabs.

Regards
Michel
Re: checking for blank line [message #376405 is a reply to message #376397] Wed, 17 December 2008 04:08 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Another example:
DECLARE
  fhandle UTL_FILE.FILE_TYPE;
  line    VARCHAR2(80);
BEGIN
  fhandle := UTL_FILE.FOPEN('TMP', 'myfile', 'R');
  LOOP
    UTL_FILE.GET_LINE(fhandle, line);
    IF line IS NULL THEN
       dbms_output.put_line('Got empty line');
    ELSE
       dbms_output.put_line('Non empty line: '||line);
    END IF;
  END LOOP;
EXCEPTION
  WHEN no_data_found THEN
     dbms_output.put_line('No more data');
END;
/
Re: checking for blank line [message #376408 is a reply to message #376397] Wed, 17 December 2008 04:18 Go to previous messageGo to next message
Supertd
Messages: 15
Registered: December 2008
Location: Africa
Junior Member
Thanks Michel,

it works fine when I use 'if trim(' ') is null ' but when I use 'if trim(fileinput) is null ' it does not work. The problem is that I do not know the exact amount of spaces on the line.
Re: checking for blank line [message #376410 is a reply to message #376408] Wed, 17 December 2008 04:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The problem is that I do not know the exact amount of spaces on the line.

It does not matter, as my example shows it, I didn't count the number of blanks.

I repeat: "Investigate if you have no other character like tabs".

Regards
Michel
Re: checking for blank line [message #376415 is a reply to message #376410] Wed, 17 December 2008 04:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suspect that your line acrtually consists of spaces and a C/R character.

Try using the DUMP command to show you exactly what is in the blank lines.
Re: checking for blank line [message #376417 is a reply to message #376410] Wed, 17 December 2008 04:41 Go to previous messageGo to next message
Supertd
Messages: 15
Registered: December 2008
Location: Africa
Junior Member
Thanks again for your response.
i have included my input file (no special characters contained-just spaces using spacebar) my code is :

LOOP
utl_file.get_line(f_inputfile_handle, fileinput); --retrieve next line

if ((fileinput = CHR(10)) or (fileinput = CHR(13)) or (trim(fileinput) is null) ) then --line before footer reached
dbms_output.put_line('blank line/spaces encountered');
utl_file.put_line(f_outputfile_handle, fileinput); --output the blank line
--create a footer record containing total records written
--write footer
exit;
else
--do some stuff
end if;

END LOOP;

the output window never shows the message.
  • Attachment: mytest.txt
    (Size: 0.05KB, Downloaded 186 times)
Re: checking for blank line [message #376430 is a reply to message #376417] Wed, 17 December 2008 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
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 (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: checking for blank line [message #376431 is a reply to message #376417] Wed, 17 December 2008 06:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Really - use the DUMP function. It shows you the Ascii value of each character in the string.
Re: checking for blank line [message #376447 is a reply to message #376430] Wed, 17 December 2008 06:41 Go to previous message
Supertd
Messages: 15
Registered: December 2008
Location: Africa
Junior Member
My apologies about the previous format of code. I have solved my problem though. Her is the line of code that I used:
if (trim(fileinput) = CHR(10) or trim(fileinput) = CHR(13) ) then ... 

I know that the input file will always have an existing footer record after the blank line. The above code caters for spaces on the line as well as blank lines(enter key only)

Thanks for all your help. Smile
Previous Topic: Please help me to solve this query
Next Topic: Better way of checking existence of record in query/table
Goto Forum:
  


Current Time: Wed Dec 07 10:23:44 CST 2016

Total time taken to generate the page: 0.06518 seconds