I have just found (oh thank you Google and the Oracle forums) the solution to my PL/SQL error
CA_ADD_DESIGN_1 (28,5): PL/SQL: ORA-01747: invalid user.table.column, table.column, or column specification
My first problem was which line does the 28 refer to, as I’m editing one large PL/SQL file, it appears to count from the line before the
CREATE OR REPLACE
PROCEDURE ca_add_design_1(
But the real problem was what did the error mean.
The third Google result had a Oracle forum post, so I should have gone there first, but I didn’t. The first two sites are just AdSense whore sites, in fact 6 of the first 10 results are AdSense whores. It makes me sad that these sites are there, as the information contain was worthless, and been top Google results means they will get a lot of hits. GRRR and for bonus points these results were from google.co.nz, using google.com the useful answer is in 5th place.
Anyway the Oracle forum answer said “you have an extra comma after your lass SET clause before the WHERE” and low and behold I did.
UPDATE ca_p_design
SET design_type_id=NVL(pDesignType,design_type_id),
session_id=NVL(SessionID,session_id),
time_end=NVL(pTimeEnd,time_end)**, **
WHERE design_id=ID;
So now my Update looks like:
UPDATE ca_p_design
SET design_type_id=NVL(pDesignType,design_type_id),
session_id=NVL(SessionID,session_id),
time_end=NVL(pTimeEnd,time_end)
WHERE design_id=ID;
and it works! I had spent ages triple checking each of my column names, and it turned out to be the column I “didn’t mention” that was upsetting it.