CODEWITHSUNDEEP
sqlstored-proceduresplsqloracle-sqldeveloper
Dan
Dan

Reputation: 1884

Executing a stored procedure inside another stored procedure using Select Query

I want to call a stored procedure on every row of a table inside my own stored procedure. In SQL Developer workspace I can simple call it like this:

SELECT my_stored_proc(...)
FROM my_table

However in my stored procedure this doesn't seem to execute:

stmt := 'SELECT my_stored_proc(...) FROM my_table';
EXECUTE IMMEDIATE stmt;

This does work:

DECLARE
l_cursor sys_refcursor;

BEGIN
OPEN l_cursor FOR stmt;
LOOP
  FETCH l_cursor INTO ...;
  EXIT WHEN l_cursor%NOTFOUND;
  my_stored_proc(...);
END LOOP;
...

My question is why is this happening.

Upvotes: 0

Views: 877

Answers (2)

peter.hrasko.sk
peter.hrasko.sk

Reputation: 4141

The answer is simple, yet complicated, and many beginner PLSQL developers seem to not be able to get a grasp on the whole concept of cursors...

Your

SELECT my_stored_proc(...)
FROM my_table

in your SQL Developer executes OK because your SQL Developer opens a cursor for your query, fetches the cursor into your data grid, then closes the cursor.

Your

stmt := 'SELECT my_stored_proc(...) FROM my_table';
EXECUTE IMMEDIATE stmt;

inside a stored procedure fails for the same reason - it opens a cursor for the dynamic SQL, yet it does not have any PLSQL variable to fetch the cursor's results into. If you want to select something, you need to

  • either open a cursor for it, fetch the data into a PLSQL variable in a loop (and do whatever you want to do with the fetched data), then close the cursor,
  • or to select/bulk select the query results into a PLSQL variable (and then do whatever you want to do with the fetched data).

... which is what you did in your third code snippet.

Upvotes: 3

Alex Poole
Alex Poole

Reputation: 191265

The behaviour in your second example is explicitly covered in a prominent note in the documentation:

If dynamic_sql_statement is a SELECT statement, and you omit both into_clause and bulk_collect_into_clause, then execute_immediate_statement never executes. For example, this statement never increments the sequence:

EXECUTE IMMEDIATE 'SELECT S.NEXTVAL FROM DUAL'

Which is essentially what you are doing. Because you don't select the result of your procedure (or, in fact your function) into a variable, the query is not executed, so the function is not called.

As you have multiple rows in your table you can't select into a scalar variable, though you could bulk collect into a collection; or use a cursor as you do in your third example. (Although, if the cursor is being opened with the same statement, you seem to be calling the function twice for each row - once when the query is executed, and then again inside the PL/SQL loop. I'm probably reading too much into your contrived example...)

Upvotes: 4

Related Questions