Thursday, July 28, 2011

Table Locked By EBS User - By Khurram

Table Locked By EBS-R12 User - By Khurram



SELECT
      d_c.owner As Schema_Name
      ,d_c.object_name As Schema_Obj
      ,d_c.object_type As Schema_Obj_Type
      ,v_p.pid As Process_ID
      ,v_p.spid AS OS_Process_ID
      ,v_s.process As Process_#
      ,v_s.status As Session_Status
      ,v_s.module As Apps_Module
      ,v_s.machine As Apps_MAchine
      ,f_u.user_name As Locked_By
      ,f_l.start_time As Locked_On
      ,v_s.osuser As OS_User
      ,v_l_o.oracle_username As Apps_User     
FROM
      fnd_logins        f_l
      ,fnd_user         f_u
      ,v$locked_object  v_l_o
      ,dba_objects      d_c
      ,v$process        v_p
      ,v$session        v_s   
WHERE
      v_l_o.object_id = d_c.object_id
AND   v_s.sid = v_l_o.session_id
AND   v_p.pid = f_l.pid(+)
AND   f_l.user_id = f_u.user_id(+)
AND   v_s.paddr = v_p.addr
AND   v_p.spid = f_l.process_spid(+)
AND   nvl(v_s.status,'ABC') != 'KILLED'
AND   d_c.object_name LIKE '%' || upper('&Table_Name_R_LeaveBlank') || '%'

;

Oracle - Foreign Currency in Word by Khurram













create or replace function Spell_Number( p_number in number )

return varchar2

as

/*------------------------------------------------------------------------
Author            Khurram Altaf
Created On        July 27, 2011
Trigger           Spell_Number
Description       Generates a Word Expression against Number
------------------------------------------------------------------------*/

type myArray is table of varchar2(255);

l_str

myArray := myArray( '',

' thousand ', ' million ',

' billion ', ' trillion ',

' quadrillion ', ' quintillion ',

' sextillion ', ' septillion ',

' octillion ', ' nonillion ',

' decillion ', ' undecillion ',

' duodecillion ' );

l_num varchar2(50) default trunc( p_number );

l_return varchar2(4000);

begin

for i in 1..l_str.count

loop

exit when l_num is null;

if ( substr(l_num, length(l_num)-2, 3) <> 0 )

then

l_return := to_char(to_date(substr(l_num, length(l_num)-2, 3),'J' ),
'Jsp' ) || l_str(i) || l_return;

end if;

l_num := substr( l_num, 1, length(l_num)-3 );

end loop;

return l_return;

end;

---------------------------------------------------------------

select Spell_Number(193246) from dual;

-------------------------------------------------------------

Wednesday, July 27, 2011

Oracle - local Currency In Word By Khurram

















CREATE OR REPLACE FUNCTION Rupees_In_Word (Rupees IN NUMBER) RETURN VARCHAR2

/*------------------------------------------------------------------------
Author            Khurram Altaf
Created On        July 27, 2011
Trigger           Ruppee_In_Word
Description       Generates a Word Expression against Number
------------------------------------------------------------------------*/

AS
   v_rupee        VARCHAR2 (4000) := NULL;
   v_paisa        VARCHAR2 (4000) := NULL;
   p_amount       VARCHAR2 (50)   := TO_CHAR (TRUNC (Rupees));
   v_point        VARCHAR2 (50)   := NULL;
  


   TYPE Rupee_Array IS TABLE OF VARCHAR2 (255);

   v_str_arry      Rupee_Array         := Rupee_Array (' Thousand ', ' Lakh ', ' Crore ');
BEGIN
   IF ((Rupees = 0) OR (Rupees IS NULL))
   THEN
      v_rupee := 'zero';
   ELSIF (TO_CHAR (Rupees) LIKE '%.%')
   THEN
      IF (SUBSTR (Rupees, INSTR (Rupees, '.') + 1) > 0)
      THEN
         v_point := SUBSTR (Rupees, INSTR (Rupees, '.') + 1);

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

         v_paisa :=
               ' AND '
            || (TO_CHAR (TO_DATE (SUBSTR (v_point, LENGTH (v_point) - 1, 2),
                                  'J'),
                         'JSP'
                        )
               )
            || ' paise ';
         p_amount := SUBSTR (Rupees, 1, INSTR (Rupees, '.') - 1);
         v_rupee :=
               TO_CHAR (TO_DATE (SUBSTR (p_amount, LENGTH (p_amount) - 2, 3),
                                 'J'
                                ),
                        'Jsp'
                       )
            || v_rupee;
         p_amount := SUBSTR (p_amount, 1, LENGTH (p_amount) - 3);

         FOR i IN 1 .. v_str_arry.COUNT
         LOOP
            EXIT WHEN (p_amount IS NULL);
            v_rupee :=
                  TO_CHAR (TO_DATE (SUBSTR (p_amount, LENGTH (p_amount) - 1,
                                            2),
                                    'J'
                                   ),
                           'Jsp'
                          )
               || v_str_arry (i)
               || v_rupee;
            p_amount := SUBSTR (p_amount, 1, LENGTH (p_amount) - 2);
         END LOOP;
      END IF;
   ELSE
      v_rupee := TO_CHAR (TO_DATE (TO_CHAR (Rupees, '999999999'), 'J'), 'JSP');
   END IF;

   v_rupee := v_rupee || ' ' || v_paisa || ' only ';
   v_rupee := REPLACE (RTRIM (v_rupee), ' ', ' ');
   v_rupee := REPLACE (RTRIM (v_rupee), '-', ' ');
   RETURN INITCAP (v_rupee);
END Rupees_In_Word;

-------------------------------------------------------------

select Rupees_In_Word (193246.65) from dual;

-------------------------------------------------------------

Friday, July 22, 2011

Muslim inventions that shaped the modern world


Muslim inventions that shaped the modern world

In 9th century Spain, Muslim inventor Abbas ibn Firnas designed a flying machine hundreds of years before Da Vinci drew plans of his own.



Muslim inventions celebrate 1,000 years of "forgotten" Muslim heritage
From coffee to cranks, items we couldn't live without today are Muslim inventions
Modern hospitals and universities both began in 9th century by North Africa

Think of the origins of that staple of modern life, the cup of coffee, and Italy often springs to mind. But in fact, Yemen is where the ubiquitous brew has its true origins.
Along with the first university, and even the toothbrush, it is among surprising Muslim inventions that have shaped the world we live in today. The origins of these fundamental ideas and objects, the basis of everything from the bicycle to musical scales are the focus of "1001 Inventions".
"There's a hole in western knowledge, they leap frog from the Renaissance to the Greeks." Hospitals as we know them today, with wards and teaching centers, come from 9th century Egypt.

10 OUTSTANDING MUSLIM INVENTIONS:

1. Surgery
Around the year 1,000, the celebrated doctor Al Zahrawi published a 1,500 page illustrated encyclopedia of surgery that was used in Europe as a medical reference for the next 500 years. Among his many inventions, Zahrawi discovered the use of dissolving cat gut to stitch wounds beforehand a second surgery had to be performed to remove sutures. He also reportedly performed the first caesarean operation and created the first pair of forceps.

2. Coffee
Now the Western world's drink du jour, coffee was first brewed in Yemen around the 9th century. In its earliest days, coffee helped Sufis stay up during late nights of devotion. Later brought to Cairo by a group of students, the coffee buzz soon caught on around the empire. By the 13th century it reached Turkey, but not until the 16th century did the beans start boiling in Europe, brought to Italy by a Venetian trader.

3. Flying machine
"Abbas ibn Firnas was the first person to make a real attempt to construct a flying machine and fly," said Hassani. In the 9th century he designed a winged apparatus, roughly resembling a bird costume. In his most famous trial near Cordoba in Spain, Firnas flew upward for a few moments, before falling to the ground and partially breaking his back. His designs would undoubtedly have been an inspiration for famed Italian artist and inventor Leonardo Da Vinci's hundreds of years later, said Hassani.

4. University
In 859 a young princess named Fatima al-Firhi founded the first degree-granting university in Fez, Morocco. Her sister Miriam founded an adjacent mosque and together the complex became the al-Qarawiyyin Mosque and University. Still operating almost 1,200 years later, Hassani says he hopes the center will remind people that learning is at the core of the Islamic tradition and that the story of the al-Firhi sisters will inspire young Muslim women around the world today.

5. Algebra
The word algebra comes from the title of a Persian mathematician's famous 9th century treatise "Kitab al- Jabr Wa l-Mugabala" which translates roughly as "The Book of Reasoning and Balancing." Built on the roots of Greek and Hindu systems, the new algebraic order was a unifying system for rationa0l numbers, irrational numbers and geometrical magnitudes. The same mathematician, Al-Khwarizmi, was also the first to introduce the concept of raising a number to a power.

6. Optics
"Many of the most important advances in the study of optics come from the Muslim world," says Hassani. Around the year 1000 Ibn Al-Haitham proved that humans see objects by light reflecting off of them and entering the eye, dismissing Euclid and Ptolemy's theories that light was emitted from the eye itself. This great Muslim physicist also discovered the camera obscura phenomenon, which explains how the eye sees images upright due to the connection between the optic nerve and the brain.

7. Music
Muslim musicians have had a profound impact on Europe, dating back to Charlemagne tried to compete with the music of Baghdad and Cordoba, according to Hassani. Among many instruments that arrived in Europe through the Middle East are the lute and the rahab, an ancestor of the violin. Modern musical scales are also said to derive from the Arabic alphabet.

8. Toothbrush
According to Hassani, the Prophet Mohammed popularized the use of the first toothbrush in around 600. Using a twig from the Meswak tree, he cleaned his teeth and freshened his breath. Substances similar to Meswak are used in modern toothpaste.

9. The crank
Many of the basics of modern automatics were first put to use in the Muslim world, including the revolutionary crank-connecting rod system. By converting rotary motion to linear motion, the crank enables the lifting of heavy objects with relative ease. This technology, discovered by Al-Jazari in the 12th century, exploded across the globe, leading to everything from the bicycle to the internal combustion engine.

10. Hospitals
"Hospitals as we know them today, with wards and teaching centers, come from 9th century Egypt," explained Hassani. The first such medical center was the Ahmad ibn Tulun Hospital, founded in 872 in Cairo. Tulun hospital provided free care for anyone who needed it -- a policy based on the Muslim tradition of caring for all who are sick. From Cairo, such hospitals spread around the Muslim world. Muslims distinguished themselves not only as theoretical scientists and scientific thinkers, but contributed through innumerable inventions to the growth of the modern sciences. Though the mediaeval Muslims had very meager resources at their command as compared to those of the present age, they achieved a great deal. They replaced the old speculative method of the Greeks with an experimental method, which in later periods formed the basis of all scientific investigations.

Wednesday, July 13, 2011

Install Oracle 10g on Windows 7

Install Oracle 10g on Windows-7 By Khurram

Oracle 10g Installation tutorial depicts step by step installation of Oracle 10g on Windows 7, 64bit (10204_vista_w2k8_x64_production_db) operating system. Installation on Windows 7 will return a operating system version compatibility error. However, there will not be any such issue during installation on older version of Windows operating system. It is important to note that this installation method is neither certified nor recommended by Oracle. If you are planning to install Oracle 10g for production purpose then please follow the Oracle specified installation guidelines and use only the correct and supported version for installation.
Step 1:
Oracle 10g installation is done via Oracle Universal Installer. As mentioned earlier, Installation of Oracle 10g on Windows 7 will return version compatibility error as shown here below

“Checking operating system version: must be 5.0,5.1,5.2 or 6.0. Actual 6.1”
Do not panic, you will only need to edit a couple of files to get the installation working. Let me show you how to handle this in a series of steps.

Step 2:
Locate the file /install/oraparam.ini after you extract the installation zip file to your local computer or external disk.

Step 3:
Change the following to include the version “Actual 6.1” as per the installation. You may see other version numbers as well. Use the appropriate version number for the changes you are going to make.

[Certified Versions]
#You can customise error message shown for failure, provide value for CERTIFIED_VERSION_FAILURE_MESSAGE
Windows=5.0,5.1,5.2,6.0

Change to
[Certified Versions]
# You can customise error message shown for failure, provide value for
CERTIFIED_VERSION_FAILURE_MESSAGE
Windows = 5.0,5.1,5.2,6.0,6.1


Step 4:
Add this additional block needed
[Windows-6.1-required]
# Minimum display colours for OUI to run
MIN_DISPLAY_COLORS = 256
# Minimum CPU speed required for OUI
# CPU = 300

Step 5:
You will need to add Windows 7 compatabilty entries in databasestageprereqdbrefhost.xml and databasestageprereqdb_prereqsdb refhost.xml.

Find the following entries and insert the following . Keep in mind that 6.1 for VERSION VALUE is based on the version we are using in this tutorials. Use the appropriate version number based on the error message you get.

<!--Microsoft Windows 7-->
<OPERATING_SYSTEM>
<VERSION VALUE="6.1"/>
</OPERATING_SYSTEM>

Here is entry for “CERTIFIED_SYSTEMS” block adding the entry for Windows 7.

<CERTIFIED_SYSTEMS>
    <OPERATING_SYSTEM>
        <VERSION VALUE="5.0"/>
        <SERVICE_PACK VALUE="1"/>
    </OPERATING_SYSTEM>
    <OPERATING_SYSTEM>
        <VERSION VALUE="5.1"/>
        <SERVICE_PACK VALUE="1"/>
    </OPERATING_SYSTEM>
    <OPERATING_SYSTEM>
        <VERSION VALUE="5.2"/>
        </OPERATING_SYSTEM>
        <!--Microsoft Windows Vista-->
    <OPERATING_SYSTEM>
        <VERSION VALUE="6.0"/>
    </OPERATING_SYSTEM>
    <!--Microsoft Windows 7-->
    <OPERATING_SYSTEM>
        <VERSION VALUE="6.1"/>
        </OPERATING_SYSTEM>
</CERTIFIED_SYSTEMS>

Note: If any of the entries does not match the Operating System version then you will see the following error after the installation starts for “Checking operating system requirements step”


 
Step 6:
Save the modified files and restart the installation running setup.exe. You will see the messages as shown here below
 
Step 7:
Change Oracle Home location and select the Installation type.
If you are not planning to use the starter database then you need to uncheck the “Create Starter Database (additional 720MB) check box option
Enter the database name and password.
 
Step 8:
Click “Next” to continue with the installation.
If you encounter any status with Error(s) during “Product-Specific Prerequisite Checks” then you need to check further before you can continue with the installation; otherwise your Oracle Server may not work.
 
 
Step 9:
As soon as all the requirements gets verified and the errors are eliminated, You will see the “Succeeded” status as below screen shot for all the Product-Specific Prerequisite checks.


   
It is assumed that this is a fresh install so no other Oracle products should be installed at this time. 

 
In this screen, the Source and Destination locations must be selected.

The Source path should reflect either your CD-ROM or the directory in which you unzipped the source files. Leave this as the default. In the example above, the source files were unzipped into c:DOCUME~1HARILOCALS~1temprar$ex12.1593diskstageproduct.xml.

For the Destination, leave the Oracle Home named oraDb10g_home1 as the default. Choose a hard disk drive that has at least 6 gigabytes of free space. In the example here, we are using the E: drive.

Leave the Oracleproduct10.1.0db_1 path as it is (the default). If you must change this to a different drive, only change the drive letter and not the directories.

Click on the Next button to select installation type. This may take up to 2 minutes (depending on the speed of your machine). Once the product information has been read, the following 3 "Available Products" options are presented: 

 
After selecting the type click on the next button to get the screen: 

 
For this install, we chose the "General Purpose" Database option. Click on the Next button to continue.

The next step is to identify the database using the Global database name 

 
Keep the default names for GLOBAL and the SID Otherwise, use the following (make a note of these for later on): Global Database Name is orcl, Oracle SID is orcl

Click on the Next button to continue to the "database file storage option" screen 

 
In this step, you need to select where the Oracle data files will be located. In large operations, we typically keep the data files on a separate disk (or disks), however, for this install, keep the default storage

Click on the Next button to move to the "Summary" screen:

 
During the install, you will notice various Oracle products being copied over to the hard disk.

Once the installation is completed, the next step will be to configure the various additional services and the database.

The following tools will be automatically starts and it is optional. It is recommended, although not required

 
Click on the next button .The Oracle Database Configuration Assistant will appear for some time while the default database is created and opened. As below 





Testing Installation
Go to start menu - > programs - >oracle-oradb10g_home5 - >application development -> click on the sql plus.

It will appears like the screen below, Once SQL Plus is running, you will be prompted for 3 things: Username, password and Host String.

Fill in the Username and password fields with the SYSTEM account and leave the Host String field blank. Since we are connecting to a local Oracle database, no Host String is required. Click on the OK button