Oracle User Defined Functions in SQLFiddle

Question

I'm trying to play with Oracle's user defined functions and virtual columns, but my only test environment is SQL Fiddle and I'm struggling to understand what I'm doing wrong as the error message is pretty nondescript.

ORA-06575: Package or function COUNTER is in an invalid state

I've tried various tweaks and copy-paste attempts from different sites, but can't see to get functions and sqlfiddle to play nicely together...

create table test (
    x number
)
//

create
  function counter()
    return number is v_result NUMBER;
    begin
        SELECT COUNT(*) INTO v_result FROM test;
        return v_result;
    end;
//

select test.*, counter() from test
//

http://sqlfiddle.com/#!4/0c001

Anyone able to make this work, or am I headed down a blind alley?

Whomever voted to close, please could you comment on your rational? — MatBailie, Dec 02 '17 at 14:11
I didn't understand it either. Could say it was more of an irrationale :) — Caius Jard, Dec 02 '17 at 20:45

score 3 · Accepted Answer · answered Dec 02 '17 at 14:05

3

Functions that take no arguments don't have any (empty) brackets after the name

http://sqlfiddle.com/#!4/0c001/5

You can query the sys.user_errors table for a list of the compilation errors. "Invalid state" means the code didn't compile

answered Dec 02 '17 at 14:05

Caius Jard

72,509
5
49
80

Oracle User Defined Functions in SQLFiddle

1 Answers1