Re: Problem with function

in oracle 7.34 and oracle 8 up to 8.05 you can't make DML operations inside a function
(or only with big workarounds) to avoid data inconsistences in the return data (which imho is bullshit)

in oracle 8.15 (and up) you need to declare that this function is allowed to do this with a pragma
simply write in the header (befor the begin):

PRAGMA AUTONOMOUS_TRANSACTION; Artur wrote:

> Please help
>
> I am fairly new to oracle, and appreciate any help or advice anyone can give
> me. I created simple function:
> CREATE OR REPLACE FUNCTION "SUSER"."USER_ADD"
> (Log IN VARCHAR2, Pass IN VARCHAR2, Created IN DATE)
> RETURN NUMBER
> IS
> UserID NUMBER;
> CNT NUMBER;
> BEGIN
> UserID := 0;
> SELECT COUNT(*) into CNT FROM SUSER.USER WHERE Login = Log;
> IF CNT < 1 THEN
> SELECT SQ_USER_ID.nextval INTO UserID FROM DUAL;
> Insert into SUSER values (UserID, Log,Pass,Created);
> ELSE
> UserID := -1;
> END IF;
> RETURN UserID;
> END;
>
> Afterr calling:
> select USER_ADD('NewUser','Pass', sysdate) from dual;
> I received an error:
>
> ERROR at line 1:
> ORA-14551: cannot perform a DML operation inside a query
> ORA-06512: at "SUSER.USER_ADD", line 11
> ORA-06512: at line 1
>
> Something is wrong with line: "Insert into SUSER values (UserID,
> Log,Pass,Created);"
> This line adds new record to SUSER table.
> How should I do this ?
> How can I get it to work?
>
> ERTON
Received on Mon Aug 13 2001 - 05:43:49 CDT