Hi Team,
Recently we had a bug in the production system (which is a coding error on our part!) but while investigating the same I noticed the something.
Executed the following:
1) Table Creation:
create table dept(
dept_no varchar2(15),
dept_name varchar2(30),
loc varchar2(30));
2) SQL Insert:
insert into dept values ('10','president's','washington d.c');
3) PL/SQL Insert:
DECLARE
l_sql VARCHAR2(32767);
BEGIN
l_sql := 'INSERT INTO DEPT VALUES (';
L_sql := l_sql || '''50''';
l_sql := l_sql ||', ''president''s'', ''washington d.c'')';
dbms_output.put_line(l_sql);
EXECUTE IMMEDIATE l_sql;
END;
And the error codes that I received when I ran the above SQL and PL/SQL Inserts in 11g and 12c are as below:
Version - 12c
SQL Error - ORA-00917: missing comma
PL/SQL Error - ORA-00917: missing comma ORA-06512: at line 8
ORA-06512: at "SYS.DBMS_SQL", line 1721
Version - 11g
SQL Error - ORA-01756: quoted string not properly terminated
PL/SQL Error - ORA-00917: missing comma ORA-06512: at line 8
The error that we get is made uniform in SQL and PL/SQL in the 12c Version, but I personally feel that 11g SQL Error makes more sense and helps in identifying the root cause of the error.
Kindly let me know your views.
I agree that it would be nice if the PL/SQL message matched the SQL one, but sorry, I can't reproduce the difference you mentioned between 11g and 12c.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
5 rows selected.
SQL>
SQL> create table dept(
2 dept_no varchar2(15),
3 dept_name varchar2(30),
4 loc varchar2(30));
Table created.
SQL>
SQL> insert into dept values ('10','president's','washington d.c');
ERROR:
ORA-01756: quoted string not properly terminated
SQL>
SQL> declare
2 l_sql varchar2(32767);
3 begin
4 l_sql := q'{insert into dept values ('10','president's','washington d.c')}';
5 execute immediate l_sql;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-00917: missing comma
ORA-06512: at line 5
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
5 rows selected.
SQL>
SQL> create table dept(
2 dept_no varchar2(15),
3 dept_name varchar2(30),
4 loc varchar2(30));
Table created.
SQL>
SQL> insert into dept values ('10','president's','washington d.c');
ERROR:
ORA-01756: quoted string not properly terminated
SQL>
SQL> declare
2 l_sql varchar2(32767);
3 begin
4 l_sql := q'{insert into dept values ('10','president's','washington d.c')}';
5 execute immediate l_sql;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-00917: missing comma
ORA-06512: at line 5