How to extract PLSQL query data to an Excel file from Oracle Database
DECLARE
output utl_file.file_type;
v_data varchar2(20000);
v_file_path varchar2(100);
BEGIN
output := utl_file.fopen('/data/test/DEV/excel'
,'Test.xls'
,'w'
,32000);
owa_sylk.show(p_file => output
,p_query => 'SELECT msi.organization_id
,msi.segment1 item_number
,msi.description item_description
FROM inv.mtl_system_items_b msi
WHERE msi.organization_id = :org_id'
,p_parm_names => owa_sylk.owasylkarray('org_id')
,p_parm_values => owa_sylk.owasylkarray('123')
,p_sum_column =>owa_sylk.owasylkarray('N','N','N') -- which columns should be summed
,p_num_column =>owa_sylk.owasylkarray('N','N','N') -- specify if the column is numeric
);
utl_file.fclose(output);
exception
when others then
DBMS_OUTPUT.PUT_LINE('Others error: '||SQLERRM);
END;
I found the information in the link below
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:728625409049
output utl_file.file_type;
v_data varchar2(20000);
v_file_path varchar2(100);
BEGIN
output := utl_file.fopen('/data/test/DEV/excel'
,'Test.xls'
,'w'
,32000);
owa_sylk.show(p_file => output
,p_query => 'SELECT msi.organization_id
,msi.segment1 item_number
,msi.description item_description
FROM inv.mtl_system_items_b msi
WHERE msi.organization_id = :org_id'
,p_parm_names => owa_sylk.owasylkarray('org_id')
,p_parm_values => owa_sylk.owasylkarray('123')
,p_sum_column =>owa_sylk.owasylkarray('N','N','N') -- which columns should be summed
,p_num_column =>owa_sylk.owasylkarray('N','N','N') -- specify if the column is numeric
);
utl_file.fclose(output);
exception
when others then
DBMS_OUTPUT.PUT_LINE('Others error: '||SQLERRM);
END;
I found the information in the link below
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:728625409049
Comments
Post a Comment