ORA-01747: invalid user.table.column, table.column, or column specification

Question

Get the above error when the execute immediate is called in a loop

Update CustomersPriceGroups set  1AO00=:disc  Where cuno=:cuno
    Parameters:   disc=66 cuno=000974
Update CustomersPriceGroups set  1AP00=:disc  Where cuno=:cuno
    Parameters:   disc=70.5 cuno=000974
Update CustomersPriceGroups set  1AQ00=:disc  Where cuno=:cuno
    Parameters:   disc=66 cuno=000974
Update CustomersPriceGroups set  1ZA00=:disc  Where cuno=:cuno
    Parameters:   disc=60 cuno=000974

What does this mean ?

Here is the code fragment

    c:=PriceWorx.frcPriceListCustomers('020','221');
LOOP
  fetch c into comno,cuno,nama,cpls;
  exit when c%notfound;
  dbms_output.put_Line(cuno);
   g:=priceWorx.frcPriceListItemGroups('020','221');
   d:=priceworx.frcCustomerDiscounts('020','221',cuno);
  loop
    fetch g into comno,cpgs,n;
    fetch d into comno,cpls,cuno,cpgs,stdt,tdat,qanp,disc,src;
    --dbms_output.put(chr(9)||cpgs);
    sQ:='Update saap.CustomersPriceGroups set "'|| trim(cpgs)||'"=:disc '
       || ' Where cuno=:cuno';
    execute immediate sQ using disc,cuno; 
    commit;
    dbms_output.put_line( sQ );
    dbms_output.put_line( chr(9)||'Parameters:   disc='|| disc||' cuno='||cuno);
    exit when g%notfound;
  end loop;
  close g;
  close d;
end loop;

Cerulean

Answer 1

Unquoted identifiers must begin with an alphabetic character (see rule 6 here). You're trying to assign a value to a column with a name starting with a number 1AO00, 1AP00 etc.

Without seeing the table definition for CustomersPriceGroups we don't know if it has columns with those names. If it does then they must have been created as quoted identifiers. If so you'll have to refer to them (everywhere) with quotes, which is not ideal - makes the code a bit harder to read, makes it easy to make a mistake like this, and can be hard to spot what's wrong. Even Oracle say, on the same page:

Note: Oracle does not recommend using quoted identifiers for database object names. These quoted identifiers are accepted by SQL*Plus, but they may not be valid when using other tools that manage database objects.

In you code you appear to be using quotes when you assign sQ, but the output you show doesn't; but it doesn't have the saap. schema identifier either. That may be because you're not running the version of the code you think, but might just have been lost if you retyped the data instead of pasting it - you're not showing the earlier output of c.cuno either. But it's also possible you have, say, the case of the column name wrong.

If the execute is throwing the error, you won't see the command being executed that time around the loop because the debug comes after it - you're seeing the successful values, not the one that's breaking. You need to check all the values being returned by the functions; I suspect that g is returning a value for cpgs that actually isn't a valid column name.

As @ninesided says, showing more information, particularly the full exception message, will help identify what's wrong.

Scrivener

Answer 2

check your query for double comma.

insert into TABLE_NAME (COLUMN1, COLUMN2,,COLUMN3) values(1,2,3);

(there is extra comma after COLUMN2).

---

Update: recently (some people have special talents) i succeed to get same exception with new approach:

update TABLE_NAME set COLUMN1=7, set COLUMN2=8

(second SET is redundant)

Chili

do explain your answer with at least two or more lines. for better understanding. – Tolkan

Thanks. I had put an additional comma before the first closing brace ). Oracle doesn't seem to bother giving good error messages. – Mcdonnell

Thanks, @yurin. You just saved my day. +1. – Derivative

Answer 3

It means that the Oracle parser thinks that one of your columns is not valid. This might be because you've incorrectly referenced a column, the column name is reserved word, or because you have a syntax error in the UPDATE statement that makes Oracle think that something which is not a column, is a column. It would really help to see the full statement that is being executed, the definition of the CustomersPriceGroups table and the full text of the exception being raised, as it will often tell which column is at fault.

Greatgranduncle

In my case, I had a trailing comma , in an insert statement. – Dalton

In my case column name was reserved word. – Musicale

Great how all of these quite different problems create the same error message. It's not as if the parser did not know what went wrong (at which if-check-branch), the engineers were just too lazy to make more specific error messages. – Mcdonnell

Also in my case the column name was a reserved word. Here's what to do about that: #11630466 – Fascinating

Answer 4

In addition to reasons cited in other answers here, you may also need to check that none of your table column names have a name which is considered a special/reserved word in oracle database.

In my case I had a table column name uid. uid is a reserved word in oracle and therefore I was getting this error.

Luckly, my table was a new table and I had no data in it. I was a able to use oracle DROP table command to delete the table and create a new one with a modified name for the problem column.

I also had trouble with renaming the problem column as oracle wouldn't let me and kept throwing errors.

Nereen

Answer 5

if you add a extra "," at the end of the set statement instead of a syntax error, you will get ORA-01747, which is very very odd from Oracle e.g

  update table1 
  set col1 = 'Y', --this odd 1
  where col2 = 123
  and col3 = 456 

Coordination

I also have the same issue. If you refer extra comma end of the set. It will have ORA-01747 in Oracle. – Systemize

Answer 6

You used oracle keyword in your SQL statement

Lineage

Answer 7

And I was writing query like. I had to remove [ and ]

UPDATE SN.TableName 
SET [EXPIRY_DATE] = systimestamp + INTERVAL '12' HOUR, 
WHERE [USER_ID] ='12345'

We recently moved from SQL Server to Oracle.

Gonzales

I feel sorry for you. Sincerely. – Trepang

Answer 8

For me, the issue was due to use to column name "CLUSTER" which is a reserved word in Oracle. I was trying to insert into the column. Renaming the column fixed my issue.

insert into table (JOB_NAME, VERSION, CLUSTER, REPO, CREATE_TS) VALUES ('abc', 169, 'abc.war', '1.3', 'test.com', 'test', '26-Aug-19 04.27.09.000000949 PM')
Error at Command Line : 1 Column : 83
Error report -
SQL Error: ORA-01747: invalid user.table.column, table.column, or column specification

Holocrine

Answer 9

The cause may also be when you group by a different set of columns than in select for example:

select tab.a, tab.b, count(*)
from ...
where...
group by tab.a, tab.c;

Shanan

Answer 10

ORA-01747: invalid user.table.column, table.column, or column specification

You will get when you miss the column relation when you compare both column id your is will not be the same check both id in your database Here is the sample Example which I was facing:

UPDATE TABLE_NAME SET APPROVED_BY='1000',CHECK_CONDITION=ID, WHERE CONSUMER_ID='200'

here Issue you will get when 'CHECK_CONDITION' and 'ID' both column id will no same If both id will same this time your query will execute fine, Check id Id both Column you compare in your code.

Seve

Answer 11

In my case, I had some.* in count. like count(dr.*)

Macneil