Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL Learning (11g)
Dynamic SQL Learning [message #654690] Wed, 10 August 2016 07:15 Go to next message
Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas
Member
Does anyone have a code example or perhaps know a good site that has very simple examples of a package that walks you through building dynamic SQL then executing it?
Re: Dynamic SQL Learning [message #654691 is a reply to message #654690] Wed, 10 August 2016 07:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Irish88 wrote on Wed, 10 August 2016 05:15
Does anyone have a code example or perhaps know a good site that has very simple examples of a package that walks you through building dynamic SQL then executing it?
unwilling or incapable to use GOOGLE yourself?

https://www.google.com/search?q=building+dynamic+SQL+then+executing+it&rlz=1C1CHZL_enUS698US698&oq=building+dynamic+SQL+then+ executing+it


BTW - It is a Bad Thing to deploy dynamic SQL since it is a serious challenge to QA & does NOT scale.
Re: Dynamic SQL Learning [message #654692 is a reply to message #654691] Wed, 10 August 2016 07:49 Go to previous messageGo to next message
Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas
Member
"Unwilling" to look on Google? No and I'm not "incapable" as you imply. I have been looking on google and other places.
Thanks for the tip though about dynamic SQL not scaling.
Re: Dynamic SQL Learning [message #654693 is a reply to message #654692] Wed, 10 August 2016 07:52 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
It not only doesn't scale but NEVER pass arguments into a string that you are executing dynamically without extensive validation on the query. It is too easy to SQL inject if you do that.
Re: Dynamic SQL Learning [message #654694 is a reply to message #654693] Wed, 10 August 2016 08:05 Go to previous messageGo to next message
Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas
Member
Thanks Bill B. I appreciate your reply back; good to know. I read something about Dynamic SQL on the Oracle site a little while back and wanted to investigate it a little bit and perhaps learn more about it. However, I have had no application specs from users requiring dynamic SQL.
Re: Dynamic SQL Learning [message #654695 is a reply to message #654694] Wed, 10 August 2016 08:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Into a single VARCHAR2 variable construct the whole SQL statement.
Use DBMS_OUTPUT to print the string before issuing execute immediate
Cut & Paste the statement into SQL*Plus to see exactly what is wrong & where.
Debug the statement using SQL*PLUS & then correct your SP.
Repeat as many time as necessary.
Re: Dynamic SQL Learning [message #654696 is a reply to message #654690] Wed, 10 August 2016 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 67880
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Database PL/SQL Language Reference
Chapter 7 PL/SQL Dynamic SQL

Re: Dynamic SQL Learning [message #655004 is a reply to message #654696] Thu, 18 August 2016 12:10 Go to previous message
Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas
Member
Thanks! I appreciate the feedback.

[Updated on: Thu, 18 August 2016 12:14]

Report message to a moderator

Previous Topic: ORA-01917 ERROR
Next Topic: Dropping tables in a stored procedure
Goto Forum:
  


Current Time: Sun Jun 13 16:30:01 CDT 2021