Answer

Calling stored procedures inside user-defined functions in Oracle Database

Could you please answer these questions related to Oracle Database?

    Requires Free Membership to View

  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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: