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

In article <b994ea8e.0201170831.52f9fa82_at_posting.google.com>, zimmerman.eric_at_con-way.com says...
>

read
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:110612348061

for a solution

>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;

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp