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