Login
Templates, Tools and Utilities
for Clarion Developers
Templates, Tools and Utilities
|
||
|
|
|
Add a comment to an Icetips ArticlePlease add your comments to this article.
Please note that you must provide both a name and a valid email address in order
for us to publish your comment. Comments are moderated and are not visible until they have been approved. Spam is never approved!
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 November 21, 2024, 7:19 am This article has been viewed 35238 times. Google search has resulted in 11593 hits on this article since January 25, 2004.
|
|