ORA-06512 - “invalid user.table.column, table.column, or column specification”

Question

I have to insert values to my_temp_table. Some columns are stored in the v_ModelsList variable. The v_ModelsList is: Model_7318802U,Model_7304773U,Model_M0347. It is necessary to use a variable with these values because these values may be different, therefore I cannot hardcode them at the insert statement. Below is a piece of code responsible for inserting the values:

EXECUTE immediate 
  'insert into my_temp_table(PRODUCTIONLINENO, WEEKSCHEDULED, SCHEDULEDSTARTS,
                             MOVEOUTS, PERCENTAGEMOVES,:ModelsList)
   values (:PRODUCTIONLINENO, :WEEKSCHEDULED, :SCHEDULEDSTARTS, 
           :MOVEOUTS, :PERCENTAGEMOVES, 1, 2, 3)
  '
USING v_ColumnList, i.PRODUCTIONLINENO, i.WEEKSCHEDULED,
      i.SCHEDULEDSTARTS, i.MOVEOUTS, i.PERCENTAGEMOVES;

When I try to execute I got error: ORA-06512: at line 102 01747. 00000 - "invalid user.table.column, table.column, or column specification"

When I executed it without these columns from valiable everything works correctly.

EXECUTE immediate
  'insert into my_temp_table(PRODUCTIONLINENO, WEEKSCHEDULED, SCHEDULEDSTARTS, 
                             MOVEOUTS,PERCENTAGEMOVES)         
   values(:PRODUCTIONLINENO, :WEEKSCHEDULED, :SCHEDULEDSTARTS, 
          :MOVEOUTS, :PERCENTAGEMOVES)
  '
USING i.PRODUCTIONLINENO, i.WEEKSCHEDULED, i.SCHEDULEDSTARTS,
      i.MOVEOUTS, i.PERCENTAGEMOVES;

Look at how I formatted your code. Use the backticks only for inline code - they don't work well for code blocks. For code blocks, select the text and then CTRL-K or use the code block formatting icon, {}, from the formatting toolbar (at the top of the window when you create or edit your post). — mathguy

mathguy mathguy · Accepted Answer · 2020-11-22T14:58:27

If you want the extra column name(s) to be "variable", you can't use a bind variable for that. This is the reason you must use dynamic SQL in the first place.

Rather:

EXECUTE immediate 
  'insert into my_temp_table(PRODUCTIONLINENO, WEEKSCHEDULED, SCHEDULEDSTARTS,
                             MOVEOUTS, PERCENTAGEMOVES,'
    || v_ColumnList || ')'
   values (:PRODUCTIONLINENO, :WEEKSCHEDULED, :SCHEDULEDSTARTS, 
           :MOVEOUTS, :PERCENTAGEMOVES, 1, 2, 3)
  '
USING i.PRODUCTIONLINENO, i.WEEKSCHEDULED, i.SCHEDULEDSTARTS,
      i.MOVEOUTS, i.PERCENTAGEMOVES;

(Not tested, since you didn't provide a test case; but this is the general idea, even if it's not entirely correct.) Notice where v_ColumnList is used - it is concatenated into the SQL statement, not passed as a bind variable.

Question though: you are adding three hard-coded values at the end: 1, 2, 3. This means that your "column list" will name exactly three columns of your table, and those columns aren't already among the hard-coded ones. (And, they should be of NUNBER data type.) Is that what you are trying to do? If not, then you will need to explain the problem in more details - you will run into new errors with your code.

ORA-06512 - “invalid user.table.column, table.column, or column specification”

1 Answers