PL/SQL Select and Update statement inside OPEN - FOR

Eltac Shikhsaidov 0
Viewed 22

So I have a pl/sql function and I want to select and update table inside open for statement It looks like this:

table_a

id | status | document_id |

create or replace function a(p_document_id in number)
    return sys_refcursor
    is
    result sys_refcursor;
begin
    open result for
        select id
        from table_a t
        where t.document_id = p_document_id;

    update table_a t
    set t.status = 1
    where id in (select id
        from table_a t
        where t.document_id = p_document_id);

    return result;

end;
/

But it is not working. Is there some method to do this? thanks in advance

plsql oracle
1 Answers

This is a sample table:

SQL> select * from table_a;

        ID DOCUMENT_ID     STATUS
---------- ----------- ----------
         1         100          0

Function you wrote compiles, but doesn't work because functions - normally - don't do DML:

SQL> select a(100) from dual;
select a(100) from dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SCOTT.A", line 11


SQL>

It, though, would work from PL/SQL, e.g.

SQL> declare
  2    l_rc sys_refcursor;
  3  begin
  4    l_rc := a(100);
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select * from table_a;

        ID DOCUMENT_ID     STATUS
---------- ----------- ----------
         1         100          1

SQL>

But, if you want to be able to call the function from PL/SQL, it has to be an autonomous transaction, which also means that you have to commit within:

SQL> rollback;

Rollback complete.

SQL> create or replace function a(p_document_id in number)
  2      return sys_refcursor
  3  is
  4      result sys_refcursor;
  5      pragma autonomous_transaction;             --> this
  6  begin
  7      open result for
  8          select id
  9          from table_a t
 10          where t.document_id = p_document_id;
 11
 12      update table_a t
 13      set t.status = 1
 14      where id in (select id
 15          from table_a t
 16          where t.document_id = p_document_id);
 17
 18      commit;                                     --> this
 19
 20      return result;
 21  end;
 22  /

Function created.

Let's try it:

SQL> select a(100) from dual;

A(100)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

        ID
----------
         1


SQL> select * from table_a;

        ID DOCUMENT_ID     STATUS
---------- ----------- ----------
         1         100          1

SQL>

This might, or might not be OK. Autonomous transactions can be tricky and we usually use them for logging purposes, so that their commit doesn't affect the main transaction.

Littlefoot0
Related