NAME column is varchar2.
Test query...
SQL> select count(*) from TABLE_NAME where NAME = 'TEST';
COUNT(*)
----------
1
Use Variable...
SQL> declare
2 V varchar2(17) := 'TEST';
3 a number;
4 begin
5 dbms_output.put_line('value='||V );
6 select count(*) into a from TABLE_NAME WHERE NAME = V;
7 dbms_output.put_line(a);
8 end;
9 /
value=TEST
10693
This PL/SQL gets wrong result from table...
So, try to new... PL/SQL... Variable using "execute immediate"
SQL> declare
2 V varchar2(17) := 'TEST';
3 a number;
4 begin
5 dbms_output.put_line('value='||V );
6 execute immediate 'select count(*) FROM TABLE_NAME WHERE NAME = :p1' into a using V ;
7 dbms_output.put_line(a);
8 end;
9 /
value=TEST
1
That is Right Result.
Remark:
This case make wrong Data... Because used "COLUMN NAME" to be "VARIABLE NAME" on PL/SQL and ..., It make sql statement like: "where column1 = column2" (see on every VERSIONs). So, That should to avoid using "COLUMN NAME" to be "VARIABLE NAME" on PL/SQL and ... , Anyway If... need to use!..., using "EXECUTE IMMEDIATE" can help!
Thank you Gary MYERS
Posts
5 comments:
In the PL/SQL for the unexpected result, is there a column V in the table that has the same value as the NAME column for 10693 rows ?
Thank You... For Your Reply.
A column NAME (V -> variable) in the table that has not the same value in the NAME column.
I test this case on 10G... that's no the problem....
I only found the problem on 9i sparc...
Have you tried a trace with binds so you can see if the correct value is put in the binds.
I'd also look at the query plan (is it using a table scan or index) and maybe any check constraints, materialized views.
Hi Gary,
Thank You again.
I tested... and got my result right today.
I confused ... and not sure the problem.
I'll test again later. Because I'd like to know and solve exactly...
Anyway my program changed to use "execute immediate" to avoid wrong result.
Thank You
Hi Gary,
Thank You for your suggestion.
It's my wrong.
DESC NAS
Name Null? Type
-----------------------------------------
USER_NAME NOT NULL VARCHAR2(255)
MAC VARCHAR2(50)
on both columns have the same data...
And I used column name to be variable name ....
So, that make my result wrong.
select count(*) from nas WHERE USER_NAME = '00:00:00:00:00:00';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=18)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'USER_NAME_IDX' (NON-UNIQUE) (Cost=1 Card=1 Bytes=18)
var mac varchar2(17);
exec :mac := '00:00:00:00:00:00';
select count(*) from nas WHERE USER_NAME = mac;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=304 Card=1 Bytes=36)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'NAS' (Cost=304 Card=1 By tes=36)
Thank You.
Post a Comment