ORA-01735: invalid ALTER TABLE option - Toad

Question

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.

Lopes

Which version of TOAD and Oracle are you using? When I execute the same syntax, I am able to alter the table. – Marchland

Resolved by using F5 instead of Execute statement button in toad. – Lopes

Sometimes in PLSQL this problem is displayed because of the length limit of the field names or Constraints, which are more than 30 characters long. – Molina

Answer 1

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

Acinus

Answer 2

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.

https://mcmap.net/q/243999/-execute-statement-or-run-script

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

Geneva

Answer 3

try this:

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

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

Equinoctial

Answer 4

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;

Marchland

Answer 5

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

Dacia