Home » SQL & PL/SQL » SQL & PL/SQL » How to process a dynamic sql ?
icon5.gif  How to process a dynamic sql ? [message #223513] Fri, 09 March 2007 04:39 Go to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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.
  1. 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?
  2. 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
icon7.gif  Re: How to process a dynamic sql ? [message #223911 is a reply to message #223745] Mon, 12 March 2007 04:42 Go to previous messageGo to next message
SaraM
Messages: 9
Registered: March 2007
Junior Member
Thank you Ross for your helpfull reply!

I found an example here:
http://www.unix.org.ua/orelly/oracle/bipack/ch02_05.htm
in Topic "2.5.4 Displaying Table Contents with Method 4 Dynamic SQL"



Re: How to process a dynamic sql ? [message #223954 is a reply to message #223911] Mon, 12 March 2007 07:13 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Yup. That's what I thought you'd say...../fa/1685/0/
Re: How to process a dynamic sql ? [message #224587 is a reply to message #223954] Wed, 14 March 2007 13:24 Go to previous message
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 Smile, 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!
Previous Topic: Read SYS_REFCURSOR using DBMS_SQL
Next Topic: PL/SQL Error
Goto Forum:
  


Current Time: Sat Dec 03 12:19:12 CST 2016

Total time taken to generate the page: 0.09484 seconds