getting error CallableStatementCallback; bad SQL grammar while calling procedure from postgreSQL

Question

i am using Spring-JDBC Support and PostgreSQL and getting error. I am able to run proc from back end. So it's compiled proc.

CREATE OR REPLACE PACKAGE schemaname.my_pkg_name
IS
PROCEDURE update_email(person_id numeric, OUT email_gratitude_id numeric);
END my_pkg_name;

CREATE OR REPLACE PACKAGE BODY schemaname.my_pkg_name
IS
PROCEDURE update_email(person_id numeric, OUT email_gratitude_id numeric) IS

begin
select email_gratitude_id into email_gratitude_id from schemaname.emp_email_dtls_tbl
where person_id=person_id;

end;
END my_pkg_name

private class EmployeeSP extends StoredProcedure
{ 
    private static final String SPROC_NAME = "schemaname.my_pkg_name.update_email"; 

    public EmployeeSP( DataSource datasource )
    { 
        super( datasource, SPROC_NAME );                    
        declareParameter( new SqlParameter("person_id", Types.INTEGER) );           
        declareParameter( new SqlOutParameter("email_gratitude_id", Types.INTEGER ) ); 

        compile();
    }

    public Object execute(int emp_id)
    { 
        Map<String,Object> results = super.execute(emp_id,null); 
        return results.get("email_gratitude_id"); 

    }
};


EmployeeSP tp = new EmployeeSP(template.getDataSource());
tp.execute(123456);

=====================================================================
21:31:48,386 ERROR [com.myproject.dao.EmailDAOImpl] (http--0.0.0.0-8080-6) org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call schemaname.my_pkg_name.update_email(?, ?)}]; nested exception is org.postgresql.util.PSQLException: ERROR: function schemaname.my_pkg_name.update_email(integer) does not exist
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1030)
    at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1064)

CREATE OR REPLACE PACKAGE is an Oracle statement. It's not valid for Postgres. Please read the manual. — a_horse_with_no_name

minion minion · Accepted Answer · 2013-05-05T00:54:39

try 'CREATE FUNCTION somefunc() RETURNS integer AS $$ ' i.e. instead of 'PACKAGE' use 'FUNCTION'. use can check Chapter 39. PL/pgSQL - SQL Procedural Language!

Also you can check difference between Oracle differences between PostgreSQL's PL/pgSQL language and Oracle's PL/SQL language

getting error CallableStatementCallback; bad SQL grammar while calling procedure from postgreSQL

1 Answers