1

Topic: Oracle SQL Developer 17.3.0: the Curve variables

[spoiler the Demonstration script - we will drive it on mentioned below clients]

set echo on
set serveroutput on
alter session set nls_numeric_characters = ".";
-- 1
select 36.6 from dual;
-- 2
declare
l_num number: = 36.6;
procedure PrintNum (p_num number) as
begin
dbms_output.put_line (p_num);
end;
begin
PrintNum (l_num);
end;
/
-- 3
declare
procedure PrintNum (p_num number) as
begin
dbms_output.put_line (p_num);
end;
begin
PrintNum (36.5 + 0.1);
end;
/
-- 4
declare
procedure PrintNum (p_num number) as
begin
dbms_output.put_line (p_num);
end;
begin
PrintNum (36.6);
end;
/

[/spoiler] [spoiler SQL*Plus - all works predictably]

set echo on
set serveroutput on
alter session set nls_numeric_characters = ".";
The session is changed.
.: 00:00:00.00
-- 1
select 36.6 from dual;
36.6
----------
36,6
.: 00:00:00.00
-- 2
declare
l_num number: = 36.6;
procedure PrintNum (p_num number) as
begin
dbms_output.put_line (p_num);
end;
begin
PrintNum (l_num);
end;
/
36,6
Procedure PL/SQL is successfully completed.
.: 00:00:00.00
-- 3
declare
procedure PrintNum (p_num number) as
begin
dbms_output.put_line (p_num);
end;
begin
PrintNum (36.5 + 0.1);
end;
/
36,6
Procedure PL/SQL is successfully completed.
.: 00:00:00.00
-- 4
declare
procedure PrintNum (p_num number) as
begin
dbms_output.put_line (p_num);
end;
begin
PrintNum (36.6);
end;
/
36,6
Procedure PL/SQL is successfully completed.
.: 00:00:00.00

[/spoiler] [spoiler Toad 10.6.1.3 - all works predictably]

SQL> set serveroutput on
SQL> alter session set nls_numeric_characters = "."
Session altered.
SQL> - 1
SQL> select 36.6 from dual
36.6
----------
36,6
1 row selected.
SQL> - 2
SQL> declare
l_num number: = 36.6;
procedure PrintNum (p_num number) as
begin
dbms_output.put_line (p_num);
end;
begin
PrintNum (l_num);
end;
36,6
PL/SQL procedure successfully completed.
SQL> - 3
SQL> declare
procedure PrintNum (p_num number) as
begin
dbms_output.put_line (p_num);
end;
begin
PrintNum (36.5 + 0.1);
end;
36,6
PL/SQL procedure successfully completed.
SQL> - 4
SQL> declare
procedure PrintNum (p_num number) as
begin
dbms_output.put_line (p_num);
end;
begin
PrintNum (36.6);
end;
36,6
PL/SQL procedure successfully completed.

[/spoiler] [spoiler Oracle SQL Developer 4.1.5.21 Build MAIN-21.78 - all works predictably]

SQL> set echo on
SQL> set serveroutput on
SQL> alter session set nls_numeric_characters = ".";
Session altered.
SQL> - 1
SQL> select 36.6 from dual;
36.6
----------
36,6
SQL> - 2
SQL> declare
l_num number: = 36.6;
procedure PrintNum (p_num number) as
begin
dbms_output.put_line (p_num);
end;
begin
PrintNum (l_num);
end;
/
PL/SQL procedure successfully completed.
36,6
SQL> - 3
SQL> declare
procedure PrintNum (p_num number) as
begin
dbms_output.put_line (p_num);
end;
begin
PrintNum (36.5 + 0.1);
end;
/
PL/SQL procedure successfully completed.
36,6
SQL> - 4
SQL> declare
procedure PrintNum (p_num number) as
begin
dbms_output.put_line (p_num);
end;
begin
PrintNum (36.6);
end;
/
PL/SQL procedure successfully completed.
36,6

[/spoiler] [spoiler Oracle SQL Developer 17.3.0.271 Build 271.2323 - unexpectedness]

SQL> set echo on
SQL> set serveroutput on
SQL>
SQL> alter session set nls_numeric_characters = ".";
Session altered.
SQL>
SQL> - 1
SQL> select 36.6 from dual;
36.6
----------
36,6
SQL>
SQL> - 2
SQL> declare
2 l_num number: = 36.6;
3 procedure PrintNum (p_num number) as
4 begin
5 dbms_output.put_line (p_num);
6 end;
7 begin
8 PrintNum (l_num);
9 end;
10 /
36,6
PL/SQL procedure successfully completed.
SQL>
SQL> - 3
SQL> declare
2 procedure PrintNum (p_num number) as
3 begin
4 dbms_output.put_line (p_num);
5 end;
6 begin
7 PrintNum (36.5 + 0.1);
8 end;
9 /
36,6
PL/SQL procedure successfully completed.
SQL>
SQL> - 4
SQL> declare
2 procedure PrintNum (p_num number) as
3 begin
4 dbms_output.put_line (p_num);
5 end;
6 begin
7 PrintNum (36.6);
8 end;
9 /
Error starting at line: 33 in command -
declare
procedure PrintNum (p_num number) as
begin
dbms_output.put_line (p_num);
end;
begin
PrintNum (36.6);
end;
Error report -
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 7
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error
occurred. For example, this error occurs if an attempt is made to
assign the value NULL to a variable declared NOT NULL, or if an
attempt is made to assign an integer larger than 99 to a variable
declared NUMBER (2).
*Action: Change the data, how it is manipulated, or how it is declared so
that values do not violate constraints.

[/spoiler]
Requests 1 - 3 from each client fulfill normally. We try to understand, why 4th falls on Oracle SQL Developer 17.3.0.
The first request - SQL. With it problems did not arise. All remaining - PL/SQL.
In 2 request we explicitly defined a variable number and we transfer its functions. In 3rd request, using addition operation, we hint that parameter, as result of addition - number. And here in 4th this hint any more we do not do, and, judging by an error, oracle considers that our literal - the line, and at a function invocation stage tries to transform it to number and stumbles on nls_numeric_characters.
[spoiler we Remove trace from a problem call and we are convinced of our assumption.]

=====================
PARSING IN CURSOR #329891744 len=261 dep=0 uid=48 oct=47 lid=48 tim=793884346175 hv=241631631 ad = ' 7ffd8950cf20 ' sqlid = ' 9frjtqh76f0cg'
DECLARE>>> SqlDevBind1Z_1 VARCHAR2 (32767) <<<: =:SqlDevBind1ZInit1; BEGIN declare
procedure PrintNum (p_num number) as
begin
dbms_output.put_line (p_num);
end;
begin
==> PrintNum (TO_NUMBER (SqlDevBind1Z_1));
end;
:AUXSQLDBIND1: = SqlDevBind1Z_1; END;
END OF STMT
PARSE #329891744:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=793884346175
BINDS #329891744:
Bind#0
oacdty=01 mxl=16386 (16000) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=873 siz=16392 off=0
kxsbbbfp=13c77fc0 bln=16386 avl=04 flg=05
value = "36.6"
Bind#1
oacdty=01 mxl=16386 (16000) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=873 siz=16392 off=0
kxsbbbfp=13bfbff8 bln=16386 avl=04 flg=05
value = "36.6"
EXEC #329891744:c=0,e=8801,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=793884371225
ERROR #2:err=6502 tim=793884371253

[/spoiler]

2

Re: Oracle SQL Developer 17.3.0: the Curve variables

AmKad;
Any it is direct client-side cursor sharing

3

Re: Oracle SQL Developer 17.3.0: the Curve variables

Similar on a new feature-hrenichu More secure

4

Re: Oracle SQL Developer 17.3.0: the Curve variables

AmKad;
He answered:

set secureliterals off

5

Re: Oracle SQL Developer 17.3.0: the Curve variables

;
On current operation Toad is not present. It is necessary  alternatives. Toad it is necessary at home.

6

Re: Oracle SQL Developer 17.3.0: the Curve variables

SQL Developer 4.2.0.17.089
the same
TO_NUMBER (SQLDEVBIND1Z_1)
To reveal, simply added
PrintNum (p_num OUT number)
PrintNum (+36.6) - certainly works.

7

Re: Oracle SQL Developer 17.3.0: the Curve variables

Recently here complained, apparently for the same reason: implies the text.
Entering without inverted commas:AA:

 select to_date (:AA, ' dd.mm ') from dual 

All apprx. And Toad simply allows to specify explicitly type at input.

8

Re: Oracle SQL Developer 17.3.0: the Curve variables

In sqlcl the same . Well that is one more certificate that is a piece of the code of the developer without a graphic muzzle
[spoiler]

$ sql andzhi4/password@localhost:1521/rabbix
SQLcl: Release 12.2.0.1.0 RC on Tue Oct 03 11:06:09 PM 2017
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Last Successful login time: Tue Oct 03 2017 11:06:09 PM +03:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
NAME OPEN_MODE
-------------------- ----------
RABBIX READ WRITE
SQL> set serveroutput on;
SQL> alter session set nls_numeric_characters = ".";
Session altered.
SQL> declare
2 procedure PrintNum (p_num number) as
3 begin
4 dbms_output.put_line (p_num);
5 end;
6 begin
7 PrintNum (36.6);
8 end;
9 /
Error starting at line: 1 in command -
declare
procedure PrintNum (p_num number) as
begin
dbms_output.put_line (p_num);
end;
begin
PrintNum (36.6);
end;
Error report -
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 7
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error
occurred. For example, this error occurs if an attempt is made to
assign the value NULL to a variable declared NOT NULL, or if an
attempt is made to assign an integer larger than 99 to a variable
declared NUMBER (2).
*Action: Change the data, how it is manipulated, or how it is declared so
that values do not violate constraints.
SQL>

[/spoiler]

9

Re: Oracle SQL Developer 17.3.0: the Curve variables

AmKad wrote:

Oracle SQL Developer 17.3.0.271

Numbering of versions under number of year all the same a frightening piece.
Actually, the concept major.minor is muddled. [. build [.revision [.somebullshit]]]. A sad show.

10

Re: Oracle SQL Developer 17.3.0: the Curve variables

env wrote:

Numbering of versions under number of year all the same a frightening piece.

Marketing considered that the reminder forces you to pass to the new faster date. But can be and reverse response. If now shamefully to go by a car 99 year, a car 49 year already a glamour!

11

Re: Oracle SQL Developer 17.3.0: the Curve variables

If function to declare in SQL fulfills (Oracle SQL Developer 17.2.0.188).

SQL> set echo on
SQL> set lines 80
SQL>
SQL> declare
2 procedure PrintNum (p_num number) as
3 begin
4 dbms_output.put_line (p_num);
5 end;
6 begin
7 PrintNum (36.6);
8 end;
9 /
Error starting at line: 4 in command -
declare
procedure PrintNum (p_num number) as
begin
dbms_output.put_line (p_num);
end;
begin
PrintNum (36.6);
end;
Error report -
ORA-06502: PL/SQL:: an error of conversion of the character in number a number or value error
ORA-06512: on line 7
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error
occurred. For example, this error occurs if an attempt is made to
assign the value NULL to a variable declared NOT NULL, or if an
attempt is made to assign an integer larger than 99 to a variable
declared NUMBER (2).
*Action: Change the data, how it is manipulated, or how it is declared so
that values do not violate constraints.
SQL>
SQL> with
2 function GetNum (p_num number) return varchar2 as
3 begin
4 return to_char (p_num);
5 end;
6 select GetNum (36.6)
7 from dual;
8 /
GETNUM (36.6)
--------------------------------------------------------------------------------
36,6

12

Re: Oracle SQL Developer 17.3.0: the Curve variables

AmKad;
Write about an error Jeff: https://www.thatjeffsmith.com/ask-a-question/
. About a failure from versions in favor of numbering on years - someone is perturbed. And what for? I too consider that it is better  improving without  and without sharp transitions from the concept on the concept, than to wait while there will be any new version of a product and it is necessary to change all. Also there is a marketing - anchor the user to the product, without allowing to jump off on the another's. Approximately as at JetBrains.

13

Re: Oracle SQL Developer 17.3.0: the Curve variables

At the same time I will ask.
SQL Developer 4.2.0.17
Export in  results of request. The pole with dates is exported as the text (with a quote ahead). In early versions also was? Now as? To overcome with adjustments of the developer?