Q

Calling stored procedures inside user-defined functions in Oracle Database

In this expert answer, Brian Peasland explains how to call a stored procedure 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

More on Oracle Database expert answers:

Read more expert answers from Brian Peasland

Submit your own expert question

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.

This was first published in August 2012

Dig deeper on Oracle database performance problems and tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

1 comment

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close