Skip to Main Content
  • Questions

  • Calling a procedure inside a function

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: August 15, 2006 - 1:26 pm UTC

Last updated: November 21, 2022 - 11:59 am UTC

Version: 9.0.2

Viewed 10K+ times! This question is

You Asked

I have a procedure that inserts in to a table test

procedure insert_data as
begin
insert into test values ('test;');
end;

Function get_data return number as
begin
insert_data;
return 1;
end;

When I call the function in the select statement as

select get_data from dual;

I receive the following error.
ORA-14551: Cannot perform aDML operation inside the query.

Is there any way that I can call a procedure inside a function.

and Tom said...

do not call functions from sql just to call a function.

only call functions from sql when you are looking to actually EXTEND the sql language.


SQL> exec dbms_output.put_line( get_data );

or

SQL> variable x number
SQL> exec :x := get_data;


just call the function, do NOT select it.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Call a procedure from a function in a query

Yemi, August 26, 2006 - 9:22 am UTC

Situations arise that you have to call this function from a different calling program understands the select syntax not the exec dbms_...option you gave. We have several PL/SQL reports that i have to convert to web based reports using XML Publisher. XML Publisher does not create datasource via pl/sql as does report builder. I want a situation where i can select a function that will pass these pl/sql(s) into a procedure. This procedure will insert resultsets into a table in the database and at the same time select results from my XML Publisher via query. I cant find a solution to this problem yet. I will be glad if you can help out.

Tom Kyte
August 27, 2006 - 9:06 pm UTC

then your function cannot modify the database state (safely)

I hesitate to mention how you might do this, autonomous tranactions, because they are evil when misapplied.

Call a procedure from a function in a query

Yemi, August 26, 2006 - 9:24 am UTC

Situations arise that you have to call this function from a different calling program understands the select syntax not the exec dbms_...option you gave. We have several PL/SQL reports that i have to convert to web based reports using XML Publisher. XML Publisher does not create datasource via pl/sql as does report builder. I want a situation where i can select a function that will execute a procedure (this procedure will insert resultsets into a table in the database) and at the same time select results from this table in my XML Publisher via query. I cant find a solution to this problem yet. I will be glad if you can help out.

XML source for XML Publisher

Franco, August 28, 2006 - 8:34 am UTC

The XML Publisher reports are created in two steps. The first step can be any process that generate valid XML. So it can also be a procedure (avoiding to insert/select to/from a table, use functions and so on...).

Calling a procedure inside a function

My, November 19, 2022 - 6:04 am UTC

I have the issues the same, but I can not output my result by table.

Can you give me some advices from this isusse?
Connor McDonald
November 21, 2022 - 11:59 am UTC

Post your test case so we can see what you're trying to do
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library