📋

Description

The ORA-30191 error indicates a missing argument list in your Oracle Database code, usually within an OCI (Oracle Call Interface) function call. It signifies that the necessary parameters are not provided when calling a specific function.

💬

Error Message

ORA-30191: missing argument list

🔍

Known Causes

3 known causes

⚠️

Missing OCIFormatEnd

The required OCIFormatEnd element is absent from the argument list. This usually occurs when constructing format strings for OCI calls incorrectly.

⚠️

Incorrect Parameter Order

The arguments within the list are not in the expected sequence. OCI functions often require specific parameter order.

⚠️

Typos in Argument Names

A simple typo in the parameter name can lead to the argument not being recognized. Double-check spelling and capitalization.

🛠️

Solutions

3 solutions available

1. Correct Syntax for PL/SQL Procedures or Functions easy

Ensure all PL/SQL subprograms have a defined parameter list, even if empty.

1

When creating or altering PL/SQL procedures or functions, ensure that a parameter list is explicitly defined. Even if a subprogram does not accept any parameters, it still requires empty parentheses `()` to indicate an empty parameter list.

CREATE OR REPLACE PROCEDURE my_procedure
IS
BEGIN
  -- Procedure logic here
  DBMS_OUTPUT.PUT_LINE('Hello World');
END;
/

CREATE OR REPLACE FUNCTION my_function
RETURN VARCHAR2
IS
BEGIN
  RETURN 'Success';
END;
/

2

Review existing PL/SQL code (procedures, functions, packages) and identify any subprograms that might be missing the `()` after their name. Correct these by adding the empty parentheses.

-- Incorrect example (would cause ORA-30191)
-- CREATE OR REPLACE PROCEDURE my_procedure
-- IS
-- BEGIN ... END;

-- Corrected example
CREATE OR REPLACE PROCEDURE my_procedure()
IS
BEGIN
  -- Procedure logic here
  DBMS_OUTPUT.PUT_LINE('Hello World');
END;
/

2. Verify SQL Statements Calling PL/SQL easy

Check that SQL statements correctly invoke PL/SQL subprograms with the appropriate argument count.

1

If you are calling a PL/SQL procedure or function from a SQL statement (e.g., within a `SELECT` statement calling a function, or an `EXECUTE IMMEDIATE` statement), ensure that the number of arguments provided matches the number of parameters defined in the PL/SQL subprogram. If the subprogram expects no arguments, use empty parentheses.

-- Assuming a function that takes no arguments:
CREATE OR REPLACE FUNCTION get_system_date RETURN DATE IS BEGIN RETURN SYSDATE; END;
/

-- Correct SQL call:
SELECT get_system_date() FROM dual;

-- Incorrect call (missing parentheses):
-- SELECT get_system_date FROM dual;

2

For `EXECUTE IMMEDIATE`, ensure the syntax is correct for passing arguments or calling a procedure/function without arguments.

-- Calling a procedure that takes no arguments:
BEGIN
  EXECUTE IMMEDIATE 'BEGIN my_procedure(); END;';
END;
/

-- Calling a function that takes no arguments:
DECLARE
  v_result VARCHAR2(100);
BEGIN
  EXECUTE IMMEDIATE 'BEGIN :1 := my_function(); END;' INTO v_result;
  DBMS_OUTPUT.PUT_LINE(v_result);
END;
/

3. Review Dynamic SQL Construction medium

Ensure dynamically generated SQL strings correctly format PL/SQL calls.

1

If you are constructing SQL statements dynamically using `EXECUTE IMMEDIATE` or other methods, pay close attention to how PL/SQL subprograms are invoked within these dynamic strings. The string literal must accurately represent the call, including empty parentheses for parameterless subprograms.

DECLARE
  v_sql VARCHAR2(200);
  v_proc_name VARCHAR2(30) := 'my_parameterless_procedure';
BEGIN
  -- Incorrect dynamic SQL construction for a parameterless procedure
  -- v_sql := 'BEGIN ' || v_proc_name || '; END;'; -- Missing ()

  -- Correct dynamic SQL construction
  v_sql := 'BEGIN ' || v_proc_name || '(); END;';
  EXECUTE IMMEDIATE v_sql;
END;
/

2

When using bind variables in dynamic SQL, ensure the entire PL/SQL call syntax within the string is correct, especially the part that invokes the subprogram.

DECLARE
  v_sql VARCHAR2(200);
  v_func_name VARCHAR2(30) := 'my_parameterless_function';
  v_result VARCHAR2(100);
BEGIN
  -- Correct dynamic SQL construction for a parameterless function
  v_sql := 'BEGIN :ret := ' || v_func_name || '(); END;';
  EXECUTE IMMEDIATE v_sql INTO v_result USING OUT v_result;
  DBMS_OUTPUT.PUT_LINE(v_result);
END;
/

Oracle Error ORA-30191