PL-SQL bind variables in TCL

Question

Could some one explain in the following TCL source code:

set sql "SELECT PROD.KEY || ' {' || PARAMETERS || '}' \
         FROM PRV_PROD_MAPPING PROD \
         WHERE PROD.SERVICE_ID = :service_id \
         AND (PROD.KEY || ' ' || PROD.KEY_VAL) \
         IN (:keys) "

what :service_id and :keys mean. Could I see the values behind by simple trace. How could I find where these vars are defined?

Thanks in advance!

What you've shown is simply the assignment of a string to a variable named` sql`. What does your program do with that string? What mechanism are you using to interact with the database? — glenn jackman, Jun 03 '13 at 10:27
You are right, but that I want to find out is how to see the value behind (:keys). When I try to call $keys, there is no such variable! — nenito, Jun 03 '13 at 11:11
They are indeed placeholders; how they're filled in depends on the driver. Those that conform to the TDBC specification will use variables or a supplied dictionary (as Glenn's answer shows). — Donal Fellows, Jun 03 '13 at 20:22

score 2 · Answer 1 · edited May 23 '17 at 12:28

2

It's not TCL its an SQL query embedded in a TCL string, specifically one that binds a variable, which is then assigned to a normal TCL variable

As glenn points out, on its own this snippet of TCL does very little. presuembly somewhere in your program you actually connect to a DB and pass it a query from this string and some other variables

edited May 23 '17 at 12:28

Community

1
1

answered Jun 03 '13 at 10:09

jk.

13,817
5
37
50

score 2 · Answer 2 · edited Jun 03 '13 at 20:20

2

If you're using TDBC, you might have

# assume the connection has already occured and is named "db"
set sql "SELECT ... WHERE a.b=:service_id IN (:keys)"
set statement [db prepare $sql]

# get the bind variables' values from the local context:
set service_id 42
set keys [join {key1 key2 key3} ,]
set resultset [$statement execute]

# or, without setting the "service_id" and "keys" variables, 
# provide them as an argument to the execute subcommand
unset service_id keys
set resultset [$statement execute {service_id 24 keys "foo,bar,baz"}]

edited Jun 03 '13 at 20:20

Donal Fellows

133,037
18
149
215

answered Jun 03 '13 at 15:58

glenn jackman

238,783
38
220
352

I'm not very good familiar with tcl, but I think it is using some tcl embedded oracle functions starting with ora*. – nenito Jun 04 '13 at 11:29

score 1 · Answer 3 · answered Jun 03 '13 at 20:35

As others pointed out it you just assign a string* to a variable.

More exactly, you assign the following string to a variable called sql

SELECT PROD.KEY || ' {' || PARAMETERS || '}'  FROM PRV_PROD_MAPPING PROD  WHERE PROD.SERVICE_ID = :service_id  AND (PROD.KEY || ' ' || PROD.KEY_VAL)  IN (:keys)

The values of :service_id and :keys are bound values that are passed later (In a oraplexec statement probably).

|| is string concatenation in SQL. So PROD.KEY || ' {' || PARAMETERS || '}' could be PRODKEY {PARAMETERS}.

*Everything is a string

score 0 · Accepted Answer · answered Jun 04 '13 at 11:22

0

I've done it using traces:

sys.DBMS_SYSTEM.SET_EV(n_sid, n_serial, 10046, 12, '');
DBMS_OUTPUT.put_line('Trace started: ' || to_char(SYSDATE,'dd.mm.yyyy hh24:mi:ss'));

answered Jun 04 '13 at 11:22

nenito

1,214
6
19
33

PL-SQL bind variables in TCL

4 Answers4