Re: Execute immediate

>>>>> "Adrien" == Adrien <Adrien_at_nospam.com> writes:

 Adrien> Hello,

 Adrien> If I execute a stored function in SQLPlus :

 Adrien> variable a number; BEGIN :a := MyFunction ( NULL ); END; /

 Adrien> It works.

 Adrien> But the same statement in an 'EXECUTE IMMEDIATE' statement in  Adrien> PL/SQL :

 Adrien> EXECUTE IMMEDIATE 'variable a number; BEGIN :a :=  Adrien> AlimGCE_CONT ( NULL ); END;';

 Adrien> doesn't work => ORA-00900: invalid SQL statement

 Adrien> Is it normal ?

 Adrien> Thanks in advance.  -- Ce message a ete poste via la
 Adrien> plateforme Web club-Internet.fr This message has been posted
 Adrien> by the Web platform club-Internet.fr

 Adrien> http://forums.club-internet.fr/

The problem is that the declaration

variable a number

is not SQL or PL/SQL. This is a SQL*Plus host variable declaration.

Also, just a tip (and my opinion), don't use execute immediate unless you have to. You really only need it for DDL and sometimes some very dynamic DML (though most of the time, there are alternatives like ref_cursors etc). Your code will be more efficient if you can code it in a way that avoids execute immediate. I only mention this as I've been seeing an increased number of PL/SQL funcitons/packages using execute immediate for simple DML which could be easily done with just a cursor or straight sql.

Tim

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!