Paradox SQL Link and ORACLE7: How to use Stored Procedures?

## Nachricht vom 08.09.94 weitergeleitet
## Ursprung : /Unversandt  (internes CrossPoint-Brett)
## Ersteller: eppert_at_fant.ping.de

## Originalempfaenger: /comp/databases/oracle

Hello out there!

We've got a problem with SQL Link for Paradox. We are using an ORACLE7- Database Server (Ver. 7.0.16.6.0) and Paradox for Windows 4.5 (5.0 is not yet available in Germany) with the SQL Link 1.0. We use Paradox as a frontend to the Server. To improve speed we want to use Stored Procedures, which are stored and compiled on the ORACLE-Server.

It is no a problem to us to create a Stored Procedure at the ORACLE-Server and to execute it when we are at the console (respectively the SQL*Plus-Prompt on a client). But we are unable to start the execution of a Stored Procedure by a Paradox client, although it is possible to start anonymous procedures by Paradox.

Can anybody help ?

Here are the details:

The way we define a anonymous procedure is to generate a ASCII-File (namend "proc1.sql") like this:

• B E G I N --------- declare cnt NUMBER := 1; cursor c1 IS SELECT IDLS, TLSLfdNr, LSPosLfdNr, IDArtikel, LiefMenge FROM hiltiuser.bench_lspos; c_idls hiltiuser.bench_lspos.IDLS%TYPE; c_tlslfdnr hiltiuser.bench_lspos.TLSLfdNr%TYPE;
  • etc.

begin
  open c1;
  LOOP
    FETCH c1 INTO c_idls, c_tlslfdnr, c_lsposlfdnr,

                  c_idartikel, c_liefmenge;

• etc. END LOOP; CLOSE c1;
• COMMIT; end;
  ----------- E N D -------------
  

In Paradox we start the procedure by these lines:

• B E G I N --------- if not db.open(":test:") then msginfo ("Error","....") endIf if not executeSQLFile (db, "proc1.sql") then msgInfo ("Error", "....") endIf
• E N D -------------

After a few seconds the work is done and everything is ok.

Then we wanted to use this work with a Stored Procedure. We created a Stored Procedure on the Server named "storproc" with exactly the same body like above:

• B E G I N --------- create procedure storproc as cnt NUMBER := 1; cursor c1 IS SELECT IDLS, TLSLfdNr, LSPosLfdNr, IDArtikel, LiefMenge FROM hiltiuser.bench_lspos; c_idls hiltiuser.bench_lspos.IDLS%TYPE; c_tlslfdnr hiltiuser.bench_lspos.TLSLfdNr%TYPE;
  • etc.

begin
  open c1;
  LOOP
    FETCH c1 INTO c_idls, c_tlslfdnr, c_lsposlfdnr,

                  c_idartikel, c_liefmenge;

• etc. END LOOP; CLOSE c1;
• COMMIT; end;
  ----------- E N D -----------
  

We tried to execute this procedure from Paradox with these commands:

• B E G I N ---------

if not db.open(":test:") then

        msginfo ("Error", "....")
endIf
if not executeSQLString (db, "execute storproc") then

        msgInfo ("Error",".....")
endIf

• E N D -------------

This fails. Unfortunaly there is no way to get the reason for the failure. We tried also with ";" at the end of the "execute"-line and further with an slash on the begining of the next line. Nothing was successful.

If you know, how to execute an Stored Procedure from within Paradox, please mail me or post in this group. If you have other experience with ORACLE7 and Paradox with SQL Link, good or bad, give me a short mail, please.

Thanks in advance, Martin.

D-44137 Dortmund (Germany)
e-mail: eppert_at_fant.ping.de
-----------------------------------------------------