DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: ORA-06502: PL/SQL: numeric or value error: character to number conversion error

  1. #1
    Join Date
    Oct 2002
    Location
    london
    Posts
    41

    ORA-06502: PL/SQL: numeric or value error: character to number conversion error

    Im a bit stumped when getting this error with the following procedure: Any ideas?

    ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at "SYS.P_REPLACE_SCHEMA", line 10
    ORA-06512: at line 1

    procedure P_REPLACE_SCHEMA
    as
    UserCount number(1);
    UserName dba_users.username%TYPE;
    cid integer;
    userCheckSql varchar2(2000);
    BEGIN
    UserName := upper('girs');
    userCheckSql := 'SELECT NVL(count(1),0) FROM dba_users WHERE username = :s' ;
    UserCount := 'EXECUTE IMMEDIATE userCheckSql USING UserName';
    IF (UserCount = 1)
    THEN
    cid := dbms_sql.open_cursor;
    dbms_sql.parse(cid, 'DROP user girs cascade' , dbms_sql.native);
    dbms_sql.close_cursor(cid);
    dbms_output.put_line('User girs dropped');
    ELSE
    dbms_output.put_line('No user girs to drop');
    END IF;
    END;

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You are trying to set UserCount, a number, to the string "EXECUTE IMMEDIATE userCheckSql USING UserName", which will not convert to a number for obvious reasons
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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