Hi,
We're working with Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production. The question from the Subject is the question of this discussion. I'm going to put it the end after this explanation of the issue.
We have a procedure that takes a parameter from java.
PROCEDURE Update_device (
-
-
L_device_name_in IN VARCHAR2,
-
-
An error occurred where a value was passed in with a single quote in it. They ( java people) are arguing apparently this value actually made it into Oracle somehow.
Thy'er saying (and quoting from the Oracle documentation):
https://docs.oracle.com/database/121/SQPUG/ch_five.htm#SQPUG438
If the SQL command containing the reference should have quote marks around the variable and you do not include them there, the user must include the quotes when prompted.
That's their argument which made no sense to me. I tried to explain Oracle will not take a string with a single quote in it like "don't". I wrote a quick script explaining that's it was impossible for a value like "don't" to make it into Oracle without flagging the "ORA-01756: quoted string not properly terminated" error.
Here it is:
SET SERVEROUTPUT ON
DECLARE
v_var VARCHAR2(10);
v_err VARCHAR (100) := SQLERRM;
BEGIN
--v_var:= l_var_in;
DBMS_OUTPUT.PUT_LINE (&v_var);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (v_err);
END in_var_tst;
/
If you try to enter "don't" at the variable input prompt you get.
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 8 18:07:43 2017
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Enter value for v_var: don't
old 10: DBMS_OUTPUT.PUT_LINE (&v_var);
new 10: DBMS_OUTPUT.PUT_LINE (don't);
ERROR:
ORA-01756: quoted string not properly terminated
SQL>
I've also tried this with UNIX Bind variables and got the same error message. However he's saying it's done, I need to ask the experts. IS THERE A WAY to pass a string in to ORACLE with a single quote in it (like "don't"). Specifically from Java?