OSB 12c管道服务调用组件中调用存储过程报错求助

Hey there, I’ve run into this exact error before when working with OSB 12c and PL/SQL stored procedures—let’s break down how to fix it.

What’s Causing This Error? #

The PLS-00653: Aggregate/table functions are not allowed in PL/SQL scope error pops up when you try to use an aggregate function (like SUM(), COUNT(), AVG()) or a table-valued function directly within a PL/SQL block context, instead of wrapping it inside a SQL statement. PL/SQL doesn’t let you call these functions directly for variable assignments or standalone execution—they have to live within a SELECT (or INSERT/UPDATE/DELETE) statement.

Step-by-Step Fixes #

• Check your stored procedure’s internal logic
  Go through the PL/SQL code of the stored procedure you’re calling. If you see lines like this:

  DECLARE
    v_total_sales NUMBER;
  BEGIN
    v_total_sales := SUM(sales.amount); -- ❌ Wrong: Aggregate function in PL/SQL scope
  END;
  

  Rewrite it to wrap the aggregate function in a SELECT INTO statement:

  DECLARE
    v_total_sales NUMBER;
  BEGIN
    SELECT SUM(sales.amount) INTO v_total_sales FROM sales; -- ✅ Correct: Aggregate in SQL scope
  END;
  

• Verify OSB’s parameter mapping
  Sometimes the OSB Pipeline Service Callout component can generate invalid PL/SQL when mapping parameters. Make sure you’re not passing an aggregate/table function call as an input parameter to the stored procedure. For example, don’t map something like COUNT(orders.id) directly as a parameter—instead, calculate that value first in a separate SQL step (or within the stored procedure itself) and pass the resulting scalar value.

• Check for table function misuse
  If you’re using a table-valued function, you can’t call it directly in PL/SQL like v_result := my_table_function();. Instead, use it in a SQL query, like:

  DECLARE
    CURSOR c_results IS SELECT * FROM TABLE(my_table_function()); -- ✅ Correct usage
  BEGIN
    -- Process the cursor
  END;
  

Quick Troubleshooting Tip #

If you’re still stuck, pull up the auto-generated PL/SQL code that OSB uses to call your stored procedure (you can find this in the service callout’s configuration). Look for any instances where aggregate/table functions are used outside of SQL statements—those are the culprits.

内容的提问来源于stack exchange，提问作者SaG