` Calling Stored Procedures in Oracle (Fadi G. Ashi) - Icetips Article
Icetips - Templates, Tools & Utilities for Clarion Developers

Templates, Tools and Utilities
for Clarion Developers

Icetips Article

Back to article list   Search Articles     Add Comment     Printer friendly     Direct link  

SQL Related Articles: Calling Stored Procedures in Oracle
2003-09-08 -- Fadi G. Ashi
 
CW 5.5 and Above/ Oracle Stored Procedure & Functions
 
After spending three days trying to figure out how to call an Oracle Stored Procedure / Function I was able to get it to work, returning a result set and returning output parameter.
 
Oracle Passed variable can be in 3 format In, In Out , Out, where In parameter can not be changed inside the Oracle stored procedure/Function while the In out and Out can be changed.
 
To return a result set from Oracle you must first create a “ref cursor” as a type, which will be a generic cursor for any result set that you need to return following is an example of creating a package called Types, that hold the “ref cursor” definition. 
CREATE OR REPLACE  PACKAGE "Fadi"."TYPES"   as 
    type CursorType is ref cursor;  
end;
In the above example we have created a “ref cursor” which called CursorType that can be referenced in any Stored procedure/Function as a valid definition of a cursor.
 
Stored Procedure/Function (Returning a Result Set)
 
Following is an example of a Stored Procedure that will return results. 
CREATE OR REPLACE  PROCEDURE "TC"."TEST_PROC"          (I in number,
    My_Cur out Types.cursorType)
as
Begin
   Open My_Cur for Select * From MyFile where MyFile_sysid >= I;  
End;
This Procedure will accept an input parameter and will return the records matching the select statement.
 
Following is an example of a Function that will return results. 
CREATE OR REPLACE  FUNCTION "TC"."TEST_FUNC"       (I in number)
    return Types.cursorType
as
My _Cur  Types.cursorType;
Begin
   Open My _Cur for Select * From MyFile where MyFile _sysid >= I;
   Return My _Cur; 
End;
As you notice the Function is very similar to the procedure except that it has a return. In Oracle you can only return one Item from a function in this case we are returning a result set.
 
The CW code will look like this to call the above procedure 
  Relate:MyFile.Open()    ! Open MyFile
  CLEAR(MyF:Record)       ! Clear the Record Buffer
  SET(MyFile)             ! Due to a bug you need to Set and Next
  Next(MyFile)            ! To initialize the Record buffer.
  CLEAR(MyF:Record)       ! Clear the buffer again.
  ! Call the Procedure and check for error
  MyFile{Prop:Sql} =  'Call Test_Proc(5)'  
  IF FILEERRORCODE() THEN STOP(FILEERROR()).  
  LOOP                    ! Loop Thru your record.
     NEXT(MyFile)
     IF ERRORCODE() THEN BREAK .
     !  Do your Stuff here
     …..
     …..
End ! Loop
Relate:Myfile.Close()    ! Close MyFile
To call the Function just replace the MyFile{Prop:Sql} = 'Call Test_Proc(5)' To MyFile{Prop:Sql} = 'Call Test_Func(5)'. Stored Procedure/Function (Returning a Result in OUT prameter)
 
Now lets See how we can Return Values from a Stored procedure.
 
Following is an example of a Stored Procedure that Total 2 number and Return the Result. 
CREATE OR REPLACE  PROCEDURE "TC"."MYTOTAL_PROC"
(I in Number,
J in Number,
Tot out Number)
as
begin
   Tot := I + J; 
end   ;
As you notice I and J are declared as IN and Tot is declared as OUT.
 
Following is CW code to retrieve the Total from the stored procedure. 
  Relate: MyFile.Open          ! Open MyFile
  Bind('Tot',tot)              ! Bind the Variable Tot is declared as Long.
  ! Call the Procedure NoResultCall is mandatory for Oracle.
  MyFile{Prop:Sql} = 'NoResulTCall MYTOTAL_PROC (2,3,&tot)' 
  ! Check for errors
  IF FILEERRORCODE() THEN STOP(FILEERROR()).  
  ! Tot now hold the Returned value which is 5
  Relate: MyFile.close   ! Close MyFile
MyFile is used only to call the Stored Procedure so it can be any file declared in your CW Dictionary. The Variable declared in CW to receive the Result can not include ":" or "_". Those what I found out so far. The call to the Procedure have to be NoResultCall this is a mandatory for Oracle.
 
Stored Procedure/Function (Returning a Result in IN OUT prameter)
 
Following is an example of a Stored Procedure that Total 3 number and Return the Result. In the 3rd passed parameter because it is declared as in out 
CREATE OR REPLACE  PROCEDURE "TC"."MYTOTAL_PROC"
(I in Number,
J in Number,
Tot in out Number)
as
begin
   Tot := Tot + I + J; 
end   ;
As you notice I and J are declared as IN and Tot is declared as IN OUT.
 
Following is CW code to retrieve the Total from the stored procedure. 
  Relate: MyFile.Open          ! Open MyFile
  Bind('Tot',tot)              ! Bind the Variable Tot is declared as Long.
  Tot = 10                     ! initialize Tot to 10. 
  ! Call the Procedure NoResultCall is mandatory for Oracle.
  MyFile{Prop:Sql} = 'NoResulTCall MYTOTAL_PROC (2,3,&tot)' 
  ! Check for errors
  IF FILEERRORCODE() THEN STOP(FILEERROR()).  
  ! Tot now hold the Returned value which is 15
  Relate: MyFile.close   ! Close MyFile
Stored Procedure/Function (Returning a Result set and an IN OUT prameter)
 
Following is an example of a Stored Procedure that Total 3 number and Return the Result. In the 3rd passed parameter because it is declared as in out and also return a Result set. 
CREATE OR REPLACE  PROCEDURE "TC"."MYTOTAL_PROC"
(I in Number,
J in Number,
Tot in out Number,
My_Cur out Types.cursorType)
as
begin
   Tot := Tot + I + J;
    Open My_Cur for Select * From MyFile where MyFile_sysid >= Tot;  
end   ;
Following is CW code to retrieve the total from the Stored Procedure as well as the Result set. 
  Relate: MyFile.Open          ! Open MyFile
  Bind('Tot',tot)              ! Bind the Variable Tot is declared as Long.
  CLEAR(MyF:Record)            ! Clear the Record Buffer
  SET(MyFile)                  ! Due to a bug you need to Set and Next
  Next(MyFile)                 ! To initialize the Record buffer.
  CLEAR(MyF:Record)            ! Clear the buffer again.
  Tot = 10                     ! initialize Tot to 10. 
  MyFile{Prop:Sql} = 'Call MYTOTAL_PROC (2,3,&tot)'  ! Call the Procedure
  IF FILEERRORCODE() THEN STOP(FILEERROR()).         ! Check for errors
  ! Tot now hold the Returned value which is 15
  LOOP                         ! Loop Thru your record.
     NEXT(MyFile)
     IF ERRORCODE() THEN BREAK .
     !  Do your Stuff here
     …..
     …..
  End ! Loop
  Relate: MyFile.close         ! Close MyFile
The only Limitation I could found is the Result set declared Cursor has to be the last parameter.

I have doubts
2003-10-31 � Rafael Vasques 
Hi,

I followed the tips in this article and created the stored procedure, but when I
call it through Clarion 5.5, using the native Oracle Driver (our Oracle version
is 8.1), I get the following error message
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
"P_GEP_TAMANHO_PAPEL_S"
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
The CW is as follows:
   Clear(TAMPAP:Record)
   TAMANHO_PAPEL_GEP{PROP:SQL} = CALL P_GEP_TAMANHO_PAPEL_S(4)
   If FileError() Then Stop(FileError()).
   End
   Loop While Access:TAMANHO_PAPEL_GEP.TryNext() = Level:Benign
      Clear(Q_DADOS)
        QD:CD_TAMANHO_PAPEL      = TAMPAP:CD_TAMANHO_PAPEL
        QD:DS_TAMANHO_PAPEL      = TAMPAP:DS_TAMANHO_PAPEL
      Add(Q_DADOS)
   End
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
What could be happening?

Thanks.
Today is April 27, 2024, 10:45 pm
This article has been viewed 35115 times.
Google search has resulted in 11593 hits on this article since January 25, 2004.


Back to article list   Search Articles   Add Comment   Printer friendly

Login

User Name:

Password:


Gold Subscription
Buy Now
Silver Subscription
Buy Now
Solo Subscription
Buy Now
Multiple Copy Pricing
Renew Subscription