[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1045
  • Last Modified:

Help! PL/SQL Question

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
smjoshi
Asked:
smjoshi
1 Solution
 
nitinpaiCommented:
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
 
skandalamCommented:
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
 
muthu_chCommented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
smjoshiAuthor Commented:
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
 
smjoshiAuthor Commented:
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
 
smjoshiAuthor Commented:
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
 
nitinpaiCommented:
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
 
smjoshiAuthor Commented:
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
 
nitinpaiCommented:
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
 
nitinpaiCommented:
Oops... I take it back.
0
 
nitinpaiCommented:
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
 
nitinpaiCommented:
No need to bind...
0
 
smjoshiAuthor Commented:
Comment accepted as answer
0
 
smjoshiAuthor Commented:
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
 
nitinpaiCommented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now