How to import data from a PL / SQL package into SPSS?

Question

I have this huge Oracle package that generates a .CSV file after executing a traditional ETL process. I am currently using a bunch of staging tables to load data from various sources and convert the data using the listagg function and ultimately dumping them like clob. I am looking at columns 300-400 with various data types.

What are we doing?

• Export to a .CSV file and then import to SPSS ... This requires a lot of work to import data and configure formatting in SPSS

What am I looking for?

• Save all the data in the form of a table in my database ... Problem: the data will be constantly changing, and the data in the table should be based on the session / transaction. In other words, old data needs to be truncated every time a new transaction is executed.

• Create a metadata file for the columns in the existing .CSV file ... Preferred Solution

• Generate a .SAV file (using the package) ... I examined this option, but no luck

I am looking for the best way to import data from an Oracle package into an SPSS program. If other options are available, I would be happy to explore this.

Answer 1

, SPSS / , , , CSV XML, "XMLTOSPSS". Oracle XML dbms_xmlquery.

CSV Oracle :

select
  c.column_name,
from all_tab_columns c
where c.owner=upper('{owner}') and c.table_name=upper('{table}')
order by c.owner, c.table_name, c.column_id;