Printed Icetips Article

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.