| EXECUTE IMMEDIATE Command (Oracle 8i) [message #64826] |
Tue, 17 February 2004 03:26  |
Patrick Tahiri
Messages: 119
Registered: January 2004
|
Senior Member |
|
|
Hi,
I have just started as a DBA in a telecom company, and I can see many times in procedures (inside packages) the EXECUTE IMMEDIATE command with the USING option to update, insert some tables or other look-up tables. Some of these tables have 200 000 rows and 2 tables have more than 3 million rows.
We are expreriencing some performance problems and I have been attributed the task to improve the packages code!
I'm wondering at performance level if using such commands (DML) inside an EXECUTE IMMEDIATE (UPDATE, INSERT) is good or bad!?? Isn't better at performance level to use the DML as it is inside the procedure (inside the package) without the EXECUTE IMMEDIATE ... USING..?
Does the DML (SELECT, INSERT; UPDATE, DELETE) embeded in an EXECUTE IMMEDIATE ... USING makes use of the indexes on the targeted tables? Or we have always a table scan with EXECUTE IMMEDIATE??
Thank you for your precious help and tips.
Regards,
Patrick Tahiri.
PS: I'm using Oracle 8i
|
|
|
|
|
|
| Re: EXECUTE IMMEDIATE Command (Oracle 8i) [message #64834 is a reply to message #64826] |
Tue, 17 February 2004 09:26  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Patrick,
if you are using static sql, then you dont need( and preferably shouldnt) to use Execute immediate. Use Execute Immediate for Dynamic SQL.
Yes,DML inside of EXECUTE IMMEDIATE should use available indexes.Eventually its the same optimizer that devices the execution plan.
-Thiru
|
|
|
|
| Re: EXECUTE IMMEDIATE Command (Oracle 8i) [message #64836 is a reply to message #64834] |
Tue, 17 February 2004 21:58 |
Patrick Tahiri
Messages: 119
Registered: January 2004
|
Senior Member |
|
|
Hi Thiru,
Thank you for all your support these last days!!
I'm quite busy as I have several tasks to be finish with asap here...
I find just now the time to say thank you for all the tips and theory you gave to me!! Very useful!!
I always look first in my Oracle documents and on forums, but it's not always "exactely" what I'm looking for! Sometimes I'm looking for some technics or "cook" technics to resolve some specific problems and I can't find it neither in books or on the net!
I'm feeling comfortable only when I fell that I understood deeply, generally speaking, the concepts, methods that I'm applying to my servers! And not only applying what it's written and advised without understanding more than that...
And you give me that + !!! GREAT!!
Best regards,
Patrick Tahiri.
|
|
|
|
| Re: EXECUTE IMMEDIATE Command (Oracle 8i) [message #65278 is a reply to message #64830] |
Sat, 17 July 2004 00:18 |
pk
Messages: 12
Registered: September 2000
|
Junior Member |
|
|
dear sir,
i am executing below code :
DECLARE
v_sql VARCHAR2(100);
v_date DATE;
BEGIN
v_sql := 'SELECT Sysdate FROM dual';
EXECUTE IMMEDIATE v_sql INTO v_date;
END;
It giving the error :
ERROR at line 6:
ORA-06550: line 6, column 11:
PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following:
:= . ( @ % ;
ORA-06550: line 7, column 1:
PLS-00103: Encountered the symbol "END" The symbol "END" was ignored.
Pls tell me <execute immediate> command execute on oracle8i, if yes then tell me solution. how i can use it
thanks,
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
