Alpha Software is focused on enabling developers to create robust, data-driven business applications that run on any PC, Tablet or Smartphone in the fastest, most efficient and cost-effective manner possible.

Share this blog:

Friday, March 12, 2010

How to use Oracle stored procedures and functions

One of our customers, Harry Titlbach from Friedrich-Ebert Stiftung in Germany, sent me an interesting question. I passed it over to Kurt Rayner, Alpha's Vice President of Research and Development. He copied me on his technical reply.

Reading that, it occurred to me that Harry's predicament and Kurt's answer could be useful to some of our readers. Here is Harry's original, unedited e-mail followed by Kurt's unedited response.

Hi Richard,

I'm evaluating Alpha Five Version 10 with the 30 day trial (15 days remaining) for
Friedrich-Ebert Stiftung, Bonn, Germany. For information about the "Friedrich-Ebert Stiftung" please look at our homepage http://www.fes.de and http://www.fes.de/sets/s_fes_i.htm.

Starting this year, we have a new project with the goal to give our colleagues abroad better, and direct support to our Oracle 10g database applications.

We still develop with Oracle Forms 6i Client/Server (1000 Forms/Reports) and have a lot of PL/SQL coding with stored Procedures/Functions and Packages in our Oracle database.

For Forms C/S in the Web today we use Windows Terminal Server and Citrix Metaframe. For various reasons we don't use the Oracle options of Web forms 10g/11g, Jdeveloper 11g and Apex yet.

I found Alpha Five on the Web and had the idea that it could help us to build some new C/S and Web solutions. To see if it would make sense to work with Alpha Five and Oracle I did some short trials.

Please look at my question at your Forum: http://msgboard.alphasoftware.com/alphaforum/showthread.php?t=84537, "Oracle stored functions."

Also by reading your documentation, it seems there is today no solution with Version 10. Are there any plans to give support for calls to Oracle Packages in Alpha Five Version 11? i.e.:return_value := PACKAGE_NAME.package_function(param_IN, param_OUT, param_INOUT, ...)

For Return values and Parameters with Oracle types e.g. NUMBER,CHAR,ARRAY,OBJECT,BOOLEAN,REFCURSOR, ...

With kind regards,
Harry Titlbach
IT-Referat
www.fes.de

There's Harry note. Here's Kurt's reply.

Hi Harry,

Sorry for taking an extra day to get back to you on this. I believe you will be pleased by the result.

First, let me clarify that the native interface to Oracle for Alpha Five is Oracle Call Interface and is accessed using the XBasic object SQL::Connection. Active link tables and Web applications access Oracle using this object.

Using SQL::Connection and XBasic, you should be able to execute any just about any SQL allowed through the interface by Oracle. For example, the internals of the Alpha Five database syntax handler for Oracle use it to create tables and indexes, generate Java and PL/SQL stored procedures, create sequences, and add triggers to newly created tables.

Second, there was indeed a bug in both the Oracle and the ODBC drivers that prevented output arguments from being populated correctly. Since this feature is not employed by casual users, it isn’t clear how old the issue is, but it has been corrected and the fix will be available in any patch created and distributed after today.

The problem you describe below can be addressed in one of two ways. You can either use the function in a SELECT statement, or execute the call in a PL/SQL script. The complete XBasic scripts are shown below for both Oracle and SQL Server, but in summary:

The correct syntax to select the result of a scalar function in Oracle is:

?c.Execute(“select COUNT_EMP(:lower, :upper) as l_Count from dual”)
?c.resultset.data("l_count")

Note: This works now.

To retrieve output parameters from a stored procedure or function you can add an argument to the SQL::Arguments object for each output parameter as follows:

dim args as sql::arguments
args.add("l_count", 0, SQL::ArgumentUsage::OutputArgument)
?c.execute("begin :l_count := COUNT_EMP(:lower, :upper); end;", args)
?args[3].Data

Notes:
1. The output argument was not being populated correctly. This has been addressed and will be available shortly in a patch.
2. The value SQL::ArgumentUsage::OutputArgument indicates that the parameter is output only. There is an enumerated value for each of (input, output and input/output).
3. The SQL is wrapped in a BEGIN/END pair. This seems to be required by OCI for the script to execute properly.

Some documents are attached (one, two, three, and four) that may be helpful in exploring the SQL database functionality in Alpha Five.

Going forward, please do not hesitate to report any issues with our interface to Oracle (or the product in general) to Alpha Software using the e-mail address a5v10bugs@AlphaSoftware.com.

Each e-mail sent to this address is forwarded to the entire development team. It is our intention to provide a robust and rich interface to each of the major databases, and knowing each issue you encounter and what you need to accomplish helps us to focus our efforts.

Thank you for letting drawing our attention to this issue. We look forward to hearing from you as you explore Alpha Five Version 10!

Regards,

Kurt Rayner
Vice President - Research and Development
Alpha Software

I can't say whether or not other Web development tool vendors provide this level of personalized support. I am proud to say that we do whenever we can.

If you run up against a wall when you're using our products and you need some help getting over it, please feel free to write to me directly. I'll make sure your question gets to the right person. (Try sending Bill Gates a note about a problem you're having with MS Access.)

1 comments :

Elliot Rosa said...

"Try sending Bill Gates a note about a problem you're having with MS Access." LMAO!

Related Posts Plugin for WordPress, Blogger...