CODEWITHSUNDEEP
oracle-database
JKStack
JKStack

Reputation: 210

Use Parameter with no need of creating stored procedures

Is it possible to use or add parameters on a simple query without the need of creating stored procedures or function? Are Bind Variables possible without creating a stored procedures?

Upvotes: 1

Views: 80

Answers (2)

tgkprog
tgkprog

Reputation: 4598

where are you running the code from? If your running from a language like Java / VB you would use a stored procedure or prepared statement.

If using from Sql *Plus terminal or other Sql UI

SQL> variable deptno number 
SQL> exec :deptno := 10 
SQL> select * from
    emp where deptno = :deptno;

From a high level language like Java or VB use stored procedures, following is from the article you linked to so not sure why you are asking this?

In fact, the answer to this is actually quite simple. When you put together an SQL statement using Java, or VB, or whatever, you usually use an API for accessing the database; ADO in the case of VB, JDBC in the case of Java. All of these APIs have built-in support for bind variables, and it's just a case of using this support rather than just concatenating a string yourself and submitting it to the database.

For example, Java has PreparedStatement, which allows the use of bind variables, and Statement, which uses the string concatenation approach. If you use the method that supports bind variables, the API itself passes the bind variable value to Oracle at runtime, and you just submit your SQL statement as normal. There's no need to separately pass the bind variable value to Oracle, and actually no additional work on your part. Support for bind variables isn't just limited to Oracle - it's common to other RDBMS platforms such as Microsoft SQL Server, so there's no excuse for not using them just because they might be an Oracle-only feature.

Not sure why you are asking as this information is there on the site http://www.akadia.com/services/ora_bind_variables.html

Upvotes: 2

Gab
Gab

Reputation: 8323

Well, assuming you will perform queries using an external programming language, prepared statements will make the job.

See http://en.wikipedia.org/wiki/Prepared_statement

Upvotes: 1

Related Questions