Problem with DBMS_SQL.PARSE
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Problem with DBMS_SQL.PARSE

  1. #1
    Join Date
    Aug 2000
    Posts
    143
    I am trying to use the following procedure for dropping users from the database, the procedure compiles ok but does not seem to work.
    create or replace Procedure test (username IN varchar2)
    is

    cursor_id integer;
    sql_string varchar2(200);
    BEGIN
    cursor_id := DBMS_SQL.OPEN_CURSOR;
    sql_string := 'drop user ' | | username | | ' cascade' ;

    dbms_output.put_line(sql_string);

    DBMS_SQL.PARSE(cursor_id, sql_string , DBMS_SQL.NATIVE);

    dbms_output.put_line('user '| | username | | 'dropped');
    DBMS_SQL.CLOSE_CURSOR (cursor_id);

    EXCEPTION
    WHEN OTHERS
    THEN
    dbms_sql.CLOSE_CURSOR (cursor_id);
    dbms_output.put_line('an error occured');
    END ;
    /
    show errors
    /


  2. #2
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    I don't use DBMS_SQL much, but I would have thought you need to call DBMS_SQL.EXECUTE to actually run the SQL. PARSE will just parse it.

    Of course, if you've got Oracle 8i you can do the whole thing much more simply using native dynamic sql. You just need:

    execute immediate 'drop user xxxx cascade';

  3. #3
    Join Date
    Aug 2000
    Posts
    143
    Using the native dynamic sql is it possible to pass a parameter into the string?

    ie. execute immediate 'drop user $user cascade';



  4. #4
    Join Date
    Apr 2001
    Posts
    118
    nealh, when using DBMS_SQL, a DDL statement will get executed at parse time. There is no need to call DBMS_SQL.EXECUTE for DDL.

    Heath

  5. #5
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    As I said, I don't use dbms_sql much nowadays. It is possible to pass a parameter to the NDS SQL statement:

    execute immediate
    'drop user '||user_name||' cascade;'

  6. #6
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    The original procedure gave an "insufficient privileges" error when I tried to run it under the SYSTEM user. However, when I added an "authid current_user", it worked:

    create or replace Procedure test (username IN varchar2) authid current_user
    is
    cursor_id integer;
    sql_string varchar2(200);
    BEGIN
    cursor_id := DBMS_SQL.OPEN_CURSOR;
    sql_string := 'drop user ' || username || ' cascade' ;
    dbms_output.put_line(sql_string);
    DBMS_SQL.PARSE(cursor_id, sql_string , DBMS_SQL.NATIVE);
    dbms_output.put_line('user '|| username || 'dropped');
    DBMS_SQL.CLOSE_CURSOR (cursor_id);
    END ;

    You may not have seen the error message if you hadn't done a "set serveroutput on" when it was run.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width