- Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
- Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!
Reply to: Any PL/SQL experts here?
Collapse
You are not logged in or you do not have permission to access this page. This could be due to one of several reasons:
- You are not logged in. If you are already registered, fill in the form below to log in, or follow the "Sign Up" link to register a new account.
- You may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
- If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.
Logging in...
Previously on "Any PL/SQL experts here?"
Collapse
-
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?
Leave a comment:
-
Originally posted by pjclarke View Post
Leave a comment:
-
Well the recordset | have looks like this
Code: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)
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 ...
Leave a comment:
-
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)
Leave a comment:
-
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.
And yet they won't have a database link. I'm sure there's some logic there somewhere.
Leave a comment:
-
Originally posted by pjclarke View PostYes, 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.
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.
Originally posted by pjclarke View PostCompany 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....
Leave a comment:
-
Originally posted by DirtyDog View PostDo 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?
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....
Leave a comment:
-
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?
Leave a comment:
-
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
Code: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
I can well see the 'Ugh' factor, but can you do better?Last edited by pjclarke; 7 January 2014, 12:28.
Leave a comment:
-
Originally posted by pjclarke View PostI 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.
Sounds like the perfect situation for Interconnect, but that's going to be expensive for the replacement.
Leave a comment:
-
"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)?
Leave a comment:
-
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.
Leave a comment:
- Home
- News & Features
- First Timers
- IR35 / S660 / BN66
- Employee Benefit Trusts
- Agency Workers Regulations
- MSC Legislation
- Limited Companies
- Dividends
- Umbrella Company
- VAT / Flat Rate VAT
- Job News & Guides
- Money News & Guides
- Guide to Contracts
- Successful Contracting
- Contracting Overseas
- Contractor Calculators
- MVL
- Contractor Expenses
Advertisers
Contractor Services
CUK News
- Streamline Your Retirement with iSIPP: A Solution for Contractor Pensions Sep 1 09:13
- Making the most of pension lump sums: overview for contractors Sep 1 08:36
- Umbrella company tribunal cases are opening up; are your wages subject to unlawful deductions, too? Aug 31 08:38
- Contractors, relabelling 'labour' as 'services' to appear 'fully contracted out' won't dupe IR35 inspectors Aug 31 08:30
- How often does HMRC check tax returns? Aug 30 08:27
- Work-life balance as an IT contractor: 5 top tips from a tech recruiter Aug 30 08:20
- Autumn Statement 2023 tipped to prioritise mental health, in a boost for UK workplaces Aug 29 08:33
- Final reminder for contractors to respond to the umbrella consultation (closing today) Aug 29 08:09
- Top 5 most in demand cyber security contract roles Aug 25 08:38
- Changes to the right to request flexible working are incoming, but how will contractors be affected? Aug 24 08:25
Leave a comment: