EXECUTE IMMEDIATE self-determines metadata
Answered
Hello,
I have a requirement for an app where I would like to EXECUTE IMMEDIATE a SQL statement that does not have a pre-determined layout (number and/or data types of columns). Given that a regular old SQL statement doesn't need pre-determined layout metadata given to it, and and EXECUTE IMMEDIATE SQL statement is, in the end, just a standard SQL statement, would it not be possible to have the EXECUTE IMMEDIATE not require the AS option in order to use the INTO option... or better yet (and/or also) the ability to return the result set (if there is one) from the EXECUTE IMMEDIATE?
Thanks.
Chris
-
Comment actionsOfficial comment
Dear Chris,
unfortunately this request would be very hard to implement.
You are correct, in the end EXECUTE IMMEDIATE is also just executing SQL. But the difference to our views is that the query can be built up dynamically, so the metadata will only be available during runtime. However, like most relational database systems we actually need to know this metadata at parse time. This is why we can not build up the dependency and metadata for this kind of query without explicitly specifying the structure so that it is available at parse time.
Having said this, we do have request for introducing variables of type "table" to our procedural language. This would allow to use such variable as output for the INTO clause without the explicit structure specification. However, this feature is not scheduled yet due to relatively low interest from other customers. We will add +1 on this feature in our product management system based on your request.
Best,
Matthias -
Hi Matthias,
Every database system I know parses input SQL at runtime and determines the metadata then, then executes the query. I don't see the difference between dynamic sql and sql entered by hand into DV Studio. Both don't know the metadata until parse time. Also, Oracle manages to be able to do an EXECUTE IMMEDIATE without knowing the metadata ahead of time, so it can't really be that hard. However, I understand that it might be an not-oft-used edge-case for you and so would not warrant too much attention...
Thanks though, for looking into it for me. To be honest, it's kind of an once-in-a-blue-moon edge-case for me too. :)
Regards,
Chris
-
Hello,
I guess I'm getting old. I can't seem to find an example in Oracle... Never the less, what I ended up doing was creating a proc that creates a view based on dynamic sql and then calling the view... it would be better to have it all-in-one, but I guess I could live with it.
Thanks a lot.
Chris
Please sign in to leave a comment.
Comments
5 comments