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 دبا _ الآراء

In Some situations where they need to translate  s they should use yes as  نعم  can be used as وقال أن


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.디 에이 _전망에스

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

Tuesday, 20 September 2011

Oracle database Instrumentation Example using debug.f

In this post we would see a practical example of Oracle instrumentation.
This example shows the instrumenting mechanism of debug.f across sessions and across schema

First we have to download and install debug.f from
http://gerardnico.com/wiki/database/oracle/debugf
which is probably the only place you might find the debug.f code.

The different procedures available for the debug purpose are

1)debug.init('ALL','C:\debugf123\temp\test.txt','SYSTEM','YES','DD/MM/YYYY HH24:MI:SS',30,'YES');

This is used to intialize the debug the first parameter 'ALL' meaning all modules(can be function,procedure or package etc) and SYSTEM meaning the schema i want to debug

2)debug.f('the first is %s',v_word1);
This one works like printf in C and the maximum you can give is 10 parameters where v_word1 is a parameter

3)debug.fa('the third is %s and %s',debug.argv(v_word1,v_amount));
This is same as debug.f but here you can give more than 10 parameters

4)debug.clear :-This is used to stop the debug.

The sample function which i have taken and modified to be instrumented is available at  http://www.shareoracleapps.com/search/label/Workflow.This site offers very good information about financial transactions and also contains code for general bank operation which would be helpful if you want to try creating a basic bank application as test project.

The sample function ruppee_to_word  is


CREATE OR REPLACE FUNCTION ruppee_to_word (amount IN NUMBER)   RETURN VARCHAR2 AS
v_length   INTEGER         := 0;  
v_num2     VARCHAR2 (50)   := NULL;  
v_amount   VARCHAR2 (50)   := TO_CHAR (TRUNC (amount));  
v_word     VARCHAR2 (4000) := NULL;  
v_word1    VARCHAR2 (4000) := NULL;
TYPE myarray IS TABLE OF VARCHAR2 (255);
v_str      myarray         := myarray (' Thousand ', ' Lakh ', ' Crore ');
BEGIN  
debug.f('the entered time %s',sysdate);
IF ((amount = 0) OR (amount IS NULL))   THEN    
v_word := 'zero';  
ELSIF (TO_CHAR (amount) LIKE '%.%')  
THEN    
IF (SUBSTR (amount, INSTR (amount, '.') + 1) > 0)    
THEN         v_num2 := SUBSTR (amount, INSTR (amount, '.') + 1);

IF (LENGTH (v_num2) < 2)        
THEN            v_num2 := v_num2 * 10;        
END IF;

v_word1 := ' AND ' || (TO_CHAR (TO_DATE (SUBSTR (v_num2, LENGTH (v_num2) - 1, 2), 'J'),'JSP'))  || ' paise ';  
debug.f('the first is %s',v_word1);    
v_amount := SUBSTR (amount, 1, INSTR (amount, '.') - 1);  
debug.fa('the second is %s and %s',debug.argv(v_word1,v_amount));      
v_word := TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 2, 3),'J'),'Jsp') || v_word;        
v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 3);
debug.fa('the third is %s and %s',debug.argv(v_word1,v_amount));
FOR i IN 1 .. v_str.COUNT        
LOOP          
EXIT WHEN (v_amount IS NULL);          
v_word :=                
TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 1,
         2),
 'J'),                           'Jsp'                          )               || v_str (i)               || v_word;            v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 2);        
 END LOOP;    
END IF;  
ELSE    
v_word := TO_CHAR (TO_DATE (TO_CHAR (amount, '999999999'), 'J'), 'JSP');  
END IF;

v_word := v_word || ' ' || v_word1 || ' only ';  
v_word := REPLACE (RTRIM (v_word), ' ', ' ');  
v_word := REPLACE (RTRIM (v_word), '-', ' ');  
debug.f('the entered time %s and the word is %s',sysdate,v_word);

RETURN INITCAP (v_word);

END ruppee_to_word;

I have created the package debug and function rupee_to_word on SYSTEM user and given the necessary priviledges to other users where the debug package can be used for instrumentation

I have then created three separate sessions to show that we can initialize debug across session and get the information about other sessions.

The first session:-

DECLARE
n VARCHAR2(4000);
l_amount NUMBER:=1234;
BEGIN
debug.init('ALL','C:\debugf123\temp\test.txt','SYSTEM','YES','DD/MM/YYYY HH24:MI:SS',30,'YES');
n:=ruppee_to_word(l_amount);
debug.f('The Translation of Rupee %s is %s',l_amount,n);
END;

The second session:-

DECLARE
n VARCHAR2(4000);
l_amount NUMBER:=2132.43;
BEGIN
n:=ruppee_to_word(l_amount);
debug.f('The Translation of Rupee %s is %s',l_amount,n);
END;

the third session 

DECLARE
n VARCHAR2(4000);
l_amount NUMBER:=456;
BEGIN
debug.init('ALL','C:\debugf123\temp\test.txt','SYSTEM','YES','DD/MM/YYYY HH24:MI:SS',30,'YES');
n:=ruppee_to_word(l_amount);
debug.f('The Translation of Rupee %s is %s',l_amount,n);
debug.clear;
END;

I had also created one more session in ATM schema to show that debugging can be done across schema  and run the debug initilation in SYSTEM Schema as
DECLARE
BEGIN
debug.init('ALL','C:\debugf123\temp\test.txt','ATM','YES','DD/MM/YYYY HH24:MI:SS',30,'YES');
END;

Then called the below block in ATM schema
 
DECLARE
n VARCHAR2(4000);
l_amount NUMBER:=456;
BEGIN
n:=ruppee_to_word(l_amount);
END;

Please find the logged data for all the sessions below


Debug parameters initialized on 20-SEP-2011 22:20:43
           USER: SYSTEM
        MODULES: ALL
       FILENAME: C:\debugf123\temp\test.txt
      SHOW DATE: YES
    DATE FORMAT: DD/MM/YYYY HH24:MI:SS
    NAME LENGTH: 30
SHOW SESSION ID: YES

Session_id     Data time                            Owner.procedure                            logged message
600 -            20/09/2011 22:20:43(         SYSTEM.RUPPEE_TO_WORD   10) the entered time 20-SEP11 
 600 - 20/09/2011 22:20:43(         SYSTEM.RUPPEE_TO_WORD   45) the entered time 20-SEP-11 and the word is ONE THOUSAND TWO HUNDRED THIRTY FOUR  only
600 - 20/09/2011 22:20:43(        SYSTEM.ANONYMOUS BLOCK    7) The Translation of Rupee 1234 is One Thousand Two Hundred Thirty Four  Only
601 - 20/09/2011 22:21:10(         SYSTEM.RUPPEE_TO_WORD   10) the entered time 20-SEP-11
601 - 20/09/2011 22:21:10(         SYSTEM.RUPPEE_TO_WORD   23) the first is  AND FORTY-THREE paise 
601 - 20/09/2011 22:21:10(         SYSTEM.RUPPEE_TO_WORD   25) the second is  AND FORTY-THREE paise  and 2132
601 - 20/09/2011 22:21:10(         SYSTEM.RUPPEE_TO_WORD   28) the third is  AND FORTY-THREE paise  and 2
601 - 20/09/2011 22:21:10(         SYSTEM.RUPPEE_TO_WORD   45) the entered time 20-SEP-11 and the word is Two Thousand One Hundred Thirty Two  AND FORTY THREE paise  only
601 - 20/09/2011 22:21:10(        SYSTEM.ANONYMOUS BLOCK    7) The Translation of Rupee 2132.43 is Two Thousand One Hundred Thirty Two  And Forty Three Paise  Only
602 - 20/09/2011 22:21:24(         SYSTEM.RUPPEE_TO_WORD   10) the entered time 20-SEP-11
602 - 20/09/2011 22:21:24(         SYSTEM.RUPPEE_TO_WORD   45) the entered time 20-SEP-11 and the word is FOUR HUNDRED FIFTY SIX  only
602 - 20/09/2011 22:21:24(        SYSTEM.ANONYMOUS BLOCK    7) The Translation of Rupee 456 is Four Hundred Fifty Six  Only

Debug parameters initialized on 20-SEP-2011 22:25:17
           USER: ATM
        MODULES: ALL
       FILENAME: C:\debugf123\temp\test.txt
      SHOW DATE: YES
    DATE FORMAT: DD/MM/YYYY HH24:MI:SS
    NAME LENGTH: 30
SHOW SESSION ID: YES

611 - 20/09/2011 22:25:17(         SYSTEM.RUPPEE_TO_WORD   10) the entered time 20-SEP-11
611 - 20/09/2011 22:25:17(         SYSTEM.RUPPEE_TO_WORD   45) the entered time 20-SEP-11 and the word is FOUR HUNDRED FIFTY SIX  only
611 - 20/09/2011 22:25:17(           ATM.ANONYMOUS BLOCK    7) The Translation of Rupee 456 is Four Hundred Fifty Six  Only

Debug parameters initialized on 20-SEP-2011 23:10:17
           USER: ATM
        MODULES: ALL
       FILENAME: C:\debugf123\temp\test.txt
      SHOW DATE: YES
    DATE FORMAT: DD/MM/YYYY HH24:MI:SS
    NAME LENGTH: 30
SHOW SESSION ID: YES

617 - 20/09/2011 23:10:31(         SYSTEM.RUPPEE_TO_WORD   10) the entered time 20-SEP-11
617 - 20/09/2011 23:10:31(         SYSTEM.RUPPEE_TO_WORD   45) the entered time 20-SEP-11 and the word is FOUR HUNDRED FIFTY SIX  only