ORA-00984

[antomack]

I am trying to insert the results of an Access DAO recordset into an Oracle table through OO4O. The code is detailed below, from which I keep getting the error
SQL execution error, ORA-00984: column not allowed here

I have checked for solutions on the net and all seem to point towards a problem with inserting data details, the last column in my code is date but it has been formatted as text so don't see why it's causing the error. I have run the insert directly in SQL*Plus and it works fine using the data as from the first record of the recordset.

Can anyone out there please help?

      Dim OraSession As OraSession
      Dim OraDatabase As OraDatabase
      Dim OraDynaset As OraDynaset
      Dim sqlStatement As OraSqlStmt
      Dim rsDetails As Recordset
      
      ' Using OO4O
      ' ---------------------------
      'Create the OraSession Object.
      Set OraSession = CreateObject("OracleInProcServer.XOraSession")
      
      'Create the OraDatabase Object by opening a connection to Oracle.
      Set OraDatabase = OraSession.OpenDatabase("mis1", "apeapp/oracle", 0&)
      
      ' Set to auto commit after running of sql
      OraDatabase.AutoCommit = True                     

      ' Do the Appends to Max_Archive and PolicyPremium
      If RsControl!QueryName = "U_Q00018" Then
        strSQL = "SELECT DISTINCT DPOLICY.Year_no as Yrno, DPOLICY.Week_no as Wkno, 'DUB' AS SysType, DPOLICY.POL_NUMBER, DPOLICY.AGENT_NUMB," _
        & "CobeType.Kind_Code as KC, CobeType.Version as Ver, CobeType.Product_Type as PType, StatusCodes.Indicator, DPOLICY.ANNUAL_P, DPOLICY.FREQUENCY, " _
        & "DPOLICY.COBE as cobe1, DPOLICY.ADDL_CONT, cstr(format(DPOLICY.COMMENCE_DATE,'dd-mmm-yy')) as ComDate FROM (DPOLICY LEFT JOIN CobeType " _
        & "ON DPOLICY.COBE = CobeType.COBE) LEFT JOIN StatusCodes ON DPOLICY.STATUS = StatusCodes.Code WHERE DPOLICY.Year_no=" & BaseTablesActualYear & " AND DPOLICY.Week_no=" & BaseTablesActualWeek & ";"
        
        ' openrecordset rsDetails from strSQL
        Set rsDetails = db.OpenRecordset(strSQL)
        
        ' loop through rsDetails and append to PolicyPremium
        Do Until rsDetails.EOF
          OraDatabase.ExecuteSQL ("INSERT INTO PolicyPremium (YEAR_NO, WEEK_NO, SYSTEM_TYPE, POLICY_NO, AGENT_NO, KIND_CODE, VERSION, PRODUCT_TYPE, STATUS, PREMIUM, FREQ, COBE, ADD_CON, COMMENCE_DATE ) " _
          & "values (rsDetails.yrno, rsDetails.wkno, rsDetails.systype, rsDetails.pol_number, rsDetails.agent_numb, rsDetails.kc, rsDetails.ver, rsDetails.ptype, rsDetails.indicator, rsDetails.annual_p, rsDetails.frequency, rsDetails.cobe1, rsDetails.addl_cont, rsDetails.comdate)")
        Loop
        
      ElseIf RsControl!QueryName = "U_Q00042" Then
        strSQL = "SELECT Max.* FROM Max;"
        
        ' openrecordset rsDetails from strSQL
        Set rsDetails = db.OpenRecordset(strSQL)
        
        ' loop through rsDetails and append to Max_Archive
        Do Until rsDetails.EOF
          OraDatabase.ExecuteSQL ("INSERT INTO Max_Archive ( YEAR_NO, WEEK_NO, MAX_YEAR_NO, MAX_WEEK_NO, SYSTEM_TYPE, POLICY_NO, KIND_CODE, VERSION, PRODUCT_TYPE, AGENT_NO, COMMENCE_DATE, STATUS, PREMIUM, FREQ, COBE, ADD_CON, PREMIUMAPETYPE, PREMIUMAPEAMOUNT, ADDCONAPETYPE, ADDCONAPEAMOUNT, APE, CONVERTED, CANCELLED ) " _
          & "values (rsDetails.Year_no , rsDetails.Week_no, rsDetails.Max_Year_no, rsDetails.Max_Week_no, rsDetails.System_Type, rsDetails.Policy_no, rsDetails.Kind_Code, rsDetails.Version, rsDetails.Product_type, rsDetails.Agent_no, rsDetails.Commence_Date, rsDetails.Status, rsDetails.Premium, rsDetails.Freq, rsDetails.COBE, rsDetails.Add_Con, rsDetails.PremiumAPEType, rsDetails.PremiumAPEAmount, rsDetails.AddConAPEType, rsDetails.AddConAPEAmount, rsDetails.APE, rsDetails.Converted, rsDetails.Cancelled)")

        Loop
                
      End If
      
      OraDatabase.Close

 

[antomack]

Meant to put in that I'm using Access 97 and Oracle 8i

 

[Turkbear]

Hi,
Check out what is actually being send in the Sql String
( maybe with a response.write after populating a variable with it) to check on the " marks..when Oracle sees a " around something it assumes a column name ---


The full error code is :

ORA-00984 

ORA-00984column not allowed here 

Cause:A column name was used in an expression where it is not permitted, such as in the VALUES clause of an INSERT statement. 

Action:Check the syntax of the statement and use column names only where appropriate.

 

[antomack]

The error definitely seems to be arising from the date being added in the last column of the insert. The value from rsDetails.ComDate is formatted as text so don't see why it's not working, the solutions I've found on the net say the problem arises because the date is being added in number format and needs to be added as text.

I've even tried using to_date(rsDetails.ComDate, 'dd-mon-yy') and to_char(rsDetails.ComDate, 'dd-mon-yy') to no avail.

Running the Insert in SQL*Plus with the same details as fail in Access works fine and appends a record so long as the date is in ''.

Does anyone out there have any other suggestions?

 

[sem]

Your insrted variables are within double quotes. So you're tying to insert the values Oracle knows nothing about. rsDetails.Year_no is declared and holds some value within YOUR application only, while INSERT statement is executed on SERVER. You should pass variable value, not its name. This may be done by creating the statement (this will impact performance though)
.."values ("+rsDetails.Year_no + "," +...

or by using bind variables