SQL error information sorting and solutions (Continuous updates) and SQL Solutions

SQL error information sorting and solutions (Continuous updates) and SQL Solutions

Sort out the various SQL errors you have encountered and corresponding solutions for future reference. The main platform is Oracle:

• ORA-01461: Can only bind the LONG value of the LONG column to insert:
  • Cause: During the insert operation, the data is larger than the field size. Oracle automatically converts the data to the long type and reports an error of insertion failure.
  • Solution: change the data size or set the field to the clob or blob type.
• "ORA-01012: not logged on" and "Connected to an idle instance ":
  • Cause: When the Oracle server is restarted, if the connection is accidentally disconnected when the shutdown command is run to shut down the database, the following error may occur when you want to start the Oracle server again:
    • Usesqlplus /nolog After entering SQL Plus, you want to connect to the databaseconn username/password as sysdba Will reportConnected to an idle instance. Error.
    • On the SQL Plus interfacestartup When the database is started, it will reportORA-01012: not logged on Error.

    By checking the information, it is determined that the error is caused by the fact that ORACLE remains locked after the database is shut down and other operations are not allowed.

  • Solution:

    • First, close the ORACLE process:

      Ps-ef | grep ora_dbw0 _ $ ORACLE_SID // find the ORACLE process kill-9 PID // kill the process by using the found process PID

    • Restart ORACLE:

      Sqlplus/nolog // enter SQL Plusstartup // start ORACLE

    Finally, the command to close ORACLE is attached to avoid incorrect shutdown operations:

    • Shutdown normal (close the database after all connections are disconnected)
    • Shutdown transactional (actively disconnect and close the database after all transactions)
    • Shutdown immediate (actively disconnects connections and transactions)
    • Shutdown abort (shut down the database immediately, because this operation will not synchronize data, clear the rollback segment, do not trigger the checkpoint, soDangerous, The instance needs to be restored at each startup)
• ORA-00913: too many values:
  • Cause: the number of fields does not match when inserting data into the table. For example:

    insert into table_1 (?, ?) values (?, ?, ?)

    This error is reported if the inserted value is one more than the field value.

  • Solution: the number of inserted fields is the same as the number of inserted values.
• ORA-01791: not SELECTed expression error:
  • Cause: If you use distinct to deduplicate and order by to sort the statement in a select query, this error is reported, as shown in figure

    select distinct a from table1 where ... order by b;

    This is because order by conflicts with distinct when sorting Column B, and column B cannot be found in distinct.

  • Solution: Add Column B to the query column, that is:

    select distinct a,b from table1 where ... order by b;