How to find trigger in oracle

You can use the following query to find out triggers for a particular table

select trigger_name from user_triggers where table_name='XXXX';

Use the name of your table instead of XXXX.

How to find constraint definition in oracle

You can find the definition of a constraint by using the following query

select DBMS_METADATA.GET_DDL('CONSTRAINT', 'XXXX') from dual;

Here instead of XXXX you have to use the name of the constraint.

How to find constraint in oracle

You can find out the name of the constraints for a particular table
from the view user_constraints .

Use the following query

select CONSTRAINT_NAME from user_constraints where table_name='XXXX';

Use the name of your table instead of XXXX.

Unable to load DLL (oci.dll)

This is a common problem faced by the asp.net developers while connecting to oracle database. If you face the problem.... just perform the following steps and hopefully your problem will be solved.

1. Log on to Windows as a user with Administrator privileges.
2. Launch Windows Explorer from the Start Menu and and navigate to the ORACLE_HOME folder. This is typically the "Ora92" folder under the "Oracle" folder (i.e. D:\Oracle\Ora92) in Oracle9iR2 or "Client_1" folder under the "Oracle\product\10.1.0" folder (i.e. D:\Oracle\product \10.1.0\Client_1) in Oracle10gR1.
3. Right-click on the ORACLE_HOME folder and choose the "Properties" option from the drop down list. A "Properties" window should appear.
4. Click on the "Security" tab of the "Properties" window. NOTE: If you are running Windows XP and do not have a "Security" tab please do the following:
(a) Open "Folder Options" in your Control Panel. (
(b) Choose the "View" tab.
(c) Under "Advanced Settings" uncheck the option "Use simple file sharing (Recommended)". The "Security" tab should now be available.
5. Click on "Authenticated Users" item in the "Name" list (on Windows XP the "Name" list is called "Group or user names").
6. Uncheck the "Read and Execute" box in the "Permissions" list under the "Allow" column (on Windows XP the "Permissions" list is called "Permissions for Authenticated Users").
7. Re-check the "Read and Execute" box under the "Allow" column (this is the box you just unchecked).
8. Click the "Advanced" button and in the "Permission Entries" list make sure you see the "Authenticated Users" listed there with: Permission = Read & Execute Apply To = This folder, subfolders and files If this is NOT the case, edit that line and make sure the "Apply onto" drop-down box is set to "This folder, subfolders and files". This should already be set properly but it is important that you verify this.
9. Click the "Ok" button until you close out all of the security properties windows. The cursor may present the hour glass for a few seconds as it applies the permissions you just changed to all subfolders and files.
10. Reboot your computer to assure that these changes have taken effect.
11. Re-execute the application and it should now work.
NOTE: If the problem continues to occur, please go back through these same 11 steps but when performing step 8 additionally check the box entitled "Replace permission entries on all child objects with entries shown here that apply to child objects" and be sure to reboot in step 10.

UNION query in Oracle

The UNION query allows you to combine the result sets of 2 or more "select" queries. It removes duplicate rows between the various "select" statements.

Each SQL statement within the UNION query must have the same number of fields in the result sets with similar data types.

The syntax for a UNION query is:

select
field1, field2, ................field_n
from tables
UNION
select
field1, field2, ................field_n
from tables;

Example #1

The following is an example of a UNION query:

select supplier_idfrom suppliers
UNION
select supplier_idfrom orders;

In this example, if a supplier_id appeared in both the suppliers and orders table, it would appear once in your result set. The UNION removes duplicates.

Example #2 - With ORDER BY Clause

The following is a UNION query that uses an ORDER BY clause:

select supplier_id, supplier_name from suppliers where supplier_id > 2000
UNION
select company_id, company_namefrom companieswhere company_id > 1000
ORDER BY 2;

Since the column names are different between the two "select" statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the "ORDER BY 2".
The supplier_name / company_name fields are in position #2 in the result set.

New Line in Crystal Report Text Object

Sometimes it is necessary to have a line break inside the text object of crystal report. We can do it using the keyword "chr". chr... takes a numeric value as its parameter. And the value 10 is used for having a line break.

An example is illustrated below........

We have to take a formula and inside the formula it can be written as :

'Name' + chr(10) + 'Country'

The output will be :

Name
Country

SQL SELECT TOP N equivalent in ORACLE

SQL Server:

SELECT TOP 5 id, name, email FROM students

ORACLE:

SELECT id, name, email FROM students WHERE ROWNUM <= 5

Well, this will work fine if we dont use any "order by" clause. what "ROWNUM <= 5" does is simply return you top 5 record in the table.. if you try the ORDER BY code, it will simply extract the top 5 records in the table then ORDER it instead of ORDERING them then select the top 5.

To solve ordering problem we can use the query like this

SELECT id, name, email FROM (select id, name, email from students order by id )
WHERE ROWNUM <= 5 ;