Board index » delphi » Stored procedures returning result sets from Oracle 8.1.7

Stored procedures returning result sets from Oracle 8.1.7

Hi,

I'm having some trouble getting a simple test Oracle stored procedure
to return me a result set through dbExpress.  My stored procedure
(which I've created both inside and outside a package for testing) is
declared as:

  CREATE OR REPLACE PROCEDURE searchcustomers(pSurname IN VARCHAR2,
pResults IN OUT MYPACKAGE.MyCurType)
  IS
  BEGIN
    OPEN pResults FOR SELECT TITLE, INIT, SURNAME FROM
CUSTOMER_ACCOUNT_DETAILS WHERE SURNAME LIKE pSurname || '%';
  END;

where the custom type MyCurType is in the package header as follows:

CREATE PACKAGE MYPACKAGE AS
  TYPE MyCurType IS REF CURSOR;
  PROCEDURE searchcustomersp(pSurname IN VARCHAR2, pResults IN OUT
MyCurType);
END MYPACKAGE;

Has anyone done this successfully?  As I say, I've tried with the proc
both inside and outside a package body.  If I point the TSQLStoredProc
component at the procedure *inside* the package, parameters are picked
up correctly by the IDE (ptInput, ftString: ptInputOutput, ftCursor),
but attempting to open yields

ORA-06550: line 1, column 7
PLS00201: identifier 'SEARCHCUSTOMERS' must be declared
ORA-06550: line 1, column 7
PL/SQL: statement ignored

Ravesh from Borland appears to have noted that this is a
package-related bug, so I've set this result to one side for now in
anticipation of a fix in D6 Update Pack 2 :)

If I attempt to use the stored procedure *outside* the package, the
parameters are again picked up correctly, but attempting to open the
procedure for the first time results in 'Access violation at address
00000000'.  Attempting to open a second time results in 'ORA-01036:
Illegal variable name/number'.

I'm using the OCI.DLL which ships with the 8.1.7 client.

Thanks,

Russ

 

Re:Stored procedures returning result sets from Oracle 8.1.7


Hi,
    I am experiencing exactly the same problems. My case is as the same as
Russ'.
Maybe an addition can be made:
* I traced the dbExpress calls and found that the query which gets stored
procedure parameters fails and double the parameters, if the names of the
stored procedures outside or inside packages are the same.
For example, if you have:
MYPROC and MYPACK.MYPROC with 2 parameters each, you get 4 parameters.

I need to develop for Oracle but I don't want to use BDE, or a third party
tool
(like DOA which worked very well with some friends of mine) but
I think Borland has failed to build an alternative for Oracle.

Yes, Ravesh try to help dbExpress users for Oracle, but Borland has not
announced an
update yet which makes me very unsatisfied...

Thank you for listening...

Russ Garner <russelldotgarnerat{*word*269}dot...@resolve.to.reply.net> wrote in
message news:b6mn4u803j3efdsvfv5p7qvash759kbi5k@4ax.com...

Quote
> Hi,

> I'm having some trouble getting a simple test Oracle stored procedure
> to return me a result set through dbExpress.  My stored procedure
> (which I've created both inside and outside a package for testing) is
> declared as:

>   CREATE OR REPLACE PROCEDURE searchcustomers(pSurname IN VARCHAR2,
> pResults IN OUT MYPACKAGE.MyCurType)
>   IS
>   BEGIN
>     OPEN pResults FOR SELECT TITLE, INIT, SURNAME FROM
> CUSTOMER_ACCOUNT_DETAILS WHERE SURNAME LIKE pSurname || '%';
>   END;

> where the custom type MyCurType is in the package header as follows:

> CREATE PACKAGE MYPACKAGE AS
>   TYPE MyCurType IS REF CURSOR;
>   PROCEDURE searchcustomersp(pSurname IN VARCHAR2, pResults IN OUT
> MyCurType);
> END MYPACKAGE;

> Has anyone done this successfully?  As I say, I've tried with the proc
> both inside and outside a package body.  If I point the TSQLStoredProc
> component at the procedure *inside* the package, parameters are picked
> up correctly by the IDE (ptInput, ftString: ptInputOutput, ftCursor),
> but attempting to open yields

> ORA-06550: line 1, column 7
> PLS00201: identifier 'SEARCHCUSTOMERS' must be declared
> ORA-06550: line 1, column 7
> PL/SQL: statement ignored

> Ravesh from Borland appears to have noted that this is a
> package-related bug, so I've set this result to one side for now in
> anticipation of a fix in D6 Update Pack 2 :)

> If I attempt to use the stored procedure *outside* the package, the
> parameters are again picked up correctly, but attempting to open the
> procedure for the first time results in 'Access violation at address
> 00000000'.  Attempting to open a second time results in 'ORA-01036:
> Illegal variable name/number'.

> I'm using the OCI.DLL which ships with the 8.1.7 client.

> Thanks,

> Russ

Re:Stored procedures returning result sets from Oracle 8.1.7


Hi,

I think that everybody's using dbExpress connecting to Oracle has the same
problems!!!

I found some other problems like when using synonyms....they don't work!!
dbExpress doesn't recognize them...

I hope that Borland solves these problems as fast as they can or we will
have to find another solution to connect to Oracle.

Wellington.

"Murat Okay" <murat_o...@yahoo.com> escreveu na mensagem
news:3c4fc2be_1@dnews...

Quote
> Hi,
>     I am experiencing exactly the same problems. My case is as the same as
> Russ'.
> Maybe an addition can be made:
> * I traced the dbExpress calls and found that the query which gets stored
> procedure parameters fails and double the parameters, if the names of the
> stored procedures outside or inside packages are the same.
> For example, if you have:
> MYPROC and MYPACK.MYPROC with 2 parameters each, you get 4 parameters.

> I need to develop for Oracle but I don't want to use BDE, or a third party
> tool
> (like DOA which worked very well with some friends of mine) but
> I think Borland has failed to build an alternative for Oracle.

> Yes, Ravesh try to help dbExpress users for Oracle, but Borland has not
> announced an
> update yet which makes me very unsatisfied...

> Thank you for listening...

> Russ Garner <russelldotgarnerat{*word*269}dot...@resolve.to.reply.net> wrote in
> message news:b6mn4u803j3efdsvfv5p7qvash759kbi5k@4ax.com...
> > Hi,

> > I'm having some trouble getting a simple test Oracle stored procedure
> > to return me a result set through dbExpress.  My stored procedure
> > (which I've created both inside and outside a package for testing) is
> > declared as:

> >   CREATE OR REPLACE PROCEDURE searchcustomers(pSurname IN VARCHAR2,
> > pResults IN OUT MYPACKAGE.MyCurType)
> >   IS
> >   BEGIN
> >     OPEN pResults FOR SELECT TITLE, INIT, SURNAME FROM
> > CUSTOMER_ACCOUNT_DETAILS WHERE SURNAME LIKE pSurname || '%';
> >   END;

> > where the custom type MyCurType is in the package header as follows:

> > CREATE PACKAGE MYPACKAGE AS
> >   TYPE MyCurType IS REF CURSOR;
> >   PROCEDURE searchcustomersp(pSurname IN VARCHAR2, pResults IN OUT
> > MyCurType);
> > END MYPACKAGE;

> > Has anyone done this successfully?  As I say, I've tried with the proc
> > both inside and outside a package body.  If I point the TSQLStoredProc
> > component at the procedure *inside* the package, parameters are picked
> > up correctly by the IDE (ptInput, ftString: ptInputOutput, ftCursor),
> > but attempting to open yields

> > ORA-06550: line 1, column 7
> > PLS00201: identifier 'SEARCHCUSTOMERS' must be declared
> > ORA-06550: line 1, column 7
> > PL/SQL: statement ignored

> > Ravesh from Borland appears to have noted that this is a
> > package-related bug, so I've set this result to one side for now in
> > anticipation of a fix in D6 Update Pack 2 :)

> > If I attempt to use the stored procedure *outside* the package, the
> > parameters are again picked up correctly, but attempting to open the
> > procedure for the first time results in 'Access violation at address
> > 00000000'.  Attempting to open a second time results in 'ORA-01036:
> > Illegal variable name/number'.

> > I'm using the OCI.DLL which ships with the 8.1.7 client.

> > Thanks,

> > Russ

Other Threads