Oracle SQL error

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 ?

yes because i didnt put it. and believe the error is in :columnType. When i change :columnType with string CUTTING, it can update cutting column successfuly. — Chriskonz
– Chriskonz, Commented Apr 17, 2014 at 7:58
You can't bind table or column names; you can only bind data values, and columnType is a column name, not a data value — Mark Baker
– Mark Baker, Commented Apr 17, 2014 at 8:10
@MarkBaker so whats your solution ?? should i put "IF" branches for every column name that is inputted ? — Chriskonz
– Chriskonz, Commented Apr 17, 2014 at 8:11

Dmitry Nikiforov · Accepted Answer · 2014-04-17 08:19:36Z

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.

Collectives™ on Stack Overflow

Oracle SQL error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related