 How to process a dynamic sql ? [message #223513] |
Fri, 09 March 2007 04:39  |
SaraM
Messages: 9
Registered: March 2007
|
Junior Member |
|
|
Hello,
first of all: sorry for my bad english..
Cause I am not able to find a solution for my problem I will ask the experts now!
The situation: our Users write any SQLs and save it into a table. In my Procedure I have to read all this SQLs, execute them and write the result with a special format for every field (also recorded from the user) in a file.
The Problem is how to fetch the columnvalues into variables cause the number and the types are not known at designtime.
Is there any way to read the column values dynamicly ?
Thank you very much for any help !
Oracle-Version: 9 and later
|
|
|
|
| Re: How to process a dynamic sql ? [message #223536 is a reply to message #223513] |
Fri, 09 March 2007 06:44  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Oh oh, bad idea, be prepared for some major headaches.
Oracle Corporation already thought of something like this, you know? They call it sqlplus.
Maybe Discoverer would be a usable alternative.
|
|
|
|
| Re: How to process a dynamic sql ? [message #223745 is a reply to message #223536] |
Sat, 10 March 2007 21:15 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Firstly, I concur with Frank - bad idea. If your company thinks it will save some money by getting you to write a Business Intelligence tool, they are sadly mistaken.
But having said that, I've been doing this for a long time so I know two things for certain.
- You are happy for the mental challenge of writing your own Business Intelligence tool. It's fun, and after all, you wont be paying for it. Why should you listen to good advice?
- Your company has a budget for your salary already. If they want to buy software for a fraction of the price that they pay you, they will have to apply for capital expenditure. That's a lot less fun than writing your own Business Intelligence tool, and hey - you're not complaining - so why should they bother.
So, with all this in mind, we kow you'll keep going until you find out what you want. So here goes:
What you are looking for in PL/SQL is called DBMS_SQL. It permits dynamic SQL where you do not know in advance the number or type of bind variables, or the number and type of returned colums.
In Pro*C (or the other precompilers) it's called Method 4 Dynamic SQL.
This is the hardest type of Dynamic SQL to write. You need to understand it quite well to do it, so I'd start reading the manual if I were you.
Ross Leishman
|
|
|
|
|
|
|
|
| Re: How to process a dynamic sql ? [message #224587 is a reply to message #223954] |
Wed, 14 March 2007 13:24 |
Ivan
Messages: 180
Registered: June 2000
|
Senior Member |
|
|
Look at this post, here:
http://www.orafaq.com/forum/m/124129/42986/#msg_124129
Basically, you'll have to turn every query into a ref cursor (easy), then dynamically step trough each column of every record.
...and God help you  , because I agree with the rest of the guys here. With an approach like that, your department is asking for trouble. Better create a set of reports and let them use it. Add more columns/reports as needed, but don't let your users write queries. Ouch!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
