Oracle packaged procedure as DW data source

Share this topic:

Link to this posting

Postby Ursego » 19 Feb 2013, 21:40

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. "living" in packages) 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, it contains only standalone procs, not packaged ones. To use a SP from a package, we will cheat PowerBuilder! Let's say, we want to use my_proc which exists in my_package. To do that, perform the following steps:

1. Create a temporary standalone procedure with exactly the same name, arguments and records set (returned via REF_CURSOR).
2. Select that 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 a dot!) just before it, so the result is my_package.my_proc.
6. Save the DW. Good job, you've done that!
7. Drop the temporary standalone proc.
User avatar
Site Admin
Posts: 112
Joined: 19 Feb 2013, 20:33

Return to Tips and Tricks

Who is online

Users browsing this forum: No registered users and 1 guest

Oracle packaged procedure as DW data source

Share this topic:

If you think that this site is not too bad, please LIKE it in Facebook. Thanks!

free counters

eXTReMe Tracker