This discussion is archived
12 Replies Latest reply: Sep 30, 2013 11:37 AM by 937454 RSS

Inserting a DML statement as string into a table column

937454 Newbie
Currently Being Moderated


Hi,

 

Is there a way I can insert a dml statement (like "UPDATE A SET COL1 = 10") into a table column using a procedure or function.

 

I can do it by simply running an INSERT statement. But how can I do it through a proc or function without getting an error "cannot perform a DML operation inside a query"

Please advice

  • 1. Re: Inserting a DML statement as string into a table column
    SomeoneElse Guru
    Currently Being Moderated

    Sure.

     

    SQL> create table t(v varchar2(100));

     

    Table created.

     

    SQL> create procedure p as

      2  begin

      3     insert into t(v) values ('update a set col1 = 10');

      4  end;

      5  /

     

    Procedure created.

     

    SQL> exec p;

     

    PL/SQL procedure successfully completed.

     

    SQL> select * from t;

     

    V

    --------------------------------------------------------------

    update a set col1 = 10

     

  • 2. Re: Inserting a DML statement as string into a table column
    937454 Newbie
    Currently Being Moderated


    Thank you, but If I do something similar using a function like below:

     

    create table t(v varchar2(100));

    create function func_a

    return varchar2

    as

    begin

         insert into t(v) values ('update a set col1 = 10');

    end;

    /

     

    select func_a from dual;

     

    ERROR:

    ORA-14551: cannot perform a DML operation inside a query

     

    Does it mean we cannot do the same using a function or can it be done in some other way with a function.

  • 3. Re: Inserting a DML statement as string into a table column
    blacknightawake Newbie
    Currently Being Moderated

    Hi, you can do that by executing dynamic SQL, here is an example:

     

    CREATE OR REPLACE PROCEDURE raise_emp_salary (column_value NUMBER,

      emp_column VARCHAR2, amount NUMBER) IS

      v_column VARCHAR2(30);

      sql_stmt VARCHAR2(200);

    BEGIN

    -- determine if a valid column name has been given as input

      SELECT COLUMN_NAME INTO v_column FROM USER_TAB_COLS

      WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = emp_column;

      sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE '

      || v_column || ' = :2';

      EXECUTE IMMEDIATE sql_stmt USING amount, column_value;

      IF SQL%ROWCOUNT > 0 THEN

      DBMS_OUTPUT.PUT_LINE('Salaries have been updated for: ' || emp_column

      || ' = ' || column_value);

      END IF;

      EXCEPTION

      WHEN NO_DATA_FOUND THEN

      DBMS_OUTPUT.PUT_LINE ('Invalid Column: ' || emp_column);

    END raise_emp_salary;

    /

     

    Please let me know if this resolves the issue, regards!

  • 4. Re: Inserting a DML statement as string into a table column
    937454 Newbie
    Currently Being Moderated

    Actually it can be done through a procedure. I was trying to see if I can do it through a function.

  • 5. Re: Inserting a DML statement as string into a table column
    GPU Explorer
    Currently Being Moderated

    You should declare the transaction as AUTONOMOUS_TRANSACTION.

     

    SQL> select * from v$version;

    BANNER                                                                         
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production   
    PL/SQL Release 11.2.0.2.0 - Production                                         
    CORE 11.2.0.2.0 Production                                                     
    TNS for Linux: Version 11.2.0.2.0 - Production                                 
    NLSRTL Version 11.2.0.2.0 - Production                                         

    SQL> drop table t;

    Table dropped.

    SQL> create table t(v varchar2(100));

    Table created.

    SQL> CREATE or replace FUNCTION func_a
      2     RETURN VARCHAR2
      3 
      4  AS
      5     PRAGMA AUTONOMOUS_TRANSACTION;
      6     v_sql   VARCHAR2 (50);
      7  BEGIN
      8     v_sql := 'update a set col1 = 10';
      9 
    10     INSERT INTO t (v)
    11          VALUES (v_sql);
    12   commit;
    13     RETURN v_sql;
    14  END;
    15  /

    Function created.

    SQL>
    SQL> select func_a from dual;

    FUNC_A                                                                         
    --------------------------------------------------------------------------------
    update a set col1 = 10                                                         

    SQL> spool off;

     

    Thanks,

    GPU

  • 6. Re: Inserting a DML statement as string into a table column
    Mike Kutz Expert
    Currently Being Moderated

    Why would you want to use a function?

    What would be the purpose of the return value?

  • 7. Re: Inserting a DML statement as string into a table column
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

     

    937454 wrote:

     

    Actually it can be done through a procedure. I was trying to see if I can do it through a function.

    Sure, you can do it through a function; you just can't call that function from a SQL statement.

    If the function returns a VARCHAR2, then you might call it like this:

     

    DECLARE

        foo   VARCHAR2 (100);

    BEGIN

        foo := fun_a;

    END;

     

    or like this:

     

    BEGIN

        dbms_output.put_line (fun_a);

    END;

  • 8. Re: Inserting a DML statement as string into a table column
    SomeoneElse Guru
    Currently Being Moderated

    > You should declare the transaction as AUTONOMOUS_TRANSACTION.

     

    A horrific idea.

     

    What if the calling transaction does a rollback?

  • 9. Re: Inserting a DML statement as string into a table column
    rp0428 Guru
    Currently Being Moderated
    Please advice

    You already are being advised in this other thread:

    https://forums.oracle.com/thread/2585432

     

    Using a different id doesn't hide the duplication. Please don't create duplicate threads.

     

    You need to follow the advice that you have been given in the other thread. There are NO shortcuts!

  • 10. Re: Inserting a DML statement as string into a table column
    937454 Newbie
    Currently Being Moderated

    Read twice before you start commenting. That other thread does not belong to me.

  • 11. Re: Inserting a DML statement as string into a table column
    rp0428 Guru
    Currently Being Moderated
    Read twice before you start commenting

    You should follow your own advice. As I said:

    Using a different id doesn't hide the duplication

  • 12. Re: Inserting a DML statement as string into a table column
    937454 Newbie
    Currently Being Moderated

    This guy needs some real help

     

    I folllowed that thread and tried to resolve it, in the process, I came across similar errors. So I created my own thread, to get my doubts answered Mr SHERLOCK.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points