My Interesting Accepted Answers in Stack Overflow (OFFTOPIC)

Stack overflow is a great site and i have been using it for the past 2+ years to help and get help from the programming community.So i had a thought of sharing some interesting answers i had done in Stack Overflow.

1)Plsql To spell Number in Italian Currency

Please find the stack overflow link here  PLSQL to spell currency in Italian 

This answer is just for showing the possibilities we can do with Oracle database and as mentioned by User APC it over relays on internet connection to database.Also nowadays most websites use SSL which makes this code invalid

Still there is no worry since we can use Oracle wallet to verify SSL certificate 

Please find my answer which even though not accepted will be useful to someone using internet in oracle database.This answer returns latitude and longitude of the address given  using google search engine

Are BING geocoding & Google geocoding a web-service, or web-api?

Interesting factor:- One interesting aspect about the above answer is that we had to specifically use the below mozilla firefox google search URL since the other common browsers Google chrome and IE do not display the latitude and longitude value in the page source returned by google search.

HttpUriType('http://www.google.co.in/search?q=find+latitude+and+longitude+of+' ||address||'&'||'ie=utf-'||'8&'||'oe=utf-'||'8&'||'aq=t'||'&'||'rls=org.mozilla'||':'||'en-US'||':'||'official'||'&'||'client=firefox-a');

2)Using Function equivalent of using Function in the "IN" Operator

Please find the stack overflow link here  Create a function returning values for a “SELECT * FROM tab WHERE name IN (function())”

This answer suggest a workaround to achieve the same effect using function returning multiple values in IN operator.The most important point to note is that using function in SELECT statement would cause index not to be used in the column used in the function

Please find the link on the article about using functions in SELECT statement 

 Interesting factor:-Instead of getting multiple value from function used in SELECT statement which doesn't work without nested table or collections .

We can  just check whether the value together with the where clause parameter returns any rows.So if the count is greater then return 1 otherwise return  0.The snippet below


Before

SELECT p_value 
               FROM parameter_table
               WHERE p_name LIKE 'A_04'

After

BEGIN 
 select count(1) into l_count from parameter_table where p_value =p_value1 
 and p_name=p_name1;
 if l_count > 0
 then 
 return 1;
 else
 return 0;
 end if;

 3)Performance improvement for Get record based on year in oracle:-

Please find the stack overflow link here Get record based on year in oracle

For getting the number of days between two dates the OP was counting each day between the two days using connect by


Interesting Factor:- We suggested an Approach instead of counting each day between year the following can be done

1. First consider only the number of days between start date and the year end of that year or End date if it is in same year
2. Then consider the years in between from next year of start date to the year before the end date year
3. Finally consider the number of days from start of end date year to end date

4)Using underscore on left side of where clause  similar to using them in LIKE operator in where clause 

Please find the stack overflow link here Compare values with different accent in oracle database

Normally oracle doesn't allow using underscore as single wild card on the left side of a where clause.The OP wanted to use it in left side since we was comparing values with different accents

Interesting Factor:- In addition to the answer the idea was to match the accent characters with underscore for the number of equivalent characters used in english. This solution is based on the assumption used by the OP to make _ (underscore) match with 'any' character in the database

SELECT word FROM test1
WHERE NLS_UPPER(word, 'NLS_SORT = XGERMAN') = 'GROSSE';

WORD
------------
GROSSE
Große
große

and use the below to match GROÑßE with GRONSSE the english equivalent accent with three _

NLS_UPPER('GROÑßE','NLS_SORT=XWEST_EUROPEAN')=UPPER('gro___e');