Linux Command Reference

Tools to Monitor Linux PerformanceLinux

How to Close and Open Ports with iptables

How to Reset "root" Password with GRUB on Enterprise Linux

Forcefully unmount a Linux disk partition

Get Tape Serial Num in Linux

Mount Windows CDROM in Linux

VNC in LinuxLinux

Validating Parameters in Oracle Reports using PL/SQL

Oracle Developer , Oracle Reports , Oracle Technical

Parameters can be populated and validated using various srw pl/sql triggers.

The following gives examples of:

Validation trigger in parameter spread sheet

Before parameter form trigger

After parameter form trigger

Before report trigger

Examples of validation triggers on the property sheet for parameter PARAM_SAL.

Query: select * from emp where sal > :PARAM_SAL

These functions validate just this one trigger. The validation occurs when

the user hits next field after inputting a value for the parameter. When the

trigger is failed it returns to the parameter form.

Example 1:

This trigger aborts the report execution if no rows match the query criteria

once the user has entered a value for param_sal.

function PARAM_SALValidTrigger return boolean is

hold_count number(4);

hold_sal number(10);

begin

hold_sal := :param_sal;

select count(*) into hold_count from emp where sal > hold_sal;

if hold_count = 0 then

srw.message(001,'this report returns no employees');

raise srw.program_abort;

end if;

return(true);

end;

Example 2

In this trigger the users value for param_sal is compared to the maximum

salary in the EMP table. If it is greater the report execution is aborted.

example query for your report: select * from emp where sal >= :parm_sal

function PARAM_SALValidTrigger return boolean is

hold_max number(10);

begin

select max(sal) into hold_max from emp;

if :param_sal > hold_max then

srw.message(002,'SAL must be equal to or less than MAX(SAL)= '||

to_char(hold_max));

raise srw.program_abort;

end if;

return(true);

end;

Example 3

'Before parameter form' triggers can be used set up the environment for the

report e.g. create a table. It can also be used to supply default parameter

values.

This function populates the initial value of the parameter param_sal with the

lowest salary value from the emp table.

function BeforePForm return boolean is

min_sal number(10);

begin

select min(sal) into min_sal from emp;

:param_sal := min_sal;

return(true);

end;

Example 4

'After parameter form' triggers can be used to validate a combination of

parameters. Failing results in a return to the PARAMETER FORM.

Query: select * from emp where job=:jb and deptno=:dt

function AfterPForm return boolean is

begin

if (:dt = 20) and (:jb = 'MANAGER') then

srw.message(003,'cannot report on Managers in Dept 20');

raise srw.program_abort;

end if;

return(true);

end;

Example 5

'Before report triggers' can be used to validate a combination of parameters.

The example below is the same as the after parameter form trigger above

other than on failure return is passed to the MAIN MENU.

A 'Before Report Trigger' is executed right before formatting the report,

that is after initializing all internal structures, opening all SQL cursors

etc. In other words, after 'compiling' the report definition.

A second use of this trigger may be to launch a number of other reports

using the SRW.RUN_REPORT procedure.

function BeforeReport return boolean is

begin

if (:dt = 20) and (:jb = 'MANAGER') then

srw.message(004,'cannot report on Managers in Dept 20');

raise srw.program_abort;

end if;

return(true);

end;

ORACLE REPORTS PERFORMANCE TIPS

Oracle Developer , Oracle SQL , Oracle Technical

Doc ID 61535.1

Performing operations in SQL may be faster than performing them in Oracle
Reports or PL/SQL.  The list below explains the most common cases where using
SQL would improve performance:

 -  perform calculations directly in your query rather than in a formula or
    summary,

 -  use a WHERE clause instead of a group filter or format trigger to exclude
    records,

 -  use the SUBSTR function to truncate character strings instead of
    truncating in Oracle Reports.

SQL can perform calculations more quickly than a summary or formula.  WHERE
and SUBSTR can reduce unnecessary fetching because they operate on the data
during, rather than after, data retrieval.


SRW.DO_SQL Statements
---------------------

SRW.DO_SQL enables you to add any DDL or DML operation to your report.  This
functionality is very valuable, but it can also be very expensive if used
unwisely.

Only use SRW.DO_SQL when necessary.  SRW.DO_SQL statements are parsed, a
cursor is opened to the database, and then the statement is executed.  Unlike
queries, an SRW.DO_SQL statement will do those things each time its owner
(a group) fetches data.  For example, if your SRW.DO_SQL statement is owned by
a group that fetches 10 records, the statement will be parsed 10 times, 10
cursors will be opened, and the statement will be executed 10 times.
Perform computations within the query or PL/SQL instead of SRW.DO_SQL
owned by a group.


CDE_MM.GET_REF
--------------

Only use the CDE_MM.GET_REF packaged procedure when necessary.  It is
intended to reduce the amount of temporary space used by Oracle Reports.
Oracle Reports will not cache a column retrieved via CDE_MM.GET_REF in a
temporary file.  While this reduces the need for temporary space, it slows
performance because the column's values must always be retrieved from the
database.


When You Should Use Multi-Query Data Models
-------------------------------------------

Reduce the number of queries in your report as much as possible.  The fewer
queries it contains, the faster your report will run.  Multi-query data models 
are easier to understand, but single-query data models tend to execute more 
quickly.

Use multi-query data models only when:

 - you are fetching many large columns from the parent and
   only a few small columns from the child,

 - you are trying to do things that SELECT does not support directly
   (multi-way outer join),

 - you have complex views (distributed queries or GROUP BY queries).

 - you need, but do not have or want, to use a view.

For a one-query report, only one cursor is opened to fetch all the master
and detail records.  For a two-query report, Oracle Reports opens two cursors
(one for each query) after appending the detail query's link to the WHERE
clause of the detail query.  For each master record fetched, Oracle must
rebind, execute, and fetch data from the detail query.


Indexes
-------

Be sure to have indexes on columns used in the SELECT statements' WHERE clauses,
on database key columns, and on the table(s) in the detail queries.  Indexes 
have little impact on master queries, because those queries access the database 
only once.  Indexes significantly improve performance of master/detail reports.
The lower the ratio of master to detail records, the more important indexes on
the detail query become for two-query reports.

Indexes are recommended for tables in the detail queries because Oracle
Reports implicitly creates a WHERE clause from the parent/child relationships
and adds it to the detail query.


IMPORTANT: Query Modifications
------------------------------

Oracle Reports modifies your queries in the following cases:

1.  For each link you create, Oracle Reports will append a clause to the
    child query as specified in the link.

    For example:
         SELECT deptno, ename, sal
         FROM emp
         WHERE sal > 1000

    If you create a link to this query using DEPTNO as the child column, a SQL
    clause of WHERE, and a condition of "equal to", then your query will be
    modified as follows:

         SELECT deptno, ename, sal
         FROM emp
         WHERE (sal > 1000) AND (deptno = :deptno)

    NOTE:  This is not true for multi-query matrix report data models.

2.  For each database column with Break Order set, Oracle Reports will
    PREPEND an ORDER BY clause to the query.

    For example:
         SELECT deptno, ename, sal
         FROM emp
         ORDER BY sal

    If you create a break group with DEPTNO as the break column, then your 
    query will be modified as follows:

         SELECT deptno, ename, sal
         FROM emp
         ORDER BY 1, sal

    These SQL statements will be sent to the database, then the SQL optimizer 
    will determine the optimal way to get the data from the database and return 
    it to Oracle Reports.  The optimizer will determine whether to use indexes, 
    which table to use as the "driving" table, and so forth.


Break Columns
-------------

When you create a break group, place as few columns as possible in the group.
Try to keep a 1:1 ratio of break columns to break groups.  Try to ensure
that the break column is as small as possible.  A break column that is shorter
in length will typically give better performance than a break column that is
longer.  For larger break columns, it may help performance to use the SUBSTR
function to reduce the length of the column.

For each break group, Oracle Reports prepends its break columns to the ORDER
BY clause of the query.  (The only exception to the rule is when the break
column is a formula column.)  By minimizing the number of break columns in
your break groups, you minimize the number of columns that are added to the
ORDER BY clause.  The fewer the columns added to the ORDER BY clause, the less
the processing that needs to be done when the query runs.  The size of the key
that Oracle Reports uses internally to build indexes will also be smaller,
resulting in better performance.


Maximum Rows And Group Filters
------------------------------

Use the Maximum Rows property in the Query property sheet to reduce the number
of records retrieved by the report's queries.  When designing a report that
accesses large amounts of data, you may want to restrict the amount of data
retrieved, so the report will run more quickly during testing.

Maximum Rows in the Query property sheet restricts the number of records
fetched by the query.  A group filter determines which records to include and
which records to exclude.  Since Maximum Rows actually restricts the amount of
data retrieved, it is faster than a group filter in most cases.

If you use a group filter of Last or Conditional, Oracle Reports must retrieve
all of the records in the group before applying the filter criteria.   Maximum
Rows or a Filter of First is faster.  Typically Maximum Rows is faster than a
Filter of First because it only retrieves as many records as needed.  The
performance difference may vary depending upon the ARRAYSIZE you have
specified.


Unused Data Model Objects
-------------------------

Make sure that you remove or suppress any data model objects that are not
actually used in your report.  If your data model includes a query that is
only used in certain cases (when a parameter is set to a certain value), you
can conditionally suppress the query with the SRW.SET_MAXROW packaged
procedure.  SRW.SET_MAXROW (queryname, 0) will cause the query to fetch no
records.


Unused Frames
-------------

Remove any unnecessary frames from the layout.  When Oracle Reports creates a
default layout, it puts frames around virtually everything.  This is done to 
protect the objects in the frames from being overwritten by other objects in 
the output.  If you know that the objects in the frames are not in danger of 
being overwritten, you can eliminate the frame without adversely affecting 
your report output.

The fewer objects in the layout, the fewer objects Oracle Reports must format
at runtime.  As a result, performance is better when you reduce the number of
objects in the layout.


Total Number Of Pages
---------------------

Limit your use of total number of pages as the source of fields (Total Logical
Pages).  When you use a total number of pages field source, Oracle Reports
must save all of the pages in temporary storage in order to determine the
total number of pages.  This can significantly increase the amount of
temporary disk space used by Reports, and the additional writing to files can
slow performance.


Format Triggers
---------------

Place PL/SQL in the Format Trigger of the object with the lowest frequency
possible.  PL/SQL in the Format Trigger of a frame instead of a field
typically makes the report run faster.

PL/SQL in Format Triggers is executed for each instance of its object.  The
lower the frequency of the object, the fewer times the PL/SQL will be executed
and the faster the report will run.


Oracle Graphics Integration
---------------------------

If an Oracle Graphics display referenced by a report uses some or all of the
same data as the report, pass the data from the report to the display.  If the
report and the display use the same data, passing the data reduces the amount
of fetching that needs to be done.  If you do not pass the data from the
report to the display, the data is actually fetched twice: once for the report
and once for the display.

Using SRW Packaged Procedures in Reports

Oracle Technical , PL/SQL

Oracle Reports is shipped with a collection of PL/SQL constructs that contain

many functions, procedures, and exceptions that you can reference in your

libraries or reports. The name of Oracle Reports' package is SRW. Therefore,

anytime you reference a construct in the SRW package, you must prefix it with

SRW (such as, SRW.DO_SQL).

SRW Packages

Oracle Reports contains the following packages:

- SRW.BREAK - SRW.RUN_REPORT_BATCHNO

- SRW.CONTEXT_FAILURE - SRW.SET_FIELD_CHAR

- SRW.DO_SQL - SRW.SET_FIELD_DATE

- SRW.DO_SQL_FAILURE - SRW.SET_FIELD_NUM

- SRW.GETERR_RUN - SRW.SET_MAXROW

- SRW.GET_PAGE_NUM - SRW.TRACE_ADD_OPTION

- SRW.INTEGER_ERROR - SRW.TRACE_END

- SRW.MAXROW_INERR - SRW.TRACE_REM_OPTION

- SRW.MAXROW_UNSET - SRW.TRACE_START

- SRW.MESSAGE - SRW.TRUNCATED_VALUE

- SRW.NULL_ARGUMENTS - SRW.UNKNOWN_QUERY

- SRW.PROGRAM_ABORT - SRW.UNKNOWN_USER_EXIT

- SRW.REFERENCE - SRW.USER_EXIT

- SRW.RUN_REPORT - SRW.USER_EXIT20

- SRW.SET_ATTR - SRW.USER_EXIT_FAILURE

- SRW.RUN_REPORT_FAILURE

Commonly Used SRW Packages And Examples

SRW.DO_SQL executes a specified SQL statement. This procedure executes

any DDL or DML statements. However, DML statements are usually faster

when they are in PL/SQL than when executed via SRW.DO_SQL. Since you

cannot perform a DDL statement in PL/SQL, this packaged procedure is useful

for performing them within Reports rather than using a user exit.

Avoid DDL statements that modify the tables on which the report is based. A

snapshot of the tables is taken prior to report execution and must remain

valid throughout the execution of the report.

Example 1 - SRW.DO_SQL

Syntax/Explanation:

SRW.DO_SQL(sql_statement CHAR); -- Executes specified SQL statement.

SRW.DO_SQL_FAILURE; -- Stops report execution upon SRW.DO_SQL failure.

SRW.MESSAGE(msg_number NUMBER, msg_text CHAR);

-- Displays a specified message and message number.

SRW.PROGRAM_ABORT; -- Stops execution of report when raised.

Example:

Create a temporary table named TEMP prior to the Runtime Parameter Form:

FUNCTION createtab RETURN BOOLEAN IS

BEGIN

SRW.DO_SQL(CREATE TABLE temp (empno NUMBER NOT NULL PRIMARY KEY,

sal NUMBER (10,2))

PCTFREE 5 PCTUSED 75');

RETURN TRUE;

EXCEPTION

WHEN SRW.DO_SQL_FAILURE THEN

SRW.MESSAGE(100,'ERROR WHILE CREATING CHECK TABLE.');

SRW.MESSAGE(50, 'REPORT WAS STOPPED BEFORE THE PARM FORM.');

RAISE SRW.PROGRAM_ABORT;

END;

Example 2 - SRW.RUN_REPORT

Syntax/Explanation:

SRW.RUN_REPORT(command_line CHAR); -- Executes specified R25RUN command

SRW.RUN_REPORT_FAILURE; -- Stops report execution when failure of

-- SRW.RUN_REPORT occurs.

Example:

Drill Down Report called from a report button:

FUNCTION foo RETURN BOOLEAN IS

BEGIN

SRW.RUN_REPORT('report=REP_A p_parm1=20'); -- calls report REP_A and

EXCEPTION -- display to screen;

WHEN SRW.RUN_REPORT_FAILURE THEN -- passes value 20 to the

SRW.MESSAGE(100, 'ERROR CALLING REPORT.') -- p_parm1 parameter

RAISE SRW.PROGRAM_ABORT;

RETURN TRUE;

END;

Note that you can only call another report to the screen using SRW.RUN_REPORT

from a button. If you issue SRW.RUN_REPORT from a report trigger, you

must pass BATCH=YES. Therefore, DESTYPE can only be FILE, PRINTER, or MAIL.

Example 3 - SRW.SET_ATTR

Syntax/Explanation

SRW.SET_ATTR applies attribute settings, such as font size or color, to layout

objects. This procedure applies formatting attributes to the current frame,

repeating frame, field, or boilerplate object. You specify which formatting

attributes should be applied to an object by defining a mask. A mask is a

collection of attributes; each attribute has one or more values. You can

change a number of attributes, such as the borderwidth, background border

color, border pattern, foreground border color, fill pattern, global text

color, weight and several other attributes. For a complete list, refer to

the "The Oracle Reports PL/SQL Package: SRW" section in the

"PL/SQL Interface" chapter of the Oracle Reports Reference Manual.

SRW.SET_ATTR(object_id NUMBER, attr SRW.GENERIC_ATTR);

object_id Is always zero. (The object must always set its own attributes.)

attr Is srw.attr (that is, the attributes to change and their values).

Example:

If salary equals 2000, this code segment sets the following attributes:

Border width = 1

Foreground border color = red

Background border color = blue

Border pattern = checker

Foreground fill color = yellow

Background fill color = green

Fill pattern = crisscross

IF :sal = 2000 THEN

SRW.ATTR.MASK := SRW.BORDERWIDTH_ATTR +

SRW.FBCOLOR_ATTR +

SRW.BBCOLOR_ATTR +

SRW.BORDPATT_ATTR +

SRW.FFCOLOR_ATTR +

SRW.BFCOLOR_ATTR +

SRW.FILLPATT_ATTR;

SRW.ATTR.BORDERWIDTH := 1;

SRW.ATTR.FBCOLOR := 'red';

SRW.ATTR.BBCOLOR := 'blue';

SRW.ATTR.BORDPATT := 'checker';

SRW.ATTR.FFCOLOR := 'yellow';

SRW.ATTR.BFCOLOR := 'green';

SRW.ATTR.FILLPATT := 'crisscross';

SRW.SET_ATTR(0, SRW.ATTR);

END IF;

RETURN TRUE;

Example 4 - SRW. SET_FIELD

Syntax/Explanation:

SRW.SET_FIELD_CHAR(object_id, text CHAR);

SRW.SET_FIELD_DATE(object_id, date DATE);

SRW.SET_FIELD_NUM(object_id, number NUMBER);

These SRW SET_FIELD procedures are very useful in format triggers. They are

often used to change the data that will be displayed in a particular item

based on a specific condition. These are invaluable, since you cannot use

assignment statements to set a field's value in Reports. SRW.SET_FIELD_XXX

must be used to accomplish this.

Example:

To display the text "LOW" in the salary_range field for every employee whose

salary is less than 20,000, create the following format trigger on the

salary_range field.

FUNCTION chgfield RETURN BOOLEAN IS

BEGIN

IF :sal < 20000 THEN

SRW.SET_FIELD_CHAR(0, 'LOW');

ELSE

END IF;

RETURN TRUE;

END;

For additional examples using the SRW.SET_FIELD_XXXX procedures in format

triggers, see:

Note31364.1 Using Format Triggers in Reports.

Running SQL*Trace on an Applications Report

Oracle Developer , Oracle Technical , SQL

There are two methods to running a Report with SQL*Trace which are:

Method #1: Normally, in order to run a report with SQL*Trace, you would have to do the following:

1. Turn SQL*Trace on by modifying your init.ora file.
2. Shut down the concurrent managers and the database.
3. Bring the database and the concurrent managers back up.
4. Arrange to run JUST that specific report without running
anything else.
5. After running the report, shut down the database and
concurrent managers.
6. Turn SQL*Trace off by modifying your init.ora file.
7. Bring the database and concurrent managers back up.

Method #2: A simpler method is as follows (detailed instructions follow):

1. Backup your current .rdf of the report.
2. Modify the .rex for the report, adding two simple lines.
3. Convert the new .rex into the new .rdf file.
4. Run the report.
5. Restore the original .rdf file.

Detailed instructions to run a Report with SQL*Trace using Method #2:

1. Backup your current .rdf and .rex files.

2. Create the .rex file if necessary:

Make sure a .rex text file of the report exists. (Customers are
not shipped the .rex text file to save space on their system).
If the .rex does not exist, create one by issuing the following
command as the applmgr user:

r25convm userid=<userid>/<password> source=<reportname>.rdf
stype=rdffile dtype=rexfile dest=<reportname>.rex

For Example:

r25convm userid=ar/ar source=RAXIIR.rdf stype=rdffile
dtype=rexfile dest=RAXIIR.rex

3. Edit the .rex file:

A. Pull the .rex file into your editor of choice and search
for 'SRWINIT'. You should find an entry similar to the
following, with a NAME of 'beforerep____':

Example I:

DEFINE TOOL_PLSQL
BEGIN
ITEMID = 103
NAME = <<"beforerep0040">>
PLSLFID_EP = (BLONG) NULLP
OBJECT_ID = 0
TYPE
PLSLFID_ST = (TLONG)
<<"SRW.USER_EXIT('FND SRWINIT');
">>
END

Example II:

BEGIN
SRW.USER_EXIT('FND SRWINIT');
IF (:p_debug_switch = 'Y') THEN
SRW.MESSAGE('1','After SRWINIT');
END IF;
END

B. Immediately after the ; which follows 'FND SRWINIT', (and
remaining within the "" that surround the line) add the
following line:

srw.do_sql('alter session set sql_trace=TRUE');

The modified versions of the above examples should look
like the following:

Example I:

DEFINE TOOL_PLSQL
BEGIN
ITEMID = 103
NAME = <<"beforerep0040">>
PLSLFID_EP = (BLONG) NULLP
OBJECT_ID = 0
TYPE
PLSLFID_ST = (TLONG)
<<"SRW.USER_EXIT('FND SRWINIT');
srw.do_sql('alter session set sql_trace=TRUE');
">>
END

Example II:

BEGIN
SRW.USER_EXIT('FND SRWINIT');
srw.do_sql('alter session set sql_trace=TRUE');
IF (:p_debug_switch = 'Y') THEN
SRW.MESSAGE('1','After SRWINIT');
END IF;
END

C. Find the line similar to the following, by searching
for 'SRWEXIT':

<<"SRW.USER_EXIT('FND SRWEXIT');
">>
END

D. Immediately after the ; which follows 'FND SRWEXIT',
but remaining within the "", add the following line:

srw.do_sql('alter session set sql_trace=FALSE');

For Example:

<<"SRW.USER_EXIT('FND SRWEXIT');
srw.do_sql('alter session set sql_trace=FALSE');
">>
END

NOTE: This is really not necessary as the trace session
will terminate when the report completes, but it's a
good idea to include it as a precautionary measure.

4. Convert the modified .rex file into a .rdf file:

To do this, issue the following command as the applmgr user -- rwcon60 (Reports 6.0.x) or r25convm (Reports 2.5.x) or r25mrepm (Reports 2.5.x):

r25convm userid=<userid>/<password> source=<reportname>.rex
stype=rexfile dtype=rdffile dest=<reportname>.rdf

For Example:

r25convm userid=ap/ap source=RAXIIR.rex stype=rexfile
dtype=rdffile dest=RAXIIR.rdf

NOTE: If you did not properly modify the .rex file, the system
will not be able to generate the .rdf file. An example of the
message you may get is: "Conversion of 'RAXNCAR.rex' cancelled".

5. Run the report:

Logon to the Applications and run the report through the concurrent
managers. When it has completed, exit the Applications.

6. Access the trace file:

The trace file will be located in the directory specified by the
user_dump_dest variable in your init.ora file.

7. Restore the original report files:

Restore the original .rdf file (and .rex if one existed before
modification). You may wish to backup the .rdf file containing
the SQL*Trace code, just in case you need to run it at a later
time. Once the issue you're troubleshooting is resolved, you
can delete it.

Click to add to Favorites Running SQL*Trace on an Applications Report (Doc ID 1019231.6) To BottomTo Bottom
Applies to:
Oracle Application Object Library - Version 11.5.10.2 to 12.1.3 [Release 11.5 to 12.1]
Information in this document applies to any platform.
***Checked for relevance on 10-Jan-2013***
Goal

The goal of this document is to provide instructions to run a report with SQL*Trace
Solution

There are two methods to running a Report with SQL*Trace which are:

Method #1: Normally, in order to run a report with SQL*Trace, you would have to do the following:

1. Turn SQL*Trace on by modifying your init.ora file.
2. Shut down the concurrent managers and the database.
3. Bring the database and the concurrent managers back up.
4. Arrange to run JUST that specific report without running
anything else.
5. After running the report, shut down the database and
concurrent managers.
6. Turn SQL*Trace off by modifying your init.ora file.
7. Bring the database and concurrent managers back up.

Method #2: A simpler method is as follows (detailed instructions follow):

1. Backup your current .rdf of the report.
2. Modify the .rex for the report, adding two simple lines.
3. Convert the new .rex into the new .rdf file.
4. Run the report.
5. Restore the original .rdf file.

Detailed instructions to run a Report with SQL*Trace using Method #2:

1. Backup your current .rdf and .rex files.

2. Create the .rex file if necessary:

Make sure a .rex text file of the report exists. (Customers are
not shipped the .rex text file to save space on their system).
If the .rex does not exist, create one by issuing the following
command as the applmgr user:

r25convm userid=<userid>/<password> source=<reportname>.rdf
stype=rdffile dtype=rexfile dest=<reportname>.rex

For Example:

r25convm userid=ar/ar source=RAXIIR.rdf stype=rdffile
dtype=rexfile dest=RAXIIR.rex

3. Edit the .rex file:

A. Pull the .rex file into your editor of choice and search
for 'SRWINIT'. You should find an entry similar to the
following, with a NAME of 'beforerep____':

Example I:

DEFINE TOOL_PLSQL
BEGIN
ITEMID = 103
NAME = <<"beforerep0040">>
PLSLFID_EP = (BLONG) NULLP
OBJECT_ID = 0
TYPE
PLSLFID_ST = (TLONG)
<<"SRW.USER_EXIT('FND SRWINIT');
">>
END

Example II:

BEGIN
SRW.USER_EXIT('FND SRWINIT');
IF (:p_debug_switch = 'Y') THEN
SRW.MESSAGE('1','After SRWINIT');
END IF;
END

B. Immediately after the ; which follows 'FND SRWINIT', (and
remaining within the "" that surround the line) add the
following line:

srw.do_sql('alter session set sql_trace=TRUE');

The modified versions of the above examples should look
like the following:

Example I:

DEFINE TOOL_PLSQL
BEGIN
ITEMID = 103
NAME = <<"beforerep0040">>
PLSLFID_EP = (BLONG) NULLP
OBJECT_ID = 0
TYPE
PLSLFID_ST = (TLONG)
<<"SRW.USER_EXIT('FND SRWINIT');
srw.do_sql('alter session set sql_trace=TRUE');
">>
END

Example II:

BEGIN
SRW.USER_EXIT('FND SRWINIT');
srw.do_sql('alter session set sql_trace=TRUE');
IF (:p_debug_switch = 'Y') THEN
SRW.MESSAGE('1','After SRWINIT');
END IF;
END

C. Find the line similar to the following, by searching
for 'SRWEXIT':

<<"SRW.USER_EXIT('FND SRWEXIT');
">>
END

D. Immediately after the ; which follows 'FND SRWEXIT',
but remaining within the "", add the following line:

srw.do_sql('alter session set sql_trace=FALSE');

For Example:

<<"SRW.USER_EXIT('FND SRWEXIT');
srw.do_sql('alter session set sql_trace=FALSE');
">>
END

NOTE: This is really not necessary as the trace session
will terminate when the report completes, but it's a
good idea to include it as a precautionary measure.

4. Convert the modified .rex file into a .rdf file:

To do this, issue the following command as the applmgr user -- rwcon60 (Reports 6.0.x) or r25convm (Reports 2.5.x) or r25mrepm (Reports 2.5.x):

r25convm userid=<userid>/<password> source=<reportname>.rex
stype=rexfile dtype=rdffile dest=<reportname>.rdf

For Example:

r25convm userid=ap/ap source=RAXIIR.rex stype=rexfile
dtype=rdffile dest=RAXIIR.rdf

NOTE: If you did not properly modify the .rex file, the system
will not be able to generate the .rdf file. An example of the
message you may get is: "Conversion of 'RAXNCAR.rex' cancelled".

5. Run the report:

Logon to the Applications and run the report through the concurrent
managers. When it has completed, exit the Applications.

6. Access the trace file:

The trace file will be located in the directory specified by the
user_dump_dest variable in your init.ora file.

7. Restore the original report files:

Restore the original .rdf file (and .rex if one existed before
modification). You may wish to backup the .rdf file containing
the SQL*Trace code, just in case you need to run it at a later
time. Once the issue you're troubleshooting is resolved, you
can delete it.
References
NOTE:1070541.6 - How To Generate A Report ( .RDF File) From The Unix Command Line
NOTE:1020489.102 - How to Generate a Report from a Windows NT Command Line
NOTE:1019231.6 - Running SQL*Trace on an Applications Report

SQL Commands

Oracle Developer , Oracle SQL , Oracle Technical , PL/SQL , SQL

SQL COMMANDS

CREATE COMMANDS

Create Table

CREATE TABEL EMP(empno number, ename varchar2(30), hiredate date);

CREATE TABLE scott.emp

(empno NUMBER CONSTRAINT pk_emp PRIMARY KEY,ename VARCHAR2(10) CONSTRAINT nn_ename NOT NULL CONSTRAINT upper_ename CHECK (ename = UPPER(ename)),job VARCHAR2(9),mgr NUMBER CONSTRAINT fk_mgr REFERENCES scott.emp(empno),hiredate DATE DEFAULT SYSDATE, sal NUMBER(10,2) CONSTRAINT ck_sal CHECK (sal > 500),comm  NUMBER(9,0)   DEFAULT NULL,          deptno NUMBER(2) CONSTRAINT nn_deptno NOT NULL CONSTRAINT fk_deptno   REFERENCES scott.dept(deptno))PCTFREE 5 PCTUSED 75;

Display the Constraints in Table:

DESC USER_CONSTRAINTS;

SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='EMPLOYEES';

Storage Example

CREATE TABLE salgrade(grade  NUMBER  CONSTRAINT pk_salgrade RIMARY KEY                     USING INDEX TABLESPACE users_a,losal NUMBER,hisal  NUMBER ) TABLESPACE human_resource STORAGE (INITIAL  6144 NEXT  6144 MINEXTENTS 1        MAXEXTENTS 5 );

PARALLEL Example

CREATE TABLE emp_dept PARALLEL AS SELECT * FROM scott.emp WHERE deptno = 10;

NOPARALLEL Example

CREATE TABLE emp_dept AS SELECT * FROM scott.emp WHERE deptno = 10;

Partitioned Table Example

CREATE TABLE stock_xactions (stock_symbol CHAR(5),stock_series CHAR(1),       num_shares NUMBER(10),price NUMBER(5,2),trade_date DATE) STORAGE INITIAL 100K NEXT 50K) LOGGING PARTITION BY RANGE (trade_date)    (PARTITION sx1992 VALUES LESS THAN (TO_DATE('01-JAN-1993','DD-MON-YYYY'))TABLESPACE ts0 NOLOGGING,PARTITION sx1993 VALUES LESS THAN TO_DATE('01-JAN-1994','DD-MON-YYYY'))TABLESPACE ts1,PARTITION sx1994 VALUES LESS THAN (TO_DATE('01-JAN-1995','DD-MON-YYYY'))TABLESPACE ts2);

Object Table Examples

Consider object type DEPT_T:

CREATE TYPE dept_t AS OBJECT(dname VARCHAR2(100),address VARCHAR2(200) );

Object table DEPT holds department objects of type DEPT_T:

CREATE TABLE dept OF dept_t;

CREATE OR REPLACE TYPE salesrep_t AS OBJECT(repId NUMBER,repName VARCHAR2(64));

CREATE TABLE salesreps OF salesrep_t;

REF Example

The following example creates object type DEPT_T and object table DEPT to store instances of all departments. A table with a scoped REF is then created.

CREATE TYPE dept_t AS OBJECT (dname   VARCHAR2(100),address VARCHAR2(200) );

CREATE TABLE dept OF dept_t;

CREATE TABLE emp(ename VARCHAR2(100),enumber NUMBER,edept REF dept_t SCOPE IS dept );

The following statement creates a table with a REF column which has a referential constraint defined on it:

CREATE TABLE emp(ename VARCHAR2(100),enumber NUMBER,edept REF dept_t REFERENCES dept);

Create Table Using Select Statements:

CREATE TABLE EMP1 AS SELECT * FROM SCOTT.EMP;

CREATE TABLE DEPTNO AS SELECT * FROM SCOTT.DEPT ORDER BY DEPTNO ASC;

Constraints on Type Columns Example

1.CREATE TYPE address AS OBJECT(hno NUMBER,street VARCHAR2(40),city VARCHAR2(20),zip VARCHAR2(5),phone VARCHAR2(10) );

2. CREATE TYPE person AS OBJECT(name VARCHAR2(40),dateofbirth DATE,    homeaddress address,manager REF person );

3.CREATE TABLE persons OF person(homeaddress NOT NULL UNIQUE (homeaddress.phone),CHECK (homeaddress.zip IS NOT NULL),CHECK (homeaddress.city <> 'San Francisco') );

Create Index

An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows. A partitioned index consists of partitions containing an entry for each value that appears in the indexed column(s) of the table. A function-based index is an index on expressions. It enables you to construct queries that evaluate the value returned by an expression, which in turn may include functions (built-in or user-defined).

PARALLEL Example

The following statement creates an index using 10 parallel execution servers, 5 to scan SCOTT.EMP and another 5 to populate the EMP_IDX index:

CREATE INDEX emp_idx ON scott.emp (ename) PARALLEL 5;

COMPRESS Example

To create an index with the COMPRESS clause, you might issue the following statement:

CREATE INDEX emp_idx2 ON emp(job, ename) COMPRESS 1;

Cluster Index Example

To create an index for the EMPLOYEE cluster, issue the following statement:

CREATE INDEX ic_emp ON CLUSTER employee;

No index columns are specified, because the index is automatically built on all the columns of the cluster key. For cluster indexes, all rows are indexed.

Partitioned Tables And Indexes

Maintenance of large tables and indexes can become very time and resource consuming. At the same time, data access performance can reduce drastically for these objects. Partitioning of tables and indexes can benefit the performance and maintenance in several ways:

We can devided the rows of a single table into multiple parts,Dividing the table's data in this manner is called "Partitioning the table". Partitioning is useful for very large tables and it is used for..

Improve the Qurey performance,it may be easier to load and delete the data in the partitions.

There are two type of prtitions

Range:

CREATE TABLE EMP1(ENO NUMBER PRIMARY KEY,NAME VARCHAR2(20),SAL NUMBER(8,2),

DEPTNO NUMBER REFERENCES DEPT(DEPTNO))

PARTITION BY RANGE(DEPTNO)

(PARTITION PART1 VALUES LESS THAN(20),

PARTITION PART2 VALUES LESS THAN(30));

Hash:

CREATE TABLE EMP1(ENO NUMBER PRIMARY KEY,NAME VARCHAR2(20),SAL NUMBER(8,2),

DEPTNO NUMBER REFERENCES DEPT(DEPTNO))

PARTITION BY HASH(DEPTNO)

PARTITIONS 2;

Composite Partitioning Tables

Composite partitioning allows range partitions to be hash subpartitioned on a different key. The greater number of partitions increases the possiblities for parallelism and reduces the chances of contention. The following example will range partition the table on invoice_date and subpartitioned these on the invoice_no giving a totol of 32 subpartitions:

CREATE TABLE invoices (invoice_no NUMBER NOT NULL, invoice_date DATE NOT NULL,

comments VARCHAR2(500)) PARTITION BY RANGE (invoice_date) SUBPARTITION BY HASH (invoice_no) SUBPARTITIONS 8

(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')),

PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')),

PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')),

PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY'));

Partitioning Indexes

There are two basic types of partitioned index:

Local - All index entries in a single partition will correspond to a single table partition (equipartitioned). They are created with the LOCAL keyword and support partition independance. Equipartioning allows oracle to be more efficient whilst devising query plans.
Global - Index in a single partition may correspond to multiple table partitions. They are created with the GLOBAL keyword and do not support partition independance. Global indexes can only be range partitioned and may be partitioned in such a fashion that they look equipartitioned, but Oracle will not take advantage of this structure.

Both types of indexes can be subdivided further:

Prefixed - The partition key is the leftmost column(s) of the index. Probing this type of index is less costly. If a query specifies the partition key in the where clause partition pruning is possible, that is, not all partitions will be searched.
Non-Prefixed - Does not support partition pruning, but is effective in accessing data that spans multiple partitions. Often used for indexing a column that is not the tables partition key, when you would like the index to be partitioned on the same key as the underlying table.

Local Prefixed Indexes

Assuming the INVOICES table is range partitioned on INVOICE_DATE, the followning are examples of local prefixed indexes:

CREATE INDEX invoices_idx ON invoices (invoice_date) LOCAL(PARTITION invoices_q1 TABLESPACE users,

PARTITION invoices_q2 TABLESPACE users,

PARTITION invoices_q3 TABLESPACE users,

PARTITION invoices_q4 TABLESPACE users);

Global Prefixed Indexes

Assuming the INVOICES table is range partitioned on INVOICE_DATE, the followning examples is of a global prefixed index:

CREATE INDEX invoices_idx ON invoices (invoice_date) GLOBAL PARTITION BY RANGE (invoice_date)

(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users,

PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users,

PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) TABLESPACE users,

PARTITION invoices_q4 VALUES LESS THAN (MAXVALUE) TABLESPACE users);

Create Procedures

A procedure is a group of PL/SQL statements that you can call by name.

Stored procedures offer advantages in the areas of development, integrity, security, performance, and memory allocation.

CREATE TABEL EMP (EMPNO NUMBER, ENAME VARCHAR2 (30);

USING SELECT STATEMENT:

CREATE OR REPLACE PROCEDURE Get_Ename(Eno IN EMP.EMPNO%TYPE,

NAME OUT EMP.ENAME%ROWTYPE) AS

BEGIN

SELECT ENAME INTO NAME FROM EMP WHERE EMPNO=Eno;

END;

/ (OR)

CREATE OR REPLACE PROCEDURE Get_Ename(Eno IN number,

NAME OUT varcahr2) AS

BEGIN

SELECT ENAME INTO NAME FROM EMP WHERE EMPNO=Eno;

END;/

Create Variable: VARIABLE X VARCHAR2(30);

EXEC GET_ENAME(7788, :X)

PRINT X;

USING DELETE STATEMENT:

CREATE OR REPLACE PROCEDURE fire_emp1(Emp_id NUMBER) AS

   BEGIN

      DELETE FROM Emp_tab WHERE Empno = Emp_id;

   END;

USING UPDATE STATEMENT:

CREATE PROCEDURE sam.credit (acc_no IN NUMBER, amount IN NUMBER) AS

   BEGIN

      UPDATE accounts

      SET balance = balance + amount

      WHERE account_id = acc_no;

   END;

Create Functions

A stored function (also called a user function) is a set of PL/SQL statements you can call by name. Stored functions are very similar to procedures, except that a function returns a value to the environment in which it is called.

CREATE FUNCTION get_bal(acc_no IN NUMBER)

   RETURN NUMBER

   IS acc_bal NUMBER(11,2);

   BEGIN

      SELECT balance

      INTO acc_bal

      FROM accounts

      WHERE account_id = acc_no;

      RETURN(acc_bal);

    END;

Create Sequence

A sequence is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

CREATE SEQUENCE eseq INCREMENT BY 10;

CREATE SEQUENCE empno start with 1 increment by 1 maxvalue 100 minvalue 1 cycle cache 5;

Select empno.nextval from dual;

Select empno.currval from dual;

Create Schema

To create multiple tables and views and perform multiple grants in a single transaction.

To execute a CREATE SCHEMA statement, Oracle executes each included statement. If all statements execute successfully, Oracle commits the transaction. If any statement results in an error, Oracle rolls back all the statements.

Create Synonym

A synonym is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class schema object,

CREATE SYNONYM market FOR scott.market_research;

CREATE PUBLIC SYNONYM emp FOR scott.emp@sales;

A synonym may have the same name as the base table, provided the base table is contained in another schema.

Create Tablespace

A tablespace is an allocation of space in the database that can contain schema objects. When you create a tablespace, it is initially a read-write tablespace.

This statement creates a tablespace named TABSPACE_2 with one datafile:

A database is divided into logical storage units called tablespaces, which group related logical structures together. For example, tablespaces commonly group all of an application's objects to simplify some administrative operations.

CREATE TABLESPACE tabspace_2

DATAFILE 'diska:tabspace_file2.dat' SIZE 20M

DEFAULT STORAGE (INITIAL 10K NEXT 50K    MINEXTENTS 1 MAXEXTENTS 999)

Create Cluster

A cluster is a schema object that contains data from one or more tables, all of which have one or more columns in common. Oracle stores together all the rows (from all the tables) that share the same cluster key. They can be created to increase the performance of data retrieval.

Clustered Tables Related data stored together more efficiently

Non Clustered Tables Related data stored differently and taking up more space.

CREATE CLUSTER personnel (department_number  NUMBER(2)) SIZE 512     STORAGE (INITIAL 100K NEXT 50K);

Adding Tables to a Cluster

The following statements add the EMP and DEPT tables to the cluster:

CREATE TABLE emp (empno  NUMBER   PRIMARY KEY, ename  VARCHAR2(10)  NOT NULL                              CHECK (ename = UPPER(ename)), job   VARCHAR2(9), mgr NUMBER REFERENCES scott.emp(empno), hiredate  DATE  CHECK (hiredate < TO_DATE ('08-14-1998', 'MM-DD-YYYY')),      sal   NUMBER(10,2)  CHECK (sal > 500), comm  NUMBER(9,0)  DEFAULT NULL, deptno NUMBER(2)  NOT NULL ) CLUSTER personnel (deptno);

CREATE TABLE dept (deptno  NUMBER(2),dname   VARCHAR2(9),loc  VARCHAR2(9))     CLUSTER personnel (deptno);

Create Trigger

A database trigger is

A stored PL/SQL block associated with a table, a schema, or the database
An anonymous PL/SQL block or a call to a procedure implemented in PL/SQL or Java

Oracle automatically executes a trigger when specified conditions occur.

Example:

CREATE TABLE departments(ID NUMBER(10) NOT NULL,DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD(CONSTRAINT dept_pk PRIMARY KEY (ID));

CREATE SEQUENCE dept_seq;

CREATE OR REPLACE TRIGGER dept_bir BEFORE INSERT ON departments

FOR EACH ROW

WHEN (new.id IS NULL)

BEGIN

  SELECT dept_seq.NEXTVAL   INTO   :new.id   FROM   dual;

END;

CREATE OR REPLACE TRIGGER dept_bir

BEFORE INSERT ON departments

FOR EACH ROW

BEGIN

  SELECT dept_seq.NEXTVAL

  INTO   :new.id

  FROM   dual;

END;

CREATE OR REPLACE TRIGGER dept_bir

BEFORE INSERT ON departments

FOR EACH ROW

BEGIN

  IF new.id IS NOT NULL THEN

    RAISE_APPLICATION_ERROR(-20000, 'ID cannot be specified');

  ELSE

    SELECT dept_seq.NEXTVAL

    INTO   :new.id

    FROM   dual;

  END IF;

END;

Create User

CREATE USER sidney IDENTIFIED BY welcome DEFAULT TABLESPACE cases_ts     QUOTA 10M ON cases_ts QUOTA 5M ON temp_ts QUOTA 5M ON system PROFILE engineer PASSWORD EXPIRE;

CREATE USER ops$george IDENTIFIED EXTERNALLY DEFAULT TABLESPACE accs_ts     TEMPORARY TABLESPACE temp_ts QUOTA UNLIMITED ON accs_ts QUOTA UNLIMITED ON temp_ts;

Create View

To define a view, a logical table based on one or more tables or views. A view contains no data itself. The tables upon which a view is based are called base tables.

CREATE VIEW dept20 AS SELECT ename, sal*12 annual_salary FROM emp WHERE deptno = 20;

CREATE VIEW clerk (id_number, person, department, position)  AS SELECT empno, ename, deptno, job  FROM emp  WHERE job = 'CLERK'  WITH CHECK OPTION CONSTRAINT wco;

CREATE VIEW EMP_DTLS AS

SELECT E.EMPNO,E.ENAME,D.DNAME,E.SAL+NVL(COMM,0) "TOTALSAL" FROM EMP E,DEPT D WHERE D.DEPTNO=E.DEPTNO;

Join View Example

CREATE VIEW ed AS  SELECT e.empno, e.ename, d.deptno, d.loc  FROM emp e, dept d  WHERE e.deptno = d.deptno

View created.

SELECT column_name, updatable  FROM user_updatable_columns  WHERE table_name = 'ED';

Read-Only View Example

CREATE VIEW clerk (id_number, person, department, position)  AS SELECT empno, ename, deptno, job FROM emp  WHERE job = 'CLERK'    WITH READ ONLY;

Create Materialized View

For data warehousing purposes, a materialized view definition can include an aggregation (SUM, COUNT(x), COUNT(*), COUNT(DISTINCT x), AVG, VARIANCE, STDDEV, MIN, and MAX) and any number of joins. Such materialized views can be used in query rewrite, an optimization technique that transforms a user request written in terms of master tables into a semantically equivalent request that includes one or more materialized view. In a data warehousing environment, all detail tables must be local.

CREATE MATERIALIZED VIEW mv1 REFRESH FAST ON COMMIT

   AS SELECT t.month, p.prod_name, SUM(f.sales) AS sum_sales

      FROM time t, product p, fact f

      WHERE f.curDate = t.curDate AND f.item = p.item

      GROUP BY t.month, p.prod_name

   BUILD IMMEDIATE;

DROP MATERIALIZED VIEW LOG ON parts;

ALTER Commands:

Alter Cluster

The following statement alters the CUSTOMER cluster in the schema SCOTT:

ALTER CLUSTER scott.customer SIZE 512 STORAGE (MAXEXTENTS 25);

ALTER CLUSTER scott.customer  DEALLOCATE UNUSED KEEP 30 K;

ADD Constraints:

ALTER TABLE EMP ADD CONSTRAINTS PK_EMPNO PRIMARY KEY(EMPNO);

ALTER TABLE EMP ADD CONSTRAINTS FK_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO);

Alter Index

ALTER INDEX scott.customer INITRANS 5 STORAGE (NEXT 100K);

Alter Procedure

ALTER PROCEDURE henry.close_acct COMPILE;

Alter Sequence

ALTER SEQUENCE eseq  MAXVALUE 1500;

Alter Table

Add Column Example

ALTER TABLE emp ADD(thriftplan NUMBER(7,2),loancode CHAR(1) NOT NULL);

Modify Column Examples

ALTER TABLE emp MODIFY (thriftplan NUMBER(9,2));

DISABLE Example

ALTER TABLE customers DISABLE UNIQUE (areaco, phoneno) CASCADE;

DROP PRIMARY KEY Example

ALTER TABLE t1 DROP (pk) CASCADE CONSTRAINTS;

ALTER TABLE t1 DROP (pk, fk, c1);

Drop Constraint Examples

ALTER TABLE dept DROP PRIMARY KEY CASCADE;

ALTER TABLE dept DROP UNIQUE (dname);

Alter Tablespace

ALTER TABLESPACE accounting BEGIN BACKUP;

ALTER TABLESPACE accounting END BACKUP;

Alter Trigger

ALTER TRIGGER reorder DISABLE;

ALTER TRIGGER reorder ENABLE;

Alter View

ALTER VIEW customer_view COMPILE;

DELETE COMMANDS

DELETE FROM temp_assign;

DELETE FROM emp WHERE JOB = 'SALESMAN'AND COMM < 100;

DELETE FROM (select * from emp)WHERE JOB = 'SALESMAN'AND COMM < 100;

DELETE FROM emp WHERE job = 'SALESMAN' AND COMM < 100 RETURNING sal INTO:1;

Delete Table

DROP TABLE test_data;

RENAME dept TO emp_dept;

CREATE TABLE temporary (newname, col2, col3)AS SELECT oldname, col2, col3 FROM static;

DROP TABLE static;

RENAME temporary TO static;

Drop Columns

Logical Delete

On large tables the process of physically removing a column can be very time and resource consuming. For this reason you may decide to logically delete it:

ALTER TABLE table_name SET UNUSED (column_name);
ALTER TABLE table_name SET UNUSED (column_name1, column_name2);

Physical Delete

To physically drop a column you can use one of the following syntaxes, depending on whether you wish to drop a single or multiple columns:

ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name DROP (column_name1, column_name2);

Delete Cluster

DROP CLUSTER geography INCLUDING TABLES   CASCADE CONSTRAINTS;

Delete Function

DROP FUNCTION riddley.new_acct;

Delete Index

DROP INDEX monolith;

Delete Procedure

DROP PROCEDURE kerner.transfer

Delete Tablespace

DROP TABLESPACE TableSpaceName INCLUDING CONTENTS CASCADE CONSTRAINTS;

Delete Trigger

DROP TRIGGER ruth.reorder;

Delete User

DROP USER bradley CASCADE;

Delete View

DROP VIEW view_data;

SELECT AND SUBQUERIES

SELECT * FROM EMP;

SELECT ‘SELECT * FROM ‘||TABLE_NAME||’;’ FROM USER_TABLES;

NULL Function

SELECT EMPNO, ENAME,SAL,COMM FROM EMP WHERE COMM IS NULL;

SELECT EMPNO,ENAME,SAL,COMM FROM EMP WHERE COMM IS NOT NULL;

SELECT SAL+NVL(COMM,0) FROM EMP;

GROUP BY Examples

SELECT * FROM emp WHERE deptno = 30;

SELECT ename, job, sal, deptno FROM emp WHERE NOT (job = 'SALESMAN' AND deptno = 30);

SELECT a.deptno "Department",a.num_emp/b.total_count "%Employees",  a.sal_sum/b.total_sal "%Salary"  FROM (SELECT deptno, COUNT(*) num_emp, SUM(SAL) sal_sum FROM scott.emp GROUP BY deptno) a,(SELECT COUNT(*) total_count, SUM(sal) total_sal FROM scott.emp) b ;

SELECT COUNT(*) * 100 FROM emp SAMPLE BLOCK (1);

SELECT deptno, MIN(sal), MAX (sal) FROM emp GROUP BY deptno;

SELECT deptno, MIN(sal), MAX (sal)FROM emp WHERE job = 'CLERK'     GROUP BY deptno;

SELECT EMP_NO,COUNT(EMP_NO) FROM EMP_ATTENDANCE_DTLS WHERE

SUBSTR(TO_CHAR(ENTRY_DT,'DD/MM/YY'),4,5)='03/05' GROUP BY EMP_NO

Display the Top or Bottom level rows

SELECT LEVEL, MAX(SAL) FROM EMP WHERE LEVEL= 1 OR LEVEL=2 CONNECT BY PRIOR SAL > SAL GROUP BY LEVEL

SELECT LEVEL, MIN(SAL) FROM EMP WHERE LEVEL=2 OR LEVEL =3 CONNECT BY PRIOR SAL < SAL GROUP BY LEVEL

SELECT EMPNO,ENAME,SAL FROM EMP A WHERE 3>=(SELECT COUNT(DISTINCT SAL) FROM EMP B WHERE B.SAL>=A.SAL) ORDER BY SAL DESC;

SELECT EMPNO,ENAME,SAL FROM EMP A WHERE 3>=(SELECT COUNT(DISTINCT SAL) FROM EMP B WHERE A.SAL>=B.SAL) ORDER BY SAL DESC;

SELECT EMPNO,ENAME,SAL FROM (SELECT * FROM EMP ORDER BY SAL DESC)

WHERE ROWNUM<=3;

SELECT EMPNO,ENAME,SAL FROM (SELECT * FROM EMP ORDER BY SAL)

WHERE ROWNUM<=3;

Having Examples

SELECT deptno, MIN(sal), MAX(sal)FROM emp WHERE job = 'CLERK'     GROUP BY deptno  HAVING MIN(sal) < 1000;

ORDER BY Examples

SELECT * FROM emp WHERE job = 'SALESMAN' ORDER BY comm DESC;

SELECT ename, deptno, sal FROM emp ORDER BY deptno ASC, sal DESC;

SELECT ename, deptno, sal FROM emp  ORDER BY 2 ASC, 3 DESC;

FOR UPDATE Examples

SELECT empno, sal, comm FROM emp, dept WHERE job = 'CLERK' AND emp.deptno = dept.deptno AND loc = 'NEW YORK' FOR UPDATE;

SELECT empno, sal, comm FROM emp, dept WHERE job = 'CLERK' AND emp.deptno = dept.deptno AND loc = 'NEW YORK' FOR UPDATE OF emp.sal;

Table Collection Examples

CREATE TYPE ProjectType AS OBJECT(pno   NUMBER, pname CHAR(31),budget NUMBER);

CREATE TYPE ProjectSet AS TABLE OF ProjectType;

Sub query Examples

SELECT ename, deptno FROM emp WHERE deptno =(SELECT deptno FROM emp  WHERE ename = 'TAYLOR');

UPDATE emp SET sal = sal * 1.1 WHERE empno NOT IN (SELECT empno FROM bonus);

CREATE TABLE newdept (deptno, dname, loc) AS SELECT deptno, dname, loc FROM dept;

SELECT * FROM EMP_DTLS WHERE TOTALSAL= (SELECT MAX(TOTALSAL) FROM EMP_DTLS);

SELECT * FROM EMP_DTLS WHERE TOTALSAL >(SELECT TOTALSAL FROM EMP_DTLS WHERE EMPNO = 7369);

SELECT * FROM EMP_DTLS WHERE TOTALSAL <(SELECT TOTALSAL FROM EMP_DTLS

WHERE EMPNO=7839) ORDER BY TOTALSAL;

Equijoin Examples

This equijoin returns the name and job of each employee and the number and name of the department in which the employee works:

SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno;

SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND job = 'CLERK';

Ex:

SELECT L.EMP_LEAVE_ID,L.EMP_NO,L.LEAVE_REASON,M.NAME,M.SURNAME FROM EMP_LEAVE_DTLS L,MST_EMPLOYEES M WHERE L.EMP_NO=M.EMP_NO AND L.EMP_NO=1421;

Self Join Example

The following query uses a self join to return the name of each employee along with the name of the employee's manager:

SELECT e1.ename||' works for '||e2.ename "Employees and their Managers"     FROM emp e1, emp e2   WHERE e1.mgr = e2.empno;

Outer Join Examples

This query uses an outer join to extend the results of the Equijoin example above:

SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno (+) = dept.deptno;

SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno (+) = dept.deptno AND job (+) = 'CLERK';

SELECT orderno, custno, TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE"     FROM orders;

The following outer join returns all customers and the dates they placed orders. The (+) operator ensures that customers who placed no orders are also returned:

SELECT custname, TO_CHAR (orderdate, 'MON-DD-YYYY') "ORDERDATE" FROM customers, orders WHERE customers.custno = orders.custno (+)

Inner Join

SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E,DEPT D WHERE

E.DEPTNO=D.DEPTNO

In Oracle 9i

SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E inner join DEPT D on

E.DEPTNO=D.DEPTNO

Left Outer Join

SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E,DEPT D WHERE

E.DEPTNO(+)=D.DEPTNO

In Oracle 9i

SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E left outer join DEPT D on

E.DEPTNO)=D.DEPTNO

Right Outer Join

SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E,DEPT D WHERE

E.DEPTNO=D.DEPTNO(+)

In Oracle 9i

SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E right outer join DEPT D on

E.DEPTNO)=D.DEPTNO

Full Outer Join

SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E,DEPT D WHERE

E.DEPTNO(+)=D.DEPTNO

UNION

SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E,DEPT D WHERE

E.DEPTNO=D.DEPTNO(+);

In Oracle 9i

SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E full outer join DEPT D on

E.DEPTNO)=D.DEPTNO

DISTINCT Example:

SELECT EMPNO,ENAME "Employee Name",SAL,JOB FROM EMP;

SELECT ENAME||JOB AS "EMP INFO" FROM EMP;

SELECT DISTINCT DEPTNO,JOB FROM EMP;

SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL BETWEEN 2000 AND 3000;

SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP WHERE DEPTNO IN(10,20);

SELECT EMPNO,ENAME,SAL FROM EMP WHERE ENAME LIKE'S%';

SELECT EMPNO,ENAME,SAL FROM EMP WHERE ENAME LIKE'%T';

SELECT EMPNO,ENAME,SAL FROM EMP WHERE JOB NOT IN('MANAGER','PRESIDENT');

SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL;

CHARACTER FUNCTIONS:

SELECT SUBSTR('SQL COMMANDS',1,3) FROM DUAL;

SELECT LOWER('ORACLE') FROM DUAL;

SELECT LENGTH('ORACLE') FORM DUAL;

SELECT INITCAP('ORACLE') FROM DUAL;

SELECT CONCAT(ENAME,JOB) FROM EMP;

SELECT LPAD(SAL,11,'*') FROM EMP;  SELECT RPAD(SAL,11,'*') FROM EMP;

NUMBER FUNCTIONS:

SELECT ROUND(2356.2345,2),ROUND(2541.012,0),ROUND(3698.235,-1) FROM DUAL;

SELECT TRUNC(2356.2345,2),TRUNC(2541.012,0),TRUNC(3698.235,-1) FROM DUAL;

SELECT EMPNO,ENAME,SAL,MOD(SAL,2000) FROM EMP WHERE EMPNO=7566;

DATE FUNCTIONS:

SELECT SYSDATE FROM DUAL;

SELECT HIREDATE,HIREDATE+7,HIREDATE-7,SYSDATE-HIREDATE FROM EMP WHERE HIREDATE LIKE'%JUN%';

SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP WHERE EMPNO=7566;

SELECT HIREDATE,ADD_MONTHS(HIREDATE,3),ADD_MONTHS(HIREDATE,-3) FROM EMP WHERE DEPTNO=20;

SELECT HIREDATE,NEXT_DAY(HIREDATE,'FRIDAY'),NEXT_DAY(HIREDATE,6) FROM EMP WHERE DEPTNO=10;

SELECT SYSDATE,NEXT_DAY(SYSDATE,'FRIDAY'),NEXT_DAY(SYSDATE,2) FROM DUAL;

CANVERTION FUNCTIONS:

SELECT TO_CHAR(SYSDATE,'DAY,DDTH MONTH YYYY') FROM DUAL;

SELECT TO_CHAR(SYSDATE,'HH:MI:SS') FROM DUAL;

SELECT TO_CHAR(SAL,'$9,0000') FROM EMP;

SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL>TO_NUMBER('1500');

SELECT * FROM EMP WHERE SUBSTR(HIREDATE,4,3)='JAN';

SELECT * FROM EMP WHERE SUBSTR(TO_CHAR(HIREDATE,'DD/MM/YY'),4,5)='01/82';

SELECT * FROM EMP WHERE SUBSTR(TO_CHAR(HIREDATE,'DD/MM/YYYY'),4,2)='01';

GENERAL FUNCTIONS:

SELECT USER FROM DUAL;

SELECT VSIZE('HELLO') FROM DUAL;

SELECT ENAME,JOB,SAL,DECODE(JOB,'CLERK',1.10*SAL,'MANAGER',2*SAL)"RECEIVED SALARY" FROM EMP;

SELECT DECODE(COMM,NULL,'NOT AVAILABLE',COMM) FROM EMP;

SELECT HIREDATE,DECODE(SUBSTR(HIREDATE,4,3),'DEC','EMPLOYEE JOINED IN DECEMBER','FEB','EMPLOYEE JOINED IN FEBRUARY',

HIREDATE) FROM EMP

SET OPARATORS:

UNION:

SELECT JOB,DEPTNO FROM EMP WHERE DEPTNO=10

                              UNION

SELECT JOB,DEPTNO FROM EMP WHERE DEPTNO=30;

UNION ALL:

SELECT JOB,DEPTNO FROM EMP WHERE DEPTNO=10

                         UNION ALL

SELECT JOB,DEPTNO FROM EMP WHERE DEPTNO=30;

INTERSECT:

SELECT JOB FROM EMP WHERE DEPTNO=10

                       INTERSECT

SELECT JOB FROM EMP WHERE DEPTNO=30;

MINUS:

SELECT JOB FROM EMP WHERE DEPTNO=10

                             MINUS

SELECT JOB FROM EMP WHERE DEPTNO=30;

TRUNCATE

TRUNCATE TABLE emp PRESERVE SNAPSHOT LOG;

TRUNCATE TABLE stock;

UPDATE

UPDATE emp SET comm = NULL WHERE job = 'TRAINEE';

UPDATE emp SET job = 'MANAGER', sal = sal + 1000, deptno = 20 WHERE ename = 'JONES';

INSERT

INSERT INTO dept VALUES (50, 'PRODUCTION', 'SAN FRANCISCO');

INSERT INTO emp (empno, ename, job, sal, comm, deptno)VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40);

INSERT INTO (SELECT empno, ename, job, sal, comm, deptno FROM emp)   VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40);

Sub Query Example

INSERT INTO bonus SELECT ename, job, sal, comm FROM emp WHERE comm > 0.25 * sal OR job IN ('PRESIDENT', 'MANAGER');

INSERT INTO scott.accounts@sales (acc_no, acc_name) VALUES (5001, 'BOWER');

Sequence Example

INSERT INTO emp VALUES  (empseq.nextval, 'LEWIS', 'CLERK',  7902, SYSDATE, 1200, NULL, 20);

GRANT PRIVILEGES AND ROLES

GRANT CREATE SESSION TO richard;

GRANT CREATE TABLE TO travel_agent;

GRANT READ ON DIRECTORY bfile_dir1 TO scott WITH GRANT OPTION;

GRANT ALL ON bonus TO jones WITH GRANT OPTION;

GRANT SELECT, UPDATE ON golf_handicap TO PUBLIC;

GRANT REFERENCES (empno), UPDATE (empno, sal, comm)ON scott.emp TO blake;

REVOKE PRIVILEGES AND ROLES

REVOKE DROP ANY TABLE FROM bill, mary;

REVOKE CREATE TABLESPACE FROM controller;

create TABLE : This is a DDL command in SQL that creates a new table in a database.

create TABLE table-name (column-name data-type [size] NOT NULL/DEFAULT default-value] CHECK (column-name 0) UNIQUE (column-name) PRIMARY KEY (column-name) FOREIGN KEY (column-name) REFERENCES table-name)

ALTER TABLE : This is a DDL command in SQL that modifies the structure of an existing table.
Syntax : ALTER TABLE table-name ADD (column-name data-type [size] [NOT NULL DEFAULT]...)
primary key definition / foreign key definition
DROP PRIMARY KEY / DROP FOREIGN KEY)
DROP TABLE : This is DDL command in SQL that deletes the an existing table. Once you delete a table, all data contained in it is lost and cannot be recovered. The storage space used by this table is also released.
Syntax : DROP TABLE table-name

Interacting with a Database :

SELECT : This is a DML command in SQL that retrieves data from the database in the form of query results. The command supports the following keywords and clauses :
FROM This keyword specifies the name of the table.
* This keyword selects all the columns of the table.
WHERE This keyword gives the search condition that specifies the data to be retrieved.
AND This operator is used to combine two or more search conditions.
ORDER BY This keyword sorts the query result on one or more columns.
GROUP BY This keyword groups the query result and lets you generate summary result for each group.
NULL values This value indicates that the data is not present.
Subquery This is the query that is place inside the main query. It passes its query result to the main query.
INSERT : This is a DML command in SQL that you use to add data in rows of a table.
SYNTAX : INSERT INTO table-name (column-names) VALUES (constant/NULL)
UPDATE : This is a DML command in SQL that you use to change data on rows of a table.
Syntax : UPDATE table-name SET column-name-value WHERE condition
DELETE : This is a DML command in SQL that removes one or more rows of data from a table.
Syntax : DELETE FROM table-name WHERE condition.
End-user's View of a Database :
Views : Views are relations that are derived from one or more source tables. Views have the following features:
Views let you restrict the access to data so that end-users see data relevant to them.
Views do not physically exist in the database and only their definition is stored by an RDBMS.
An RDBMS accesses the source tables for data to be retrieved from a view.
Any changes that users make to views do not reflect in the source tables if the view has been created using a Join condition.
Views created WITH CHECK OPTION allows for an added measure of security in a view. For example, the user will not be able to insert or update a row that could not be selected by the view-with check option prevents this from happening.
create VIEW : A view can be created using the create VIEW command.
Syntax : create VIEW view-name (column-names) AS query.
Retrieving Data from a View : Once you create a view, you can retrieve data from it using the SELECT command, just as you do for a table.
Restricting Access to a Database :

GRANT : This is a DCL command in SQL that you use to grant a specific set of authorities to one or more users.
Syntax : GRANT (SQL command) (column-names) ON table-name to user-name.
REVOKE : This is a DCL command in SQL that you use to take away a specific set of authorities from one or more users.
Syntax : REVOKE (SQL command) ON table-name to user-name

Introduction

Materialized views are stored summaries of queries containing precomputed results. Materialized views greatly improve data warehouse query processing.The existence of a materialized view is transparent to SQL applications, so a DBA can create or drop materialized views at any time without affecting the validity of SQL applications.

Materialized views improve query performance by precalculating expensive join and aggregation operations on the database prior to execution time and storing these results in the database. The query optimizer can make use of materialized views by automatically recognizing when an existing materialized view can and should be used to satisfy a request. It then transparently rewrites the request to use the materialized view. Queries are then directed to the materialized view and not to the underlying detail tables or views. Rewriting queries to use materialized views rather than detail relations results in a significant performance gain.

Materialized views can be refreshed automatically whenever the data is changed in the underlying tables. The refresh method can be incremental (fast refresh) or complete. Incremental method re-populates only the changed data. Complete method truncates and rebuilds the view. The refresh process can be enabled by adding the REFRESH clause while creating the materialized view. You can suppress the refresh process for the entire life of the view.

Example 1 :

The following statement creates and populates a materialized view SALES_BY_MONTH_BY_STATE. The materialized view will be populated with data as soon as the statement executes successfully, and subsequent refreshes will be accomplished by reexecuting the materialized view's query.

CREATE MATERIALIZED VIEW sales_by_month_by_state
TABLESPACE my_ts
PARALLEL (10)
ENABLE QUERY REWRITE
BUILD IMMEDIATE
REFRESH COMPLETE AS
SELECT t.month, g.state, SUM(sales) AS sum_sales
FROM fact f, time t, geog g
WHERE f.cur_date = t.cur_date
AND f.city_id = g.city_id
GROUP BY month, state;

The fact table stores the actual data about the sales by month and state, time table has the day,month, year dimenstions and geog stores the state names. This view is built and populated with data immediately.

Lets asssume that you asked oracle to calculate the same values in a query and if query_rewrite is allowed on your server, the optimizer will automatically use the values in the above precomputed view.

NOTES:

1. Set QUERY_REWRITE_ENABLE=TRUE in init.ora

2. Set JOB_QUEUE_PROCESSES=1 ( 1 or more based on your requirements).

RESTRICTIONS:

1. Materialized views consume storage space. Be aware to provide storage parameters according to the data it holds.

2. There cannot be any set functions like UNION, MINUS in the underlying query for materialized views.

Syntax for Drop the users:

If user BRADLEY's schema contains no objects, you can drop BRADLEY by issuing the statement:

DROP USER bradley;

If BRADLEY's schema contains objects, you must use the CASCADE clause to drop BRADLEY and the objects:

DROP USER bradley CASCADE;

Partitions

Clustered and Non-clustered Tables

PL/SQL

It Procedural Language, we can execute a block of statements at a time.

Structure of PL/SQL Program:

It contains Three Sections

DECLAR

Declaration of Variable, constraints and cursors;

BEGIN

Execution of SQL and PL/SQL Statements;

EXCEPTION

Exception Handlers;

END;

There are two types of PL/SQL blocs.

1. Anonymous Block: According to Oracle server it any PL/SQL block is not having any specific name is called “Anonymous Block”.

2. Name Block: According to Oracle server it any PL/SQL block is having specific name is called “Name Block”.

Datatypes: What ever datatypes SQL supports, the same datatypes PL/SQL support. And addition it support BOOLEN datatype.

CHAR it is fixed length the range is 32,767.

VARCHAR2 the range is 32,767.

CREATING A FILE: SQL> ED <FILENAME> EX: ED ADDTION

EXECUT THE FILE : SQL> @<FILENAME> EX: @ ADDTION.

DISPLAY THE OUTPUT: SET SERVEROUTPUT ON;

Program for Adding Two Numbers:

DECLARE

A NUMBER :=&A;

B NUMBER :=&B;

C NUMBER;

BEGIN

C:=A+B;

DBMS.OUTPUT.PUT_LINE(‘SUM=’||C);

END;

DBMS.OUTPUT: It is a predefine Package is used to display the output on the screen.

PUT_LINE: It is a procedure defined inside of DBMS.OUTPUT package.

Finding the Length of String?

DECLARE

A NUMBER:=&A;

B NUMBER;

BEGIN

B:=LENGTH(A);

DBMS.OUTPUT.PUT_LINE(‘Length of’|| A ||‘is’|| B);

END;

Find the First and Last dist of String

DECLARE

A VARCHAR2(32767):='&A';

F CHAR;

L CHAR;

BEGIN

F:=SUBSTR(A,1,1);

L:=SUBSTR(A,-1);

DBMS_OUTPUT.PUT_LINE('First Char is'||F);

DBMS_OUTPUT.PUT_LINE('Last Char is'||L);

END;

Flow of PL/SQL Program:

1. SEQUENCIAL.

2. CONDITIONAL/SELECTIVE.

3. LOOPING/ ITERATION.

CONTROL STATEMENTS:

IF STATEMENT:

Syntax: IF CONDITION1 THEN

Statement to Execute;

ELSE

Statement to Execute;

END IF;

IF CONDITION1 THEN

Statement to Execute;

ELSIF CONDITION2 THEN

Statement to Execute;

ELSIF CONDITION3 THEN

Statement to Execute;

ELSE

Statement to Execute;

END IF;

Example:

DECLARE

A NUMBER:=&A;

B NUMBER:=&B;

C NUMBER:=&C;

D NUMBER;

BEGIN

IF A=1 THEN

D:=B+C;

ELSIF A=2 THEN

D:=B-C;

ELSE

DBMS_OUTPUT.PUT_LINE('INVALID OPTION');

END IF;

DBMS_OUTPUT.PUT_LINE('SUM '||D);

END;

Loops: If we want to execute the same statement number of time the we will use loops.

There are three types of loops.

1. Simple Loop: In simple loop if the condition is false then execute the given statement.

Syntax:

LOOP

EXIT WHEN CONDITION;

Statement to Execute;

END LOOP;

Example:

DECLARE

I NUMBER:=1;

BEGIN

LOOP

EXIT WHEN I>5;

DBMS_OUTPUT.PUT(' '||I);

I:=I+1;

END LOOP;

DBMS_OUTPUT.NEW_LINE();

END;

2. While Loop: If the condition is true then it will execute the given statement.

Syntax:

WHILE CONDITION LOOP

Statement to Execute;

END LOOP;

Example:

DECLARE

I NUMBER :=&I;

BEGIN

WHILE I<=5 LOOP

DBMS_OUTPUT.PUT(' '||I);

I:=I+1;

END LOOP;

DBMS_OUTPUT.NEW_LINE();

END;

3. For Loop: Variable using For Loop is not require to DECLARE, No need of Increment and Variable using by the For Loop is Not editable.

Syntax:

FOR Variable in EXPRESSION1..EXPRESSION2 LOOP

Statement to Execute;

END LOOP;

Example:

BEGIN

FOR I IN 1..5 LOOP

DBMS_OUTPUT.PUT(' '||I);

END LOOP;

DBMS_OUTPUT.NEW_LINE();

END;

Print the given String in Reveres Order.

DECLARE

A VARCHAR2(32767):='&A';

--B VARCHAR2(32767)

C CHAR;

BEGIN

FOR I IN 1..LENGTH(A) LOOP

C:=SUBSTR(A,-I,1);

DBMS_OUTPUT.PUT(C);

END LOOP;

DBMS_OUTPUT.NEW_LINE();

END;

Find the given String is Palindrome or Not.

DECLARE

A VARCHAR2(32767):='&A';

B VARCHAR2(32767);

C CHAR;

BEGIN

FOR I IN 1..LENGTH(A) LOOP

C:=SUBSTR(A,-I,1);

B:=B||C;

END LOOP;

IF A=B THEN

DBMS_OUTPUT.PUT_LINE(A||' IS PALINDROME');

ELSE

DBMS_OUTPUT.PUT_LINE(A||' IS NOT PALINDROME');

END IF;

END;

Find the given Number is Amstrong Number or not.

Amstrong No: It sum of cube of all the digits=Amstrong No

Ex: 1³+5³+3³=153.

DECLARE

A NUMBER :=&A;

C NUMBER:=0;

BEGIN

FOR I IN 1..LENGTH(A) LOOP

C:=C+POWER(SUBSTR(A,I,1),3);

END LOOP;

IF A=C THEN

DBMS_OUTPUT.PUT_LINE(A ||' IS AMSTRONG NUMBER');

ELSE

DBMS_OUTPUT.PUT_LINE(A ||' IS NOT AMSTRONG NUMBER');

END IF;

END;

Accessing data from the Tables using PL/SQL:

DECLARE

ENO NUMBER:=&ENO;

N VARCHAR2(32767);

S NUMBER;

J VARCHAR2(32767);

BEGIN

SELECT ENAME,SAL,JOB INTO N,S,J FROM EMP WHERE EMP.EMPNO=ENO;

DBMS_OUTPUT.PUT_LINE('Employee Name is '||N);

DBMS_OUTPUT.PUT_LINE('Employee Salary is '||S);

DBMS_OUTPUT.PUT_LINE('Employee job is '||J);

END;

Exception Handle:

In PL/SQL we have two types of errors one is ‘Syntax Errors’ which are encountered during the Compilation. Second one is ‘Runtime Errors’ which are encountered during the Runtime.

If the PL/SQL block is not compiled successfully, whatever transactions we done in the block, that will Rollback automatically.

Syntax:

Exception

When No_Data_Found Then

DBMS_OUTPUT.PUT_LINE(‘Record Not Existed..’);

Exception is used to handle runtime errors in PL/SQL program. It is two types

1. Predefine Exception

a. Define

b. Un define

2. User Define Exception.

Define Exception: It is the exception which is having Error Number with a specific Name.

Un Define Exception: It is the exception which is having Error Number without having any name.

Predefine Exception are

1. NO_DATA_FOUND

2. TOO_MANY_ROWS

3. DUP_VAL_ON_INDEX

4. VALUE_ERROR

5. INVALID_NUMBER

6. ZERO_DEVIDE

7. CURSOR_ALREADY_OPEN

8. INVALID_CURSOR

9. PROGRAM_ERROR

10. STORAGE_ERROR

11. LOGIN_DEFINE.

Example:

DECLARE

A NUMBER:=&A;

N EMP.ENAME%TYPE;

J EMP.JOB%TYPE;

S NUMBER;

INC NUMBER;

EXCESS_SALARY EXCEPTION;

BEGIN

SELECT ENAME,JOB,SAL INTO N,J,S FROM EMP WHERE EMPNO=A;

DBMS_OUTPUT.PUT_LINE('Employee Old Salary='||S);

IF S>=800 OR S<=1500 THEN

INC:=500;

ELSIF S>1500 OR S<=3000 THEN

INC:=1000;

ELSE

INC:=1000;

END IF;

UPDATE EMP SET SAL=SAL+INC WHERE EMPNO=A;

DBMS_OUTPUT.PUT_LINE('Employee Number='||A);

DBMS_OUTPUT.PUT_LINE('Employee Name='||N);

DBMS_OUTPUT.PUT_LINE('Employee Job='||J);

DBMS_OUTPUT.PUT_LINE('Employee Incremented Salary='||S);

IF S+INC>5500 THEN

RAISE EXCESS_SALARY;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('Employee Number Not Exist');

WHEN EXCESS_SALARY THEN

UPDATE EMP SET SAL=5500 WHERE EMPNO=A;

DBMS_OUTPUT.PUT_LINE('EXCESS SALARY');

END;

CURSOR: It is a work area that can store the executed SQL Statement result data. It is used to handle the multiple records in PL/SQL program. It is two types

1. Implicit Cursor: System can open a cursor for internal processes such type of cursors are called ‘Implicit cursors’.

2. Explicit Cursor: Users can open a cursor for accessing the data, that type of user define cursors are called ‘Explicit Cursors’.

Handle the Cursors: There are three ways to handle the cursors.

1. Open the cursor using ‘open<cursor name>’

2. Fetch data from cursor using ‘fetch <cursor name>’

3. Close the cursor using ‘close <cursor name>’

Attributes of Cursor:

1. %ISOPEN: It returns true if the cursor is open else it returns false

2. %FOUND: It returns true if the record fetch from the workspace else it returns false.

3. %NOTFOUND: It returns true if the record not fetch from the workspace else it returns true.

4. %ROWCOUNT: It returns the count of rows.

Example:

DECLARE

CURSOR C IS SELECT * FROM EMP;

E C%ROWTYPE;

TOT NUMBER:=0;

S NUMBER;

BEGIN

OPEN C;

DBMS_OUTPUT.PUT_LINE(RPAD('EMPNO',15,'-')||RPAD('NAME',20,'-')||RPAD('JOB',15,'-')||'SALARY');

DBMS_OUTPUT.PUT_LINE(RPAD('_',60,'_'));

LOOP

FETCH C INTO E;

EXIT WHEN C%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(RPAD(E.EMPNO,15,'-')||RPAD(E.ENAME,20,'-')||RPAD(E.JOB,15,'-')||E.SAL);

S:=E.SAL;

--DBMS_OUTPUT.PUT_LINE(S);

TOT:=TOT+S;

END LOOP;

DBMS_OUTPUT.PUT_LINE(RPAD('_',60,'_'));

DBMS_OUTPUT.PUT_LINE('Total Number of Rows='||C%rowcount);

DBMS_OUTPUT.PUT_LINE('Total Salary='||TOT);

DBMS_OUTPUT.PUT_LINE('Average Salary='||ROUND(TOT/C%ROWCOUNT,2));

CLOSE C;

END;

Using For Loop:

DECLARE

CURSOR C IS SELECT * FROM EMP;

E C%ROWTYPE;

TOT NUMBER:=0;

CTR NUMBER:=0;

BEGIN

DBMS_OUTPUT.PUT_LINE(RPAD('EMPNO',15,'-')||RPAD('NAME',20,'-')||RPAD('JOB',15,'-')||'SALARY');

DBMS_OUTPUT.PUT_LINE(RPAD('_',60,'_'));

FOR E IN C LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(E.EMPNO,15,'-')||RPAD(E.ENAME,20,'-')||RPAD(E.JOB,15,'-')||E.SAL);

TOT:=TOT+NVL(E.SAL,0);

CTR:=C%ROWCOUNT;

END LOOP;

DBMS_OUTPUT.PUT_LINE(RPAD('_',60,'_'));

DBMS_OUTPUT.PUT_LINE('Total Number of Rows='||CTR);

DBMS_OUTPUT.PUT_LINE('Total Salary='||TOT);

DBMS_OUTPUT.PUT_LINE('Average Salary='||ROUND(TOT/CTR,2));

END;

PARAMETRIC CURSOR:

DECLARE

A NUMBER:=&DEPTNO;

CURSOR C(DNO NUMBER) IS SELECT * FROM EMP WHERE DEPTNO=DNO;

E C%ROWTYPE;

TOT NUMBER:=0;

CTR NUMBER;

BEGIN

OPEN C(A);

DBMS_OUTPUT.PUT_LINE(RPAD('EMPNO',15,' ')||RPAD('NAME',20,' ')||RPAD('JOB',15,' ')||'SALARY');

DBMS_OUTPUT.PUT_LINE(RPAD('_',60,'_'));

IF C%NOTFOUND THEN

RAISE_APPLICATION_ERROR(-20000,'Record Not Found');

END IF;

CLOSE C;

FOR E IN C(A) LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(E.EMPNO,15,' ')||RPAD(E.ENAME,20,' ')||RPAD(E.JOB,15,' ')||E.SAL);

TOT:=TOT+NVL(E.SAL,0);

CTR:=C%ROWCOUNT;

END LOOP;

DBMS_OUTPUT.PUT_LINE(LPAD('_',60,'_'));

DBMS_OUTPUT.PUT_LINE('Number of Rows='||CTR);

DBMS_OUTPUT.PUT_LINE('Totla Salary='||TOT);

DBMS_OUTPUT.PUT_LINE('Average Salary='||ROUND(TOT/CTR,2));

END;

PROCEDURES & FUNCTIOS:

Procedure: A procedure is a group of PL/SQL statements that you can call by name. It may or may not return a value.

Syntax:

CREATE OR REPLACE PROCEDURE <PROCEDURE NAME>(parameter datatype) IS/AS

LOCAL DECLARATION;

BEGIN

EXECUTED STATEMENTS;

EXCEPTION

EXCEPTION HANDLER;

END;

Execution of Procedure: EXEC <Procedure Name> (Input values);

Procedure divided into 2 parts

1. Procedure Specifications: Before IS/AS is called procedure specification.

2. Procedure Body: After IS/AS is call body.

Once the procedure is created successfully it will crate object code that will store in the server. This object code is called Pseudo Code.

There are three types of Parameters in Procedure. IN, OUT, INOUT.

IN parameter is readable but not writable. It’s constant.

OUT parameter is writable not readable.

INOUT parameter is both readable and writable.

CREATE OR REPLACE PROCEDURE EMP_DTLS(ENO IN NUMBER,NAME OUT VARCHAR2,SALARY OUT NUMBER,D_NAME OUT VARCHAR2) IS

BEGIN

SELECT E.ENAME,E.SAL,D.DNAME INTO NAME,SALARY,D_NAME FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO AND EMPNO=ENO;

DBMS_OUTPUT.PUT_LINE(RPAD('NAME',15,' ')||RPAD('SALARY',15,' ')||'Department');

DBMS_OUTPUT.PUT_LINE(LPAD('-',50,'-'));

DBMS_OUTPUT.PUT_LINE(RPAD(NAME,15,' ')||RPAD(SALARY,15,' ')||D_NAME);

END;

DECLAR VARIABLES;

SQL> VAR N VARCHAR2(30);

SQL> VAR S NUMBER;

SQL> VAR D VARCHAR2(30);

SQL> EXEC EMP_DTLS(7369,:N,:S,:D)

Functions: It should return a single value the advantage of function is used to Reusability, Readable:-Decreasing the coding.

Modularity: - Dividing the program into number of modules

Easy to Maintain.

Syntax: CREATE OR REPLACE FUNCTION <function name>( Parameter Name Datatype) RETURN Datatype IS/AS

DECLARATION STATEMENTS;

BEGIN

EXECTION STATEMENTS;

EXCEPTION

EXCEPTION HANDLERS;

END;

EXAMPLES:

CREATE OR REPLACE FUNCTION PRIM(A NUMBER) RETURN VARCHAR2 IS

BEGIN

FOR I IN 2..A-1 LOOP

IF MOD(A,I)=0 THEN

RETURN 'IT IS NOT PRIM NUMBER';

EXIT;

END IF;

END LOOP;

RETURN 'IT IS PRIM NUMBER';

END;

EXEC: SELECT PRIM(23) FROM DUAL;

Finding the Employee Experience in EMP TABLE:

CREATE OR REPLACE FUNCTION EXPE(A NUMBER) RETURN NUMBER IS

X NUMBER;

BEGIN

SELECT ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12,0) INTO X FROM EMP WHERE EMPNO=A;

RETURN X;

END;

EXEC: SELECT EXPE(EMPNO) FROM EMP;

SELECT * FROM EMP WHERE EXPE(EMPNO)=23;

Finding the Month from the Given Date:

CREATE OR REPLACE FUNCTION MTH(A DATE) RETURN NUMBER IS

BEGIN

RETURN TO_CHAR(A,'MM');

END;

EXEC: SELECT MTH(SYSDATE) FROM DUAL;

SELECT MTH(HIREDATE) FROM DUAL;

SELECT * FROM EMP WHERE MTH(HIREDATE)=3;