Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved DW from Stored Procedure

  1. How-to
0
Votes
Undo
  1. Olan Knight
    Olan Knight
  2. PowerBuilder
  3. Monday, 16 November 2020 17:42 PM UTC

PB2019R3
IM V10.5
Oracle 12C
Native connection to Oracle

~~~~~~~~~~~~~~~~~~~~~~~
UPDATE:  As you can probably tell, I have not created a DW from a stored procedure. However, I did find an example writtem by Terry Dykstra on using SPs for DWs. Apparently the trick is to have the SP return a REF CURSOR.
~~~~~~~~~~~~~~~~~~~~~~~

Boss has a script which is in the dw. It returns nothing, but it does update the tables needed for a report.
@extract_master.sql

The report has a retrieval.
select col1, col2, col3
from  table1

UNION

select col1, col2, col3
from  table12
;

What we want is a DW that is populated from the stored procedure, with the result set to be placed into the dw.


Q:  If I remove the SELECT from the report and put it at the bottom of the stored procedure, can I then create a DW that uses this SP to populate the rows? Do I need to create a Manual Result Set?


Thank You!

RESTclient retrieve dw structure
App Crash with MultiThread
Responses (3)
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Wednesday, 18 November 2020 14:31 PM UTC
  2. PowerBuilder
  3. # 1

Hi,

I've attached a file with the SQL of an Oracle stored procedure example which I use as the source of a dw.

 

create or replace procedure SP_DW_TRANS_LIST
( as_item_customer varchar2,
as_item_user varchar2,
cur out sys_refcursor)
IS
begin

delete from cc_temp_translist where item_user = as_item_user;
delete from CC_TEMP_TRANSLIST2 where ITEM_USER = as_item_user;

INSERT INTO CC_TEMP_TRANSLIST
( ITEM_USER,
ITEM_CUSTOMER,
......)
SELECT CC_ITEM.user_id,
CC_ITEM.customer,
CC_ITEM.company,
....
....;

-- we could instead of inserting into cc_temp2, do an update on cc_temp, but that's slower:
INSERT INTO CC_TEMP_TRANSLIST2
( ITEM_USER,
ITEM_CUSTOMER,
ITEM_COMPANY,
.....

MAX_PAYMENT_DATE
)
SELECT item_user,
item_customer,
item_company,
item_trans,
...F_GET_MAX_PAYMENT_DATE(childcustomer, item_trans )
from cc_temp_translist
where item_user = as_item_user;

OPEN cur FOR SELECT ITEM_TAGFLAG,
ITEM_INITFLAG,
ITEM_ACTION,
ACTION_DESCRIPTION,

.......
where item_user = as_item_user;
END SP_DW_TRANS_LIST;

 

 

regards

Attachments (1)
example-oracle-sp-datawindow-select.zip
Comment
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Tuesday, 17 November 2020 02:48 AM UTC
  2. PowerBuilder
  3. # 2

Hi, Olan -

Have you looked at the online documentation?

https://docs.appeon.com/pb2019r2/connecting_to_your_database/ch11s06.html

I don't speak Oracle, but several folks in the Community besides yourself do. Keep asking questions and after Elevate has concluded, I'm sure they will be offering up the benefit of their experiences.

Regards, John

Comment
Olan Knight
  1. Olan Knight
  2. Tuesday, 17 November 2020 14:27 PM UTC
Thank you, John, I have NOT seen the documentation.

I went looking for it a couple of days ago but time constraints and my own lack of knowledge all meant that I did not find what I was seeking.
  1. Helpful
There are no comments made yet.
Andrew Barnes
Andrew Barnes Accepted Answer Pending Moderation
  1. Monday, 16 November 2020 22:44 PM UTC
  2. PowerBuilder
  3. # 3

Q:  If I remove the SELECT from the report and put it at the bottom of the stored procedure, can I then create a DW that uses this SP to populate the rows?

If I am understanding what you are asking, the answer would be yes.  We have a number a stored procs that perform some processing and finish with a SELECT that are used to populate a DataWindow.  When the DW is retrieved, the SP is executed and the result set of the SP's SELECT gets loaded into the DW.

Do I need to create a Manual Result Set?

No.  When you create your DataWindow control, you select a stored procedure as the data source, and the DataWindow Painter figures out the result set for you.

Comment
Olan Knight
  1. Olan Knight
  2. Monday, 16 November 2020 23:57 PM UTC
Wonderful, thank you!

Does the output from the stored procedure need to be a REF CURSOR?

- When we tried a simple SELECT without an INTO, the procedure would not compile.
  1. Helpful
Andrew Barnes
  1. Andrew Barnes
  2. Tuesday, 17 November 2020 00:22 AM UTC
I am using MS SQL Server, but I do not need to use cursors for it to work with PowerBuilder as a DataWindow source. I think the only requirement is that the last part of the SP is a select statement such that when I execute the SP, it gives me a result set.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.