Reply to: Any PL/SQL experts here?

[darrylmg]

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

[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?

[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

[pjclarke]

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

[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 ...

[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/communi...sql_and_pl_sql)

[pjclarke]

Use the dynamic SQL to collect it into a PL/SQL table. Pass that type as a parameter into the function and have the function pipe the row out.

Uh-huh, that's where my head's at, and pretty much where we came in. Its just that niggling step of INSERTing a variable number of columns into a target 'table', even when the target table is a known and fixed format....

And yet they won't have a database link. I'm sure there's some logic there somewhere.

You'd have thought being a DBA requied a certain capacity for logical thought, wouldn't you? Experience indicates otherwise.

[DirtyDog]

Yes, you can do EXECUTE IMMEDIATE ... BULK COLLECT INTO. The headache is INSERTING into a variable set of columns into the target table (which isn't a physical table, though I can create a PL/SQL table of the required type.)

Data volumes are tiny and will remain so.

Assuming that the target table has a known and fixed number of columns...

Use the dynamic SQL to collect it into a PL/SQL table. Pass that type as a parameter into the function and have the function pipe the row out.

Company standard is to expose data as a PIPELINED function out of a package, this is read by a tool called Data Integrator (Not the Business Objects one, this is by Pervasive Software), which has a direct connectiona to the source and target databases....

And yet they won't have a database link. I'm sure there's some logic there somewhere.

[pjclarke]

Do it as a ref cursor - loop through the dynamic SQL and insert into the target table. You may be able to do a bulk collect, I'm not sure off the top of my head. That's only going to be good if there is a small(ish) data volume to transfer, though.

How do you actually get the output from your system into the new one? Where does the third party tool read it from?

Yes, you can do EXECUTE IMMEDIATE ... BULK COLLECT INTO. The headache is INSERTING into a variable set of columns into the target table (which isn't a physical table, though I can create a PL/SQL table of the required type.)

Data volumes are tiny and will remain so.

Company standard is to expose data as a PIPELINED function out of a package, this is read by a tool called Data Integrator (Not the Business Objects one, this is by Pervasive Software), which has a direct connectiona to the source and target databases....

[DirtyDog]

Do it as a ref cursor - loop through the dynamic SQL and insert into the target table. You may be able to do a bulk collect, I'm not sure off the top of my head. That's only going to be good if there is a small(ish) data volume to transfer, though.

How do you actually get the output from your system into the new one? Where does the third party tool read it from?

[pjclarke]

A view on the history table? Hmmmm, liking it.

[pjclarke]

Thanks for the replies:

Yes, the target table is effectively constant however db links are banned in production, paranoid DBA.

I'd welcome alternate, more elegant approaches, I'm a little rusty. Here's the full requirement, note the database design concepts are not mine, but I have to work with it

There is a staging database which contains (will contain) metadata and data from all feeds into and out of clientco. There's an 'attribute' table that holds metadata on every attribute (field) under control and an attribute value history table that holds the actual values supplied as a series of name/value pairs with date and other stuff. The values are all conveted to VARCHAR.

So if 'balance' is supplied daily by a particular input feed, there will be one row on the attrib hist table for the balance value for each day for each account. Other tables hold data on the sources, targets and mappings between them.

I effectively need to do a pivot: retrieve the values for a set of attributes (attrib hist rows) on a particular date, each selected row becomes a column in the output recordset, and there will be one row per account (I am simplifying slightly).

The set of attributes to return is retrieved from one of the control tables, that is not a problem, but because it is liable to change it means the PIVOT verb is not available, you cannot use a SELECT or a variable in the list of columns to pivot, it requires a hard-coded list, apparently.

So yes, I am generating some SQL on the fly, like

SELECT    AVH.ILIM_ID, 
          AIX.EXTERNAL_ID,  
          MAX(CASE WHEN ATTR.ATTRIB_NAME = 'COUNTRY_CODE' THEN AVH.ATTRIB_VALUE END) AS ISSUE_CNTRY_CD,
          MAX(CASE WHEN ATTR.ATTRIB_NAME = 'CURRENCY_CODE' THEN AVH.ATTRIB_VALUE END) AS LOC_CRRNCY_CD,
          MAX(CASE WHEN ATTR.ATTRIB_NAME = 'TICKER' THEN AVH.ATTRIB_VALUE END) AS TICKER, 
		  [etc]
FROM      data_provider dp, 
          SSDS_ATTRIB_VALUE_HISTORY AVH, 
		  SSDS_ATTRIB ATTR, 
		  ASSET_ID_XREF AIX, 
		  EXTERNAL_ID_TYPE eit 
WHERE     ATTR.ATTRIB_ID = AVH.ATTRIB_ID
AND       DP.PROVIDER_ID = ATTR.PROVIDER_ID 
          [Filters on Date etc...]
GROUP BY  AVH.ILIM_ID, 
          AIX.EXTERNAL_ID

Running that through EXECUTE IMMEDIATE gives me the data I need, the next step is to use that data to sparsely populate the fixed-form table that I need to return.

I can well see the 'Ugh' factor, but can you do better?

[DirtyDog]

I am using dynamic SQL to generate a recordset. It is dynamic because it is part of a metadata driven solution and the columns returned by the query are not known at compile time, and need to be capable of being changed without recompilation.

I don't see how you are going to get around not having to recompile - if the source data columns change, then do the target columns not change as well? Or is the target table something like COL1 VARCHAR2(4000), COL2 VARCHAR2(4000) etc.

Sounds like the perfect situation for Interconnect, but that's going to be expensive for the replacement.

[d000hg]

"Ugh" is my first thought. By "dynamic SQL" do you simply mean you're building query strings on the fly in the main application (or through another query)?

[DirtyDog]

Is the target table constant? If so, then you could create a database link between the two databases to be able to access that second one.