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

From: Eric Zimmerman <zimmerman.eric_at_con-way.com>
Date: 17 Jan 2002 08:31:07 -0800
Message-ID: <b994ea8e.0201170831.52f9fa82@posting.google.com>

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.

The frustration
GetByRefs proc does not want to work and I am assuming at this point it is the fact that it has something to do with the IN (pRefs) part of the SELECT statement. Here is the code to initialize the connection and command object:

cnTMS.Open "Provider=MSDAORA.1;Password=XXXXXX;User ID=XXXXXX;Data Source=XXXXXX;Persist Security Info=True"

With cmdReferenceNumbers

.CommandType = adCmdStoredProc
.CommandText = "TMAudit.GetByRefs"
.ActiveConnection = cnTMS
.Parameters.Append .CreateParameter("pRefs", adVarChar,
adParamInput, 100)
End With

The application then takes a listbox full of items and creates a string in the form of:

'Ref1','Ref2','Ref3'

and so on. I then set the pRefs Parameter to this value and execute the command object, like this:

cmdReferenceNumbers.Parameters("pRefs").Value = RefNums Set rsResults = cmdReferenceNumbers.Execute

The resulting recordset contains no rows even tho if I run the same SQL statement in SQL*plus it finds records! Also, if I do a rs.open "SQL STATEMENT" in VB I get what I expect (where SQL STATEMENT is the equivalent of the SQL command the stored procedure would run if this were working). I am thinking there may be some weirdness in how Oracle is seeing the string once it is passed into the stored procedure. Bear in mind the other 2 procedures in the package are executed in the same way and work.

Has anyone seen this or know of a work around? I am wondering if I can pass in an array value and then use that to create the string in Oracle, but I am looking for some guidance on the best way to handle this. The problem I need to solve is passing in data to form an IN clause in a WHERE statement.

Here is Package Header:
CREATE OR REPLACE PACKAGE TMAUDIT
IS
/* Cursor definitions */
  CURSOR RefNum IS -- Reference number Search

  SELECT  RFRC_NUM_T.RFRC_NUM,
          SHPM_T.FRM_PKUP_DTT,
          SHPM_T.TRPT_ODR_CD,
          SHPM_T.SCLD_WGT,
          SHPM_T.TOT_PCE,
          SHPM_T.TOT_SKID,
          SHPM_T.FRM_CTY_NAME,
          SHPM_T.FRM_STA_CD,
          SHPM_T.FRM_PSTL_CD,
          SHPM_T.TO_CTY_NAME,
          SHPM_T.TO_STA_CD,
          SHPM_T.TO_PSTL_CD
  FROM
          RFRC_NUM_T,
          SHPM_T
  WHERE
          RFRC_NUM_T.SHPM_ID = SHPM_T.SHPM_ID;

  CURSOR FromCode IS -- Ship From Code Search   SELECT LDAT_T.SHPG_LOC_CD,

		  LDAT_T.NAME,
		  ADDR_T.BLK_BLDG,
		  ADDR_T.ST_NAME,
		  ADDR_T.UNIT,
          ADDR_T.CTY_NAME,
          ADDR_T.STA_CD,
          ADDR_T.PSTL_CD
  FROM 	  ADDR_T,
          LDAT_T

  WHERE ADDR_T.ADDR_ID = LDAT_T.ADDR_ID;   CURSOR ToCode IS -- Ship From Code Search

  SELECT  CNSE_T.SHPG_LOC_CD,
          CNSE_T.NAME,
          ADDR_T.BLK_BLDG,
          ADDR_T.ST_NAME,
          ADDR_T.UNIT,
          ADDR_T.CTY_NAME,
          ADDR_T.STA_CD,
          ADDR_T.PSTL_CD
   FROM   CNSE_T,
      	  ADDR_T

   WHERE CNSE_T.ADDR_ID = ADDR_T.ADDR_ID; /* More Cursor definitions */

	TYPE RefNumCur IS REF CURSOR RETURN RefNum%ROWTYPE;
	TYPE FromCodeCur IS REF CURSOR RETURN FromCode%ROWTYPE;
	TYPE ToCodeCur IS REF CURSOR RETURN ToCode%ROWTYPE;

/* Procedure definitions */
	PROCEDURE GetByRefs(pRefs IN VARCHAR2,	RefNumCursor IN OUT
RefNumCur);
  	PROCEDURE GetFromCodes(pZip IN VARCHAR2, pBlock IN VARCHAR2,
FromCodeCursor IN OUT FromCodeCur);
	PROCEDURE GetToCodes(pZip IN VARCHAR2, pBlock IN VARCHAR2,

ToCodeCursor IN OUT ToCodeCur);

END TMAudit;

Here is Package Body:
CREATE OR REPLACE PACKAGE BODY TMAUDIT
    AS
PROCEDURE GetByRefs(pRefs IN VARCHAR2, RefNumCursor IN OUT RefNumCur) IS
  BEGIN
    OPEN RefNumCursor FOR

       SELECT  RFRC_NUM_T.RFRC_NUM,
          SHPM_T.FRM_PKUP_DTT,
          SHPM_T.TRPT_ODR_CD,
          SHPM_T.SCLD_WGT,
          SHPM_T.TOT_PCE,
          SHPM_T.TOT_SKID,
          SHPM_T.FRM_CTY_NAME,
          SHPM_T.FRM_STA_CD,
          SHPM_T.FRM_PSTL_CD,
          SHPM_T.TO_CTY_NAME,
          SHPM_T.TO_STA_CD,
          SHPM_T.TO_PSTL_CD
  FROM
          RFRC_NUM_T,
          SHPM_T
  WHERE
          RFRC_NUM_T.SHPM_ID = SHPM_T.SHPM_ID AND
          RFRC_NUM_T.RFRC_NUM IN (pRefs);

  END GetByRefs;

PROCEDURE GetFromCodes(pZip IN VARCHAR2, pBlock IN VARCHAR2, FromCodeCursor IN OUT FromCodeCur)
IS

	BEGIN
		OPEN FromCodeCursor FOR
      SELECT 	LDAT_T.SHPG_LOC_CD AS "TM Code",
		LDAT_T.NAME AS "Name",
          	ADDR_T.BLK_BLDG AS "Block",
          	ADDR_T.ST_NAME AS "Street",
          	ADDR_T.UNIT AS "Unit",
                ADDR_T.CTY_NAME AS "City",
                ADDR_T.STA_CD AS "ST",
                SUBSTR(ADDR_T.PSTL_CD,0,5) AS "Zip"
      FROM   	ADDR_T,
          	LDAT_T
      WHERE 	ADDR_T.ADDR_ID = LDAT_T.ADDR_ID AND
                ADDR_T.PSTL_CD LIKE pZip AND
                ADDR_T.BLK_BLDG LIKE pBlock;
	END GetFromCodes;

PROCEDURE GetToCodes(pZip IN VARCHAR2, pBlock IN VARCHAR2, ToCodeCursor IN OUT ToCodeCur)
IS

	BEGIN
		OPEN ToCodeCursor FOR
      SELECT    CNSE_T.SHPG_LOC_CD AS "TM Code",
            	CNSE_T.NAME AS "Name",
                ADDR_T.BLK_BLDG AS "Block",
          	ADDR_T.ST_NAME AS "Street",
          	ADDR_T.UNIT AS "Unit",
                ADDR_T.CTY_NAME AS "City",
                ADDR_T.STA_CD AS "ST",
                SUBSTR(ADDR_T.PSTL_CD,0,5) AS "Zip"
      FROM   	ADDR_T,
          	CNSE_T
      WHERE 	ADDR_T.ADDR_ID = CNSE_T.ADDR_ID AND
                ADDR_T.PSTL_CD LIKE pZip AND
                ADDR_T.BLK_BLDG LIKE pBlock;
	END GetToCodes;

END TMAUDIT; Received on Thu Jan 17 2002 - 10:31:07 CST