Any PL/SQL experts here?

[DirtyDog]

I'm lost - knock up a quick table creation script and data script for someone to work through, and bung it on OTN (https://community.oracle.com/community/developer/english/oracle_database/sql_and_pl_sql)

 

[pjclarke]

Well the recordset | have looks like this

INT_ID  EXTERNAL_ID          ISSUE_CNTRY_CD LOC_CRRNCY_CD ASSET_CRRNCY_CD TICKER (...etc)
14701   10909534             IE             EUR           EUR             IRISH 
46189   103204741            DE             EUR           EUR             DBR    
3908    10909129             NL             EUR           EUR             NETHER 
41955   63212925             LU             EUR           EUR             LGB 
45398   88455125             IE             EUR           EUR             IRISH
...
(etc)

The above recordset has a dynamic set of columns (e.g. next run there may be no TICKER column, or some additional column(s) may appear). I need to create a second recordset from this data, of a known and fixed layout and with a larger number of columns, that is the columns in the first recordset are a subset of those in the second. Columns that are in the second recordset but not the first need top be NULL.

It's the dynamic nature of the columnset that gives me the headache. The answer's probably staring me in the proverbial, but my brain isn't seeing it ...

 

[pjclarke]

Done : https://community.oracle.com/thread/2618239

 

[DirtyDog]

Done : https://community.oracle.com/thread/2618239

First response will be "what database version are you on?" so I'd get that in there before that

 

[DirtyDog]

One last go to try and get my head round it - does (for example) TICKER always go into the same column in the target table? Or is the target table something like COL1, COL2, COL3 etc and it goes into any one of those?

 

[darrylmg]

Can you use a global temporary table to store the output?