• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1710
  • Last Modified:

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 :-)
0
MickyMc
Asked:
MickyMc
  • 3
  • 2
2 Solutions
 
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
 
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now