PLS-00103: Encountered the symbol ")"

Question

I am creating a web app which requires connecting to a database and getting the catagories of various types of reports. I am getting a weird error when it comes to executing my SQL via storedproc in java. SELECT * FROM RPT_CTGR; is the sql I submit but when I look at the stack trace it comes back as this:

 org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call SELECT * FROM RPT_CTGR;(?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 38:
PLS-00103: Encountered the symbol ")" when expecting one of the following:

   . ( * @ % & = - + < / > at in is mod remainder not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like like2
   like4 likec as between || indicator multiset member
   submultiset

    org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)
    org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:969)
    org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1003)
    org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:144)
    org.ifmc.qies.reportaudit.impl.CataImpl.search(CataImpl.java:59)
    org.ifmc.qies.reportaudit.web.CataAction.execute(CataAction.java:31)
    org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
    org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
    org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
    org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

Which for some reason is appending the extra "(?)" to the end of my statement which causes the error. Any ideas?

Code

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.ifmc.qies.reportaudit.dao.CataDao;
import org.ifmc.qies.reportaudit.model.Catagory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import oracle.jdbc.OracleTypes;

public class CataImpl extends JdbcDaoSupport implements CataDao {
    protected static ApplicationContext ctx = null;
//BaseStoredProcedure extends StoredProcedure
    public class CataProc extends BaseStoredProcedure{

        public CataProc(DataSource ds, String name) {
            super(ds,name);
            System.out.println(name);
            System.out.println(getSql());
            declareParameter(new SqlOutParameter("catagories", OracleTypes.CURSOR,
                    new RowMapper() {
                        public Object mapRow(ResultSet rs, int rowNum)
                                throws SQLException {

                            Catagory t = new Catagory();
                            t.setCatId(rs.getString(1));
                            t.setCat_name(rs.getString(2));
                            System.out.println(t.getCat_name());
                            return t;
                        }
                    }));
        }


    }
@Override
    public List search() {
    String[] paths = {"V:path/to/applicationcontext.xml"};
    Map params=new HashMap();
    ctx = new FileSystemXmlApplicationContext(paths);
    DataSource ds = (DriverManagerDataSource)ctx.getBean("dataSource");
        CataProc proc = new CataProc(ds,"SELECT * FROM RPT_CTGR;");
        Map results = proc.execute(params);
        List catagory = (List)results.get("catagories");
        //Test
        System.out.println(catagory.get(1).toString());
        return catagory;
    }



}

I get all my print statements except for the catagories.get(1).toString to return properly the SQL I submitted

What is `declareParameter`? Sounds like that is adding the `(?)` at the end of the statement. Also, is the semicolon redundant? IIRC it used to be required that you omit semicolons but it's been a long time so I'm not sure. — Jim Garrison, Aug 15 '17 at 17:24
declareParameter does as it says. declares that catagories will be the parameter that holds the output of the sql, in this case the catagory object. But I did take it out,as it did seem unnecessary but the same issue happens just instead of (?) its just (). I took out the sqloutparameter but that didn't change anything. Also, just from this website https://docs.spring.io/spring/docs/current/spring-framework-reference/html/jdbc.html#jdbc-StoredProcedure, it does say I have to have that declare parameter for every new sqlparameter. — Geogrio, Aug 15 '17 at 17:38
It seems that you are sending a request to call a store procedure and you are sending a select statement instead. You are sending `SELECT * FROM RPT_CTGR;` as the *name* of the Store Procedure. — alayor, Aug 15 '17 at 17:46

score 0 · Accepted Answer · answered Aug 15 '17 at 17:54

You should create a Store Procedure in your Oracle Database which executes your SELECT statement. You can see this question as reference on how to do it.

E.g.

CREATE OR REPLACE PROCEDURE getCatagories(categories in out sys_refcursor) is
  ...
  SELECT * FROM RPT_CTGR;
  ...

After that, you need to call the store procedure by its name.

public List search() {
  ...
  DataSource ds = (DriverManagerDataSource) ctx.getBean("dataSource");
  CataProc proc = new CataProc(ds,"getCatagories");
  Map results = proc.execute(params);
  List catagory = (List)results.get("catagories");
  ... 
}

PLS-00103: Encountered the symbol ")"

1 Answers1