You Asked

Hello,

I need to check if a file content is written on a single line or it is slit in multiple line.
For example, I want to make difference between the following 2 structures:

123'456'789'

vs.

123'
456'
789'

My only idea is to check if the file have more than 1 line.

Is there an easy option to do that or should I made an algorithm for that?

Thank you,

and Connor said...

A quick search for a line feed should do, eg in my case file1 is one line, file2 is 2 lines long.

SQL> set serverout on
SQL> declare
  2    b bfile := bfilename('TEMP','file1.txt');
  3    i int;
  4  begin
  5    dbms_lob.fileopen(b);
  6    i := dbms_lob.instr(b,utl_raw.cast_to_raw(chr(10)),1,1);
  7    dbms_output.put_line(i);
  8    dbms_lob.fileclose(b);
  9  end;
 10  /
0

PL/SQL procedure successfully completed.

SQL> set serverout on
SQL> declare
  2    b bfile := bfilename('TEMP','file2.txt');
  3    i int;
  4  begin
  5    dbms_lob.fileopen(b);
  6    i := dbms_lob.instr(b,utl_raw.cast_to_raw(chr(10)),1,1);
  7    dbms_output.put_line(i);
  8    dbms_lob.fileclose(b);
  9  end;
 10  /
32

PL/SQL procedure successfully completed.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ionut Preda, August 30, 2018 - 7:29 am UTC

The usage of WITH_PLSQL hint into a MERGE statement

Ion, December 08, 2021 - 2:56 pm UTC

Hi Tom,

I have the following piece of code:

merge /*+ WITH_PLSQL */ into test a
    using (
        with function to_upper(val varchar2) return varchar2 is
        begin
            return upper(val);
        end;
        select to_upper(c2) as c2 from test
    ) b
on (upper(a.c1) = b.c2)
when matched then 
    update set a.c3 = upper(a.c1)

Especially I didn't put any ending char in the end (neither ";", nor "/").
The script works very nice only when I am ending it with "/".
When I end the script with ";", I receive the following error: ORA-00933: SQL command not properly ended
In order to pass over compilation, I can use a dynamic SQL but I want to compile it into a procedure.

Please advise how to write the code in order to be compilable.

Thank you,

December 09, 2021 - 12:25 am UTC

SQL> show sqlterminator
sqlterminator ";" (hex 3b)

by default means that a semicolon means we are going to execute something. You'd need to turn that off in order to make it compile

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

The usage of WITH_PLSQL hint into a MERGE statement

More to Explore

PL/SQL demos

PL/SQL docs

check the 2nd line of a file

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

The usage of WITH_PLSQL hint into a MERGE statement

More to Explore

PL/SQL demos

PL/SQL docs