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
– MatBailie, Commented Dec 2, 2017 at 14:11
I didn't understand it either. Could say it was more of an irrationale :) — Caius Jard
– Caius Jard, Commented Dec 2, 2017 at 20:45

Caius Jard · Accepted Answer · 2017-12-02 14:05:35Z

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 2, 2017 at 14:05

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle User Defined Functions in SQLFiddle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related