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
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
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
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
- 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
- Then consider the years in between from next year of start date to the year before the end date year
- 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ßeand 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');
