[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Help! PL/SQL Question

Posted on 2000-08-03
15
Medium Priority
?
1,039 Views
Last Modified: 2012-05-05
Hello All
I'm trying to drop users using the DBMS_SQL package but somehow my scripts nevers runs through. It just prints out the first user and then exits.
 Here's the script

thanks
Sunit sjoshi@ingr.com
CREATE OR REPLACE PROCEDURE DropUser(strUserName IN VARCHAR2 DEFAULT NULL)
IS
 CURSOR curGetUsers
 IS
 SELECT username from all_users
 WHERE username like '%_' || UPPER(strUserName) ;

 recGetUsers curGetUsers%ROWTYPE;

 expNoUserName EXCEPTION;
 --cidSelect INTEGER;                        --Store    
 SelectUser Cursor ID
 cidDrop INTEGER;                              --Store  
 DropUser Cursor ID
 --sqlSelect VARCHAR2(100)            --Store Select String
 sqlDrop VARCHAR2(50);            --Store the Drop SQL String
 usersDropped INTEGER;            --Store the Result of the Execute

BEGIN
      IF strUserName IS NULL THEN
            RAISE expNoUserName;
      END IF;

      IF NOT curGetUsers%ISOPEN
      THEN
            OPEN curGetUsers;
      END IF;

      LOOP
         FETCH curGetUsers INTO recGetUsers;
       DBMS_OUTPUT.PUT_LINE('User dropped: ' ||  
                              recGetUsers.username);
      EXIT WHEN curGetUsers%NOTFOUND;
      
      cidDrop := DBMS_SQL.OPEN_CURSOR;
      sqlDrop := 'DROP user :userName cascade;';

      DBMS_SQL.PARSE(cidDrop, sqlDrop, DBMS_SQL.V7);
      DBMS_OUTPUT.PUT_LINE(' SQL: ' || sqlDrop);
      DBMS_SQL.BIND_VARIABLE(cidDrop, ':userName',
                               recGetUsers.username);

      usersDropped := DBMS_SQL.EXECUTE(cidDrop);
      DBMS_SQL.CLOSE_CURSOR(cidDrop);

 END LOOP;
 CLOSE curGetUsers;

 EXCEPTION
  WHEN expNoUserName THEN
      DBMS_OUTPUT.PUT_LINE ('ERROR: Provide a project name  
                              like exec dropUser("proj1")');
 WHEN OTHERS THEN
       CLOSE curGetUsers;
      DBMS_SQL.CLOSE_CURSOR(cidDrop);
END;
0
Comment
Question by:smjoshi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 1

Expert Comment

by:nitinpai
ID: 3761119
This statement is wrong:
sqlDrop := 'DROP user :userName cascade;';
u don't have to provide a semicolon. Correct it to
sqlDrop := 'DROP user :userName cascade';

Also, never let the when others exception leave without knowing what the error was. U can always display the error messages and numbers, they will be useful for debugging.
0
 

Expert Comment

by:skandalam
ID: 3766420
As nitinpai inicated, ";" is the culprit. Also it is suggested that to write " user dropped output message " after
usersDropped := DBMS_SQL.EXECUTE(cidDrop);

statement. Actually it is not dropping even a single user. The placement of message is misleading.
0
 

Expert Comment

by:muthu_ch
ID: 3771602
Hi,
in sqldrop  don't use : and don't use sql_bind also.

Instead you try like this
 
sqlDrop:='DROP user '||userName||' cascade;';

I hope it will work.

Regards,
Muthu

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:smjoshi
ID: 3774078
Hi Guys
 Tried what you all said, i.e taking off the ';'. So now it is
sqlDrop := 'DROP user :userName cascade';
 The error message that SQLCODE and SQLERRM yield are
ERROR#: -1935
DESC: ORA-01935: missing user or role name
 I'm logged in as system/manager...any ideas what I'm doing wrong here.

thanks
Sunit
0
 

Author Comment

by:smjoshi
ID: 3775332
Hi Guys
 Tried what you all said, i.e taking off the ';'. So now it is
sqlDrop := 'DROP user :userName cascade';
 The error message that SQLCODE and SQLERRM yield are
ERROR#: -1935
DESC: ORA-01935: missing user or role name
 I'm logged in as system/manager...any ideas what I'm doing wrong here.

thanks
Sunit
0
 

Author Comment

by:smjoshi
ID: 3776757
Hi Guys
 Tried what you all said, i.e taking off the ';'. So now it is
sqlDrop := 'DROP user :userName cascade';
 The error message that SQLCODE and SQLERRM yield are
ERROR#: -1935
DESC: ORA-01935: missing user or role name
 I'm logged in as system/manager...any ideas what I'm doing wrong here.

thanks
Sunit
0
 
LVL 1

Expert Comment

by:nitinpai
ID: 3776956
When I do this at SQL prompt:
SQL> drop user ;
drop user
         *
ERROR at line 1:
ORA-01935: missing user or role name


SQL>

I receive the error that u have received. Which means that the sql statement that is being constructed is invalid.
0
 

Author Comment

by:smjoshi
ID: 3777677
I Found out that it 'excepts' out right after the Parse statement. Here's the new script and the error messages

Before SQL: DROP user :userName cascade
ERROR#: -1935
DESC: ORA-01935: missing user or role name

CREATE OR REPLACE PROCEDURE DropUser(strUserName IN VARCHAR2 DEFAULT NULL)
IS
      CURSOR curGetUsers
      IS
            SELECT username from all_users
            WHERE username like '%_' || UPPER(strUserName) ;

      recGetUsers curGetUsers%ROWTYPE;

      expNoUserName EXCEPTION;
      error_code       NUMBER;
      error_msg VARCHAR2(250);

      cidDrop INTEGER;                              --Store DropUser Cursor ID
      sqlDrop VARCHAR2(50);            --Store the Drop SQL String
      usersDropped INTEGER;            --Store the Result of the Execute

BEGIN
      IF strUserName IS NULL THEN
            RAISE expNoUserName;
      END IF;

      IF NOT curGetUsers%ISOPEN
      THEN
            OPEN curGetUsers;
      END IF;

      LOOP
            --select username from all_users where username like '%_MYTEST';
            FETCH curGetUsers INTO recGetUsers;
            EXIT WHEN curGetUsers%NOTFOUND;
            
            cidDrop := DBMS_SQL.OPEN_CURSOR;
            sqlDrop := 'DROP user :userName cascade';
            DBMS_OUTPUT.PUT_LINE('Before SQL: ' || sqlDrop);

            DBMS_SQL.PARSE(cidDrop, sqlDrop, DBMS_SQL.V7);
            DBMS_OUTPUT.PUT_LINE(' After SQL: ' || sqlDrop);
            
            DBMS_SQL.BIND_VARIABLE(cidDrop, ':userName', recGetUsers.username);

            usersDropped := DBMS_SQL.EXECUTE(cidDrop);
            DBMS_OUTPUT.PUT_LINE('User dropped: ' || recGetUsers.username);

            DBMS_SQL.CLOSE_CURSOR(cidDrop);

      END LOOP;
      CLOSE curGetUsers;

      EXCEPTION
            WHEN expNoUserName THEN
                  DBMS_OUTPUT.PUT_LINE ('ERROR: Provide a project name like exec dropUser("proj1")');
            WHEN OTHERS THEN
                  error_code := SQLCODE;
                  error_msg := SQLERRM;
                  DBMS_OUTPUT.PUT_LINE('ERROR#: ' || error_code);
                  DBMS_OUTPUT.PUT_LINE('DESC: ' || error_msg);
                  CLOSE curGetUsers;
                  DBMS_SQL.CLOSE_CURSOR(cidDrop);
END;
0
 
LVL 1

Expert Comment

by:nitinpai
ID: 3778812
instead of:

DBMS_OUTPUT.PUT_LINE('Before SQL: ' || sqlDrop);

                      DBMS_SQL.PARSE(cidDrop, sqlDrop, DBMS_SQL.V7);
                      DBMS_OUTPUT.PUT_LINE(' After SQL: ' || sqlDrop);

                      DBMS_SQL.BIND_VARIABLE(cidDrop, ':userName', recGetUsers.username);

try this:

DBMS_OUTPUT.PUT_LINE('Before SQL: ' || sqlDrop);
                      DBMS_SQL.BIND_VARIABLE(cidDrop, ':userName', recGetUsers.username);
                      DBMS_SQL.PARSE(cidDrop, sqlDrop, DBMS_SQL.V7);
                      DBMS_OUTPUT.PUT_LINE(' After SQL: ' || sqlDrop);
that is u have to bind before parsing.
0
 
LVL 1

Expert Comment

by:nitinpai
ID: 3778932
Oops... I take it back.
0
 
LVL 1

Accepted Solution

by:
nitinpai earned 150 total points
ID: 3779246
This is what will get ur job done:
                      cidDrop := DBMS_SQL.OPEN_CURSOR;
                      sqlDrop := 'DROP user '||recGetUsers.username||' cascade';
                      DBMS_OUTPUT.PUT_LINE('Before SQL: ' || sqlDrop);
                      DBMS_SQL.PARSE(cidDrop, sqlDrop,DBMS_SQL.V7);

0
 
LVL 1

Expert Comment

by:nitinpai
ID: 3779253
No need to bind...
0
 

Author Comment

by:smjoshi
ID: 3781492
Comment accepted as answer
0
 

Author Comment

by:smjoshi
ID: 3781493
Thanks nitin...that worked although I still can't understand why the bind one didn't. Also the statement
usersDropped := DBMS_SQL.EXECUTE(cidDrop);
evaluates to 0, any ideas why.

Sunit
0
 
LVL 1

Expert Comment

by:nitinpai
ID: 3781633
DBMS_SQL.EXECUTE:
This function executes a given cursor. This function accepts the ID number of the cursor and returns the number of rows processed. The return
value is only valid for INSERT, UPDATE, and DELETE statements; for other types of statements, including DDL, the return value is undefined and
should be ignored.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question