Thursday, 22 June 2017

Function to convert numbers to words for cheques(OFFTOPIC)

create or replace function fn_num_words(input_no number) return varchar2 is
  Result varchar2(32767);
  type rec_tens is table of varchar2(32767) index by PLS_integer;
  l_tens rec_tens;
  L_HUN  REC_TENS;
  type rec_num is table of varchar2(32767) index by PLS_integer;
  l_num     rec_num;
  L_VISITED BOOLEAN := false;
  l_twin    boolean := false;
  l_zero    boolean := false;
begin
  l_tens(0) := '';
  l_tens(1) := 'TEN';
  l_tens(2) := 'TWENTY';
  l_TENS(3) := 'THIRTY';
  l_tens(4) := 'FORTY';
  l_tens(5) := 'FIFTY';
  l_tens(6) := 'SIXTY';
  l_tens(7) := 'SEVENTY';
  l_tens(8) := 'EIGHTY';
  l_tens(9) := 'NIGHTY';
  L_HUN(1) := '';
  L_HUN(2) := '';
  L_HUN(3) := 'HUNDRED';
  L_HUN(4) := 'THOUSAND';
  L_HUN(5) := '';
  L_HUN(6) := 'HUNDRED';
  L_HUN(7) := 'MILLION';
  L_HUN(8) := '';
  L_HUN(9) := 'HUNDRED';
  L_HUN(10) := 'BILLION';
  L_HUN(11) := '';
  L_HUN(12) := 'HUNDRED';
  L_HUN(13) := 'TRILLION';
  l_num(11) := 'ELEVEN';
  l_num(12) := 'TWELVE';
  l_num(13) := 'THIRTEEN';
  l_num(14) := 'FOURTEEN';
  l_num(15) := 'FIFTEEN';
  l_num(16) := 'SIXTEEN';
  l_num(17) := 'SEVENTEEN';
  l_num(18) := 'EIGHTEEN';
  l_NUM(19) := 'NINETEEN';
  l_num(1) := 'ONE';
  l_num(2) := 'TWO';
  l_num(3) := 'THREE';
  l_num(4) := 'FOUR';
  l_num(5) := 'FIVE';
  l_num(6) := 'SIX';
  l_num(7) := 'SEVEN';
  l_num(8) := 'EIGHT';
  l_NUM(9) := 'NINE';
  l_num(0) := '';
  l_num(10) := 'TEN';
  if length(input_no) = 1 then
    result := l_num(input_no);
  else
    IF instr(substr(input_no, -2), 1) = 1 AND L_VISITED = FALSE then
 
      select l_num(substr(input_no, -2)) INTO RESULT FROM DUAL;
      L_VISITED := true;
    ELSif L_VISITED = FALSE then
      SELECT l_tens(substr(input_no, -2, 1)) || ' ' ||
             l_num(substr(input_no, -1))
        into result
        from dual;
      L_VISITED := trUE;
    END IF;

  END IF;
  if length(input_no) > 2 then

    IF (substr(input_no, -3, 1) = 0) and
       ((substr(input_no, -2, 1) <> 0) or (substr(input_no, -1, 1) <> 0)) then
      l_zero := true;
    end if;

    if l_zero then
      result := ' AND ' || result;
    end if;
  end if;
  for rec in 1 .. length(input_no) loop
    dbms_output.put_line('rec' || rec);
    dbms_output.put_line('l_hun(rec)' || l_hun(rec));
    if rec <= 5 then
      if rec = 3 and
         (substr(input_no, -rec, 1) <> 0 or length(input_no) = 3) then
        result := l_num(substr(input_no, -rec, 1)) || ' ' || l_hun(rec) ||
                  ' AND ' || result;
      end if;
 
      IF rec > 3 and substr(input_no, - (rec + 1)) <> 0 and mod(rec, 2) = 0 then
        IF instr(substr(input_no, - (rec + 1)), 1) = 1 then
          result := l_num(substr(input_no, - (rec + 1), 2)) || ' ' ||
                    l_hun(rec) || ' ' || result;
        elsIF instr(substr(input_no, - (rec + 1), 1), 1) <> 1 and
              substr(input_no, - (rec + 1), 1) <> 0 then
          result := l_tens(substr(input_no, - (rec + 1), 1)) || ' ' ||
                    l_num(substr(input_no, -rec, 1)) || ' ' || l_hun(rec) || ' ' ||
                    result;
        end if;
      elsif rec > 3 and mod(length(input_no), 2) = 0 and
            substr(input_no, -rec, 1) <> 0 and mod(rec, 2) = 0 then
        result := l_num(substr(input_no, -rec, 1)) || ' ' || l_hun(rec) || ' ' ||
                  result;
      end if;
 
    elsif rec in (6) and (substr(input_no, -rec, 1) <> 0) then
      result := l_num(substr(input_no, -rec, 1)) || ' ' || l_hun(rec) || ' ' ||
                result;
    elsif rec in (7) and length(input_no) = 7 then
      result := l_num(substr(input_no, -rec, 1)) || ' ' || l_hun(rec) || ' ' ||
                result;
 
    elsif rec in (7) then
      IF instr(substr(input_no, - (rec + 1)), 1) = 1 then
        result := l_num(substr(input_no, - (rec + 1), 2)) || ' ' ||
                  l_hun(rec) || ' ' || result;
   
      elsIF rec in (7) AND (substr(input_no, -rec, 1) <> 0) then
        result := l_tens(substr(input_no, - (rec + 1), 1)) || ' ' ||
                  l_num(substr(input_no, -rec, 1)) || ' ' || l_hun(rec) || ' ' ||
                  result;
      END IF;
    elsif rec in (9) and (substr(input_no, -rec, 1) <> 0) then
      result := l_num(substr(input_no, -rec, 1)) || ' ' || l_hun(rec) || ' ' ||
                result;
    elsif rec in (10) and length(input_no) = 10 then
      result := l_num(substr(input_no, -rec, 1)) || ' ' || l_hun(rec) || ' ' ||
                result;
    elsif rec in (10) then
      IF instr(substr(input_no, - (rec + 1)), 1) = 1 then
        result := l_num(substr(input_no, - (rec + 1), 2)) || ' ' ||
                  l_hun(rec) || ' ' || result;
   
      elsIF rec in (10) AND (substr(input_no, -rec, 1) <> 0) then
        result := l_tens(substr(input_no, - (rec + 1), 1)) || ' ' ||
                  l_num(substr(input_no, -rec, 1)) || ' ' || l_hun(rec) || ' ' ||
                  result;
   
      END IF;
    elsif rec in (12) and (substr(input_no, -rec, 1) <> 0) then
      result := l_num(substr(input_no, -rec, 1)) || ' ' || l_hun(rec) || ' ' ||
                result;
    elsif rec in (13) and length(input_no) = 13 then
      result := l_num(substr(input_no, -rec, 1)) || ' ' || l_hun(rec) || ' ' ||
                result;
    end if;

  end loop;

  return(Result);
end fn_num_words;

Thursday, 4 May 2017

Good concept of Islamic banking

Oracle database tables,functions,procedures can be named in Arabic for Islamic banking

Monday, 1 May 2017

Easy implementation of Foreign language in Oracle database

As per my understanding oracle database is difficult to use in Arabic

The implementation can be done by using google translate 

For example dba_views

The simple implementation is

dba_views can be translated with below steps

1. Translate views into الآراء
2.Translate dba into دبا
3.Translate dba_views  as دبا _ الآراء
4.Build a different dictionary from a to z and translate in verse order in database.
Korean Language implementation

1.Convert Korean characters into dba_views

2.For example 

i.d into 디 
ii.b into 비 
iii.a into 에이
iv.dba can be converted into  디 에이
v.view can be converted into 전망
vii.s can be converted into 에스
vi.디 에이 _전망에스
Viii.build a dictionary with vowels in oracle database. Banks with oracle database in Tamil Nadu can use translate Tamil using the following link from wikipedia

Regards,
P Santhiagu 

Monday, 1 September 2014

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


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


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');





Monday, 10 October 2011

(OFFTOPIC) Searching a Long Column using PLSQL in Oracle Database 10g

This post is just the continuation of the post to search long column in SQL ,the only difference being this is done for searching in PLSQL

Here i have used the concept of global temporary table with clause on commit delete rows,this concept of global temporary tables is very interesting,for all the information check it in http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2213

create global temporary table LONG_TEST_TEMP
(
TABLE_NAME VARCHAR2(30),
COLUMN_NAME VARCHAR2(30),
ROW_ID VARCHAR2(30),
CLOB_DATA CLOB
)
on commit delete rows;

The below is a normal table where you can get the result of the search item

create table CHECK_VALUE_INFO
(
TABLE_NAME VARCHAR2(30),
COLUMN_NAME VARCHAR2(30),
ROW_ID VARCHAR2(30)
)

Compile the below program to search for the LONG column in table with and without the COMMIT to see one good difference between them

This program also supports a wild card search of a LONG column value within the schema with mainly the overhead on time and resources

create or replace procedure pr_check_value_long(p_value IN OUT VARCHAR2,
p_table IN VARCHAR2 DEFAULT NULL,
p_sql_col IN VARCHAR2 DEFAULT NULL) is
Cursor cr_usr is
select *
from user_tab_columns
where data_type = 'LONG'
and table_name = NVL(p_table,
table_name)
and column_name = NVL(p_sql_col,
column_name);
l_sql_text VARCHAR2(32000);
l_count NUMBER;
l_sql_col VARCHAR2(32000);
BEGIN
for rec in cr_usr
Loop
l_sql_col := '"' || rec.column_name || '"';
BEGIN
EXECUTE IMMEDIATE 'insert into long_test_temp select ' || '''' ||
rec.table_name || '''' || ',' || '''' ||
l_sql_col || '''' || ',' || 'rowid' || ',' ||
'to_lob(' || l_sql_col || ')' || ' from ' ||
rec.table_name;
l_sql_text := ' insert into CHECK_VALUE_INFO SELECT TABLE_NAME,COLUMN_NAME,ROW_ID
FROM long_test_temp WHERE clob_data LIKE ' || '''' || '%' ||
p_value || '%' || '''';
EXECUTE IMMEDIATE l_sql_text;
l_sql_text := NULL;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;
end loop;
p_value := NULL;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;

Below is a example of doing the search for the column LONG_VALUE of datatype LONG and table name TEST_LONG_VALUE

DECLARE
P_VALUE VARCHAR2(4000):='131';
BEGIN
pr_check_value_lonG(p_value,'TEST_LONG_VALUE','LONG_VALUE');
END;

Also an example of how you can check the result

SELECT a.*  FROM test_long_value a WHERE a.ROWID IN
(SELECT ROW_ID FROM CHECK_VALUE_INFO b where b.table_name='TEST_LONG_VALUE' and b.column_name='LONG_VALUE')

Happy times searching :) 

Monday, 26 September 2011

(OFFTOPIC) Simplest method for searching a LONG column using SQL IN Oracle Database 10G

In this example we will see how we can search a LONG column using SQL.

The simple concepts used in the example are LONG column can be converted to CLOB using TO_LOB function ,CLOB column is searchable and rowid is used to uniquely identify a row in a table.

First create a Table with values as the rowid and LONG column of the table in which you want to search the LONG column.

Create table test_long_search as select rowid row_id, to_lob(message) message  from message_log;

Here Message_Log is the table which contains the LONG column you want to search.

Then you can search the value as

select * from test_long_search where message like '%FAILED FOR SOME REASON%'

you will get

ROW_ID
MESSAGE
AAAswsAAKAAAK8OAAA
<CLOB>
AAAswsAAKAAAK/kAAC
<CLOB>


Then in the main table you can search like

select * from message_log where rowid in ('AAAswsAAKAAAK8OAAA','AAAswsAAKAAAK/kAAC');

EMAILED
REFERENCE_NO
RECEIVER
MESSAGE
Y
123456
SAN
<Long>
Y
123456
SAN
<Long>

we hope this example was helpfull :)

Saturday, 24 September 2011

Debugging Oracle Forms Application server(9i and 10g)

There is a very good Oracle documentation on manually configuring  Oracle form trace and debugging available at Oracle site at Tips, Techniques and Debugging of Oracle forms Application server.

This link talks about the debugging and tracing options available in Oracle 9iAS but most of the things are applicable to Oracle 10gAS as well.

Documents available at this link mainly talk about Diagnostics methods

1)Enabling Form trace:-Topics like how to configure form trace,start  the trace and also how to convert trace file to readable XML files

2)Servlet logs:-

  • It provides a record of all Oracle9i Forms sessions, including session start and endtimes, and the user’s IP address and host name 
  •  Monitoring of Oracle9i Forms-related network traffic and performance 
  • It also gives Information for debugging site configuration problems (debug-level
    logging)
     
3)Debugging Client and Server crash  and also discussess about Common forms(FRM errors) and how to solve them.

 4)There is also a Sample Application which you can use to learn some functions in forms and as well as debug it.The code for the Sample Application is available at
http://sheikyerbouti.developpez.com/tutoforms10g/files/tutoforms10g.zip

In case you are not able to find the Debugging documentation or the link expires then do a google search for the following pdf files

1)tutoforms10g.pdf
2)trace9i-133379.pdf
3)forms9idiagnostics-129282.pdf

If you have any questions do mention in the Comments section

Happy Debugging