Cant execute command usiing Execute Immediate in TOAD

Hi All,

I,ve done this before and this is wrecking my head. I,m in toad in an SQL Editor and want to test some code so I can put in my script. I have to create an index set so I can use cat search. When I tried to use Execute Immediate I kept getting ora----900 error.  So then I tried something simple like

execute immediate 'Select sysdate from dual;'; and got the same error. I have done something similar in packages and run them in toad so my head is wrecked. What am I missing - thanks Mick - wannabee DBA :-)
MickyMcAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
execute immediate is not a SQL statement, it would need to be in a PL/SQL block.

begin
  execute immediate '...';
end;
/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
and, something like

execute immediate 'select sysdate from dual;';

won't work for 2 reaasons:

1- don't put the ";" as part of the statement being executed.

2 - a statement that returns something, like a select, must have an "into" clause to receive the output

0
MickyMcAuthor Commented:
Sorry posters had to split the points after LMAO at how daft I was. Trying to do something to quick when you are rusty. You both cured the 2 issues I had. When I looked at my old code it was in a PL/SQL routine and the select with no into...aahhhhh  :-)

Much appreciated Mick
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sdstuberCommented:
glad we could help
0
MickyMcAuthor Commented:
thanks sbstube.... just one last quick one. Ive hit another similar issue trying to create an index set to use catsearch on. Heres my script if you could look at it....

BEGIN
EXECUTE IMMEDIATE 'CTXSYS.CREATE_INDEX_SET (''IDXSET_RECKEYWDDATEDESC'')';
COMMIT;

Ive also tried CTXSYS.CTX_DLL.Create_Index_set and CTX_DLL.Create_Index_set inside the execute but cant get it to run.

I can create the index alright but not the index -set.  I,m using Toad again. I,ve also tried using quotes around the statement and without them. Any ideas

Begin
Execute immediate ('CREATE INDEX RECORD.IDX_REC_KEYWD_DATE_DESC ON RECORD.RECORD (Keywords) INDEXTYPE IS CTXSYS.CTXCAT');
End;
 
 
0
sdstuberCommented:
"cant get it to run"

what happens?  lock up, error message, machine catches fire, car stalls, Ralph Nader wins election?


In any case, I would try it like this...

begin
   ctxsys.ctx_ddl.create_index_set('IDXSET_RECKEYWDDATEDESC');
end;
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.