CODEWITHSUNDEEP
javasqlgroovy
Carl
Carl

Reputation: 645

Calling a function from Groovy Sql

I am trying to call a function in Oracle that truncates the table name I pass in. Unfortunately, I keep getting the following error and have no clue as to why.

WARNING: Failed to execute: {? = call trunc_table(?)} because: Invalid column type
java.sql.SQLException: Invalid column type
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:147)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:209)
    at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3462)
    at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:126)
    at oracle.jdbc.

I am able to select rows from the table and print to the console. Also, I was able to call the dbms_utility.get_time() function with no problems.

Here is the offending code.

      def storedProcCall =
      """{? = call trunc_table(?)}"""
   sql.call(storedProcCall, [Sql.BOOLEAN, 'YFS_ZIP_CODE_LOCATION'])
   {
     println it
    }

I expect to get back a true or false to find out if the function was successful

create or replace
FUNCTION                    TRUNC_TABLE
(table_name IN VARCHAR2) RETURN BOOLEAN
IS

cid INTEGER; -- Cursor ID.

BEGIN

cid := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(cid, 'TRUNCATE TABLE ' || table_name, dbms_sql.native);


DBMS_SQL.CLOSE_CURSOR(cid);

RETURN TRUE;

EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cid);
dbms_output.put_line('Error truncating table: '||table_name);
dbms_output.put_line(SQLERRM);
RETURN FALSE;
END TRUNC_TABLE;

Upvotes: 2

Views: 6415

Answers (1)

Nicholas
Nicholas

Reputation: 16056

The issue is that a BOOLEAN in Oracle only exists within the scope of PL/SQL and so it will not bind back to a JDBC variable.

I took your function code, made the return type NUMBER and returned 0 for false and 1 for true. Then I called it like this:

import groovy.sql.*;
import java.sql.*;
sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger", "oracle.jdbc.driver.OracleDriver");
sql.call("{? = call TRUNC_TABLE(?)}", [Sql.NUMERIC, "FOO"]) { result ->
    println result;
}

The result was 1 for a good table and 0 for a non-existent table (or, presumably, for any error in the function execution).

Upvotes: 8

Related Questions