CODEWITHSUNDEEP
sqloracle-sqldeveloper
Tyll'a
Tyll'a

Reputation: 551

Oracle SQL Developer: possible to pass parameters to SQL script?

I have the following SQL script (all names replaced with metasyntactic variables):

DELETE FROM FOO
WHERE UPPER(BAR)=? AND UPPER(BAZ)=? AND UPPER(QUX)=? AND UPPER(QUUX)=? AND UPPER(CORGE)=?;

When I run it in SQL Developer, I get the following error, as expected: SQL Error: Missing IN or OUT parameter at index:: 1

Is there a way in SQL Developer to pass test parameters to these values (in this case, BAR, BAZ, QUX, QUUX, and CORGE) or do I have to manually replace the question marks? Thanks!

UPDATE: I've been looking on the internet to try to find the answer with no luck; none of the articles I found deal with the FOO=? syntax. I know that that is the proper syntax to access (and in this case, delete from) the database from a Java program, but I would like to test the SQL script before embedding it in the program.

Upvotes: 3

Views: 5610

Answers (1)

Jeroen
Jeroen

Reputation: 1638

A query that way is called a prepared statement. It is used as a way to send SQL statements from a program to the database.

First you have to prepare the prepared statement object and later you supply the parameters and finally execute the statement. When setting the parameters, you refer to each question mark in the order they apear in the statement. So in you case, BAR would be parameter 1 and CORGE will be parameter 5.

http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

For testing a query like this is quite useless in my experience. You could change the paramters to variables or even substitution variables if that is more convenient while testing.

http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch5.htm#i1211130

Upvotes: 1

Related Questions