asked on

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 :-)

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MickyMc

ASKER

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

Sean Stuber

glad we could help

MickyMc

ASKER

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;

Sean Stuber

"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;