Call a parameterized Oracle query from ADODB in Classic ASP

Question

I’m currently working on a classic ASP project talking to an Oracle database. I’m trying to find a way to safely call an Oracle PL/SQL script and passing parameters with ADO. The currently solution builds the SQL script by hand with embedded variables like this:

strSQL = "SELECT field1, etc FROM my_table WHERE (field = '" & filter_value & "')"

This, of course, is ugly and insecure, and open to abuse.

The code that I have so far (purloined from various non classic asp based web sites) looks like this:

dim strSQL, oConn, oCommand, oParam
set oConn = server.createobject("ADODB.Connection")
oConn.Open myConnString

strSQL = "SELECT field1, etc FROM my_table WHERE (field = :filter_field)"

dim oFilteredList
set oFilteredList = Server.CreateObject("ADODB.Command")
oFilteredList.ActiveConnection = oConn
oFilteredList.CommandText = strSQL
oFilteredList.CommandType = adCmdText
oFilteredList.NamedParameters = True

set oParam = oFilteredList.CreateParameter("filter_field", adVarChar, adParamInput, 10, filter_value)
oFilteredList.Parameters.Append oParam

set rsResults = oFilteredList.Execute

This causes the error “Parameter object is improperly defined. Inconsistent or incomplete information was provided”

What is the correct method of calling Oracle / PL/SQL with named parameters from ADO? I need to use named parameters because the actual SQL code is somewhat more complex, and different parameters are used multiple times throughout the SQL command.

Answer 1

How do you have filter_value defined? If it's not declared as a String or if you've assigned a string longer than 10 characters (as you've indicated when creating the parameter), you'll have issues with that.

Additionally (and partly for my own reference), named parameters are not supported via OraOLEDB (i.e. ADODB).

See Oracle® Provider for OLE DB Developer's Guide 11g Release 1 (11.1) or follow the "Command Parameters" heading link on any of the previous versions (8iR3, 9i, 9iR2, 10g, 10gR2):

Command Parameters

When using Oracle ANSI SQL, parameters in the command text are preceded by a colon. In ODBC SQL, parameters are indicated by a question mark (?).

OraOLEDB supports input, output, and input and output parameters for PL/SQL stored procedures and stored functions. OraOLEDB supports input parameters for SQL statements.

"Note: OraOLEDB supports only positional binding."

That said, this should have no bearing on your query when using OraOLEDB:

oFilteredList.NamedParameters = True

I've had success running queries exactly as the rest of your example shows though on Oracle 10gR2.

You don't show your connection string, so I must assume it to be valid. Behavior can differ depending on options there, so here's what I successfully use:

`"Provider=OraOLEDB.Oracle;Data Source=TNSNAMES_ENTRY;User ID=XXXX;Password=YYYY;DistribTx=0;"`