12 Replies Latest reply: Sep 30, 2013 1:37 PM by 937454 RSS

    Inserting a DML statement as string into a table column

    937454


      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

          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


            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

              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

                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

                  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

                    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

                      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

                        > 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
                          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

                            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
                              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

                                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.