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,v chPasswordNmbr
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 STUDENTTABLESPA CE
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,v chPasswordNmbr
FROM SYS.STDTABLE WHERE iRowId = iNextRowId;
/* EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN;*/
end;--begin in while
end loop;--in while
end;--procedure
7 2050
You can execute any DDL commands from with a procedure by using EXECUTE IMMEDIATE .
Thank you, that did it. Thank you so much, It was an itch I couldn't scratch for days. :-)
Well, all worked fine. Said procedure successfully completed. But no users were created. AM I missing something???
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 :=vchPasswordNm br
36 DEFAULT TABLESPACE STUDENTTABLESPA CE
37 QUOTA 10M ON example
38 QUOTA 10M ON system';
39
40 -- processing.
41 SELECT iRowId, STDAccount, STDPASSWORD INTO iCurrentRowId, vchACCOUN
Name,vchPasswor dNmbr
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>
Both the threads are merged for better management and readibility of the forum.
MODERATOR
Does the invoking user have CREATE USER system previledge. ?
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: dinesh prasad |
last post by:
I'm trying to use a servlet to process a form, then send that data to
an SQL server stored procedure. I'm using the WebLogic 8 App. server.
I am able to retrieve database information, so I know my application
server can talk to the database.
I've determined the failure occurs when the the following statement is
executed: cstmt.execute(); (due to the failure of println statements
placed afterwards). I get the following error after trying to...
|
by: Michael |
last post by:
This one's really got me. I have a VB.NET (version 1.1.4322) project
that provides an easy way to execute stored procedures on a generic
level. When I run the code on computer A (running SQL Server 2000
version 08.00.0194) the code works great. However, computer B
(running SQL Server 2000 version 08.00.0534) bombs when I try to
execute the sproc saying 'Could not find stored procedure
'spmw_ReadByPage'. My thought process went as...
|
by: Micah Gentry |
last post by:
Is there any way to allow a user to use the xp_cmdshell extended
stored procedure without giving that user execute permissions to
xp_cmdshell in SQL server 6.5? Let me clarify. Lets say I (as the
dbo) create a stored procedure called sp_send_err:
CREATE PROCEDURE sp_send_err @CompID varchar(20) AS
declare @strCMD varchar(255)
select @strCMD = "master.dbo.xp_cmdshell 'net send " + @CompID + "
""ERROR!""', no_output"
execute (@strCMD)
|
by: Martin Feuersteiner |
last post by:
Dear Group
I'm having two stored procedures, sp_a and sp_b
Content of stored procedure A:
CREATE PROCEDURE dbo.sp_a
SELECT * FROM a
GO
Content of stored procedure B:
|
by: Dino L. |
last post by:
How can I run stored procedure (MSSQL) ?
| |
by: Michael |
last post by:
Running DB2 v7 UDB ("DB2 v7.1.0.93", "n031208" and "WR21333") on Windows XP,
I am unable to find out why the "Build for Debug" option within Stored
Procedure Builder is not enabled on Java stored procedures. It is enabled
for SQL stored procedures.
It is possible to "Build" and "Run" the Java SPs, it just isn't possible to
click on the "Build for Debug" option. Thanks for any help in advance.
Michael
|
by: masri999 |
last post by:
I have a requirement in SQL 2005 in Development database
1. Schema dbo owns all objects (tables,views,SPs,UDFs etc) .
2. Only DBA's ( who are database owners ) can create, alter tables .
Developer's should not create or alter tables .
3. Developers can create/alter Stored Procedure/User Defined functions
in dbo schema and can execute SP/UDF.
4. Developers should have SELECT,INSERT,DELETE,UPDATE on tables (
tables in dbo schema
|
by: SOI_0152 |
last post by:
Hi all!
Happy New Year 2008. Il hope it will bring you love and happyness
I'm new on this forum.
I wrote a stored procedure on mainframe using DB2 7.1.1 and IBM language c.
Everything works fine.
Now we decided to move from mainframe IMS-DB2 to Windows 2003 server-DB2 UDB
for LUW 9.5.
|
by: billmaclean1 |
last post by:
I need to write a stored procedure that selects from a table and
returns the result set. I don't always know the TableSchema that I
need to use when qualifying the table at run-time
Example: The correct table could either be dbo.MyTable or
zzz.MyTable.
I want the user to enter the name of the schema as a parameter of the
procedure at run-time.
|
by: priyamtheone |
last post by:
I'm trying to create a stored procedure in MSSQL Server 2005 that'll perform the following jobs:
1) Create a login.
2) Create an user in TestDB database for the login created in step 1.
3) Assign the role 'db_generaluser' to the user created in step 2.
The login name and password for the login to be created will be supplied from externally through input parameters. If this procedure executes successfully it returns 0 else 1 to the caller...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |