title: Invoking procedure defaults without binding values to parameters in Jdbc
body: <p>I am trying to call a <code>PL/SQL</code> procedure which has defaults defined for some of its parameters. I am doing this using <code>CallableStatement</code> in <code>JDBC</code>.</p>
  
  <p>This procedure has a large number of parameters with defaults defined. I do not want to explicitly set the defaults in the Java code. Doing this would make maintaining the code harder. If the PL/SQL code changes , would have to make the same changes in the Java code too.</p>
  
  <p>Is it possible to accomplish this in JDBC ? For instance just bind values to the parameter you are interested in and ignore the rest.</p>
  
  <p>I tried this on the following sample procedure :</p>
  
  <pre><code>  -- PURPOSE: Takes a parameter which has defaults set. Returns the value of the same parameter
    -- Example of: FUNCTION that takes a parameter with DEFAULT values  
    FUNCTION handle_defaults(empId IN NUMBER DEFAULT 20 , empCity IN VARCHAR2) RETURN NUMBER IS
    BEGIN
      RETURN empId;
    EXCEPTION
      WHEN others THEN
      dbms_output.put_line('Error!');
    END handle_defaults;
  </code></pre>
  
  <p>Here is the relevant portions of the code (<strong>NOTE</strong>: Have stripped off the try catch block , cleaning up of database resources etc for sake of readability)</p>
  
  <pre><code>         // Create a database connection
           conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PWD);
  
           // Create a query string
           String queryStr = "{ ? = call HR.EMP_PKG.handle_defaults( ? , ? ) }";
  
           // Create a Callable Statements
           callStmt = conn.prepareCall(queryStr);
  
           // Bind values to the IN parameter
           callStmt.setString(3, "Mumbai");
  
           // Register OUT parameter 
           callStmt.registerOutParameter(1, java.sql.Types.NUMERIC);
  
           // Execute the Callable Statement
           callStmt.execute();
  
           // Retrieve the value of the OUT parameter
           parameterValue = callStmt.getInt(1);
  
           System.out.println("Value returned : " + parameterValue);
  </code></pre>
  
  <p>I get the following error:</p>
  
  <pre><code>Exception occured in the database
  java.sql.SQLException: Missing IN or OUT parameter at index:: 2
  Database error code: 17041
  </code></pre>
  
  <p>As a desperate attempt I also tried to pass Nulls for those parameters. Just included the following line:</p>
  
  <pre><code>callStmt.setNull(2, java.sql.Types.NUMERIC);
  </code></pre>
  
  <p>I get the following result:</p>
  
  <pre><code>Value returned : 0
  </code></pre>
  
  <p>That makes sense bcoz <code>setNull</code> supplies SQL Null to the parameter.  </p>
  
  <p>I am using <code>Oracle 11g</code> and <code>Oracle 12c Jdbc Driver Version 12.1.0.2</code>.</p>