Sokrates on Oracle

Archive for the ‘PL/SQL’ Category

Why is it not possible to raise an exception when handling no_data_needed ?

Posted by Matthias Rogel on 8. November 2014

It seems impossible to raise an exception when handling no_data_needed.

See

create or replace function demo return sys.odciNumberList pipelined
as
begin
  pipe row(1);
  pipe row(1/0);
exception when others then
  dbms_output.put_line('in exception handler, sqlcode: ' || sqlcode);
  raise program_error;
end;
/

sokrates@11.2 > select * from table(demo());
ERROR:
ORA-06501: PL/SQL: program error
ORA-06512: at "SOKRATES.DEMO", line 8
ORA-01476: divisor is equal to zero



no rows selected

in exception handler, sqlcode: -1476

As expected, we handle exception raised by division by zero, there we dbms_output some info and then raise program error.

But now:

sokrates@11.2 > select * from table(demo()) where rownum=1;

COLUMN_VALUE
------------
           1

in exception handler, sqlcode: -6548
sokrates@11.2 >

oops – not expected.
“where rownum=1” causes ORA-06548 been raised and thus we again handle that. We dbms_output some info and then – no error is raised !

Why ?
I have no idea and couldn’t find any in docs either.

Update 2014/11/09:

I found the answer in Bug 13088409 – RAISE_APPLICATION_ERROR ignored in a WHEN OTHERS / WHEN NO_DATA_FOUND exception handler of a pipe-lined table function (Doc ID 13088409.8)

The point of the bug is that an exception handler

procedure h;

just behaves like

begin
   h;
exception when others then null;
end;

, when it is handling NO_DATA_NEEDED raised by a calling SQL.
h might be called either from a WHEN NO_DATA_NEEDED or a WHEN OTHERS – block.

The note says
… Versions confirmed as being affected
11.2.0.2

The fix for 13088409 is first included in
12.1.0.1 (Base Release)
11.2.0.4 (Server Patch Set)

My test case was on 11.2.0.3

Posted in PL/SQL | 7 Comments »

PL/SQL oddity

Posted by Matthias Rogel on 12. May 2014

sokrates@12.1 > create procedure p is begin null; end this_does_not_compile;
  2  /

Warning: Procedure created with compilation errors.

sokrates@12.1 > show errors
Errors for PROCEDURE P:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/32	 PLS-00113: END identifier 'THIS_DOES_NOT_COMPILE' must match 'P'
	 at line 1, column 11

sokrates@12.1 > REM expected
sokrates@12.1 > create function f return number is begin return null; end this_does_not_compile;
  2  /

Warning: Function created with compilation errors.

sokrates@12.1 > show errors
Errors for FUNCTION F:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/52	 PLS-00113: END identifier 'THIS_DOES_NOT_COMPILE' must match 'F'
	 at line 1, column 10

sokrates@12.1 > REM expected
sokrates@12.1 > create package pk is end this_does_not_compile;
  2  /

Warning: Package created with compilation errors.

sokrates@12.1 > show errors
Errors for PACKAGE PK:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/19	 PLS-00113: END identifier 'THIS_DOES_NOT_COMPILE' must match 'PK'
	 at line 1, column 9

sokrates@12.1 > REM expected
sokrates@12.1 > REM 
sokrates@12.1 > REM but now:
sokrates@12.1 > REM 
sokrates@12.1 > create table t ( i int );

Table created.

sokrates@12.1 > create trigger tr before insert on t begin null; end funny_this_DOES_COMPILE;
  2  /

Trigger created.

sokrates@12.1 > show errors
No errors.

Posted in 12c, PL/SQL | 4 Comments »

utl_file.fopen raises exception when file to be opened has more than one name

Posted by Matthias Rogel on 15. June 2011

On Unix, creating a hard link is just the same as giving a file another name.
I observed an interesting issue when using utl_file with hard links:

11.2.0.2.0 > create directory tmp as '/tmp';
Directory created.
11.2.0.2.0 > !echo hi > /tmp/t.txt
11.2.0.2.0 > declare
2 f utl_file.file_type;
3 l varchar2(100);
4 begin
5 f:= utl_file.fopen('TMP', 't.txt', 'r');
6 utl_file.get_line(f, l, 100);
7 utl_file.fclose(f);
8 dbms_output.put_line('line = "' || l || '"');
9 end;
10 /
line = "hi"
PL/SQL procedure successfully completed.

so far, so good

now:

11.2.0.2.0 > !ln /tmp/t.txt /tmp/another_name_for_t.txt
11.2.0.2.0 > /
declare
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 5

utl_file.fopen throws exception when file to be opened has more than one name.
I wonder why

Posted in 11g, PL/SQL | Leave a Comment »