At which situation IN is better than EXIST ?

Well, the two are processed very differently

Select * from T1 where x in ( select y from T2 )

is typically processed as:

select * 

  from t1, ( select distinct y from t2 ) t2

 where t1.x = t2.y;

Sub query is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to the original table - typically.

As opposed to:

select * from t1 where exists (select null from t2 where y = x)

That is processed more like:

   for x in (select * from t1)

   loop

      if (exists (select null from t2 where y = x.x)

      then 

         OUTPUT THE RECORD

      end if

   end loop

It always results in a full scan of T1 whereas the first query can make use of an index on T1(x).

So, when is where exists appropriate and in appropriate?  Lets say the result of the sub query:

    (select y from T2)

is "huge" and takes a long time.  But the table T1 is relatively small and executing ( select null from t2 where y = x.x ) is very very fast (nice index on t2(y)).  Then the exists will be faster as the time to full scan T1 and do the index probe into T2 could be less then the time to simply full scan T2 to build the subquery we need to distinct on.

Let’s say the result of the sub query is small -- then IN is typically more appropriate.

If both the sub query and the outer table are huge -- either might work as well as the other -- depends on the indexes and other factors.

The ROT (rule of thumb) here is :

BIG outer query and SMALL inner query = IN. SMALL outer query and BIG inner query = WHERE EXISTS. 

How to Troubleshoot Distributed Transactions ? (ORA-2019, ORA-2058, ORA-2068, ORA-2050, FAILED DISTRIBUTED TRANSACTIONS)

How to Troubleshoot Distributed Transactions ? 

Database "A" has init.ora parameter 
db_domain incorrectly specified as ".WORLD", that causes the init.ora parameter "service_names" to be "A..WORLD". 

A select from GLOBAL_NAMES shows "A.WORLD". Database "B" has init.ora parameters of "db_domain=WORLD" and SERVICE_NAMES=B.WORLD". 

Select from GLOBAL_NAME shows "B.WORLD". The table synonyms referring to the remote tables work for both databases to data manipulation. But, the synonyms on database "A" cannot be used to describe the tables on "B" (ORA-02019). 

Can I safely change the "A" init.ora parameter from "db_domain=.WORLD" to "db_domain=WORLD"? I know from my test databases that that parameter inhibits the describe. 

Set GLOBAL_NAMES=FALSE in the init.ora and restart the database.

The syntax is as follows: 
Create public database link  
Connect to uid identified by pw 
Using 'alias'; 

Ensure that global_names = true in the destination server's init.ora file. If the global name = true parameter is set in the init.ora, check the value of the db_name and db_domain parameters in that same init.ora file. Log into the destination server via sqlplus and do a SELECT * FROM GLOBAL_NAME. This value should match the db_name specified in the init.ora file. If the global name = true parameter exists in the init.ora file, then the name of the dblink must be either that databases sid or global dbname. 

Enabling Tracing and Session Information with 10gR2 Enterprise Manager and Forms

ENABLING SESSION DETAILS AND TRACE LOGGING
-------------------------------------------
By default the formsweb.cfg file is set up with session detail and trace 
logging disabled. In order to enable these features, the formsweb.cfg file in 
the ORACLE_HOME/forms90/server ( ORACLE_HOME/forms/server in 10gR2) directory 
must be modified and the middle-tier restarted.


Change the Configuration File
-----------------------------
For session details: 

1) Modify the em_mode parameter to: 
   em_mode=1

For trace logging:

1) Modify the following parameters:
   allow_debug=true

2) If the allowURLConnections parameter appears in formsweb.cfg, ensure that
   the parameter is set to: 
   allowURLConnections=true

   The default for allowURLConnections is true, so if it does not appear in the
   formsweb.cfg file, no modifications are required for this parameter.

3) Run a form

4)  From  EM -> Forms Link -> User Sessions

5)  Select Trace Group -> debug

6)  Select a session.

7)  Click the 'Trace ON' button.

8)  Click the 'View Trace Log' Icon to view it.

9.) Now a Login request comes up. 
    (The documentation does not clearly explain 
    to which "administrators" group is being referenced.  Most would assume the
    EM administrator would have access. This is not true. The "administrators"
    group referenced in the documentation relates to JAZN security and not the
    OS or EM.)

    A default "administrators" group is created with a default user and 
    password.  The default username and password is as follows:
    username = admin
    password = welcome

To change the default or create your own users, refer to the 
Oracle Application Server Containers for J2EE Security Guide or
Java Authentication and Authorization Service Sample.


Restart the Middle Tier
-----------------------

The middle tier must be restarted in order for the changes to the formsweb.cfg
file to take effect. The commands to restart the middle tier: 

1) /bin/emctl stop iasconsole
2) /opmn/bin/opmnctl stopall
3) /bin/emctl start iasconsole
4) /opmn/bin/opmnctl startall

Enabling roles in Oracle Forms

create or replace view FRM50_ENABLED_ROLES as
select urp.granted_role role,
sum(distinct decode(rrp.granted_role,
   'ORAFORMS$OSC',2,
   'ORAFORMS$BGM',4,
   'ORAFORMS$DBG',1,0)) flag
from  sys.user_role_privs urp, role_role_privs rrp
where urp.granted_role = rrp.role (+)
  and urp.granted_role not like 'ORAFORMS$%'
group by urp.granted_role;

create public synonym FRM50_ENABLED_ROLES for system.FRM50_ENABLED_ROLES;

create role ORAFORMS$OSC;
create role ORAFORMS$DBG;
create role ORAFORMS$BGM; 

grant SELECT on FRM50_ENABLED_ROLES to public;

Email From PL/SQL (9i, 10g)

Email From PL/SQL In Oracle9i

The UTL_SMTP package was introduced in Oracle8i and can be used to send emails from PL/SQL. In it's simplest form a single string or variable can be sent as the message body using:

DECLARE

  l_mailhost    VARCHAR2(64) := 'mail.mycompany.com';

  l_from        VARCHAR2(64) := 'me@mycompany.com';

  l_to          VARCHAR2(64) := 'you@mycompany.com';

  l_mail_conn   UTL_SMTP.connection;

BEGIN

  l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);

  UTL_SMTP.helo(l_mail_conn, l_mailhost);

  UTL_SMTP.mail(l_mail_conn, l_from);

  UTL_SMTP.rcpt(l_mail_conn, l_to);

  UTL_SMTP.data(l_mail_conn, 'Single string message.' || Chr(13));

  UTL_SMTP.quit(l_mail_conn);

END;

/

Multi-line messages can be written by expanding the UTL_SMTP.DATA command as follows:

DECLARE

  l_mailhost    VARCHAR2(64) := 'mail.mycompany.com';

  l_from        VARCHAR2(64) := 'me@mycompany.com';

  l_subject     VARCHAR2(64) := 'Test Mail';

  l_to          VARCHAR2(64) := 'you@mycompany.com';

  l_mail_conn   UTL_SMTP.connection;

BEGIN

  l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);

  UTL_SMTP.helo(l_mail_conn, l_mailhost);

  UTL_SMTP.mail(l_mail_conn, l_from);

  UTL_SMTP.rcpt(l_mail_conn, l_to);

  UTL_SMTP.open_data(l_mail_conn);

 

  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || Chr(13));

  UTL_SMTP.write_data(l_mail_conn, 'From: ' || l_from || Chr(13));

  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || l_subject || Chr(13));

  UTL_SMTP.write_data(l_mail_conn, 'To: ' || l_to || Chr(13));

  UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));

 

  FOR i IN 1 .. 10 LOOP

    UTL_SMTP.write_data(l_mail_conn, 'This is a test message. Line ' || To_Char(i) || Chr(13));

  END LOOP;

 

  UTL_SMTP.close_data(l_mail_conn);

  UTL_SMTP.quit(l_mail_conn);

END;

/

The UTL_SMTP package requires Jserver which can be installed by running the following scripts as SYS:

SQL> @$ORACLE_HOME/javavm/install/initjvm.sql

SQL> @$ORACLE_HOME/rdbms/admin/initplsj.sql

Extract the directory path from a full file path

Question:  I have a string that contains the full path to a file. How can I extract the directory path from the string in Oracle?

For example, if I have a full file path as follows:

    'c:\windows\temp\example.xls'

I want to retrieve the following:

    'c:\windows\temp'


Answer:  You can create a custom function in Oracle that will retrieve the directory path from the string value.

The following function called get_dir_path will extract the directory path. It will work with both Windows and UNIX system file paths.

    CREATE or REPLACE function get_dir_path
       (p_path IN VARCHAR2)
    RETURN varchar2

    IS
         v_dir VARCHAR2(1500);

    BEGIN

        -- Parse string for UNIX system
        IF instr(p_path,'/') > 0 THEN
            v_dir := substr(p_path,1,(instr(p_path,'/',-1,1)-1));

        -- Parse string for Windows system
        ELSIF instr(p_path,'\') > 0 THEN
            v_dir := substr(p_path,1,(instr(p_path,'\',-1,1)-1));

        -- If no slashes were found, return the original string
        ELSE
            v_dir := p_path;
        END IF;

        RETURN v_dir;

    END;


Once the above function has been created, you can reference this function in your SQL statement. For example,

    SELECT get_dir_path('c:\windows\temp\example.xls')
    FROM dual;

This SQL statement would return 'c:\windows\temp'.

Forms User Sessions Link Hangs When Using It Through Application Server Control (ASC)

Symptoms

1. Bring up Application Server Control.
2. Click on the Forms link.
3. Click on the User Sessions link.
4.  All that is seen is a an hour glass and no page appears. What should be seen is a page showing information about active forms sessions.  Information such as Process ID, CPU Usage,  Memory Usage, IP Address, etc.
5.  The following tests help identify the problem area:

Test 1

1. Ensure that em_mode is set to one (1) in the formsweb.cfg
2. Run a couple of instances of the test form -> http://machine.domain:port/forms/frmservlet
and ensure the test form comes up.
3. Go to your MIDDLE_TIER_HOME\sysman\admin\scripts and make a backup copy of this file:
formsproc.pl
4. Now, rename ..\formsproc.pl to something else, say formsproc.pl.org
5. Now click on the 'User Session' link and note if you see any different behavior.

Note: What you should normally see is the 'User Session' page that shows Process ID, CPU Usage,
IP  Address, etc but you would not see any session data because formsproc.pl is what provides the
session info data to the page.

6. Rename formsproc.pl.org to it's original name -> formsproc.pl

Test 2. 

This test is to determine if a perl script can be run on your machine as this is needed to
gather the forms 'User Session' data.

1. Ensure you have accomplished steps 1 & 2 from Test 1.
2. Open a command prompt on the middle_tier machine.
3. Set the oracle home. For example, if the middle tier home is 'D:\mid_1012' then issue this
command:

set oracle_home=d:\mid_1012

4. Issue the following command to move to the perl directory:

cd %oracle_home%\perl\5.6.1\bin\MSWin32-x86

6. Now issue this command and note the results: (this is the script that ASC calls to
show forms data)

perl %ORACLE_HOME%\sysman\admin\scripts\formsproc.pl %ORACLE_HOME%\sysman\admin\scripts %ORACLE_HOME%

After a few seconds, depress the 'Enter' key and you may see the message ->

'The system cannot find the path specified'

However, after a few seconds Forms session info should scroll on the screen. If it doesn't you may get an error like the following ->

"cannot connect to Interix subsystem"  (This is an indication of the problem)
Cause
In this case the installation/configuration of the UNIX Interix subsystem is preventing the necessary Forms perl script (formsproc.pl) from running.
Solution

Assuming that the error message "cannot connect to Interix subsystem"  occurred when running Test 2 then de-install the UNIX Interix subsystem.

If an error message related to a different 3rd part software product occurred in Test 2 then de-install it.