Reading a XMLType column in a oracle DB using JDBC

Question

I am trying to read an XMLType column into a Clob type variable using JDBC. I am getting an error when I do this.... Any ideas?

java.sql.SQLException: Invalid column name
    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:197)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:261)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:269)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:490)
    at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3353)
    at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1883)
    at oracle.jdbc.driver.OracleResultSet.getClob(OracleResultSet.java:358)
    at persistance.FilmDAO.findFilmsWithXML(FilmDAO.java:626)

This is my code:

public ArrayList<FilmBean> findFilmsWithXML() throws SQLException {

        ArrayList<FilmBean> filmList = new ArrayList<FilmBean>();

        try {
            currentConnection = ConnectionPool.getInstance().getConnection();

            String searchQuery = SqlHelper.getInstance().getSqlQuery(705);

            System.out.println("Query: " + searchQuery);

            currentStatement = currentConnection.prepareStatement(searchQuery);

            currentResultSet = currentStatement.executeQuery();

            while (currentResultSet.next()) 
            {
                FilmBean filmDTO = new FilmBean();
                filmDTO.setFilmId(Integer.parseInt(currentResultSet.getString("PK_ID_FILM")));
                filmDTO.setPoster(currentResultSet.getString("POSTER"));
                filmDTO.setTitle(currentResultSet.getString("TITLE"));
                filmDTO.setXml(currentResultSet.getClob("MYXML"));
                filmList.add(filmDTO);
            }
        }
        [...]
    }

The setXml() method sets a Clob type value into the filmDTO object in the model.

EDIT

The query was missing an alias:

SELECT to_clob("MYXML") As MYCLOB, PK_ID_FILM, POSTER, TITLE FROM FILM Where MYXML is not null

Jon Heller · Accepted Answer · 2012-07-30 03:44:34Z

1

You are missing an alias after to_clob(XMLElement("MYXML")). The column MYXML does not exist in the query.

answered Jul 30, 2012 at 3:44

Jon Heller

37.1k6 gold badges84 silver badges150 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jean-François Beaulieu Over a year ago

This returns <MYXML></MYXML>, however I think there is a problem with the SQL query because I want to return the contents of an XMLType column....

Jean-François Beaulieu Over a year ago

Nevermind! I have changed my query to: SELECT to_clob("MYXML") As MYCLOB and it works correctly now!

Collectives™ on Stack Overflow

Reading a XMLType column in a oracle DB using JDBC

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related