Java PL/SQL 'Execute Immediate'

Question

I am programming Java application on Oracle database. The PL/SQL statement I am using is:

DECLARE
USER_COUNT INTEGER;
BEGIN
  SELECT COUNT(*) INTO USER_COUNT FROM dba_users WHERE username=?;
  IF (USER_COUNT = 0) THEN
    EXECUTE IMMEDIATE 'CREATE USER ? IDENTIFIED BY ?';
    EXECUTE IMMEDIATE 'GRANT CREATE SESSION, CREATE TABLE, CREATE ANY INDEX TO ?';
  ELSE
    raise_application_error(-20101, 'User ? already exists. Please drop it or choose another username.');
  END IF;
END;
/

But I got a lot of 'invalid column index' errors if there are quotes around question marks. For instance:

    EXECUTE IMMEDIATE 'CREATE USER ? IDENTIFIED BY ?';

is not working, but

    EXECUTE IMMEDIATE CREATE USER ? IDENTIFIED BY ?;

is good.

However if I choose to use second form, I got another syntax error:

java.sql.SQLException: ORA-06550: line 6, column 23:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

 ( - + case mod new not null <an identifier>
 <a double-quoted delimited-identifier> <a bind variable>
 continue avg count current exists max min prior sql stddev
 sum variance execute forall merge time timestamp interval
 date <a string literal with character set specification>
 <a number> <a single-quoted SQL string> pipe
 <an alternatively-quoted string literal with character set specification>
 <an alternat

Please advise what to do.

Any reason you don't just run the 3 statements from Java? – Andreas Oct 06 '16 at 07:17 — Andreas, Oct 06 '16 at 07:17

William Robertson · Answer 1 · 2016-10-06T09:37:26.530

If ? is a bind variable, you can't use those in a create user statement. It needs the complete literal text.

btw did you really mean CREATE ANY INDEX? That will let the new user index any table in the entire database. Normally plain CREATE INDEX should be sufficient.

It would better to define one or more roles in advance, and then just grant those to the new user, rather than hardcoding every possible privilege. Should the user be allowed to create views, procedures, types etc? If it's going to be a developer account, should it be able to debug code and define locks? And so on...

(Just out of interest, do you write Java in uppercase, or just PL/SQL?)

score 1 · Accepted Answer · answered Oct 06 '16 at 07:07

1

Try this.

DECLARE
   USER_COUNT   INTEGER;
BEGIN
   SELECT COUNT (*)
     INTO USER_COUNT
     FROM dba_users
    WHERE username =?;
  IF USER_COUNT = 0 THEN

    EXECUTE IMMEDIATE 'CREATE USER  '||?|| 'IDENTIFIED BY  '||?;
    EXECUTE IMMEDIATE 'GRANT CREATE SESSION, CREATE TABLE, CREATE ANY INDEX TO  '|| ?;
  ELSE
    raise_application_error(-20101, 'User ? already exists. Please drop it or choose another username.');
  END IF;
END;
/

answered Oct 06 '16 at 07:07

XING

9,608
4
22
38

You forgot the `?` in the error message. – Andreas Oct 06 '16 at 07:16
I really dont know java ..so I assumed '?' is a bind variable passed it via JAVA frontend. Correct me if am wrong.btw in oracle we use '&; if we want to pass bind variables to anonymous blocks – XING Oct 06 '16 at 07:26

score 0 · Answer 3 · edited May 23 '17 at 11:48

0

You cannot use bind variables when working with DDL in EXECUTE IMMEDIATE.

You can refer @ below link, a better explaination is available.

Create user from string variables in a PL/SQL block

edited May 23 '17 at 11:48

Community

1
1

answered Oct 06 '16 at 07:20

Rakesh Pacharne

81
2
10

Java PL/SQL 'Execute Immediate'

3 Answers3