Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Using variable as column name in update statement

Posted on 2003-03-21
5
Medium Priority
?
1,875 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
  • 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month10 days, 3 hours left to enroll

571 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