Validating null in pl sql brad garrett dating show

select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release - 64bi PL/SQL Release - Production CORE Production TNS for Solaris: Version - Production NLSRTL Version - Production SQL select dump(c1), dump(c2) from t1; DUMP(C1) ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ DUMP(C2) ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ NULL NULL NULL NULL So has this been changed in 10g R2 or I am doing something wrong?

Thanks Hi I have seen s QL statements as follows not sure if it's meaningful?

In the above example, col1 variable is declared as VARCHAR2 and the empty string is treated as NULL.

SQL DECLARE 2 col1 CHAR(1); 3 BEGIN 4 col1 := ''; 5 DBMS_OUTPUT.

If you are encountering this issue, you can open an SR with support and reference the bug# (or even take over 'ownership' of it) So a key question above seems to be ignored.

Maybe this clarifies it: drop table a40od620.exp2; create table a40od620.exp2 (i1 number, c1 char(5), c2 varchar(10)); insert into a40od620.exp2 values(1, 'hello', 'there'); insert into a40od620.exp2 values(2, '', ''); insert into a40od620.exp2 values(3, NULL, NULL); insert into a40od620.exp2 values(4, 'A', 'A'); update a40od620.exp2 set c1=replace(c1,'A',''), c2=replace(c2,'A','') where i1=4; insert into a40od620.exp2 values(5, replace('A','A',''), replace('A','A','')); select * from a40od620.exp2; select * from a40od620.exp2 where c1 is null; select * from a40od620.exp2 where c2 is null; select i1, length(c1),length(c2) from a40od620.exp2; quit; I1 C1 C2 ---------- ----- ---------- 1 hello there 2 3 4 5 I1 C1 C2 ---------- ----- ---------- 2 3 5 I1 C1 C2 ---------- ----- ---------- 2 3 4 5 I1 LENGTH(C1) LENGTH(C2) ---------- ---------- ---------- 1 5 5 2 3 4 5 5 So the ONLY way I have so far to insert an empty string into a char column is to insert a dummy string, 'A' and then use replace to set it back to an empty string ''.

The length of the string is 1 in this case eventhough I have assigned an empty string to col1.

December 29, 2008 - am UTC just select * from t1 where c1 = (select c2 from t2) would do it. but it seems the 'intent' was to make "null=null" in this case - to retrieve nulls (not that their intent was fulfilled - just that is what I think they were trying to do): declare 2 type p is table of int index by varchar2(1); 3 t p; 4 begin 5 t(null) := 1; 6 dbms_output.put_line(t(null)); 7 end; 8 / declare * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: NULL index table key value ORA-06512: at line 5 March 16, 2012 - am UTC I don't set bugs I file to 'public', I wait for them to be worked on and fixed/resolved before that happens.'' when assigned to a varchar2(1) becomes '' which is a zero length string and a zero length string is NULL in Oracle (it is no long '') Now, I don't know what you mean by "we'll have to modify our code due to this" since this behaviour has been consistent for quite a while. WORLD select * from v$version; BANNER ---------------------------------------------------------------- Oracle7 Server Release - Production Release PL/SQL Release - Production CORE Version - Production (LSF Alpha) TNS for SVR4: Version - Production NLSRTL Version - Production [email protected] DECLARE 2 col1 VARCHAR2(1); 3 BEGIN 4 col1 := ''; 5 DBMS_OUTPUT.The insert of '' gets translated directly to a null, even for the char column, and the direct replace seems to first translate to the '' which is then translated into a null on insert.Is there no way to insert an empty string into a char variable??

Search for validating null in pl sql:

validating null in pl sql-85validating null in pl sql-53validating null in pl sql-58validating null in pl sql-3

Leave a Reply

Your email address will not be published. Required fields are marked *

One thought on “validating null in pl sql”

  1. It isn't without its detractors, however, with many pointing out just how creepy it is to be giving your information to random people you pass in the street.