Oracle PL/SQL: Correct way to call a function that performs DML operations and returns a value?

Data and Databases
1

I’m working with a PL/SQL function that has this signature:

FUNCTION ProcessOrder (user_num IN NUMBER, order_id IN NUMBER, delivery_type IN VARCHAR2 DEFAULT NULL, notes IN VARCHAR2 DEFAULT NULL) RETURN NUMBER;

I’m struggling to execute this function properly in Oracle SQL Developer. Since it contains DML operations, I can’t use it in a SELECT statement. I tried using this approach that I found online:

variable result number;
execute :result := package_name.ProcessOrder(1234, 98765, NULL, 'test');
print result;

But I keep getting this error:

ORA-01008: not all variables bound
Details:
BEGIN :result := package_name.processorder(1234, 98765, null, 'test'); END;
Error at line 1
ORA-01008: not all variables bound

What is the correct syntax to manually execute this type of function that performs DML and returns a value? I need to test it with specific parameters but can’t figure out the right way to call it.

2

The issue with your variable binding approach is likely related to SQL Developer’s session handling. Instead of using bind variables, I recommend using a simple anonymous PL/SQL block for testing functions that perform DML operations. You can declare a local variable, call your function, and display the result using DBMS_OUTPUT. Here is the syntax: DECLARE result_value NUMBER; BEGIN result_value := package_name.ProcessOrder(1234, 98765, NULL, 'test'); DBMS_OUTPUT.PUT_LINE(result_value); END; Make sure to enable DBMS_OUTPUT in SQL Developer before running this block. This approach eliminates binding issues and provides a straightforward way to test your function with specific parameters while handling the DML operations properly.

3

hmm, have you tried using a simple select package_name.ProcessOrder(1234, 98765, null, 'test') from dual; first? i know you mentioned DML issues but sometimes it helps to see what exactly fails. also curious - what kind of DML operations are happening inside your function? might affect how you need to handle the transaction.

4

try wrapping it in an anonymous block instead. something like declare v_result number; begin v_result := package_name.ProcessOrder(1234, 98765, NULL, 'test'); dbms_output.put_line('Result: ' || v_result); end; should work fine for testing.