Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL ??
Dynamic SQL ?? [message #262928] Tue, 28 August 2007 09:22 Go to next message
CocoPop
Messages: 12
Registered: August 2007
Location: England
Junior Member
Hey guys and girls, I was just trying to delete from a table while also using user input.

Now if i was writing a a basic block and not compiling it into a procedure or a function etc then I could do something along the lines of :

v_App := '&Var';

but if I wanted to create it as a procedure to call over and over I thought mabey I could use Dynamic SQL in some way or other, I've tried several things but can't get any of them to work.....

Any ideas ?

thanks
Re: Dynamic SQL ?? [message #262930 is a reply to message #262928] Tue, 28 August 2007 09:44 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
I've tried several things but can't get any of them to work.....
Show us. Maybe it just needs a slight 'tweak' and it'll give us a much clearer idea of what you need
Re: Dynamic SQL ?? [message #262934 is a reply to message #262928] Tue, 28 August 2007 09:52 Go to previous messageGo to next message
CocoPop
Messages: 12
Registered: August 2007
Location: England
Junior Member
Ok well I've never done dyanmic SQL before but as I understand it it remains uncompiled in the Procedure until run time so I have tried this

declare

v_TempVar varchar2(50);

begin

v_TempVar := '&Temp';

execute immediate v_TempVar;

in hope that it would read it as SQL bu it didn't so I tried also to make TempVar into this string

v_SQL := 'Select "&Temp" into v_TempVar from Dummy'

and then execute immediate v_SQL

but alas no it didn't like it, but to be fair I was just trying anything so...... I'm stuck

thanks again anyone who can help.
Re: Dynamic SQL ?? [message #262951 is a reply to message #262928] Tue, 28 August 2007 10:30 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Can I ask a silly question ..

Why are you trying to do this with dynamic sql ? It seems like overkill.

Just curious.

Re: Dynamic SQL ?? [message #262957 is a reply to message #262928] Tue, 28 August 2007 10:43 Go to previous messageGo to next message
CocoPop
Messages: 12
Registered: August 2007
Location: England
Junior Member
Your very correct it's a great big waste of time Razz I just wondereed if it could be done.

That and the guy sitting over from me said I couldn't do it grrrr Anyways looks like I can't so :'(

thanks though, if anyone does know how ot do it thought I would appreciate the help

thank you
Re: Dynamic SQL ?? [message #262963 is a reply to message #262928] Tue, 28 August 2007 11:14 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
It is possible however it is slower and may cause SQL injection.
You should not call dynamic SQL unless it is the only way.
For example, it is not wise to give access to EXECUTE IMMEDIATE 'anything' for application end user.

Just to put you on the right direction:
EXECUTE IMMEDIATE is a PL/SQL statement, so it has to be called in PL/SQL context (anonymous block (BEGIN END), stored procedure, SQL*Plus EXEC statement).
However bear in mind, you cannot use substitution variable (&temp.) in the stored procedure (it will be substituted during its creation); you shall pass the sql command as a parameter.
As stated before, it shall not be done, especially EXECUTE IMMEDIATE 'any_passed_string'.
Re: Dynamic SQL ?? [message #263033 is a reply to message #262963] Tue, 28 August 2007 17:26 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Since it is all about pride and proving that it can be done...
SET SERVEROUT ON
DECLARE
  v_TempVar varchar2(50);
  v_SQL     varchar2(100);
BEGIN
  v_SQL := '&temp';

  EXECUTE IMMEDIATE v_SQL INTO v_TempVar;

  DBMS_OUTPUT.PUT_LINE(v_TempVar);
END;
/

Enter value for temp: SELECT ''SOME VALUE'' FROM dual
old   5:   v_SQL := '&temp';
new   5:   v_SQL := 'SELECT ''SOME VALUE'' FROM dual';
SOME VALUE

PL/SQL procedure successfully completed.


You were close, but as others stated, just because you can doesn't mean you should
Re: Dynamic SQL ?? [message #263163 is a reply to message #262928] Wed, 29 August 2007 04:30 Go to previous message
CocoPop
Messages: 12
Registered: August 2007
Location: England
Junior Member
Thanks guys Very Happy

Previous Topic: PL/SQL - Circular dependencies
Next Topic: Adding array values to an array
Goto Forum:
  


Current Time: Sat Dec 10 20:17:58 CST 2016

Total time taken to generate the page: 0.08993 seconds