Please suggest me how to do the comparison
DECLARE TYPE TEM IS TABLE OF VARCHAR2(1000); EM TEM; P_MAIL VARCHAR2(1000); BEGIN P_MAIL := UPPER('firstname.lastname@example.org'); SELECT EMAIL BULK COLLECT INTO EM FROM ( SELECT UPPER(EMAIL_ID) AS EMAIL FROM SONARA_CONSULTANT_DETAILS UNION SELECT EMAIL_ID_2 AS EMAIL FROM SONARA_CONSULTANT_DETAILS ) WHERE EMAIL IS NOT NULL; FOR I IN EM.FIRST..EM.LAST LOOP IF P_MAIL = EM(I) THEN // Here i am comparing the mail id with collection but it always goes to INVALID DBMS_OUTPUT.PUT_LINE('VALID'); ELSE DBMS_OUTPUT.PUT_LINE('INVALID'); END IF; END LOOP; END; }
Sudhir_Meru wrote:Your code does not show any validation. It shows filtering. By doing it the most inefficient, slow and unscalable way possible.
I am using oracle 11g XE, I am doing a validation using bulk collect collection.
If the entire table wants to be compared to a specific value:
select * from table where column = 'some-value'
select pk, case when column = 'value' then 'VALID' else 'INVALID' end as MATCH_RESULT from table
Which gives me the following result
DECLARE TYPE TEM IS TABLE OF VARCHAR2(1000); EM TEM; P_EMAIL VARCHAR2(1000) := 'email@example.com'; BEGIN WITH SONARA_CONSULTANT_DETAILS AS ( SELECT 'firstname.lastname@example.org' AS emailAddr FROM DUAL UNION SELECT 'email@example.com' AS emailAddr FROM DUAL UNION SELECT 'firstname.lastname@example.org' AS emailAddr FROM DUAL UNION SELECT 'email@example.com' AS emailAddr FROM DUAL UNION SELECT 'firstname.lastname@example.org' AS emailAddr FROM DUAL UNION SELECT 'email@example.com' AS emailAddr FROM DUAL UNION SELECT 'firstname.lastname@example.org' AS emailAddr FROM DUAL UNION SELECT 'email@example.com' AS emailAddr FROM DUAL ) SELECT emailAddr BULK COLLECT INTO EM FROM SONARA_CONSULTANT_DETAILS; FOR indx IN EM.FIRST .. EM.LAST LOOP IF UPPER(P_EMAIL) = UPPER(EM(indx)) THEN DBMS_OUTPUT.PUT_LINE('VALID'); ELSE DBMS_OUTPUT.PUT_LINE('INVALID'); END IF; END LOOP; END; /
INVALID INVALID INVALID INVALID INVALID INVALID INVALID VALID PL/SQL procedure successfully completed. SQL>
ix := em.first; while true loop if upper(em(ix)) = upper(test_value) then ... /* valid */ else ... /* invalid */ end if; if ix >= em.last then exit; else ix ;= em.next; end if; end loop;
dariyoosh wrote:So never mind that this approach is wrong, consumes private process memory unnecessarily, is slow, cannot scale, and fails to use the SQL language correctly?
It seems that you don't compare everywhere in uppercase, so for me this is rather a letter case problem in your string addresses.
Etbin wrote:The sample code is for processing an associative array - makes little sense to step through a normal array in this fashion.
Generally you can't be sure the em table does not contain null values.
I don't remember ever doing something alike so consider it as template/pseudocode
You can therefore safely use a FOR loop to step though the array as follows:
SQL> declare 2 type TObjectNames is table of varchar2(30); 3 nameArray TObjectNames; 4 begin 5 select 6 object_name bulk collect into nameArray 7 from all_objects 8 where 1=2; 9 10 if nameArray is null then 11 dbms_output.put_line( 'nameArray is null' ); 12 else 13 dbms_output.put_line( 'nameArray is not null' ); 14 end if; 15 end; 16 / nameArray is not null PL/SQL procedure successfully completed. SQL>
for i in 1..nameArray.Count loop // do stuff end loop;
dariyoosh wrote:Then point out that string comparison is case sensitive. But you elected to post FLAWED code showing that. Which the OP may very well take as the correct way to do it, copy-and-paste it, and use it - not understanding that code that seems to work does not imply correctly designed and written code.
@ Billy Verreynne
The question was not about the approach and I don't remember to have given any idea about this (as you already did in fact properly) yet, he didn't understand what was the problem with his (non efficient) solution which I believe was the letter case I just wanted to point that out (as it may appear in his future problems).
So take a Cafe and calm down.Coffee you mean? How about you first deleting that flawed sample code?