[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1053
  • 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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