[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Cant execute command usiing Execute Immediate in TOAD

Posted on 2008-11-03
6
Medium Priority
?
1,649 Views
Last Modified: 2013-12-19
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
Comment
Question by:MickyMc
  • 3
  • 2
6 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 1000 total points
ID: 22868769
execute immediate is not a SQL statement, it would need to be in a PL/SQL block.

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

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 22868946
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
 

Author Closing Comment

by:MickyMc
ID: 31512798
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 74

Expert Comment

by:sdstuber
ID: 22869960
glad we could help
0
 

Author Comment

by:MickyMc
ID: 22870037
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 22870119
"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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question