Oracle SQL错误

Question

I am trying to work with ORACLE PL/SQL variable but I am having a problem with the :columnType variable

my query is

 $doneStatus = 1;
$cuttingUpdateParse = oci_parse($conn, "UPDATE FABRICATION SET :columnType = $doneStatus
                                        WHERE HEAD_MARK = :headmarkToUpdate AND ID = :idToUpdate");

echo 'PASSED VARIABLE COLUMN TYPE: '.$_POST["columnType"].'<br/>';
echo 'PASSED VARIABLE HEADMARK: '.$_POST["headmark"].'<br/>';
echo 'PASSED VARIABLE ID: '.$_POST["headmark_id"].'<br/>';


oci_bind_by_name($cuttingUpdateParse, ":headmarkToUpdate", $_POST["headmark"]);
oci_bind_by_name($cuttingUpdateParse, ":idToUpdate", $_POST["headmark_id"]);
oci_bind_by_name($cuttingUpdateParse, ":columnType", $_POST["columnType"]);

PASSED VARIABLE COLUMN TYPE: CUTTING PASSED VARIABLE HEADMARK: TEST1 PASSED VARIABLE ID: 2

Warning: oci_execute(): ORA-01747: invalid user.table.column, table.column, or column specification in C:\xampp\htdocs\WeltesInformationCenter\update_bar\process_class.php on line 38

The error is when i am inputting :columnType in the sql query. So anybody has a suggestion on how to make the :columnType dynamically change ?

Answer 1

You try to use bind variable to specify column name at runtime - Oracle prohibits this. It needs to know the object name explicitly. Just use concatenation insteand of binding:

SQL> declare
  2   column_name varchar2(100) := 'VALUE#';
  3   id int := 2;
  4   val varchar2(10) := 'XXX';
  5  begin
  6    execute immediate
  7    'update t set :column_name = :column_value where id = :id'
  8    using column_name, val, id;
  9  end;
 10  /
declare
*
error in line 1:
ORA-01747: invalid user.table.column, table.column, or column specification 
ORA-06512: in  line 6 


SQL>   declare
  2   column_name varchar2(100) := 'VALUE#';
  3   id int := 2;
  4   val varchar2(10) := 'XXX';
  5  begin
  6    execute immediate
  7    'update t set '||column_name||' = :column_value where id = :id'
  8    using val, id;
  9  end;
 10  /

PL/SQL procedure completed.