"ORA-00942: The table or view does not exist" cause and Solution

Source: Internet
Author: User
Tags powerdesigner

Using Oracle database, using powerdesigner design, generate SQL File Import query appears "ORA-00942: Table or view does not exist", very depressed, this problem has appeared before, originally solved, however, because I haven't used it for a long time, I forgot it this time, which makes me waste some time. To avoid forgetting it again, write it down and share it with everyone.

1. cause of the problem

Oracle is case sensitive. When we create an SQL script to create a table, Oracle automatically converts the table name and field name to uppercase,

Eg:

create table T_WindRadar  (   wr_id                VARCHAR2(64)                    not null,   wr_reciveTime        DATE,   wr_image             BLOB,   constraint PK_T_WINDRADAR primary key (wr_id));

 

However, Oracle also supports the "" syntax. After the table name or field name is added with "", Oracle will not convert it to uppercase.
Eg:

create table "T_WindRadar"  (   "wr_id"                VARCHAR2(64)                    not null,   "wr_reciveTime"        DATE,   "wr_image "            BLOB,   constraint PK_T_WINDRADAR primary key (wr_id));

If "" is added, then the General SQL statement query will produce "ORA-00942: Table or view does not exist", so the SQL script needs to add the table name "".
Eg:

select * from  "T_WindRadar";

 

This problem usually does not occur when we write SQL. However, when we use powerdesigner to design a database, this kind of problem often occurs because we don't pay attention to it, because the SQL file generated by powerdesigner is "" by default.

2. Solution

Because we use powerdesigner, you do not need to manually rewrite the SQL script, as long as you set powerdesigner to regenerate.

In powerdesiger, find the edit current DBMS under the database in the physical data model menu,

Select script-> SQL-> Format. There is a casesensitivityusingquote. Its comment is "determines if the case sensitiworkflow for identifiers is managed using double quotes ",

Indicates whether double quotation marks are used to specify the case sensitivity of the identifier. You can see that the default value of values on the right is "yes", change it to "no", and click "Apply.

When the SQL statement is generated again, no quotation marks are provided for the table and field names.

"ORA-00942: The table or view does not exist" cause and Solution

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.