Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 77 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Data Definition Use SQL Function Return SYS_REFCURSOR
Summary
Data Definition Use SQL Function Return SYS_REFCURSOR
Content
Hello,
Is it possible to call a function which returns SYS_REFCURSOR as output to be used in data definition?
I have a function which would return SYS_REFCURSOR and the SQL would be used is the below
SELECT my_func(param) FROM DUAL
The above would return more than one rows. The column heading vary based on the parameter being passed to my_func function. In this case, how can I define <dataStructure> elements in data definition
elements in data definition xml file? Another problem is how can I define headings in template xml file?
Appreciate any insight in this
Answers
-
Hi,
I have handle it dynamically by global temporary table as follow
1. Create GT table wit dummy columns line coloumn1, column2......columnn and one extra column record_dentifier(Header/Line)
2. for header record identifier insert one row which will contains all you headings...that will be tag in rtf
3. for the line insert record identifier as line and insert the data.
4. there will be two sql in data template one will be select column1, columns2.....columnn from gt_table where record_identifier = 'HEADER'
and another will be select column1, columns2.....columnn from gt_table where record_identifier = 'LINE'
Regards,
Brajesh
0 -
Hi ,
You can call package.procedure from beforereport trigger then execute your ref cursor inside the procedure and populate the data into one global temporary table.
Constrict your data template based on that global temporary table.
Regards,
Brajesh
0 -
Constrict your data template based on that global temporary table.
This is where I have one problem, the data from refcursor varies based on the parameter and the column heading will have different names, to be precise the headings are dynamic in nature, it is not static.
So how do I need construct data definition and template headings?
Thanks
0 -
Finally I got my package example where I used..
For Header only one record with status H
lv_dynamic_query := 'INSERT INTO XX_PA_DWLD_FC_ADJ_GT
( PROJECT_NUMBER
, TOP_TASK_NUMBER
, PROJECT_ID
, TOP_TASK_ID
, department_name
, total_fte
, total_baselined_amount
, currency_code
, COL1
, COL2
, COL3
, COL4
, COL5
, COL6
, COL7
, COL8
, COL9
, COL10
, COL11
, COL12
, COL13
, COL14
, COL15
, COL16
, COL17
, COL18
, COL19
, COL20
,Col_type)
VALUES( NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, '||lrec_sel_period_by_date.period_names||'
,NULL,NULL,''H''
)';
EXECUTE IMMEDIATE lv_dynamic_query;
for line multiple records with status L
and in data model you can identify records from status H and L for header and line
FOR lrec_get_res_org1 IN lcu_get_res_org(lrec_forecast_adj_data.project_id
,lrec_forecast_adj_data.task_id
)
LOOP
lrec_sel_total_funding := NULL;
OPEN lcu_sel_total_funding(lrec_forecast_adj_data.project_id
,lrec_forecast_adj_data.task_id
,lrec_sel_period.period_start_date
,lrec_sel_period.period_end_date
,lrec_get_res_org1.resource_assignment_id);
FETCH lcu_sel_total_funding INTO lrec_sel_total_funding;
CLOSE lcu_sel_total_funding;
lv_dynamic_query := NULL;
lv_dynamic_query := 'INSERT INTO XX_PA_DWLD_FC_ADJ_GT
( PROJECT_NUMBER
, TOP_TASK_NUMBER
, PROJECT_ID
, TOP_TASK_ID
, DEPARTMENT_NAME
, TOTAL_FTE
, TOTAL_BASELINED_AMOUNT
, CURRENCY_CODE
, RES_ORG
, COL1
, COL2
, COL3
, COL4
, COL5
, COL6
, COL7
, COL8
, COL9
, COL10
, COL11
, COL12
, COL13
, COL14
, COL15
, COL16
, COL17
, COL18
, COL19
, COL20
,Col_type)
VALUES( '''||lrec_forecast_adj_data.PROJECT_NUMBER
||''' ,'''|| lrec_forecast_adj_data.top_task
||''' ,'|| lrec_forecast_adj_data.PROJECT_ID
||' ,'|| lrec_forecast_adj_data.TASK_ID
||' ,'''|| lrec_forecast_adj_data.department_name
||''' ,'|| NVL(lrec_sel_total_fte.total_fte,0)
||' ,'|| lrec_forecast_adj_data.total_baselined_amount
||' ,'''|| lrec_forecast_adj_data.currency_code
||''' ,'''|| lrec_get_res_org1.res_org
||''' ,'|| lrec_sel_total_funding.burden_cost
||' ,NULL,NULL,''L'')';
bs_debug('FAN',' lv_dynamic_query '||lv_dynamic_query);
EXECUTE IMMEDIATE lv_dynamic_query;
END LOOP;
0 -
My apologies for the late reply, due to other issues I have not been able
to test your approach. I will certainly try and will update you.
On 17 Oct 2017 09:39, "community-admin" <community-prod-admin_ww@oracle.com>
0