|Programmer's Guide to the Oracle Precompilers, 1.8||
-- misspelled keyword WHERE
EXEC SQL DELETE FROM EMP WERE DEPTNO = 20;
-- missing parentheses around column names COMM and SAL
EXEC SQL INSERT INTO EMP COMM, SAL VALUES (NULL, 1500);
Rules of semantics specify how valid external references are made. Thus, semantic checking verifies that references to database objects and host variables are valid and that host-variable datatypes are correct. For example, the following embedded SQL statements contain semantic errors:
-- nonexistent table, EMPP
EXEC SQL DELETE FROM EMPP WHERE DEPTNO = 20;
-- undeclared host variable, emp_name
EXEC SQL SELECT * FROM EMP WHERE ENAME = :emp_name;
The rules of SQL syntax and semantics are defined in the Oracle7 Server SQL Reference.
You can specify the following values for SQLCHECK:
The precompiler gets the information for a semantic check from embedded DECLARE TABLE statements or, if you specify the option USERID, by connecting to Oracle and accessing the data dictionary. You need not connect to Oracle if every table referenced in a data manipulation statement or PL/SQL block is defined in a DECLARE TABLE statement.
If you connect to Oracle but some information cannot be found in the data dictionary, you must use DECLARE TABLE statements to supply the missing information. A DECLARE TABLE definition overrides a data dictionary definition if they conflict.
When checking data manipulation statements, the precompiler uses the Oracle7 set of syntax rules found in the Oracle7 Server SQL Reference but uses a stricter set of semantic rules. As a result, existing applications written for earlier versions of Oracle might not precompile successfully when SQLCHECK=SEMANTICS.
Specify SQLCHECK=SEMANTICS when precompiling new programs. If you embed PL/SQL blocks in a host program, you must specify SQLCHECK=SEMANTICS.
If some of the needed information cannot be found in the data dictionary (because your program refers to a table not yet created, for example), you must supply the missing information using the DECLARE TABLE statement.
To connect to Oracle, specify the option USERID on the command line, using the syntax
where username and password comprise a valid Oracle userid. If you omit the password, you are prompted for it. If, instead of a username and password, you specify
the precompiler tries to connect to Oracle automatically with the userid
where prefix is the value of the Oracle initialization parameter OS_AUTHENT_PREFIX (the default value is OPS$) and username is your operating system user or task name.
If you try connecting to Oracle but cannot (for example, if the database is unavailable), the precompiler stops processing and issues an error message. If you omit the option USERID, the precompiler must get needed information from embedded DECLARE TABLE statements.
The syntax of the DECLARE TABLE statement is
EXEC SQL DECLARE table_name TABLE
(col_name col_datatype [DEFAULT expr] [NULL|NOT NULL], ...);
where expr is any expression that can be used as a default column value in the CREATE TABLE statement.
If you use DECLARE TABLE to define a database table that already exists, the precompiler uses your definition, ignoring the one in the data dictionary.
Copyright © 1996-2001, Oracle Corporation.
All Rights Reserved.