Hi,

Greetings!

Requirement : Extract and transpose data from a table and export it into an excel sheet.

I am trying to transpose the data using Pivot. Since the number of columns and rows will vary, therefore I have created the query dynamically.
The query is working fine. I need to export the data into an excel sheet. So I used SPOOL but it does not work when I use 'EXECUTE IMMEDIATE'.

The data in the table is like below :

Create table UDA_VALUES(UDA_ID NUMBER(10), UDA_VALUE NUMBER(10));

INSERT INTO UDA_VALUES (1, 10);
INSERT INTO UDA_VALUES (1, 20);
INSERT INTO UDA_VALUES (1, 30);

INSERT INTO UDA_VALUES (2, 40);
INSERT INTO UDA_VALUES (2, 50);
INSERT INTO UDA_VALUES (2, 60);
INSERT INTO UDA_VALUES (2, 70);

INSERT INTO UDA_VALUES (3, 80);
INSERT INTO UDA_VALUES (3, 90);

And the output is expected as below in the spreasdheet :

1 2 3
-- --- ---
10 40 80
20 50 90
30 60
70
So I would need some guidance on how to export data fetched from a dynamic query. Since this job needs to be executed daily, I have created a shell script where in I connect to SQLPLUS to execute the PL/SQL block and create the query.
In the same block, I am using EXECUTE IMMEDIATE. Before opening the PL/SQL block, I used SPOOL filename.

Hope to get a response soon!!

Thanks !!

set echo off
set feedback off

spool temp.sql
begin
    dbms_output.put_line( 'select * from dual' );
    dbms_output.put_line( '/' );
end;
/
spool off
spool data.csv
@temp
spool off