?
Solved

Using variable as column name in update statement

Posted on 2003-03-21
5
Medium Priority
?
1,873 Views
Last Modified: 2012-05-04
hello,

I have a procedure, with the following update statement,

update table1 set VARIABLE_NAME = 1 where id = 3


I get the error "virtual column not allowed here", and if I replace VARIABLE_NAME with the name of one of the columns, it works...

Is there any way to get Oracle to read this and get the VALUE for the VARIABLE_NAME, instead of reading it literally?

ps - this is inside a cursor loop, so it is only returning one variable_name result each time it hits the loop, in case anyone was wondering why I was using a variable here....

thanks in advance for any help!
0
Comment
Question by:tpicone
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 2

Accepted Solution

by:
mszacik earned 1000 total points
ID: 8184676
I would suggest Native Dynamic SQL.  

Here is a quick example

VARIABLE_NAME := field_name_to_update;

sql_stmt = 'update table1 set ' || VARIABLE_NAME || ' = 1 where id = 3';

EXECUTE IMMEDIATE sql_stmt;

There is much more to it such as binding variables etc.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 8193848
Another way is to use the DBMS_SQL package. It is more complex, but gives full control over the dynamic SQL statement. An example:

FUNCTION       CALC ( v_var in varchar2) return number is
 /* || used for calculatng the dynamic fomula value which was defined  || */
 /* || by the user . it takes in the expression to be calcualted and   || */
 /* || gives its numeric value eg if input is '12*12' output is 144    || */
 qry varchar2(2000) := '';
 l_opnamt number(14,2);
 v_cursorid integer;
 v_dummy integer;
 v_amt number(14,2);
 begin
 QRY:=QRY||'SELECT '|| V_VAR||' FROM DUAL';
 -- use DBMS_SQL to get opening balance
  v_cursorid := dbms_sql.open_cursor;
  dbms_sql.parse(v_cursorid,qry,DBMS_SQL.V7);
 -- define output variables
  dbms_sql.define_column(v_cursorid,1,v_amt);
 -- execute the statement
  v_dummy := dbms_sql.execute(v_cursorid);
 -- run through fetch loop
  loop
   if dbms_sql.fetch_rows(v_cursorid) = 0 then
    exit;
   end if;
 -- retrieve rows from buffer into pl/sql variable
   dbms_sql.column_value(v_cursorid,1,v_amt);
  end loop;
 -- close the cursor
  dbms_sql.close_cursor(v_cursorid);
    l_opnamt :=  v_amt;
    return(nvl(l_opnamt,0));
 end ;
0
 
LVL 48

Expert Comment

by:schwertner
ID: 8193865
Starting from Oracle8i one can use the "EXECUTE IMMEDIATE" statement to execute dynamic SQL and PL/SQL statements (statements created at run-time). Look at these examples. Note that statements are NOT terminated by semicolons:
     EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';

     -- Using bind variables...
     sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
     EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;

        -- Returning a cursor...
     sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
     EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;

One can also use the older DBMS_SQL package (V2.1 and above) to execute dynamic statements. Look at these examples:
     CREATE OR REPLACE PROCEDURE DYNSQL AS
       cur integer;
       rc  integer;
     BEGIN
       cur := DBMS_SQL.OPEN_CURSOR;
       DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
       rc := DBMS_SQL.EXECUTE(cur);
       DBMS_SQL.CLOSE_CURSOR(cur);
     END;
     /

More complex DBMS_SQL example using bind variables:
     CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
       v_cursor integer;
       v_dname  char(20);
       v_rows   integer;
     BEGIN
       v_cursor := DBMS_SQL.OPEN_CURSOR;
       DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);
       DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
       DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
       v_rows := DBMS_SQL.EXECUTE(v_cursor);
       loop
         if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
            exit;
         end if;
         DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
         DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);
       end loop;
       DBMS_SQL.CLOSE_CURSOR(v_cursor);
     EXCEPTION
       when others then
            DBMS_SQL.CLOSE_CURSOR(v_cursor);
            raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);
     END;
     /

0
 
LVL 48

Expert Comment

by:schwertner
ID: 8193870
Starting from Oracle8i one can use the "EXECUTE IMMEDIATE" statement to execute dynamic SQL and PL/SQL statements (statements created at run-time). Look at these examples. Note that statements are NOT terminated by semicolons:
     EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';

     -- Using bind variables...
     sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
     EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;

        -- Returning a cursor...
     sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
     EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;

One can also use the older DBMS_SQL package (V2.1 and above) to execute dynamic statements. Look at these examples:
     CREATE OR REPLACE PROCEDURE DYNSQL AS
       cur integer;
       rc  integer;
     BEGIN
       cur := DBMS_SQL.OPEN_CURSOR;
       DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
       rc := DBMS_SQL.EXECUTE(cur);
       DBMS_SQL.CLOSE_CURSOR(cur);
     END;
     /

More complex DBMS_SQL example using bind variables:
     CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
       v_cursor integer;
       v_dname  char(20);
       v_rows   integer;
     BEGIN
       v_cursor := DBMS_SQL.OPEN_CURSOR;
       DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);
       DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
       DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
       v_rows := DBMS_SQL.EXECUTE(v_cursor);
       loop
         if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
            exit;
         end if;
         DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
         DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);
       end loop;
       DBMS_SQL.CLOSE_CURSOR(v_cursor);
     EXCEPTION
       when others then
            DBMS_SQL.CLOSE_CURSOR(v_cursor);
            raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);
     END;
     /

0
 

Author Comment

by:tpicone
ID: 8195483
Perfect, just what I was looking for - thanks, and thanks to everyone else for their input - I just knew there had to be a way....
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question