Re: calling Stored procedure from MS Access returning recordset?

Stefan Daxenbichler wrote:
>
> Hi!
>
> I managed to call stored procedures from MS Access via ODBC, also opening
> server-side views.
>
> [snip]
>
> But how do I define this procedure with oracle? I found no way to
> returning a cursor or something similar.
> Is it possible at all?
>
> Thanx & bye
> Stefan

I don't think Oracle will let you return anything like a recordset from a Function/Procedure. What you might try instead, is creating a PL/SQL Package. Give it a private cursor that it uses, and have Access call a PL/SQL Function/Procedure in the package to read each record (and then the Proc/Func reads the cursor). Sorry, but I have know idea what the MS Access syntax would look like for this, but the PL/SQL syntax would be something like:

CREATE PACKAGE test AS

	PROCEDURE InitializeCursor(Parameter1 IN Number);
	PROCEDURE GetRecord(Field1 IN TableName.Field1%TYPE, Field2 IN TableName.Field2%Type);
	PROCEDURE CloseCursor;

	CURSOR	TestCursor(CursorParam1 IN Number) IS
		SELECT Field1, Field2 FROM TableName
		Where SomeSearchField = CursorParam1;

	PROCEDURE InitializeCursor(Parameter1 IN Number) IS
	BEGIN
		OPEN TestCursor(Parameter1);
	END;

	PROCEDURE GetRecord(Field1 OUT TableName.Field1%TYPE, Field2 OUT TableName.Field2%Type) IS
	BEGIN
		FETCH TestCursor Into Field1, Field2;
	END;

	PROCEDURE CloseCursor IS
	BEGIN
		CLOSE TestCursor;
	END;

Test.InitialzeCursor(123);
-- loop of some sort

        Test.GetRecord(Variable1, Variable2);
Test.CloseCursor;

Hope this helps you.

-- 
-------------------------------------------------
Ken Johnson -  Technical Consultant
Tapestry Computing, Inc. http://www.tapestry.com