9 Replies Latest reply on Jul 1, 2011 4:19 PM by Keith Jamieson

    How to avoid this ORA-14551 error?

    814030
      I would want to log in a table called T_LOG ( a number, b varchar2(1000)) the values of the parameters of a function f(x number, y varchar2) each time this function is executed. So, I went in the source of the function and made an insert of one row :
      <h3>Insert into T_LOG(a,b) VALUES(x,y);</h3>

      but I also have a select statement where the function f is used, and now, when I run the select statement, I get the error ORA-14551 : cannot perform a dml operation inside a query. Please, how could I log the parameters of the function each time it is used and  be able to do a select statement using this function?
      Thanks.
        • 1. Re: How to avoid this ORA-14551 error?
          Robert Angel
          You cannot, as you should not be performing DML actions inside a function.


          regards,

          Robert.
          • 2. Re: How to avoid this ORA-14551 error?
            Karthick2003
            Oracle allows you to write DML statement like INSERT, UPDATE and DELETE inside a function. But the thing is such functions cannot be used in SQL statement. You can use only them as stand alone functions in your PL/SQL block.

            Said that if you can explain your requirement clearly we can give you a better answer.
            • 3. Re: How to avoid this ORA-14551 error?
              Dbb
              If you are looking for something that it audit the access by select to a table or table rows, use BBDD audit.

              . :-) any help with my english is wellcome :-) .
              • 4. Re: How to avoid this ORA-14551 error?
                814030
                Please, what I want is to be simultaneously able to log the function f parameters' values each time f is executed and be able to do a select statement using this function.
                Augustin UN wrote that I can use audit. But I don't see how to do to log the parameter's values each time this function is executed, using audit.
                Thanks.
                • 5. Re: How to avoid this ORA-14551 error?
                  BluShadow
                  As Robert suggests it would be bad practice to be doing DML inside a function that is being called as part of a query, however, in some rare cases you may want some form of auditing and you can do it using PRAGMA AUTONOMOUS_TRANSACTION as follows...
                  SQL> create table t_log(a number, b number);
                  
                  Table created.
                  
                  SQL> ed
                  Wrote file afiedt.buf
                  
                    1  create or replace function sal_increase(empno number, sal number) return number is
                    2  pragma autonomous_transaction;
                    3  begin
                    4    insert into t_log(a,b) values (empno, sal);
                    5    commit;
                    6    return sal*1.1;
                    7* end;
                  SQL> /
                  
                  Function created.
                  
                  SQL> select empno, sal, sal_increase(empno, sal) as new_sal
                    2  from emp;
                  
                       EMPNO        SAL    NEW_SAL
                  ---------- ---------- ----------
                        7369        800        880
                        7499       1600       1760
                        7521       1250       1375
                        7566       2975     3272.5
                        7654       1250       1375
                        7698       2850       3135
                        7782       2450       2695
                        7788       3000       3300
                        7839       5000       5500
                        7844       1500       1650
                        7876       1100       1210
                        7900        950       1045
                        7902       3000       3300
                        7934       1300       1430
                  
                  14 rows selected.
                  
                  SQL> select * from t_log;
                  
                           A          B
                  ---------- ----------
                        7369        800
                        7499       1600
                        7521       1250
                        7566       2975
                        7654       1250
                        7698       2850
                        7782       2450
                        7788       3000
                        7839       5000
                        7844       1500
                        7876       1100
                        7900        950
                        7902       3000
                        7934       1300
                  
                  14 rows selected.
                  
                  SQL>
                  ... HOWEVER!!!! CAUTION should be taken as you need to understand what an Autonomous transaction is.

                  Essentially, if you consider it as though it's a transaction taking place in a seperate session, then you can see that if the current session were to perform some form of DML itself and then choose to ROLLBACK due to some error or other condition, anything that has been committed as part of the autonomous transaction (and an autonomous transaction MUST contain a commit) cannot itself be rolled back automatically by Oracle. So you could be left with information written that is inconsistent with the rest of your processing.

                  Usually, AUTONOMOUS_TRANSACTION is used for logging or auditing purposes only.
                  • 6. Re: How to avoid this ORA-14551 error?
                    388670
                    Hi,

                    you can use UTL_FILE package to write to a file instead of using a table .

                    The output is ok even in case of rollback.

                    Regards,

                    Agostinho
                    • 7. Re: How to avoid this ORA-14551 error?
                      Billy~Verreynne
                      afmbpt wrote:

                      you can use UTL_FILE package to write to a file instead of using a table .

                      The output is ok even in case of rollback.
                      Yeah.. and performance and usability suffers - and security is compromised.

                      Performance - writing to UTL_FILE cannot be compared to writing to a SQL table using SQL (that is serviced by features like db buffer cache, parallel query, etc).

                      Usability - great, the data is now in a flat file... so, how is that now queried, joined and indexed and used? What about concurrency?

                      Security - the database security stops at the database's border... and exclude database data written to an external container (like a flat file).

                      If you are using UTL_FILE in a database, then you are either doing something wrong, or dealing with legacy stuff. As avoiding an ORA-14551 using UTL_FILE is not legacy, then you must be doing something wrong...
                      • 8. Re: ORA-14551 error?
                        865053
                        “ORA-14551: cannot perform a DML operation inside a query: ORA-06512: at my_pkg, line 259 ORA-01722: invalid number”

                        I've been trying to debug this error for days, but just can not figure out where the problem is. I have a table functions and a procedure created in a package. The procedure calls the function. Each time I run the procedure I get the ORA-14551 error and does nothing. Below is the syntax:

                        FUNCTION get_ee_hours_used_f
                        ( in_from_date IN DATE
                        , in_to_date IN DATE
                        , in_as_of_date IN DATE
                        , in_client_number IN VARCHAR2
                        )
                        RETURN t_ee_hours_used_nt PIPELINED
                        IS

                        --Declare explicit cursors
                        CURSOR v_cur IS
                        SELECT col1,col2,col3,col4,col5,col6 from employee a , dept b
                        where a.dept_id = b.dept_id;

                        BEGIN

                        --Open the cursor
                        OPEN v_cur;

                        --Loop through the cursor results
                        LOOP
                        --Bulk fetch the results with a limit of 500 rows
                        FETCH v_cur BULK COLLECT INTO v_ee_hours_used_aa LIMIT 500;

                        --Exit the loop when no results were returned
                        EXIT WHEN v_ee_hours_used_aa.COUNT = 0;

                        --Loop through the associative array, piping each row
                        FOR v_array_index IN 1 .. v_ee_hours_used_aa.COUNT LOOP
                        v_ee_hours_used_rec := v_ee_hours_used_aa(v_array_index);
                        PIPE ROW(v_ee_hours_used_rec);
                        END LOOP;

                        --Clear the array
                        v_ee_hours_used_aa.DELETE;

                        END LOOP;

                        --Close the cursor
                        CLOSE v_ee_hours_used_cur;

                        --Return to the calling module
                        RETURN;

                        EXCEPTION
                        WHEN OTHERS THEN
                        IF v_ee_hours_used_cur%ISOPEN THEN
                        CLOSE v_ee_hours_used_cur;
                        END IF;
                        v_ee_hours_used_aa.DELETE;
                        RAISE;

                        END get_ee_hours_used_f;

                        ---Procedure
                        PROCEDURE report_csv_p
                        ( in_from_date IN DATE
                        , in_to_date IN DATE
                        , in_as_of_date IN DATE
                        , in_client_number IN VARCHAR2
                        , in_report_title IN VARCHAR2
                        , in_file_name_prefix IN VARCHAR2
                        , in_file_export_directory IN VARCHAR2
                        , out_file_name OUT VARCHAR2
                        , out_record_count OUT PLS_INTEGER
                        ) IS


                        --Declare explicit cursors
                        CURSOR v_report_cur IS
                        SELECT * FROM TABLE(get_ee_hours_used_f(in_from_date, in_to_date, in_as_of_date, in_client_number));

                        BEGIN

                        -- Output the header information

                        --Open the cursor
                        OPEN v_report_cur;

                        --Loop through the cursor and write the details to the file
                        LOOP
                        --Bulk fetch the data into the associative array with a limit
                        FETCH v_report_cur BULK COLLECT INTO v_report_aa LIMIT 500;

                        --Exit when no results are found
                        EXIT WHEN v_report_aa.COUNT = 0;

                        --Loop through the array, construct and write each line of the CSV file
                        FOR v_array_index IN 1 .. v_report_aa.COUNT LOOP
                        v_record_count := v_record_count + 1;
                        v_report_rec := v_report_aa(v_array_index);
                        v_csv_line := v_report_rec.col1||v_report_rec.col2||v_report_rec.col3||v_report_rec.col4
                                       ||v_report_rec.col5||v_report_rec.col6;

                        UTL_FILE.put_line(v_file_id, v_csv_line);

                        END LOOP;

                        --Clear the array
                        v_report_aa.DELETE;

                        END LOOP;

                        --Close the cursor
                        CLOSE v_report_cur;

                        --Flush and close the file
                        UTL_FILE.fflush(v_file_id);
                        UTL_FILE.fclose(v_file_id);

                        --Set the output variables
                        out_file_name := v_file_name;
                        out_record_count := v_record_count;

                        EXCEPTION
                        WHEN TRIP_ER.PKG_EXCEPTION.invalid_path THEN
                        RAISE_APPLICATION_ERROR(SQLCODE, 'Oracle Directory "' || in_file_export_directory || '" not found');

                        WHEN OTHERS THEN
                        IF v_report_cur%ISOPEN THEN
                        CLOSE v_report_cur;
                        END IF;
                        IF UTL_FILE.is_open(v_file_id) THEN
                        UTL_FILE.fclose(v_file_id);
                        END IF;
                        --v_report_aa.DELETE;
                        RAISE;

                        END report_csv_p;

                        Edited by: user8738152 on Jul 1, 2011 9:14 AM

                        Edited by: user8738152 on Jul 1, 2011 9:15 AM
                        • 9. Re: ORA-14551 error?
                          Keith Jamieson
                          Really?? And I can see the answer provided by Blueshadow.

                          @Billy: You could always use an external table over the flat file and query that.

                          Me puts helmet on before Billy whacks me with Lead Pipe. :0