A procedure can not create a user?

[brig]

I am reading and thinking that a user (or any object) cannot be created within a procedure. Is that correct? I am new to
PL/ SQL, not programming, but do apologize.
I have a StdtTable (in SYS) with 3 columns: account, password, rowid.
I just want to read each row and create a user. This is what I have, does anyone have any idea's? The code is sloppy as I am stuck on the create. :-( Thank you. Oh, I am running this is SQLDeveloper.

CREATE OR REPLACE
PROCEDURE makeStudents

AS
-- declare all variables!

iReturnCode int:=0;
iNextRowId int:=0;
iCurrentRowId int:=0;
iLoopControl int :=13;
iRowId INT :=0;
vhELLO VARCHAR2 (30 CHAR) := 'HELLO';
vchACCOUNName varchar2(7):='';
vchPasswordNmbr varchar2(9):='';


BEGIN

SELECT iRowId into iNextRowId FROM SYS.STDTable;

-- Retrieve the first row
SELECT iRowId, STDAccount, STDPASSWORD INTO iCurrentRowId, vchACCOUNName,vchPasswordNmbr
FROM SYS.STDTABLE WHERE iRowId = iNextRowId;
/* EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN;*/
-- start the main processing loop.

WHILE iLoopControl > iNextRowId
LOOP
BEGIN

CREATE USER vchACCOUNName
IDENTIFIED BY vchPasswordNmbr
DEFAULT TABLESPACE STUDENTTABLESPACE
QUOTA 10M ON example
QUOTA 10M ON system;

-- processing.

SELECT iRowId into iNextRowId
FROM SYS.STDTable
WHERE iRowId > iCurrentRowId;

SELECT iRowId, STDAccount, STDPASSWORD INTO iCurrentRowId, vchACCOUNName,vchPasswordNmbr
FROM SYS.STDTABLE WHERE iRowId = iNextRowId;

/* EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN;*/

end;--begin in while
end loop;--in while
end;--procedure

[debasisdas]

You can execute any DDL commands from with a procedure by using EXECUTE IMMEDIATE .

[brig]

Thank you, that did it. Thank you so much, It was an itch I couldn't scratch for days. :-)

[brig]

Well, all worked fine. Said procedure successfully completed. But no users were created. AM I missing something???

[brig]

I am very confused. I ran the script below and it worked and was completed. But where are the users it was supposed to create? Am I to do something in addition to running the procedure? I am so sorry if this is a baby question.
Thank you
Brig


SQL> create or replace
2 PROCEDURE makeStudents
3
4 AS
5 -- declare all variables!
6
7 iReturnCode int:=0;
8 iNextRowId int:=0;
9 iCurrentRowId int:=0;
10 iLoopControl int :=13;
11 iRowId INT :=0;
12 vchACCOUNName varchar2(7):='';
13 vchPasswordNmbr varchar2(9):='';
14
15
16 BEGIN
17
18 -- SELECT iRowId into iNextRowId FROM SYS.STDTable;
19
20 -- Retrieve the first row
21 SELECT iRowId, STDAccount, STDPASSWORD INTO iCurrentRowId, vchACCOUNName,
vchPasswordNmbr
22 FROM SYS.STDTABLE
23 WHERE iRowId = iCurrentRowId;
24 DBMS_OUTPUT.PUT_LINE('Accout is ' || vchACCOUNName);
25 EXCEPTION
26 WHEN NO_DATA_FOUND THEN
27 RETURN;
28 -- start the main processing loop.
29
30 WHILE iLoopControl > iCurrentRowId
31 LOOP
32 BEGIN
33
34 EXECUTE IMMEDIATE 'CREATE USER vchACCOUNName
35 IDENTIFIED BY :=vchPasswordNmbr
36 DEFAULT TABLESPACE STUDENTTABLESPACE
37 QUOTA 10M ON example
38 QUOTA 10M ON system';
39
40 -- processing.
41 SELECT iRowId, STDAccount, STDPASSWORD INTO iCurrentRowId, vchACCOUN
Name,vchPasswordNmbr
42 FROM SYS.STDTABLE WHERE iRowId = iCurrentRowId;
43 EXCEPTION
44 WHEN NO_DATA_FOUND THEN
45 RETURN;
46 DBMS_OUTPUT.PUT_LINE('Accout is ' || vchACCOUNName);
47 end;--begin in while
48 end loop;--in while
49 end;--procedure
50 /

Procedure created.

SQL> begin
2 makestudents;
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>

[debasisdas]

Both the threads are merged for better management and readibility of the forum.


MODERATOR

[debasisdas]

Does the invoking user have CREATE USER system previledge. ?

[brig]

Hi, Yes I am doing this under SYS. Someone told me I shouldn't use SYS and create tables there. Should I use system?
I also think my code is just wrong. I have changed it many times. I think I need to start over, thinking clearly. Then I will repost if it doesn't work.

Thank you so much for listening and helping :-)
brig