OraFAQ Forum: SQL & PL/SQL » Still: creating user and inserting data

Home » SQL & PL/SQL » SQL & PL/SQL » Still: creating user and inserting data

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Still: creating user and inserting data [message #3075]

Tue, 03 September 2002 04:53

Christine Pollhaus
Messages: 18
Registered: May 2002

Junior Member

I wrote my proc like this now:

CREATE OR REPLACE
PROCEDURE NEWUSER (user_in IN CHAR, pw_in IN CHAR, name_in IN CHAR)
IS
BEGIN
EXECUTE IMMEDIATE
'CREATE USER '||user_in||' IDENTIFIED BY '||pw_in||' DEFAULT TABLESPACE PROFILE_C TEMPORARY TABLESPACE TEMP ';
EXECUTE IMMEDIATE
'GRANT STANDARD_USER TO '||user_in||' ';
EXECUTE IMMEDIATE
'INSERT INTO PFC_BWV_ANWENDER (USER_ID, USER_NAME, SPRACHE, THEMA, DATEN_GRP) VALUES ('||user_in||' , '||name_in||' , spr , th , grp) ' ;
END;

On complation Ora says VALID but on execution SQL*PLUS says:

SQL> execute newuser ('TEST', 'Dr. Helmut Test', 'TEST')
BEGIN newuser ('TEST', 'Dr. Helmut Test', 'TEST'); END;

*
FEHLER in Zeile 1:
ORA-00922: Fehlende oder ung�ltige Option
ORA-06512: in "PROFILEC.NEWUSER", Zeile 4
ORA-06512: in Zeile 1

what is it ????

Any help available ???

Report message to a moderator

Re: Still: creating user and inserting data [message #3078 is a reply to message #3075]

Tue, 03 September 2002 05:24

Keith
Messages: 88
Registered: March 2000

Member

Grants are your problem I think.

Before running the procedure grant create user and grant all to the user you're about to run the procedure as.

Note that your last insert statement does not need to be within an EXECUTE IMMEDIATE block, as it's straight SQL.

Let me know if this works

Report message to a moderator

Re: Still: creating user and inserting data [message #3085 is a reply to message #3075]

Tue, 03 September 2002 22:58

Christine Pollhaus
Messages: 18
Registered: May 2002

Junior Member

Hi Keith,

Thanks really a lot. I DID IT.

Works like this now:

CREATE OR REPLACE
PROCEDURE NEWUSER (user_in IN CHAR, pw_in IN CHAR, name_in IN CHAR)
IS
spr CHAR(1) := 'D';
th CHAR(3) := 'SYS';
grp CHAR(1) := '.';
BEGIN
EXECUTE IMMEDIATE
'CREATE USER '||user_in||' IDENTIFIED BY '||pw_in||' DEFAULT TABLESPACE PROFILE_C TEMPORARY TABLESPACE TEMP ';
EXECUTE IMMEDIATE
'GRANT STANDARD_USER TO '||user_in||' ';
INSERT INTO PFC_BWV_ANWENDER (USER_ID, USER_NAME, SPRACHE, THEMA, DATEN_GRP) VALUES (''||user_in||'' , ''||name_in||'' , spr , th , grp);
commit;
END;

was great from ya!

Regards christine

Report message to a moderator

Previous Topic:	urgent
Next Topic:	DECODE Function

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 03:50:13 CDT 2024