Calling stored procedures inside user-defined functions in Oracle Database

Could you please answer these questions related to Oracle Database?

1. Why can't we call a stored procedure inside user-defined functions?
2. Can we create tables inside triggers?

You can call a stored procedure inside a user-defined function. Consider this example:

SQL> create table test_tab (tab_id number);

Table created.

SQL> insert into test_tab values (10);

1 row created.

SQL>  insert into test_tab values (20);

1 row created.

SQL> commit;

SQL> create table test_tab_audit (tab_id number, aud_date date);

Table created.

SQL> create or replace procedure test_proc (id number)

  2  as

  3  begin

  4     null;

  5  end;

  6  /

Procedure created.

SQL> create or replace function test_fn(v_id number)

  2  return number

  3  as

  4  begin

  5     test_proc(v_id);

  6     return v_id+1;

  7  end;

  8  /

Function created.

SQL> select tab_id,test_fn(tab_id) from test_tab;

    TAB_ID TEST_FN(TAB_ID)

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

        10              11

        20              21

Now you are pretty limited as to what you can do in that procedure called by the function if that function is called from a SELECT statement. You cannot perform data manipulation language (DML) or data definition language (DDL) inside a query or you might get one of the following errors:

• ORA-14551: cannot perform a DML operation inside a query
• ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML

But here I call a function that calls a stored procedure just fine:

SQL> create or replace procedure test_proc (id number)

  2  as

  3  begin

  4     insert into test_tab_audit values (id,sysdate);

  5     commit;

  6  end;

  7  /

Procedure created.

SQL> declare

  2     val number;

  3  begin

  4     val:=test_fn(10);

  5  end;

  6  /

PL/SQL procedure successfully completed.

SQL> select * from test_tab_audit;

    TAB_ID AUD_DATE

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

        10 30-JUL-12

As for your question about creating tables inside triggers: Yes, you can do that. Use the EXECUTE IMMEDIATE command.

SQL> CREATE TRIGGER test_tab_trigger

  2  BEFORE INSERT ON test_tab

  3  FOR EACH ROW

  4  BEGIN

  5     EXECUTE IMMEDIATE 'create table another table (id number)';

  6  END;

  7  /

Trigger created.

But the better question is should you create a table inside a trigger? It is normally a bad idea to create a table dynamically. Your application's tables should be pre-created. In addition, every INSERT, UPDATE, or DELETE that causes the trigger to fire will take a longer time to complete because you will have to wait for the table to be created before the DML command will finish. It is normally a bad idea to code such a thing in a trigger.