As Larry said, I think that you need to pass an OUT parameter as the 2nd parameter to receive what you are calling pCursor in your procedure. Have you ever used the {resultset 3, fld1, fld2, fld3) type of syntax that MS shows in knowledgebase article Q221189? I've tried that in various forms and I end up with a different error than you get with your example -- "Cannot bind PL/SQL Records or Cursor Refererences" (S1C00). I'm using MS ODBC Driver for Oracle 2.573.7713.00.
I'm working on a set of Oracle SPs to do table CRUD operations myself. Here's what I have so far. The GetAll isn't working, even if I code it like the GetByPK, because I get an error that the table index is out of range. I'm getting ideas from a WROX book titled "Visual Basic Oracle 8", as well as from you and Larry here. The hardest part is trying to figure out what we can pass from VFP to Oracle, especially in the area of OUT parameters that aren't just simple variables or fields.
Anyway, I'm posting this in case my more wordy GetByPK might suffice for you until somebody figures out how to pass the ref cursor or record type back.
CREATE OR REPLACE PACKAGE Test_Data AS
-- This doesn't work because I can't seem to "bind a ref cursor"
-- CURSOR c1 IS
-- SELECT pk, testchar, testvc2, testdate, testdecimal FROM Test;
-- TYPE DataCursor IS REF CURSOR RETURN c1%ROWTYPE;
-- So have to do this instead (Yech).
TYPE tblPK IS TABLE OF test.pk%TYPE
INDEX BY BINARY_INTEGER;
TYPE tblTestChar IS TABLE OF test.testchar%TYPE
INDEX BY BINARY_INTEGER;
TYPE tblTestVC2 IS TABLE OF test.testvc2%TYPE
INDEX BY BINARY_INTEGER;
TYPE tblTestDate IS TABLE OF test.testdate%TYPE
INDEX BY BINARY_INTEGER;
TYPE tblTestDecimal IS TABLE OF test.testdecimal%TYPE
INDEX BY BINARY_INTEGER;
--PROCEDURE GetAll
-- ( curData OUT DataCursor );
PROCEDURE GetByPK
( nPK_In IN Test.PK%TYPE,
nPK OUT tblPK,
cTestChar OUT tblTestChar,
cTestVC2 OUT tblTestVC2,
dTestDate OUT tblTestDate,
nTestDecimal OUT tblTestDecimal );
-- This is the one I'd like to use instead.
-- PROCEDURE GetByPK
-- ( nPK_In IN Test.PK%TYPE,
-- curData OUT DataCursor );
PROCEDURE InsertRecord
( nPK OUT Test.PK%TYPE,
cTestChar IN Test.TestChar%TYPE,
cTestVC2 IN Test.TestVC2%TYPE,
dTestDate IN Test.TestDate%TYPE,
nTestDecimal IN Test.TestDecimal%TYPE );
END Test_Data;
/
CREATE OR REPLACE PACKAGE BODY Test_Data AS
-- PROCEDURE GetAll ( curData OUT DataCursor ) IS
-- BEGIN
--
-- OPEN curData FOR
-- SELECT pk, testchar, testvc2, testdate, testdecimal
-- FROM test;
--
-- END;
PROCEDURE GetByPK
( nPK_In IN Test.PK%TYPE,
nPK OUT tblPK,
cTestChar OUT tblTestChar,
cTestVC2 OUT tblTestVC2,
dTestDate OUT tblTestDate,
nTestDecimal OUT tblTestDecimal ) IS
-- PROCEDURE GetByPK
-- ( nPK_In IN Test.PK%TYPE,
-- curData OUT DataCursor ) IS
-- Example of usage from VFP:
--
-- cSQL = "{call Test_Data.GetByPK(1, {resultset 3, npk, ctestchar, ctestvc2, dtestdate, ntestdecimal})}"
-- ? SQLEXEC ( xx, cSQL, "c_Test" )
-- BROWSE
-- This doesn't work because I can't seem to "bind a ref cursor"
--
-- BEGIN
-- OPEN curData FOR
-- SELECT pk, testchar, testvc2, testdate, testdecimal
-- FROM test
-- WHERE pk = nPK_In;
-- So create a cursor instead and loop through it to fill table collections, one per field (yech!).
CURSOR curData IS
SELECT pk, testchar, testvc2, testdate, testdecimal
FROM test
WHERE pk = nPK_In
ORDER BY pk;
table_index NUMBER :=1;
BEGIN
FOR recData IN curData LOOP
npk ( table_index ) := recData.pk;
ctestchar ( table_index ) := recData.testchar;
ctestvc2 ( table_index ) := recData.testvc2;
dtestdate ( table_index ) := recData.testdate;
ntestdecimal ( table_index ) := recData.testdecimal;
table_index := table_index + 1;
END LOOP;
END;
PROCEDURE InsertRecord
( nPK OUT Test.PK%TYPE,
cTestChar IN Test.TestChar%TYPE,
cTestVC2 IN Test.TestVC2%TYPE,
dTestDate IN Test.TestDate%TYPE,
nTestDecimal IN Test.TestDecimal%TYPE ) IS
-- Example of usage from VFP:
--
-- cSQL = "begin Test_Data.InsertRecord(npk=>?@nPK, cTestChar=>?cTestChar, cTestVC2=>?cTestVC2, dTestDate=>?dTestDate, nTestDecimal=>?nTestDecimal); end;"
-- nPK = 0
-- cTestChar = "Three"
-- cTestVC2 = "Three"
-- dTestDate = {3/3/2003}
-- nTestDecimal = 3.33
-- ? nPK
--
-- OR
--
-- cSQL = "begin Test_Data.InsertRecord(?@nPK, ?cTestChar, ?cTestVC2, ?dTestDate, ?nTestDecimal); end;"
-- nPK = 0
-- cTestChar = "Four"
-- cTestVC2 = "Four"
-- dTestDate = {4/4/2004}
-- nTestDecimal = 4.44
-- ? SQLEXEC ( xx, cSQL, "c_Test" )
-- ? nPK
BEGIN
SELECT seqTest.nextval INTO nPK FROM DUAL;
INSERT INTO test
( pk, testchar, testvc2, testdate, testdecimal )
VALUES
( nPK, cTestChar, cTestVC2, dTestDate, nTestDecimal );
COMMIT;
END;
END Test_Data;
/
>Anyone know why when I try passing variables by reference to an Oracle package using VFP SPT, this error is returned? --
>
>Connectivity error: [Oracle][ODBC][Ora]ORA-06550: line 1, column 7:
>PLS-00306: wrong number or types of arguments in call to 'RETURNCURSOR'
>ORA-06550: line 1, column 7:
>PL/SQL: Statement ignored
>
>The VFP code I am using:>LOCAL lcSQL
>PRIVATE pnCount
>pnCount = 10
>lcSQL = "begin ArticleGetMostPopular.ReturnCursor(nCount=>?pnCount); end;"
>lnRetVal = SQLEXEC(lnHandle, lcSQL, 'crsMostPopular')
>The Oracle package is:>CREATE OR REPLACE PACKAGE ArticleGetMostPopular AS
> Type RetCursor Is Ref Cursor;
> Procedure ReturnCursor(
> nCount IN number,
> pCursor OUT RetCursor);
>END;
>/
>show error
>CREATE OR REPLACE PACKAGE BODY ArticleGetMostPopular AS
> Procedure ReturnCursor(
> nCount IN number,
> pCursor OUT RetCursor) is
> Begin
> open pCursor for
> select * FROM
> (SELECT Article.ID, COUNT(*) AS Count
> FROM Article, ArticleKeyPhrase
> WHERE ArticleKeyPhrase.ArticleID = Article.ID
> GROUP BY Article.ID
> ORDER BY Count DESC)
> where ROWNUM <= nCount;
> End;
>END;
>/
>show error
>--
>
>TIA!
