Hi all, I am having difficulty in attempting to do something very specific in PL/SQL. I am currently working on an editable Interactive Report screen based on a table. As part of this setup I have created a stored process. I pass through certain values from the screen to the process as follows and set them to variables as follows:
IDVALUE (Number) - Passed ID number of the row being edited
FIELDNAME (Varchar2) - Passed name of the field being edited
FIELDVALUE (Varchar2) - Passed value of the field
I want to run a PL/SQL statement to update the relevant column of the relevant row of the table with the passed value. After reading online I have gathered that to utilise a variable as part of a PL/SQL statement you need to put ' || || ' around it. Below is the update table statement I initially attempted to run:
UPDATE HEADCOUNT_VACANCIES SET ' || FIELDNAME || ' = ' || FIELDVALUE ||' WHERE ID = || IDVALUE;
However this comes back with an invalid user.table.column, table.column or column specification error. I read on another page that if passed variables are used the statement has to be stored as a varchar and then executed. So I tried setting the statement up as a varchar variable and then executing it as follows:
SQLBLOCK := 'UPDATE HEADCOUNT_VACANCIES SET ' || FIELDNAME || ' = ' || FIELDVALUE ||' WHERE ID = ' || IDVALUE';
EXECUTE IMMEDIATE SQLBLOCK;
This comes back with an "encountered the following when expecting one of the following" error. Has anyone used passed variables in PL/SQL before? If so please can you inform me where I am going wrong? In order to attempt to solve the issue, I have cut down the process code to the below, but it still doesn't work:
DECLARE
FIELDNAME VARCHAR(100);
FIELDVALUE VARCHAR(100);
IDVALUE NUMBER;
SQLBLOCK VARCHAR(2000);
BEGIN
FIELDNAME := 'IN_OUT_STATUS';
FIELDVALUE := 'IN';
IDVALUE := 1;
SQLBLOCK := 'UPDATE HEADCOUNT_VACANCIES SET ' || FIELDNAME || ' = ' || FIELDVALUE ||' WHERE ID = ' || IDVALUE';
EXECUTE IMMEDIATE SQLBLOCK;
END;
Any help greatly appreciated.