Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Problem with function

Problem with function

From: Artur <aerton_at_poczt.onet.pl>
Date: Mon, 13 Aug 2001 11:10:37 +0200
Message-ID: <9l85j8$r24$1@news.onet.pl>

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 - 04:10:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US