Page 1 of 1

Oracle packaged procedure as DW data source

PostPosted: 19 Feb 2013, 21:40
by Ursego
This tip can be interesting only for programmers building PB applications against Oracle RDBMS. I will explain how to use not-standalone stored procedures (i.e. a member of a package) as DataWindow's data source.

If you have selected "Stored procedure" as the data source for your DW, you see a dropdown of stored procs, available to the application. But, unfortunately, the dropdown contains only standalone procs, not packaged ones. :evil: To use a SP from a package, we will cheat PowerBuilder! :lol: Let's say, we want to use my_proc which exists in my_package. To do that, perform the following steps:

1. Create a standalone procedure with exactly the same name, arguments (including OUT REF_CURSOR / OUT SYS_REFCURSOR) and returned record set as the procedure in the package.
2. Select that standalone proc in the dropdown to be the data source of your DW (reconnect to the database if it doesn't appear).
3. Save the DW.
4. Open the DW's source (right click -> "Edit Source").
5. Find my_proc and add my_package. (with the dot!) just before it, so the result is my_package.my_proc.
6. Save the DW. Good job, you did the impossible! :o
7. Drop the standalone proc.