SQL

How to Resolve ORA-00903: invalid table name

Posted on
In this post, I will introduce 3 main error patterns about ORA-00903, they are: Violating Naming Rules Missing Quotes Incorrect Syntax ORA-00903 due to Violating Naming Rules Without complying with database object naming rules, we can’t create a table with unusual strings in normal way. Let’s see some cases that throw ORA-00903. First of all, […]
SQL

How to Resolve ORA-00904 Invalid Identifier

Posted on
Oracle Database Object Name Recognition Examples
General Naming Rules about ORA-00904 Normally, Oracle treats most identifiers as case insensitive ones excepts password. But there’re some special usages should be taken care of in case of ORA-00904 invalid identifier generated. Not only ORA-00904, but ORA-00903 and ORA-00911 are also related to invalid usage on object identifiers. More specifically, ORA-00903 alerts users for […]
SQL

4 Types of SQL in Relational Database

Posted on
Types of SQL Statements SQL statements in relational database management system (RDBMS) could sometimes be too complicated to be analyzed for. As SQL developers, we should at least be able to distinguish what types of the statements that are using. According to conventional SQL classification, there are 4 basic types of SQL statements. Data Query […]
DDL

How to Export Column Comment DDL

Posted on
Column Comment DDL During metadata migration, I found all column comments with multi-byte characters are garbled. So what I was going to do next is to correct the garbled data and then feed all column comment into the target database. Now where we can find column comment DDL? For exporting column comments, we can use […]
PL/SQL

How to Use Bind Variable in Oracle

Posted on
Shareable Cursor Using bind variables can make statements with minor differences become literally the same so as to share the same cursor when executions. That means, SQL engine doesn’t have to parse those similar statements every time they are executed, it reuses the shared cursor to service millions of executions. Unshareable Cursors In contrast, dynamic […]
Query

How to Compare Two Tables Definition

Posted on
Compare Two Tables’ Column Names Whenever you concern the differences between two tables’ definition, you can use queries to compare the columns of two tables. For example, a table named EMPLOYEES_2 which is very similar with the original table EMPLOYEES. And we’d like to know the differences in columns. We can compare both tables like […]
SQL

How to Resolve ORA-00947: not enough values

Posted on
ORA-00947: not enough values
ORA-00947 With respect to ORA-00913: too many values, column list mismatching can result another type of error, called ORA-00947: not enough values. Let’s see how we reproduce the error. First of all, we create an empty table employees_2 from employees. SQL> create table employees_2 as select * from employees where 1 = 2; Table created. […]
Query

How to Resolve ORA-00913: too many values

Posted on
ORA-00913: too many values
ORA-00913 When SELECT Let’s see a SQL statement containing a subquery that can reproduce ORA-00913 for an example. SQL> select * from employees where department_id in (select department_id, manager_id from departments where location_id = 1700); select * from employees where department_id in (select department_id, manager_id from departments where location_id = 1700)         […]
Query

How to Limit Number of Rows Returned in Oracle

Posted on
Limit Number of Rows Returned In MySQL, we use LIMIT n cluase to limit number of rows returned. In Oracle, we may need to do more works to fetch our target rows. First, we listed all sorted data like the following: SQL> conn hr/hr; Connected. SQL> select employee_id, first_name, last_name from employees order by 3; […]