Re: Problem with function

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

Oracle is right: you cannot insert, update or delete some data while doing a select-statement.

> 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?

If you only want to add a new user do the following:

• start code -------------

set serveroutput on

declare

   ret_code number;
begin

   dbms_output.enable( 1000000 );
   ret_code := USER_ADD('NewUser','Pass', sysdate);    dbms_output.put_line( 'ReturnCode: ' || to_char( ret_code ) ); end;

/

• end code --------------

regards, Stephan Received on Tue Aug 14 2001 - 03:04:57 CDT