Stack Overflow
Stack Overflow

Questions tagged [bind-variables]

A bind variable is an unspecified value (in other words, a placeholder) in a prepared database statement/command. The actual values of the bind variable is to be assigned when the statement or command is executed.

147 questions
91
votes
7 answers

MySQL parameterized queries

I am having a hard time using the MySQLdb module to insert information into my database. I need to insert 6 variables into the table. cursor.execute (""" INSERT INTO Songs (SongName, SongArtist, SongAlbum, SongGenre, SongLength,…
Specto
  • 8,271
  • 5
  • 23
  • 20
63
votes
8 answers

What does the colon sign ":" do in a SQL query?

What does : stand for in a query? INSERT INTO MyTable (ID) VALUES (:myId) How does it fetch the desired value? Edit: Also what is that sign called? I wanted to search on google, but what's the name for :?
Jla
  • 11,304
  • 14
  • 61
  • 84
24
votes
2 answers

Python cx_Oracle bind variables

I am a Python newbie, I am having troubles in the use of bind variables. If I execute the code below everything works fine. bind= {"var" : "ciao"} sql = "select * from sometable where somefield =…
Giovanni De Ciantis
  • 357
  • 1
  • 2
  • 11
11
votes
3 answers

Oracle OCI, bind variables, and queries like ID IN (1, 2, 3)

Succinct Version: I'm looking for a C++ OCI adaptation of the following Java technique, where code is able to bind an array of numbers (the array size can vary) into a non-PL/SQL SELECT statement and then use the resulting array in a WHERE ID IN…
StilesCrisis
  • 15,972
  • 4
  • 39
  • 62
8
votes
5 answers

How to inline a variable in PL/SQL?

The Situation I have some trouble with my query execution plan for a medium-sized query over a large amount of data in Oracle 11.2.0.2.0. In order to speed things up, I introduced a range filter that does roughly something like this: PROCEDURE…
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
8
votes
2 answers

Oracle - using bind variable in LIKE clause of dynamic cursor

I am using dynamic cursor for fetching data. Query that is being executed looks similar to: query := 'SELECT column1, column2 FROM my_table WHERE column1 LIKE ''%:bv1%'''; And the cursor itself is executed like this: OPEN…
Peter Gubik
  • 347
  • 1
  • 3
  • 10
7
votes
3 answers

use of bind variable

Can we use a bind variable in oracle inside a procedure or function ? I'm trying to update a bind variable inside my procedure. Can I do so in any case? if (condition) then :v_bind:=10; end if; Can I do the above thing inside a procedure or…
jasmeet
  • 71
  • 1
  • 1
  • 2
5
votes
2 answers

Java prepared statement parameters for oracle alter session query

I tried executing the oracle alter session query for changing the language settings but it fails with an error "ORA-01036: illegal variable name/number". preparedStatement = connection.prepareStatement("ALTER SESSION SET NLS_SORT =…
Andy Dufresne
  • 6,022
  • 7
  • 63
  • 113
5
votes
1 answer

Why does dbms_sql.parse containing incorrect PL/SQL block with bind variables succeed unexpectedly?

The PL/SQL block below fails as expected: SQL> declare 2 i int; 3 begin 4 i := dbms_sql.open_cursor; 5 dbms_sql.parse(i,'begin dontexist; dbms_output.put(''a''); end;',1); 6 dbms_sql.close_cursor(i); 7 end; 8 …
Rob van Wijk
  • 17,555
  • 5
  • 39
  • 55
5
votes
4 answers

Issue with Oracle bind variables not using index properly

In my scenario, the following query runs fast (0.5 seconds on a table with 70 million rows): select * from Purchases where (purchase_id = 1700656396) and, it even runs fast using bind variables: var purchase_id number := 1700656396 select * from…
Jay Sullivan
  • 17,332
  • 11
  • 62
  • 86
4
votes
6 answers

Declaration of multiple values in Oracle BIND Variables

I am trying to pass multiple values about 3000 values, to a BIND variable in Oracle SQL PLUS command prompt like.. SELECT JOB FROM EMP WHERE JOB IN :JOB -- bind variable value I want to see my result, as all the values in EMP table on column…
dilipece2001
  • 41
  • 1
  • 1
  • 2
4
votes
3 answers

use bind variable with LIKE % operator

In my PL/SQL have this big dynamic SQL using bind variables. I use the DBMS_SQL package to bind and execute the generated query. Based on certain criteria, where-clauses are added to the dynamic SQL. When I just use an "in" or a "=" to match my…
Tijs
  • 43
  • 1
  • 4
4
votes
2 answers

Are PL/SQL variables in cursors effectively the same as bind parameters?

I've heard that using bind variables is (can be) more efficient, because for subsequent calls with a different bind value, the query itself is still the same, so it doesn't need to be parsed anymore. I understand why this is the case for fixed…
GolezTrol
  • 114,394
  • 18
  • 182
  • 210
4
votes
2 answers

How to get tracing info for binding variables passed through OracleParameter in ODP.NET?

After Googling a lot and not finding what I'm looking for I decided to ask this question. I'm using binding variables as demonstrated in this awesome article from 2005 titled The Values That Bind by Mark A. Williams, like this: OracleParameter…
Leniel Maccaferri
  • 100,159
  • 46
  • 371
  • 480
4
votes
1 answer

Using bind variable in 'IN' clause

I want to query a list of numbers into a plsql variable and use that in a in clause in another sql query. I created a testcase below of what I want to do. I did google for the solution and I think it must be possible somehow but I just don't get it…
opi
  • 244
  • 2
  • 9
1
2 3
…
9 10