ORA-01735: invalid ALTER TABLE option - Toad

When i execute below SQL in Toad its throws "ORA-01735: invalid ALTER TABLE option".

ALTER TABLE CALCULATE ADD (CAL_METHOD VARCHAR2(50), REMARKS VARCHAR2(500));

But when execute in SQL Developer its running successful, Is there any issue with SQL / Toad. Please advice me.

5 Answers

In TOAD I suppose, you need to execute it as a script (press F5) rather that running it as a statement.

Your SQL is correct but the problem is TOAD restrict Statements and Scripts for each button. I assume the cause of the error is you are trying to run ALTER TABLE command using Execute Statement or F9 key. First of all, let's see what is different between Statement and Script

Execute Statement will give you a list of all the results in a sortable table. It will also only run the statement under the cursor (or highlighted). You will be prompted for bind variables when you run the statement (any placeholder: in front of it).

E.g.

select * from customers where customer_id = :id

will prompt for a value for id

Execute Script will execute all statements in the worksheet, and give a text readout of the results. It will not prompt you for the values of bind variables.

As you can understand ALTER TABLE returns only a text output. So you have to use Execute as Script or F5

try this:

ALTER TABLE CALCULATE ADD (CAL_METHOD VARCHAR2(50));
ALTER TABLE CALCULATE ADD ( REMARKS VARCHAR2(500));

I am using TOAD 11.0.6 and Oracle database version is 11gR2

See the below scripts which I am able to execute by F5 or by clicking the green arrow as shown in the enclosed screenshot

CREATE TABLE calculate (col NUMBER);
ALTER TABLE calculate ADD (cal_method VARCHAR2(50), remarks VARCHAR2(500));
SELECT * FROM calculate;

I end up drop the column first then add back

ALTER TABLE SITE_NUMBER DROP COLUMN CREATOR_ID;
ALTER TABLE SITE_NUMBER ADD (CREATOR_ID varchar2(12))