Home » SQL & PL/SQL » SQL & PL/SQL » Error creating users in Procedure (merged)
Error creating users in Procedure (merged) [message #255155] Mon, 30 July 2007 19:55 Go to next message
brig
Messages: 14
Registered: July 2007
Junior Member
HI all,

I made a procedure (not a very good one I admit, but my first) and it seems to work when run in sqldeveloper and isqlplus. I receive a message stating completed. It reads from a StdTable, accountName, passWord, and rowId. But, I am embarrassed to say, this isn't really creating users. Did I do something wrong? AM I suppose to be calling something else to run this? I put output lines in to check as I go along, but they don't work either. I am just learning and have read until my eyes are blurry. I do program in other languages, but I am missing something here. I checked the DB and the procedure is there, as is the table I am reading from. Any help would be appreciated.
thank you,
brig


SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 30 20:45:08 2007
SQL> SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE MakeStudents
AS
-- declare all variables!
IreTurnCode INT := 0;
InexTrowId INT := 0;
iCurrentRowId INT := 0;
iLoopControl INT := 13;
iRowId INT := 0;
vchaccounName VARCHAR2(7) := '';
vchPasswordnmbr VARCHAR2(9) := '';
BEGIN

SELECT iRowId,
stdAccount,
stdPassword
INTO iCurrentRowId,
vchaccounName,
vchPasswordnmbr
FROM sys.stdTable
WHERE iRowId = iCurrentRowId;

dbms_Output.Put_Line('Accout is '
||vchaccounName);--checking

EXCEPTION
WHEN No_Data_Found THEN
RETURN;
-- start the main processing loop.

WHILE iLoopControl > iCurrentRowId LOOP
BEGIN
EXECUTE IMMEDIATE 'CREATE USER vchACCOUNName
IDENTIFIED BY :=vchPasswordNmbr
DEFAULT TABLESPACE STUDENTTABLESPACE
QUOTA 10M ON example
QUOTA 10M ON system';

-- processing.

SELECT iRowId,
stdAccount,
stdPassword
INTO iCurrentRowId,
vchaccounName,
vchPasswordnmbr
FROM sys.stdTable
WHERE iRowId = iCurrentRowId;
EXCEPTION
WHEN No_Data_Found THEN
RETURN;

dbms_Output.Put_Line('Accout is '
||vchaccounName);--testing

END;--begin in while

END LOOP;--in while

END;--procedure


Procedure created.

SQL> begin
2 makestudents;
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
Re: Procedures says completed but no user created? [message #255189 is a reply to message #255155] Mon, 30 July 2007 23:54 Go to previous messageGo to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Brig,

Your SQL statements doesn't make any sense.

Where are you incrementing iCurrentRowId and iRowId?

How could you assign a 0 to Rowid which is of type char?

Describe the table properly.

what does this irowid mean in your SQL Statement?


SELECT iRowId,
stdAccount,
stdPassword
INTO iCurrentRowId,
vchaccounName,
vchPasswordnmbr
FROM sys.stdTable
WHERE iRowId = iCurrentRowId;


Please clarify the above queries.

Regards
Srini...

[Updated on: Mon, 30 July 2007 23:55]

Report message to a moderator

Re: Procedures says completed but no user created? [message #255213 is a reply to message #255155] Tue, 31 July 2007 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Procedures says completed but no user created? [message #255249 is a reply to message #255213] Tue, 31 July 2007 02:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Also, you seem to have created one of your tables (STD) in the SYS schema.

Don't do this. Ever.
No arguments, just don't. SYS is not a schema to be used for development.

Also also, I don't think you've posted the code that you're using.
After the first SELECT statement, you've got an EXCEPTION statement. There is no END after this exception statement, and there is no BEGIN before it other than the one to mark the start of the procedure.

Re: Procedures says completed but no user created? [message #255267 is a reply to message #255155] Tue, 31 July 2007 04:27 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Just a little demonstration:
SQL> set serveroutput on
SQL> begin
  2    dbms_output.put_line( 'block before' );
  3    raise no_data_found;
  4    dbms_output.put_line( 'block after' );
  5  exception
  6    when no_data_found then
  7      dbms_output.put_line( 'exception before' );
  8      return;
  9      dbms_output.put_line( 'exception after' );
 10  end;
 11  /
block before
exception before

PL/SQL procedure successfully completed.

SQL> 

As all your first SELECT probably ends with no_data_found error (are you aware the WHERE condition is ALWAYS true as iRowId = iCurrentRowId = 0?), and when handling this exception you RETURN immediately (the following code is not executed), so no output is written.
Re: Procedures says completed but no user created? [message #255332 is a reply to message #255155] Tue, 31 July 2007 08:19 Go to previous messageGo to next message
brig
Messages: 14
Registered: July 2007
Junior Member
Wow, you guys sure gave me many things to think about.
I thought making the table under SYS was the right thing to do. Embarassed

I should then use 'system' to make this table?
I had taken out a select statement and changed my where clause and never put back the original. Which had a different variable it was checking against.

I am also not sure 'yet' how to use built in functions (where to find them) from PL/SQL, like exceptions, raise no data etc. I am reading a bit and writing as I go along. Knowing other languages I "assumed" I could follow along with the logic. I will rewrite this code and read more.
Thank you so much..
Smile
brig


Re: Procedures says completed but no user created? [message #255335 is a reply to message #255155] Tue, 31 July 2007 08:28 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
NO not system.

You should create your own schema under which to define your objects.

Re: Procedures says completed but no user created? [message #255339 is a reply to message #255332] Tue, 31 July 2007 08:36 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
from the manuals

SYS

When any database is created, the user SYS is automatically created and granted the DBA role.

All of the base tables and views for the database's data dictionary are stored in the schema SYS. These base tables and views are critical for the operation of Oracle. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by Oracle. They should never be modified by any user or database administrator, and no one should create any tables in the schema of user SYS. (However, you can change the storage parameters of the data dictionary settings if necessary.)

Ensure that most database users are never able to connect using the SYS account.

SYSTEM

When a database is created, the user SYSTEM is also automatically created and granted the DBA role.

The SYSTEM username is used to create additional tables and views that display administrative information, and internal tables and views used by various Oracle options and tools. Never create in the SYSTEM schema tables of interest to individual users.


What you do is: create your own user and use that for your development.

MHE
Error creating users in Procedure. Duplicate names. [message #256250 is a reply to message #255155] Fri, 03 August 2007 10:20 Go to previous messageGo to next message
brig
Messages: 14
Registered: July 2007
Junior Member
Hi All,

I have a Table (STDTable) with 3 columns: STDAccount, STDpassword, IRowId.
I am getting a user conflicts error when I 'get the next' user info from the table. I am using SQLDeveloper.

Below is the procedure and the error that follows. I understand I should not be creating users in the sys schema, but I am still learning and don't know how to make a new schema to put these users into. So for now, this is what I am doing. I changed this procedure from one I had, this time using a cursor.
THANK YOU for any help or guidance.
brig


CREATE OR REPLACE PROCEDURE MakeNewStudents
IS

--Declare, don't select rows yet

CURSOR stdCurSor IS
SELECT iRowId,
stdAccount,
stdPassword
FROM sys.stdTable;
iLoopCount INT := 1;
InexTrowId INT := 1;
iCurrentRowId INT := 0;
iRowCount INT := 0;
iRowNo INT := 0;
vchaccounName VARCHAR2(7) := '';
vchPasswordnmbr VARCHAR2(9) := '';

BEGIN
OPEN stdCurSor;

LOOP
FETCH stdCurSor INTO iRowNo,
vchaccounName,
vchPasswordnmbr;

dbms_Output.Put_Line('Is ther an Name ? '
||vchaccounName);--testing

EXIT WHEN stdCurSor%NOTFOUND;

IF iRowNo = InexTrowId THEN
InexTrowId := InexTrowId + 1;

EXECUTE IMMEDIATE 'CREATE USER vchACCOUNName
IDENTIFIED BY vchPasswordNmbr
DEFAULT TABLESPACE STUDENTTABLESPACE
QUOTA 10M ON example
QUOTA 10M ON system';
END IF;

iRowCount := stdCurSor%ROWCOUNT;

dbms_Output.Put_Line('Number of Rows '
||iRowCount); --testing

END LOOP;--in FOR

CLOSE stdCurSor;
END;--procedure

Connecting to the database Brig.
ORA-01920: user name 'VCHACCOUNNAME' conflicts with another user or role name
ORA-06512: at "SYS.MAKENEWSTUDENTS", line 26
ORA-06512: at line 2
Is ther an Name ? ada6464
Number of Rows1
Is ther an Name ? hol3743
Process exited.
Disconnecting from the database Brig.
Re: Error creating users in Procedure. Duplicate names. [message #256254 is a reply to message #256250] Fri, 03 August 2007 10:26 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
What patr of "ORA-01920: user name 'VCHACCOUNNAME' conflicts with another user or role name" do you NOT understand.
You'll need to use a different name every time you run your code.
Re: Error creating users in Procedure. Duplicate names. [message #256256 is a reply to message #256250] Fri, 03 August 2007 10:32 Go to previous messageGo to next message
brig
Messages: 14
Registered: July 2007
Junior Member
Thanks, I got what the error means,
But I am using a variable. Am I using it incorrectly??
Re: Error creating users in Procedure. Duplicate names. [message #256258 is a reply to message #256250] Fri, 03 August 2007 10:35 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
Use code formatting (see http://www.orafaq.com/forum/t/59964/74940/)
>But I am using a variable
I disagree.
Oracle reports ORA-01920 when you try to create a user which already exists.
Re: Error creating users in Procedure. Duplicate names. [message #256259 is a reply to message #256256] Fri, 03 August 2007 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First,
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Then,
EXECUTE IMMEDIATE 'CREATE USER '||vchACCOUNName||' 
IDENTIFIED BY '||vchPasswordNmbr||' 
DEFAULT TABLESPACE STUDENTTABLESPACE 
QUOTA 10M ON example 
QUOTA 10M ON system';

NEVER grant quota on SYSTEM tablespace.
On contrary you can grant quota on his default tablespace, maybe.

Regards
Michel
Re: Error creating users in Procedure. Duplicate names. [message #256263 is a reply to message #256259] Fri, 03 August 2007 10:47 Go to previous messageGo to next message
brig
Messages: 14
Registered: July 2007
Junior Member
(I did read the posts and always format my code with your formatter)

Thank you so much, that worked. But now I get this error.
(My passwords in the table are in this format 999991234)

Connecting to the database Brig.
ORA-00988: missing or invalid password(s)
ORA-06512: at "SYS.MAKENEWSTUDENTS", line 33
ORA-06512: at line 2
Is there a Name ? ada6464
Process exited.
Disconnecting from the database Brig
Re: Error creating users in Procedure. Duplicate names. [message #256264 is a reply to message #256250] Fri, 03 August 2007 10:51 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
Why do you expect us to debug CODE that we can NOT see?
You're On Your Own (YOYO)!

Use CUT & PASTE of whole session!

[Updated on: Fri, 03 August 2007 10:52] by Moderator

Report message to a moderator

Re: Error creating users in Procedure. Duplicate names. [message #256265 is a reply to message #256263] Fri, 03 August 2007 10:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
(I did read the posts and always format my code with your formatter)

You didn't read well otherwise you'd see there is code tags to post code in a pretty way as I did, as many did in many posts.

Post what Ana asked, with line numbers, we can't count.

Regards
Michel
Re: Error creating users in Procedure. Duplicate names. [message #256270 is a reply to message #256250] Fri, 03 August 2007 11:08 Go to previous messageGo to next message
brig
Messages: 14
Registered: July 2007
Junior Member
I am trying the format again.
This is the error.
Thank you for being patient.
brig
Here is the error.

Connecting to the database Brig.
ORA-00988: missing or invalid password(s)
ORA-06512: at "SYS.MAKENEWSTUDENTS", line 28
ORA-06512: at line 2
Is there a Name ? ada6464
Process exited.
Disconnecting from the database Brig.



CREATE OR REPLACE PROCEDURE MakeNewstuDents
IS
--Declare, don't select
  CURSOR stdCurSor IS 
    SELECT iRowId,
           stdAccount,
           stdPassword
    FROM   sys.stdTable;
  iLoopCount       INT := 1;
  InexTrowId       INT := 1;
  iCurrentRowId    INT := 0;
  iRowCount        INT := 0;
  iRowNo           INT := 0;
  vchaccounName    VARCHAR2(7) := '';
  vchPasswordnmbr  VARCHAR2(9) := '';
BEGIN
  OPEN stdCurSor;
  
  LOOP
    FETCH stdCurSor INTO iRowNo,
    vchaccounName,
    vchPasswordnmbr;
    
    dbms_Output.Put_Line('Is there a Name ? '
                         ||vchaccounName);
    
    EXIT WHEN stdCurSor%NOTFOUND;
    
    IF iRowNo = InexTrowId THEN
      InexTrowId := InexTrowId + 1;
      
      EXECUTE IMMEDIATE 'CREATE USER '
                        ||vchaccounName
                        ||' 
        IDENTIFIED BY '
                        ||vchPasswordnmbr
                        ||' 
        DEFAULT TABLESPACE STUDENTTABLESPACE 
    --    QUOTA 10M ON example 
        QUOTA 10M ON STUDENTTABLESPACE';
    END IF;
    
    iRowCount := stdCurSor%ROWCOUNT;
    
    dbms_Output.Put_Line('Number of Rows'
                         ||iRowCount);
  END LOOP;--in FOR  
  
  CLOSE stdCurSor;
END;--procedure
Re: Error creating users in Procedure. Duplicate names. [message #256272 is a reply to message #256250] Fri, 03 August 2007 11:12 Go to previous messageGo to next message
brig
Messages: 14
Registered: July 2007
Junior Member
Line 28 is the Line before the EXECUTE IMMEDIATE statement
Re: Error creating users in Procedure. Duplicate names. [message #256273 is a reply to message #256250] Fri, 03 August 2007 11:13 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
1) Post what Ana asked, with line numbers, we can't count.
Error? what error? I don't see any error.

Since you seem insistant on (ab)using EXECUTE IMMEDIATE, you should construct the SQL statement in a string variable & print it out before trying to EXECUTE IMMEDIATE.
When it fails, take the string via CUT & PASTE into SQL*Plus and run it to allow SQL*Plus to identify exactly where the error is.

[Updated on: Fri, 03 August 2007 11:21] by Moderator

Report message to a moderator

Re: Error creating users in Procedure. Duplicate names. [message #256275 is a reply to message #256250] Fri, 03 August 2007 11:21 Go to previous messageGo to next message
brig
Messages: 14
Registered: July 2007
Junior Member
I posted the code and the error. I forgot to post the line numbers so I posted the line number after.
Per the format of this forum it would not let me enter the line
numbers.

Why are you being so nasty? I would have loved to have been there when you were first learning. This is the newbie forum isn't it? I am trying to cooperate with all the rules. If you can not help me or have no encouraging words, then don't reply.
Re: Error creating users in Procedure. Duplicate names. [message #256278 is a reply to message #256250] Fri, 03 August 2007 11:24 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
 sqlplus

SQL*Plus: Release 10.2.0.2.0 - Production on Fri Aug 3 09:23:16 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Enter user-name: username/password

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE OR REPLACE PROCEDURE MakeNewstuDents
IS
  2    3  --Declare, don't select
  4    CURSOR stdCurSor IS 
  5      SELECT iRowId,
  6             stdAccount,
  7             stdPassword
  8      FROM   sys.stdTable;
  9    iLoopCount       INT := 1;
 10    InexTrowId       INT := 1;
 11    iCurrentRowId    INT := 0;
 12    iRowCount        INT := 0;
 13    iRowNo           INT := 0;
 14    vchaccounName    VARCHAR2(7) := '';
 15    vchPasswordnmbr  VARCHAR2(9) := '';
 16  BEGIN
 17    OPEN stdCurSor;
 18    
 19    LOOP
 20      FETCH stdCurSor INTO iRowNo,
 21      vchaccounName,
 22      vchPasswordnmbr;
 23      
 24      dbms_Output.Put_Line('Is there a Name ? '
 25                           ||vchaccounName);
 26      
 27      EXIT WHEN stdCurSor%NOTFOUND;
 28      
 29      IF iRowNo = InexTrowId THEN
 30        InexTrowId := InexTrowId + 1;
 31        
 32        EXECUTE IMMEDIATE 'CREATE USER '
 33                          ||vchaccounName
 34                          ||' 
 35          IDENTIFIED BY '
 36                          ||vchPasswordnmbr
 37                          ||' 
 38          DEFAULT TABLESPACE STUDENTTABLESPACE 
 39      --    QUOTA 10M ON example 
 40          QUOTA 10M ON STUDENTTABLESPACE';
 41      END IF;
 42      
 43      iRowCount := stdCurSor%ROWCOUNT;
 44      
 45      dbms_Output.Put_Line('Number of Rows'
 46                           ||iRowCount);
 47    END LOOP;--in FOR  
 48    
 49    CLOSE stdCurSor;
 50  END;--procedure
 


Why is this so difficult?

If you are dissatisfied with the timeliness, quality, tone of any response here, you are entitled to a full & complete refund.

[Updated on: Fri, 03 August 2007 11:43] by Moderator

Report message to a moderator

Re: Error creating users in Procedure. Duplicate names. [message #256281 is a reply to message #256272] Fri, 03 August 2007 11:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think the message is quite clear: "ORA-00988: missing or invalid password(s)".
You didn't set the password variable: it is NULL.

Regards
Michel
icon7.gif  Re: Procedures says completed but no user created? [message #256343 is a reply to message #255155] Fri, 03 August 2007 22:10 Go to previous messageGo to next message
brig
Messages: 14
Registered: July 2007
Junior Member
Thank you so much. You have been so helpful.
Re: Error creating users in Procedure. Duplicate names. [message #256345 is a reply to message #256250] Fri, 03 August 2007 22:14 Go to previous message
brig
Messages: 14
Registered: July 2007
Junior Member
Hi,

The password wasn't Null. However, it started with a number (the default way we do it at work, read in from a file). I imported that file when I created the table. I changed the file and redid the table and it all worked GREAT!!!! All users have been created.

Thank you for your help. I know its frustrating on your end.
brig
Previous Topic: Why this random drawing doesn't do the thing intended
Next Topic: list partition table
Goto Forum:
  


Current Time: Fri Dec 09 09:57:32 CST 2016

Total time taken to generate the page: 0.08724 seconds