Solved

Help! PL/SQL Question

Posted on 2000-08-03
15
1,001 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
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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 50 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

759 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now