how to solve write data definition operations in java?

Question

I am trying to execute the query below:

    String sql="create or replace view toprecords as select * from(select directory_name,file_name,sum(value) from tfidfvalues where directory_name =? group by(directory_name,file_name) order by(sum(value)) desc) where rownum<=10";
                        stmt = con.prepareStatement(sql);
                        stmt.setString(1, directory_names.get(x));

                        stmt.executeUpdate();

When I execute it in oracle it works fine but when I try to implement it in java it shows the following error:

    java.sql.SQLException: ORA-01027: bind variables not allowed for data definition operations

SO how to overcome this exception?

*"When I execute it in oracle it works fine"* ... how, exactly, are you using a prepared statement "in oracle" ? — Brian Roach, Jan 06 '14 at 20:07
the error is pretty clear, you can't bind variables in `DDL` operations — nachokk, Jan 06 '14 at 20:07
@BrianRoach In oracle means the query works fine but shows error when embeded in prepare statement — Subodh Mankar, Jan 08 '14 at 14:59
[Why cannot I use bind variables in DDL/SCL statements in dynamic SQL?](http://stackoverflow.com/q/25489002/1461424) — sampathsris, Jul 15 '15 at 03:37

score 0 · Accepted Answer · answered Jan 06 '14 at 20:08

0

The message says it all, you can't use bind variables in DDL operations.

What you will have to do is create your view without the where clause, and instead apply the filtering when you query your view.

answered Jan 06 '14 at 20:08

Keppil

45,603
8
97
119

Or just modify the SQL String in Java. Though that can lead to a bigger problem if the input is untrusted, obviously. – Brian Roach Jan 06 '14 at 20:10
@Brian: True, but then you are also just hacking your way around the fundamental issue with the approach. – Keppil Jan 06 '14 at 20:11
@Keppil I dint exactly get what you are trying to say. Can you please elaborate how should I modify my query – Subodh Mankar Jan 06 '14 at 20:24
@Subodh: I assume you are going to query the view you are creating here later on. So, instead of using the `where` clause when creating the view, you use it later when querying your view. This will give you the same result. – Keppil Jan 06 '14 at 20:27
@Keppil wouldn't that make the view possibly very large? (which may be undesirable if this action is run very frequently...) – user1766760 Jan 06 '14 at 21:58

how to solve write data definition operations in java?

1 Answers1