Oracle packaged procedure as DW data source


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. a member of a package) as the DataWindow's data source.

If you have selected "Stored procedure" as the data source for your DW, then you see the dropdown with all the stored procedures which exist in the DB. 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 is contained in my_package. To do that, perform the following steps:

1. Create a temporary standalone procedure with exactly the same name & arguments as the real procedure in the package (but doing nothing - we need only the outer interface).
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 in the dropdown).
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 have done the impossible! :o
7. Drop the temporary standalone proc.

Do the same for the procs, used for insert, update and delete (DataWindow painter > menu "Rows" > "Stored Procedure Update").
User avatar
Ursego
Site Admin
 
Posts: 143
Joined: 19 Feb 2013, 20:33



Ketones are a more high-octane fuel for your brain than glucose. Become a biohacker and upgrade yourself to version 2.0!



cron
Traffic Counter

eXTReMe Tracker