Oratcl...

[Dionisios Pothos]

How I can pass SQL lists in an orabindexec bind variable?
For example:

orasql $cursor "SELECT * FROM x WHERE x.col IN (:1)" -parseonly
set x [list a b c]
orabindexec $cursor :1 $x

The above doesn't work, Oratcl doesn't format {a b c} to a proper SQL list
'a','b','c' so the query doesn't return anything. I have tried several
formatings but nothing!!!

Any ideas????

Thanks

[Ulrich Schöbel]

Hi Dionisios,

do the formatting yourself:

orabindexec $cursor :1 "'[join $x ',']'"

Best regards

Ulrich

"Dionisios Pothos" <dpo...@gridintelligent.com> schrieb im Newsbeitrag
news:amk95l$cqv$1...@news5.svr.pol.co.uk...

[Dionisios Pothos]

This still doesn't work !!

"Ulrich Schöbel" <us...@aladyn.de> wrote in message
news:amkmoc$6gppb$1...@ID-148741.news.dfncis.de...

[Ulrich Schöbel]

I don't have an Oracle machine at hand right now,
but shouldn't a SQL statement be terminated by a semicolon?

What does the array oramsg contain?

Best regards

Ulrich

"Dionisios Pothos" <dpo...@gridintelligent.com> schrieb im Newsbeitrag

news:ampdke$n37$1...@news8.svr.pol.co.uk...

[Kieran]

I'm no Oratcl expert, but I think the problem is that you are
effectively trying to pass a variable number of parameters to a prepared
query. This is unlikely to work.

However,

orasql $cursor "SELECT * FROM x WHERE x.col IN (:1 :2 :3)" -parseonly
orabindexec $cursor :1 a :2 b :3 c

will work.

However, I imagine that in your real code you won't know long the list
is in advance, so that won't help very much.

I ran into a similar problem with Informix's libraries, and ending up
having do something rather ugly along the lines of:

prepare_query my_query_5 {select * from x where y in (?,?,?,?,?)}

foreach sublist [split_into_5s $list] {
exec_qry my_query_5 [lindex sublist 0] ... [lindex sublist 4]
}

Regards,
Kieran Elby

[Todd M. Helfter]

In article <ampdke$n37$1...@news8.svr.pol.co.uk>,

You have a problem with the way oracle allows binds to occur, an IN () item
is not a single element.

Try this

set x [list a b c]

set bind_list {}
set in_list {}
set comma {}

set l [llength $x]
for {set i 0} {$i < $l} {incr i} {
set in_list "$in_list $comma :$i"
set bind_list "$bind_list :$i [lindex $x $i]"
set comma {,}
}

set sql "select * from x where x.col in ( $in_list )"

# oratcl 4.0 syntax
oraparse $cur $sql
eval orabind $cur $bind_list
oraexec $cur
while {[orafetch $cur -datavariable d] == 0} {
puts $d
}

# oratcl 3.3 syntax
orasql $cur $sql -parseonly
eval orabindexec $cur $bind_list
orafetch $cur {puts @0}

Todd

--
Todd M. Helfter
Database Analyst/Programmer
Purdue University
t...@purdue.edu