Re: Oracle Stored procedure, the WHERE...IN statement, and frustration!

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Thu, 17 Jan 2002 18:38:33 +0100
Message-ID: <pu2e4u4uiav1r996tab88c5pp50erb0cea@4ax.com>

On 17 Jan 2002 08:31:07 -0800, zimmerman.eric_at_con-way.com (Eric Zimmerman) wrote:

>Hello,
>Background Info
>Oracle 8 running on Solaris
>VB6 SP5 with ADO 2.6
>Using a package that contains stored procedures. Package header and
>body are at bottom of post
>
>The good
>GetFromCodes and GetToCodes work perfectly. I can pass in the
>parameters of command object and get the expected results in an ADO
>Recordset.
>
>The problem
>The problem I need to solve is passing data into Oracle which will
>form an IN clause in a WHERE statement.

You need to use dynamic sql, as you need to construct the in clause. Just passing a string to in doesn't work. Hence you need to use dbms_sql in 8.0 and OPEN <refcursor var> for :sqlstatement in 8i and beyond.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Jan 17 2002 - 11:38:33 CST