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 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. 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 is contained in my_package. To do that, perform the following steps:

1. Create a standalone procedure with exactly the same name & arguments as the real 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 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 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: 128
Joined: 19 Feb 2013, 20:33



IF you want to ((lose weight) OR (have unbelievable (brain function AND mental clarity))) THEN click:




cron
free counters

eXTReMe Tracker