How to execute stored procedure with SwiftOracle.

[bhregmi]

You example helped me to fetch data with Select statement. Can you please guide me how to execute stored procedure with it ?

[iliasaz]

It's pretty straightforward actually:

cursor = try conn.cursor()   
let sqlStr = "begin your_procedure( param1 => :p1, param2 => :p2 ); end;"   
try cursor.execute(sqlStr, params: [":p1": BindVar(value1), ":p2": BindVar(value2) ])

[iliasaz]

Yes, you can set conn.autoCommit(true). You can also use let conn = req.oraConnPool.pool.getConnection(tag: "", autoCommit: true)

[bhregmi]

Is ":p2": BindVar(value2) is the output result of the stored procedure or do i need to change anything to get the output result ? With your help i was able to send the input parameters and now i am puzzled with the result set of the stored procedure. Your help is highly appreciated . Basically I am stuck with "how to display output values"

[iliasaz]

Unfortunately, the original SwiftOracle only supported one way, input bind variables. The output value returned by a procedure is not mapped to Swift BindVar instance. I haven't modified this interface yet.

There is a simple workaround: turn your procedure into a function that returns a value and use select myfunc(input_param) from dual;

If you can't modify the procedure, you can wrap it into a function to return the modified value. Here is an example.

Assuming your procedure is as follows, and parameter1 is in out parameter, and we want to return the modified value.

create or replace procedure test_proc(parameter1 in out varchar2) as
begin
  parameter1 := 'modified value'; 
end test_proc;

We can create a wrapper function as follows:

create or replace function test_func(parameter1 in varchar2) return varchar2 as
  myVar varchar2(100) := parameter1;
begin
  test_proc(myVar);
  return myVar;
end test_func;

And then use
select test_func('some value') from dual;

This will return "modified value".

In Swift, we would simple invoke the above select statement:

let sqlStr = "select test_func(:p1) from dual"
try cursor.execute(sqlStr, params: [":p1" : BindVar("some value")])
// fetch the data
while let row = cursor.nextSwifty() {
    for f in row.fields {
        responseString += "\(f.toString)\t"
    }
}

[bhregmi]

Can we apply this method if the return value is in RefCursor ?

[iliasaz]

You can certainly do it on the server side (in SQL). And then see this example from OCILIB , there is a section named "Ref cursor Example".
However, SwiftOracle does not currently support data type OCI_Statement. So you may have to follow the above example calling OCILIB C methods directly.

[bhregmi]

Thanks a lot. I was able to complete this project in vapor. It was a small report server for internal use and i managed to complete the backend in Vapor with your help.

Are you planning to add the feature of displaying OCI_Statement any time soon , or any tentative timeline? If its sooner then I will start another project in Vapor else I have to use Python/Django as an alternative . I prefer to make my office dev environment Swifty rather than Pythonic lol .

[iliasaz]

You're welcome! I'm glad my examples and the library helped you with your project. I, too, had done a few small projects with Django, FastAPI, and other Python libs before I came across Swift and Vapor, and I loved Swift/Vapor combo. I am going to continue improving SwiftOracle library. In fact, I'm working on some changes to the data types now as my other project depends on them. However, I cannot commit to any particular timeline as this isn't my day job :-)

[bhregmi]

Kudos to your effort in improving SwiftOracle library and hopefully we will be able to use it in our next project. I will start working in front end with dummy data and if the library gets updated I will use vapor else Django is there. And again Thanks a lot, i wouldn't have completed my project without ur help. Btw do you use Vapor Discord channel , if so may i message you there ?

[iliasaz]

Thanks. I come to vapor discord channel from time to time, you can find me as ilia.sazonov#2059