OraFAQ Forum: SQL & PL/SQL » Dynamic SQL Learning

Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL Learning (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Dynamic SQL Learning [message #654690]

Wed, 10 August 2016 07:15

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?

Report message to a moderator

Re: Dynamic SQL Learning [message #654691 is a reply to message #654690]

Wed, 10 August 2016 07:21

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.

Report message to a moderator

Re: Dynamic SQL Learning [message #654692 is a reply to message #654691]

Wed, 10 August 2016 07:49

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.

Report message to a moderator

Re: Dynamic SQL Learning [message #654693 is a reply to message #654692]

Wed, 10 August 2016 07:52

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.

Report message to a moderator

Re: Dynamic SQL Learning [message #654694 is a reply to message #654693]

Wed, 10 August 2016 08:05

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.

Report message to a moderator

Re: Dynamic SQL Learning [message #654695 is a reply to message #654694]

Wed, 10 August 2016 08:08

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.

Report message to a moderator