Data Definition Use SQL Function Return SYS_REFCURSOR

Question

Summary

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

​

Jacobelia S · Answer

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>

Brajesh Shukla-95078 · Answer

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

, '||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;

Brajesh Shukla-95078 · Answer

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