DBMS_SQL.PARSE PROBLEM IN STORED PROCEDURE
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: DBMS_SQL.PARSE PROBLEM IN STORED PROCEDURE

  1. #1
    Join Date
    Sep 2002
    Posts
    4

    Unhappy

    Hi,
    I have a stored procedure that use the dbms_sql package in order to create or alter database users from within Forms Designer version 4.5.
    This is like :
    ----
    PROCEDURE CHANGE_PASSWORD IS
    CURSOR_NAME INTEGER;
    RET INTEGER;
    BEGIN
    CURSOR_NAME := DBMS_SQL.OPEN_CURSOR;
    dbms_sql.parse(CURSOR_NAME,'ALTER USER joseph IDENTIFIED BY "JOSEPH"',DBMS_SQL.V7);
    RET := DBMS_SQL.EXECUTE(CURSOR_NAME);
    END;
    ----
    When I call the stored procedure from within the forms Designer 4.5 the execution of the above block is interrupted in the "dbms_sql.parse..." statement.The last statement "DBMS_SQL.EXECUTE(CURSOR_NAME)" is not executed and the password didn't change.
    No error message or error code is generated.
    N.B.:If I call the procedure from within SQL*PLUS ,the procedure work very well.
    Please help to resolve this problem?
    Thanks a lot.

  2. #2
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Try this:
    PROCEDURE CHANGE_PASSWORD IS
    CURSOR_NAME INTEGER;
    RET INTEGER;
    BEGIN
    EXECUTE IMMEDIATE 'ALTER USER joseph IDENTIFIED BY ''JOSEPH'' ';
    END;
    Cheers!
    Cheers!
    OraKid.

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Originally posted by balajiyes
    EXECUTE IMMEDIATE 'ALTER USER joseph IDENTIFIED BY ''JOSEPH'' ';
    Probably it doesn't work because: ... BY "JOSEPH"',DBMS_SQL.V7);
    This is Oracle 7.x or 8.0 and EXECUTE IMMEDIATE ... doesn't support on its.

    maybe u can use :... BY "JOSEPH"',DBMS_SQL.NATIVE);
    as more flexible solution for ur situation.



  4. #4
    Join Date
    Sep 2002
    Posts
    4

    DBMS_SQL.NATIVE doesn't work

    I tried to use DBMS_SQL.NATIVE but it didn't work,the statement can't be parsed,also the EXECUTE IMMEDIATE didn't work . Can I have other suggestions?
    Thanks a lot.

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