6 Replies Latest reply on Jul 13, 2005 5:50 PM by George

    java.sql.SQLException: ORA-00904: invalid column name

    George Newbie

      I am trying to get a simple session facade to run using JBoss 4.0.2 and Oracle 8i. The code compiles fine the beans deploy fine but when I run the client I get the following error...

      2005-07-12 09:03:51,046 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.District#findNameGivenNumber] Executing SQL: SELECT t0_o.countyNbr, t0_o.districtNbr FROM DISTRICT t0_o WHERE (t0_o.districtNbr = ?)
      2005-07-12 09:03:51,046 TRACE [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.District#findNameGivenNumber] param: i=1, type=VARCHAR, value=99
      2005-07-12 09:03:51,077 ERROR [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.District#findNameGivenNumber] Find failed
      java.sql.SQLException: ORA-00904: invalid column name

      Does anyone know what causes this? Willing to give reward for a solution.

        • 1. Re: java.sql.SQLException: ORA-00904: invalid column name
          Stefan Schmidt Newbie

          Check whether your table DISTRICT really has mixed uppercase/lowercase column names (countyNbr, districtNbr). If so, those column names should be surrounded by quotation marks ("countyNbr", "districtNbr").

          You might want to try something like this in SQLPLUS:
          DESC DISTRICT
          SELECT t0_o."countyNbr", t0_o."districtNbr" FROM DISTRICT t0_o WHERE (t0_o."districtNbr" = 99);

          • 2. Re: java.sql.SQLException: ORA-00904: invalid column name
            Stefan Schmidt Newbie

             

            "schmidts" wrote:

            SELECT t0_o."countyNbr", t0_o."districtNbr" FROM DISTRICT t0_o WHERE (t0_o."districtNbr" = 99);


            this should read:

            SELECT t0_o."countyNbr", t0_o."districtNbr" FROM DISTRICT t0_o WHERE (t0_o."districtNbr" = '99');


            (note the '99' as the excerpt of your log files suggests that "districtNbr" is a VARCHAR column.)

            • 3. Re: java.sql.SQLException: ORA-00904: invalid column name
              Ulf Schroeter Novice

              Maybe you should enable CMP debugging, so you can see all database specific SQL statements within your server log file. Add the following entry

               <category name="org.jboss.ejb.plugins.cmp">
               <priority value="DEBUG"/>
               </category>
              


              to your ..conf/log4j.xml.

              Regards
              Ulf

              • 4. Re: java.sql.SQLException: ORA-00904: invalid column name
                George Newbie

                In the actual Oracle databse the coulumn names are county_nbr and district_nbr. The orion-ejb-jar.xml shown below should translate the cmp-field-mapping name to the persistence-name. I placed this file next to the ejb-jar.xml file in the META-INF folder.

                <?xml version = '1.0' encoding = 'windows-1252'?>
                <!DOCTYPE orion-ejb-jar PUBLIC "-//Evermind//DTD Enterprise JavaBeans 1.1 runtime//EN" "http://xmlns.oracle.com/ias/dtds/orion-ejb-jar.dtd">
                <orion-ejb-jar>
                <enterprise-beans>
                <session-deployment name="SessionEJB"/>
                <entity-deployment name="District" data-source="jdbc/PlutoDS" table="PLUTO.DISTRICT">
                <primkey-mapping>
                <cmp-field-mapping>

                <cmp-field-mapping name="countyNbr" persistence-name="COUNTY_NBR" persistence-type="CHAR(2)"/>
                <cmp-field-mapping name="districtNbr" persistence-name="DISTRICT_NBR" persistence-type="CHAR(2)"/>

                </cmp-field-mapping>
                </primkey-mapping>
                <cmp-field-mapping name="countyNbr" persistence-name="COUNTY_NBR" persistence-type="CHAR(2)"/>
                <cmp-field-mapping name="districtNbr" persistence-name="DISTRICT_NBR" persistence-type="CHAR(2)"/>
                <cmp-field-mapping name="districtName" persistence-name="DISTRICT_NAME" persistence-type="VARCHAR2(50)"/>
                <cmp-field-mapping name="districtAddr" persistence-name="DISTRICT_ADDR" persistence-type="VARCHAR2(50)"/>
                <cmp-field-mapping name="districtCity" persistence-name="DISTRICT_CITY" persistence-type="VARCHAR2(50)"/>
                <cmp-field-mapping name="districtState" persistence-name="DISTRICT_STATE" persistence-type="CHAR(2)"/>
                <cmp-field-mapping name="districtZipCode" persistence-name="DISTRICT_ZIP_CODE" persistence-type="CHAR(9)"/>
                <cmp-field-mapping name="districtAreaCode" persistence-name="DISTRICT_AREA_CODE" persistence-type="CHAR(3)"/>
                <cmp-field-mapping name="districtPhNbr" persistence-name="DISTRICT_PH_NBR" persistence-type="CHAR(7)"/>
                <cmp-field-mapping name="ficaId" persistence-name="FICA_ID" persistence-type="NUMBER(9)"/>
                <cmp-field-mapping name="unempInsId" persistence-name="UNEMP_INS_ID" persistence-type="NUMBER(9)"/>
                <cmp-field-mapping name="stateEmprId" persistence-name="STATE_EMPR_ID" persistence-type="NUMBER(5)"/>
                <cmp-field-mapping name="hideShowMgmtCodeFlag" persistence-name="HIDE_SHOW_MGMT_CODE_FLAG" persistence-type="CHAR(1)"/>
                <cmp-field-mapping name="validateSacsAcctFlag" persistence-name="VALIDATE_SACS_ACCT_FLAG" persistence-type="CHAR(1)"/>
                <cmp-field-mapping name="altFicaEmprPct" persistence-name="ALT_FICA_EMPR_PCT" persistence-type="NUMBER(11,6)"/>
                <cmp-field-mapping name="altFicaEmpPct" persistence-name="ALT_FICA_EMP_PCT" persistence-type="NUMBER(11,6)"/>
                <cmp-field-mapping name="altFicaVendorNbr" persistence-name="ALT_FICA_VENDOR_NBR" persistence-type="NUMBER(5)"/>
                <cmp-field-mapping name="workerCompRate" persistence-name="WORKER_COMP_RATE" persistence-type="NUMBER(11,6)"/>
                <cmp-field-mapping name="workHoursPerMonth" persistence-name="WORK_HOURS_PER_MONTH" persistence-type="NUMBER(11,2)"/>
                <cmp-field-mapping name="encumbNewFiscalYearFlag" persistence-name="ENCUMB_NEW_FISCAL_YEAR_FLAG" persistence-type="CHAR(1)"/>
                <cmp-field-mapping name="accrualFlag" persistence-name="ACCRUAL_FLAG" persistence-type="CHAR(1)"/>
                <cmp-field-mapping name="updateUserid" persistence-name="UPDATE_USERID" persistence-type="CHAR(10)"/>
                <cmp-field-mapping name="updateTimestamp" persistence-name="UPDATE_TIMESTAMP" persistence-type="DATE"/>
                <cmp-field-mapping name="taxpayerIdNbr" persistence-name="TAXPAYER_ID_NBR" persistence-type="NUMBER(9)"/>
                <cmp-field-mapping name="contact1099PhAreaCode" persistence-name="CONTACT_1099_PH_AREA_CODE" persistence-type="CHAR(3)"/>
                <cmp-field-mapping name="contact1099PhNbr" persistence-name="CONTACT_1099_PH_NBR" persistence-type="CHAR(7)"/>
                <cmp-field-mapping name="contactW2PhAreaCode" persistence-name="CONTACT_W2_PH_AREA_CODE" persistence-type="CHAR(3)"/>
                <cmp-field-mapping name="contactW2PhNbr" persistence-name="CONTACT_W2_PH_NBR" persistence-type="CHAR(7)"/>
                <cmp-field-mapping name="useDistrict1099TinFlag" persistence-name="USE_DISTRICT_1099_TIN_FLAG" persistence-type="CHAR(1)"/>
                <cmp-field-mapping name="useDistrictW2TinFlag" persistence-name="USE_DISTRICT_W2_TIN_FLAG" persistence-type="CHAR(1)"/>
                <cmp-field-mapping name="finTrnsfrApprovalReqdFlag" persistence-name="FIN_TRNSFR_APPROVAL_REQD_FLAG" persistence-type="CHAR(1)"/>
                <cmp-field-mapping name="budResApprovalReqdFlag" persistence-name="BUD_RES_APPROVAL_REQD_FLAG" persistence-type="CHAR(1)"/>
                <cmp-field-mapping name="reportEmprPaidFlag" persistence-name="REPORT_EMPR_PAID_FLAG" persistence-type="CHAR(1)"/>
                <cmp-field-mapping name="reportLeaveBalanceFlag" persistence-name="REPORT_LEAVE_BALANCE_FLAG" persistence-type="CHAR(1)"/>
                <cmp-field-mapping name="apBeginAccrualOption" persistence-name="AP_BEGIN_ACCRUAL_OPTION" persistence-type="CHAR(1)"/>
                <cmp-field-mapping name="validateFund01Flag" persistence-name="VALIDATE_FUND_01_FLAG" persistence-type="CHAR(1)"/>
                <cmp-field-mapping name="sacsDistrictType" persistence-name="SACS_DISTRICT_TYPE" persistence-type="CHAR(1)"/>
                <cmp-field-mapping name="logActivityFlag" persistence-name="LOG_ACTIVITY_FLAG" persistence-type="CHAR(1)"/>
                <cmp-field-mapping name="payRateDecimalFlag" persistence-name="PAY_RATE_DECIMAL_FLAG" persistence-type="CHAR(1)"/>
                <cmp-field-mapping name="hwSpouseLinkAllowedFlag" persistence-name="HW_SPOUSE_LINK_ALLOWED_FLAG" persistence-type="CHAR(1)"/>
                <cmp-field-mapping name="depreciationMethod" persistence-name="DEPRECIATION_METHOD" persistence-type="CHAR(1)"/>
                <cmp-field-mapping name="districtPhNbrExt" persistence-name="DISTRICT_PH_NBR_EXT" persistence-type="CHAR(5)"/>
                <cmp-field-mapping name="contact1099PhNbrExt" persistence-name="CONTACT_1099_PH_NBR_EXT" persistence-type="CHAR(5)"/>
                <cmp-field-mapping name="contactW2PhNbrExt" persistence-name="CONTACT_W2_PH_NBR_EXT" persistence-type="CHAR(5)"/>
                <cmp-field-mapping name="sdiAccountNbr" persistence-name="SDI_ACCOUNT_NBR" persistence-type="CHAR(8)"/>
                </entity-deployment>
                </enterprise-beans>
                </orion-ejb-jar>

                • 5. Re: java.sql.SQLException: ORA-00904: invalid column name
                  Stefan Schmidt Newbie

                  Here we are. What's JBoss got to do with orion-ejb-jar.xml? Nothing!

                  What you need is jbosscmp-jdbc.xml for customizing the database mapping.

                  • 6. Re: java.sql.SQLException: ORA-00904: invalid column name
                    George Newbie

                    As it turns out JBoss ignores the orion-ejb-jar.xml that is created by JDeveloper. What I had to do was use the information in orion-ejb-jar.xml to create a jbosscmp-jdbc.xml from scratch. The container was then able to send a query to the Oracle database with the correct column names.