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

Advertisements

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 »