Posts Tagged ‘oracle’

« Older Entries

Oracle – Frequently Asked Questions

Wednesday, April 29th, 2009

1. What is ORACLE?

Oracle is a Relational Database Management System (RDBMS) which is used widely for business applications.

 2. What is SGA?

The System Global Area (SGA) is a shared memory region allocated by ORACLE that contains data and control information for one ORACLE instance.

 3. What is the function of Checkpoint (CKPT)?

The Checkpoint (CKPT) process is responsible for signaling DBWR at checkpoints and updating all the data files and control files of the database.

The CKPT is also useful to get the point in time from where to begin the recovery in case of failure.

 Which background process is responsible for writing the dirty buffers from the write list to the data files during a checkpoint?

The DBWn is usually responsible for this.

 What are the components of SGA? 

The components of the SGA are Database buffers, Redo Log Buffer and the Shared Pool.

  4. What constitute an ORACLE Instance?

SGA and ORACLE background processes together constitute an ORACLE instance.

 What do Database Buffers contain?

Database Buffers store the most recently used blocks of database data. They can also contain modified data that has not yet been permanently written to disk.

 5. What do Redo Log Buffers contain?

Redo Log Buffers store redo entries or a log of changes made to the database.

  6. What is Shared Pool?

Shared pool is composed of two parts; Library Cache and Data Dictionary Cache.

The Library Cache contains the shared SQL areas, private SQL areas, PL/SQL procedures and packages, and control structures such as locks and Library Cache handles. The shared SQL area contains the parse tree and execution plan; whereas the private SQL area contains values for bind variables and runtime buffers. The data dictionary cache holds most recently used database dictionary information.

 

 7. What is the default undo management mode in ORACLE?

The default undo management mode is manual.

 8. How can you dynamically size the SGA?

You can do this using the Alter System command.

  9. What is the Multiple Block size?

We can assign different block sizes for each table space. This is called the Multiple Block size.

 

 10. Where are the compiled functions and procedures stored in memory?

These are stored in the Library Cache.

  11. What is SQL*PLUS?

It is a browser-based interface to communicate with the database.

  12. What are the advantages of creating clusters in ORACLE?

It improves the efficiency of join queries and they are storage efficient.

  13. What are Bind Variables?

A bind variable is a variable that you declare in a host environment. Bind variables can be used to pass run-time values, either number or character, into or out of one or more PL/SQL programs. The PL/SQL programs use bind variables as they would use any other variable. You can reference variables declared in the host or calling environment in PL/SQL statements, unless the statement is in a procedure, function, or package. This includes host language variables declared in precompiler programs, screen fields in Oracle Developer Forms applications, and iSQL*Plus bind variables.

  14. How to create Bind Variables?

To declare a bind variable in the iSQL*Plus environment, use the command VARIABLE. For example, you declare a variable of type NUMBER and VARCHAR2 as follows:

VARIABLE return_code NUMBER

VARIABLE return_msg  VARCHAR2(30)

Both SQL and iSQL*Plus can reference the bind variable, and iSQL*Plus can display its value through the iSQL*Plus PRINT command.

  15. Explain few programming guidelines for PL/SQL ?

To produce clear code and reduce maintenance when developing a PL/SQL block follow the below listed guidelines

-              Documenting code with comments

-              Developing a case convention for the code

-              Developing naming conventions for identifiers and other objects

-              Enhancing readability by indenting

 16. What are the advantages of using cursors with FOR LOOPS?

A cursor FOR loop is a shortcut because of the following reasons

-              the cursor is opened automatically when the for loop is encountered for the first time and fetches the first row

-              rows are fetched once for each iteration in the loop

-              The loop itself is terminated automatically at the end of the iteration where the last row is fetched.

  17. What is the use of NO WAIT option with SELECT…. FOR UPDATE clause?

The optional NOWAIT keyword tells Oracle not to wait if requested rows have been locked by another user. Control is immediately returned to your program so that it can do other work before trying again to acquire the lock. If you omit the NOWAIT keyword , Oracle waits until the rows are available.

 18. Can we use raise system defined exceptions  explicitly by issuing the RAISE statement?

Yes, you can. For example : RAISE NO_DATA_FOUND

  19. Can we handle the exception raised by RAISE_APPLICATION_ERROR?

Yes. For example, you can write “WHEN OTHERS” exception handler, and handler for this.

 20. Can we assign default values to all modes of parameters in a subprogram?

You can assign default values only to parameters of the IN mode. OUT and IN OUT parameters are not permitted to have default values.

  21. How Procedures are different from Functions?

You create a procedure to store a series of actions for later execution. A procedure can contain zero or more parameters that can be transferred to and from the calling environment, but a procedure does not have to return a value.

You create a function when you want to compute a value, which must be returned to the calling environment. A function can contain zero or more parameters that are transferred from the calling environment. Functions should return only a single value, and the value is returned through a RETURN statement. Functions used in SQL statements cannot have OUT or IN OUT mode parameters.

  22. What is the use of SHOW ERRORS command?

SHOW ERRORS command is used  at the SQL prompt to obtain compilation errors for the last object you compiled.

You can also use the command with a specific program unit. The syntax is as follows:

SHOW ERRORS [{FUNCTION|PROCEDURE|PACKAGE|PACKAGE BODY|TRIGGER|VIEW} [schema.]name]

Example : SHOW ERRORS PROCEDURE p1

Using the SHOW ERRORS command, you can view only the compilation errors that are generated by the latest statement that is used to create a subprogram. The USER_ERRORS data dictionary view stores all the compilation errors generated previously while creating subprograms.

  23. How to drop one member of a package?

You cannot drop members of a package using DROP command. When you drop the package, all the members of the package are automatically dropped. To drop or add any members of the package, rewrite the whole package again.

  24. What will happen to the dependent constructs of a package ,if we change the package specification?

Changes to the package body do not require recompilation of dependent constructs, whereas changes to the package specification require recompilation of every stored subprogram that references the package. To reduce the need for recompiling when code is changed, place as few constructs as possible in a package specification.

  25. Explain the firing sequence for a trigger on a table.

The sequence is as below :

a.            Before Statement Trigger

b.            Before Row Trigger

c.             After row trigger

d.            After statement Trigger

  26. Can we write INSTEAD OF triggers on tables?

No, it is written only on views

  27. What will happen to triggers when the table on which the trigger is written?

All triggers on a table are automatically dropped when the  table is dropped.

 

 

 

Tags: , , , , , , ,
Posted in Technical | No Comments »

NTLM Authentication

Tuesday, March 24th, 2009

Introduction:

This BOK details how to get a Java-based web-application to negotiate with a IE web client for username and domain information. This is a common requirement for web-based applications especially ones that do not want to bore users with a login page. IE will negotiate a user’s password hashes with the webserver, which checks their authenticity against a windows domain controller. If valid, the user’s username and domain will be accessible to the webserver servlets.

NTLM Authentication and how we achieve it:

 
The method HttpServletRequest.getRemoteUser() should return the username of the person using the browser which fired a request to this Servlet.
This method, however works correctly only if the user has been authenticated first by a webserver authentication scheme -
which could be BASIC,DIGEST or CLIENT-CERT. This is the kind of setup the the Apache webserver provides, giving a challenge-response, username-password method of authentication.

What we do here is use a Servlet filter provided as part of the open-source jCIFS package, to get an IE user's username and domain. 

This filter will take the trouble of intercepting user requests, asking IE for the user's password hashes,validating them against a windows domain controller and enabling HttpServletRequest.getRemoteUser() to return the windows user id.
 
Please note this method will not work for non-IE clients, simply because this is a proprietary extension by Microsoft. 
 
For other browsers you will have to rely on BASIC or certificate-based authentication.

How to setup your web application:

 First, we need to download a jcifs jar from http://jcifs.samba.org. I have tested this with jcifs version 0.7.14.jCIFS is from the makers of Samba and provides APIs to access Windows shares, networks and the ability to authenticate against a Windows domain controller. Place this jar under WEB-INF/lib of your web application. There is a filter called jcifs.http.NtlmHttpFilter which implements all the wizadry above. You need to register it in your application's web.xml descriptor:
 
<web-app>
...
    <!-- NTLM HTTP Authentication only works with MSIE -->
    
    <filter>
        <filter-name>NTLM HTTP Authentication Filter</filter-name>
        <filter-class>jcifs.http.NtlmHttpFilter</filter-class>

        <!-- CCD will help you with a PDC and WINS server ip at your location. -->
        <init-param>
            <param-name>jcifs.http.domainController</param-name>
            <param-value>192.168.170.5</param-value>
        </init-param>

        <init-param>
            <param-name>jcifs.netbios.wins</param-name>
            <param-value>192.168.166.13</param-value>
        </init-param>
    </filter>

    <!-- This is the url under which we need access to the username and domain. -->

    <filter-mapping>
        <filter-name>NTLM HTTP Authentication Filter</filter-name>
        <url-pattern>/*</url-pattern>
    </filter-mapping>

...

</web-app>
 
That's it. Now all IE requests to your webserver urls as specified in the web.xml entries are negotiated so that you can call a HttpServletRequest.getRemoteUser() to get the remote user's username in the form.

            DOMAIN\username.

Please note at no point will a password dialog pop up for the user, the password hashes are picked from IE and validated with the domain controller.
 
 
Example code for a servlet :
 
 
public void doGet( HttpServletRequest req,
                        HttpServletResponse resp )
throws IOException, ServletException
        
        {

PrintWriter out = resp.getWriter();

resp.setContentType( “text/html” );
out.println( “<HTML><HEAD><TITLE>NTLM HTTP Authentication Example</TITLE></HEAD><BODY>” );
out.println( “<h2>NTLM HTTP Authentication Example</h2>” );

out.println( req.getRemoteUser() + ” logged in” );

 

}

If the filter has not been configured properly, a null will be printed for the above call to req.getRemoteUser().

 

References:

  • Web Link :- http://jcifs.samba.org.

Tags: , , , , , , , , , , , ,
Posted in Technical | No Comments »

Jsp – Oracle Connectivity

Monday, March 23rd, 2009

Introduction

This document deals with the JSP connectivity with Oracle.This also exemplifies adding,updating, deleting and retrieving records from database(Oracle) through JSP.

Prerequisites

  • Tomcat Server.
  • Oracle Client.
  • Oracle Server.

Target Readers : All

Getting Started

Copy a jar file class12.jar from Oralce_root->jdbc->lib->class12.jar
to Tomcat Server in Tomcat_root->common->lib.

Database Connectivity

<%
try{
Class.forName(”oracle.jdbc.driver.OracleDriver”).newInstance();

//Creating the connection object
Connection con = DriverManager.getConnection(”jdbc:oracle:thin:” + “@172.24.205.62:1521:infosys”,”Uname”,”Pwd”);

con.close();
}
catch (SQLException sqle){
throw new SQLException();
}
%>

Note:

172.24.205.62 -> IP Address of Oracle Server.
1521 -> Port Number.
infosys -> Host String.
Uname -> UserName.
Pwd -> Password.

Adding a Record

<%
try{
Class.forName(”oracle.jdbc.driver.OracleDriver”).newInstance();

//Creating the connection object
Connection con = DriverManager.getConnection(”jdbc:oracle:thin:” + “@172.24.205.62:1521:infosys”,”Uname”,”Pwd”);

PreparedStatement stmt = con.prepareStatement(”insert into Table_Name values(?,?,?)”);
stmt.setString(1,column1);
stmt.setInt(2,column2);
stmt.setString(3,column3);

stmt.executeUpdate();

con.close();
}
catch (SQLException sqle){
throw new SQLException();
}
%>

Where column1,column2,column3 can be static values or can be taken from the previous page by establishing session.

Deleting a Record

<%
try{
Class.forName(”oracle.jdbc.driver.OracleDriver”).newInstance();

//Creating the connection object
Connection con = DriverManager.getConnection(”jdbc:oracle:thin:” + “@172.24.205.62:1521:infosys”,”Uname”,”Pwd”);

PreparedStatement stmt = con.prepareStatement(”delete from Table_Name where EmpName=’XYZ’”);
stmt.executeUpdate();

con.close();
}
catch (SQLException sqle){
throw new SQLException();
}
%>

Updating a Record

<%
try{
Class.forName(”oracle.jdbc.driver.OracleDriver”).newInstance();

//Creating the connection object
Connection con = DriverManager.getConnection(”jdbc:oracle:thin:” + “@172.24.205.62:1521:infosys”,”Uname”,”Pwd”);

PreparedStatement stmt = con.prepareStatement(”update Table_Name set Designation=’Software Engineer’ where EmpName=’XYZ’”);
stmt.executeUpdate();

con.close();
}
catch (SQLException sqle){
throw new SQLException();
}
%>

Retrieving a Record

<%
String empName=”";
int empNumber=0;
String empDesignation=”";

try{
Class.forName(”oracle.jdbc.driver.OracleDriver”).newInstance();

//Creating the connection object
Connection con = DriverManager.getConnection(”jdbc:oracle:thin:” + “@172.24.205.62:1521:infosys”,”Uname”,”Pwd”);

PreparedStatement stmt = con.prepareStatement(”select * from Table_Name”);
ResultSet rs = stmt.executeQuery();

while(rs.next())
{
empName = rs.getString(”EMPNAME”);
empNumber = rs.getInt(”EMPNO”);
empDesignation = rs.getString(”DESIGNATION”);

out.println(empName);
out.println(empNumber);
out.println(empDesignation);
}

con.close();
}
catch (SQLException sqle){
throw new SQLException();
}
%>

Tags: , , , , ,
Posted in Technical | No Comments »

Intro to Juniper Routers

Monday, March 23rd, 2009

1.   INTRODUCTION TO JUNIPER

    ROUTERS

 

Juniper Networks Router Design

 

The central design principle of the Juniper Networks platform centers on a separation of the control and forwarding planes within the router. The Routing Engine and the Packet Forwarding Engine, respectively, represent these planes.

 

 

 

Routing Engine Overview

 

The Routing Engine in a Juniper Networks router is the central location for control of the system responsible for:

 

•     Storing the JUNOS software & Performing software upgrades

 

•     Monitoring and configuring the router.

 

•     Troubleshooting tools like Telnet, ping, or traceroute

 

•     Operating all routing protocols and making all routing table decisions, building a master routing table with the best path to each destination selected.

 

•     Placing the best paths into the forwarding table on the Routing Engine and copying that same data into the forwarding table on the Packet Forwarding Engine.

 

Packet Forwarding Engine Overview

 

The Packet Forwarding Engine is the central location

responsible for:

 

•     Data packet forwarding through the router.

 

•     Controlling the router’s throughput speed and capacity by the specially designed hardware.

 

•     Forwarding of data packets across any interface in the router.

 

The main portions of the Packet Forwarding Engine are the Physical Interface Card (PIC), the Flexible PIC Concentrator (FPIC), and a switching control board.

 

Physical Interface Card

 

The physical media in your network connects to the

Physical Interface Card (PIC) in the router.

 

Up to four individual PICs are contained on an FPC. A media-specific ASIC is located on each PIC.

 

Flexible PIC Concentrator

 

The Flexible PIC Concentrator (FPC) connects to both the switching control board and the router’s interfaces within the Packet Forwarding Engine. 

 

Command-Line Interface

 

The JUNOS software CLI contains two main modes:

 

1. Operational and

2. Configuration.

 

Operational Mode

Operational mode displays the current router status, and used for verifying and troubleshooting the router. We enter operational mode on the router after a successful login attempt. 

 

user@Juniper>

 

The default prompt for the JUNOS software is a combination of our username and the router hostname. 

In addition, the > character tells that we are in operational mode.

As with most router operating systems, the JUNOS software uses a command hierarchy paradigm within operational mode.

 

Configuration Mode

 

Configuration mode provides with a method for altering the current environment.

 

We access the router’s configuration mode hierarchy with either the configure or edit command:

 

user@Juniper> edit or configure

 

Entering configuration mode

 

[edit]

 

user@Juniper#

 

The # character tells that we are in operational mode.

and our current level in the hierarchy is displayed above the router’s hostname. The [edit] portion of the output on Juniper tells us that we are at the top of the configuration hierarchy.

 

Context-Sensitive Help

The question mark (?) character gives context-sensitive help to navigate the command hierarchy.

 

We often use the help function at a specific hierarchy level, but it also provides assistance in locating specific options within a particular level. 

 

For example, let’s locate the possible commands starting with the letter i within the show hierarchy:

 

user@Junieper> show i?

 

Possible completions:

 

igmp Show information about IGMP

ike Show IKE information

ilmi Show ILMI information

interfaces Show interface information

 

 

Command Completion

 

The JUNOS software CLI provides with a command completion function. Each unique combination of characters at a particular hierarchy level expands into the full command when we use either the spacebar or the Tab key.

 

user@Juniper> sh<space>ow

 

user@Juniper> sh<space>ow c<tab>

^

‘c’ is ambiguous.

Possible completions:

chassis Show chassis information

user@Juniper> show c

 

The router returns an error message telling us that there are multiple commands in the show hierarchy that start with the letter c. The output informs that ‘c’ is ambiguous and displays the possible commands that begin with the requested letter.

 

 

Getting Help from the Router

 

The jdocs Software Component package contains the entire JUNOS software documentation set on the router and is accessed through the user CLI. 

 

We can find conceptual information on network topics by using the help topic command. 

 

user@Juniper>help topic ospf area-backbone

 

Configure the Backbone Area

You must create a backbone area if your network consists of multiple areas. An ABR must have at least one interface in the backbone area, or it must have a virtual link to a router in the backbone area. The backbone comprises all area border routers and all routers that are not included in any other area. You configure all these routers by including the following area statement at the [edit protocolsospf] hierarchy level (for routing instances, include the statement at the [edit routing-instances routing-instance-name protocols ospf] hierarchy level):

 

[edit protocols ospf]

area 0.0.0.0;

 

 

Getting Help from the Router

 

When we are ready to configure our router to support an OSPF area, we can view specific configuration information using the help reference command:

 

user@Juniper> help reference ospf area

area

Syntax

area area-id;

Hierarchy Level

[edit protocols ospf],

[edit routing-instances routing-instance-name protocols ospf]

Description

Specify the area identifier for this router to use when participating in OSPF routing. All routers in an area must use the same area identifier to establish adjacencies.

 

Specify multiple area statements to configure the router as an area border router. An area border router automatically summarizes routes between areas; use the area-range statement to configure route summarization. By definition, an area border router must be connected to the backbone area either through a physical link or through a virtual link. To create a virtual link, use the virtual-link statement.

 

Using the run Command

 

One very useful command that exists in configuration mode is run. When we use this command, the router allows us access to operational mode commands from within the configuration mode. 

 

This flexibility enables us to easily verify information on the router.

 

user@Juniper# run show interfaces

Physical interface: so-0/0/0, Enabled, Physical link is Up

Interface index: 11, SNMP ifIndex: 13

Description: Sydney to Sao Paulo

Link-level type: PPP, MTU: 4474, Clocking: Internal, SONET mode,

Speed: OC3, Loopback: None, FCS: 16, Payload scrambler: Enabled

Device flags : Present Running

 

 

Using the Pipe through a command

 

display This option allows the router to show you additional data associated with the command.

 

except This option allows you to omit any line in the output containing the text string we provide.

 

find This option prompts the router to begin the output at the first occurrence of the text string we provide.

 

match This option prompts the router to display only lines in the output containing the text string we provide.

user@Juniper> show interfaces terse | match inet

fe-0/0/1.0 up up inet 10.0.31.1/24

 

set This option used in conjunction with display through two consecutive pipe through to display only lines in output containing text string which have been configured using set command

 

user@Juniper> show interfaces terse | match at-0/2/0 | display set

set interfaces at-0/2/0 unit 100 family inet address 10.0.1.1/24

 

 

Altering the Configuration

 

We enter new information into the configuration with the set command:

 

edit]

user@router# edit system

[edit system]

user@router# set host-name Juniper

 

The router now has a hostname of Juniper instead of router. The host-name variable is actually in the [edit system] hierarchy directory. We used the edit command to move into that directory and then configured the hostname. We can enter multiple directory names between the variable and use the set command as long as the directories are in a direct downward line. We move back to the top of the hierarchy using top command and change the hostname to Shiraz:

 

[edit system]

user@router# top

[edit]

user@router# set system host-name Shiraz

 

Using the set Command

 

If we begin configuring the router in the [edit system] directory. The possible options at that hierarchy level are:

 

[edit system]

user@router# set ?

Possible completions:

+ apply-groups Groups from which to inherit configuration data

+ authentication-order Order in which authentication methods are invoked

> backup-router IPv4 router to use while booting

Compress-configuration-files compress the router configuration files

 

When we examine the output closely, we might notice that some command options are preceded with a character—either an angle bracket (>) or a plus sign (+). The angle bracket is used to designate lower-level directories. The plus sign shows command variables you can configure that may have multiple values assigned. 

Finally, some options do not have any characters preceding

them. These are configurable variables that may contain only a single possible value.

 

 

Altering the Configuration

 

We can view the changes we’ve made to the configuration by issuing the show command. This command displays any configuration in your current directory and all subdirectories below our current location. Using this command at the top of the hierarchy displays the entire configuration:

 

[edit]

user@router# show

version 5.3R1.2;

system {

host-name Shiraz;

 

To view the configuration just within the [edit system] directory, we may either move to that level with the edit command or add the hierarchy name to the show command from the top of the configuration:

 

[edit]

user@router# show system

host-name Shiraz;

 

 

Using the delete Command

 

We remove variables from the configuration with the delete command.

 

[edit]

user@router# delete system radius-server 172.30.10.1

[edit]

user@router# show system

host-name Shiraz;

root-authentication {

 

The Candidate Configuration

 

We’ve been changing the hostname of the router but that the router’s prompt hasn’t changed. This is because when we enter configuration mode, we are actually viewing (and changing) a file called the candidate configuration. The candidate configuration allows us to make configuration changes without causing operational changes to the current operating configuration, called the active configuration. 

 

The router implements the changes in the candidate configuration when we use the commit command.

 

 

Using the compare Command

 

We may enter or exit configuration mode as many times as we wish without implementing our changes. If we do this several times, we may forget the exact changes we’ve made. In this situation, you can utilize a pipe command called compare in conjunction with the show command. 

 

This prompts the router to compare the current candidate configuration to the active configuration running on the router. Differences between the two files are displayed with either a plus (+) or a minus (-) sign. The plus sign represents variables in the candidate configuration that are not present in the active configuration; we’ve added them to the file. The minus sign shows the opposite; we’ve deleted variables from the file. Let’s use this command on our router to see the difference between the candidate and active configurations:

 

[edit]

user@router# show | compare

[edit system]

- host-name router;

+ host-name Shiraz;

 

 

Using the commit Command

 

No changes we make to the router become effective until we use this command, Each time we commit our configuration, the router performs several tasks. The candidate configuration is examined for syntax and semantic problems and if any single problem exists,the candidate is not implemented.

 

If the candidate configuration possesses no errors, the router then implements the new configuration and makes changes to the operating environment as needed. Finally, the existing active configuration is saved on the router for future use.

 

[edit]

user@router# commit

commit complete

[edit]

user@Shiraz#

 

The commit complete message tells us that the process was successful. The commit process always implements the entire configuration at once. Any errors encountered during

a commit procedure result in no portion of the configuration changing.

 

 

Using the commit Command

 

Suppose that there was an error in the configuration the router does not implement the changes we made and supplies an error message informing us of the problem:

 

[edit]

user@router# commit

Policy error: Policy Advertise-Routes referenced but not defined

error: configuration check-out failed

[edit]

user@router#

 

In addition to the configuration check-out failed message, we see that the router’s hostname did not change. It appears that a policy called Advertise-Routes was referenced in configuration without ever being created in the first place.

 

[edit]

user@router# delete protocols ospf export Advertise-Routes

[edit]

user@router# commit

commit complete

[edit]

user@Shiraz#

 

Using the commit Command

 

The commit command has several options we may use to alter its operation. 

 

[edit]

user@Shiraz# commit ?

Possible completions:

<[Enter]>   Execute this command

and-quit      Quit configuration mode if commit succeeds

                  at Time at which to activate the configuration  

                  changes

check          Check only, do not apply changes

confirmed    Automatically rollback if not confirmed

synchronize Synchronize commit on both routing engines

| Pipe through a command

[edit]

user@Shiraz# commit

 

The router always remains in configuration mode, by default, after committing the configuration. We may exit back to operational mode with the addition of the and-quit option.

 

Using the commit Command

 

We can have the router verify the validity of the configuration without implementing the changes by using the check option. We might use this option after making a number of changes to the router and we want to be sure you have all of the required portions of the configuration in place. After running the syntax and semantic checks, the router does not implement the changes. We’re either notified of a successful check or your errors are reported to you:

 

[edit]

user@Shiraz# commit check

configuration check succeeds

[edit]

user@Shiraz#

 

The syntax and semantic checks the router performs verify only that information is present in the configuration that allows the router to implement the candidate file. No verification is ever completed to see if the configuration actually does what you wanted it to do in the network; that is our job.

 

Restoring an Old Configuration

 

When the router commits a configuration, it also saves the existing configuration to a file. This single file is not the only old configuration file saved, however. The JUNOS software saves up to nine previous configuration files for our use. The current active configuration is named junper.conf and is file number 0. The most recent active configuration is called juniper.conf.1.gz and is file number 1. 

 

We place one of these files into the candidate configuration with the rollback command. To actually implement the old configuration file, we must still issue the commit command to make the candidate configuration

the new active configuration.

 

[edit]

user@Shiraz# rollback 1

load complete

[edit]

user@Shiraz# commit

commit complete

[edit]

user@Shiraz#

 

JUNOS Software Routing Tables

 

The JUNOS software provides multiple routing tables that are used to store routes for our network. Each table is represented within the output of the show route command. The software provides default tables that the operating system builds on an as-needed basis. 

 

These tables include the following:

 inet.0

 inet.1

 inet.2

 inet.3

 inet.4

 inet6.0

 mpls.0

 bgp.l3vpn.0

 bgp.l2vpn.0

 routing-instance.inet.0

 

Each of the default tables contains separate route information.

 

JUNOS Software Routing Table

 

Table inet.0

 

Used to store IPv4 unicast routes.

 

Table inet.1

 

Used to store IPv4 multicast routes.

 

Table inet.2

 

Used to store IPv4 unicast routes used by multicast routing protocols to prevent routing loops. This process is called the Reverse Path Forwarding 

 

Table inet.3

 

Contains the egress IP address of a MPLS label switched path (LSP).

 

Table inet.4

 

Stores information learned using the Multicast Source Discovery Protocol

 

 

JUNOS Software Routing Table

 

Table mpls.0

 

Actually not routing but is instead a switching table storing MPLS label

 

Table routing-instance.inet.0

 

Used to store MPLS VPN routes.

 

Table bgp.l3vpn.0

 

Stores routing information in a Layer 3 virtual private network

 

Table bgp.l2vpn.0

 

Stores routing information in a Layer 2 VPN environment.

 

 

Table inet6.0

 

Routing table contains IPv6 unicast routes.

 

JUNOS Software Preference Values

 

Each route in the routing table is assigned a protocol preference value. These values assist the table in selecting the active route when an individual prefix is installed from multiple sources. The preference value informs the routing table which protocols are more believable, with a lower value preferred. The valid value range is between 0 and 4,294,967,295 (2^32 -1).

 

JUNOS Software Preference Values

 

 

 

 

6.           JUNIPER ROUTER PHYSICAL 

    INTERFACS

 

Types of Interfaces

 

A Juniper Networks platform contains two types of

interfaces. Permanent interfaces are always present in each router, while Transient interfaces are inserted in or removed from the router by a user.

 

Permanent Interfaces

 

The permanent interfaces on a Juniper Networks platform perform two vital roles—management and operation. The management functionality is performed primarily by theFxp0 interface. This Management Ethernet interface provides us with an out-of-band method for connecting to the router. 

 

The fxp0 interface on a Juniper Networks router does not provide forwarding capabilities for transit data packets. 

 

The fxp1 interface connects the Routing Engine to the Packet Forwarding Engine. This communications link is how routing protocol packets reach the Routing Engine to update the routing table. The forwarding table updates reach the Packet Forwarding Engine across this interface as well.

 

 

Types of Interfaces

 

Transient Interfaces

 

Transient interfaces receive a user’s data packet and then transmit that packet toward the final destination. 

 

These interfaces are physically located on a Physical Interface Card (PIC) and can be inserted and removed from the router at any time. This property gives them their transient nature.

 

We must configure each transient interface before using it for operational purposes. In addition, the JUNOS software allows to configure transient interfaces that are not currently in the physical chassis. As the software activates the router’s configuration, it detects which interfaces are actually present and activates only those transient interfaces. 

 

Should we install new physical interfaces in the router (for which some configuration exists), the JUNOS software activates the parameters for that transient interface.

 

Interface Naming

 

A router’s interfaces are located on a PIC. The PIC is located on a particular Flexible PIC Concentrator (FPC), which is inserted in a router’s chassis. 

 

Interface Naming Structure

 

The JUNOS software follows a consistent naming structure of

media_type-fpc/pic/port.unit

The portions of the interface names include the following:

media_type

A two-character designator that uniquely identifies the type of physical interface

fpc

The physical slot in the chassis where the interface is located

pic

The slot on the FPC that contains the interface

port

The location on the PIC where the interface port is located

unit

The logical portion of the interface that contains properties, such as an IP address

 

 

Media Types

 

The media type portion of the interface name allows the JUNOS software to identify each physical interface. The two-letter representation relates closely to the actual type of interface used. 

 

ae

Aggregated Ethernet interface

as

Aggregated SONET/SDH interface

at

Asynchronous Transfer Mode (ATM) interface

ds

DS0 interface (including Multichannelized DS-3 interfaces)

e1

E1 interface (including Channelized STM-1 to E1 interfaces)

e3

E3 interface

es

Encryption interface

fe

Fast Ethernet interface

 

 

Media Types

 

fxp

Management and Internal Ethernet interfaces

ge

Gigabit Ethernet interface

gr

Generic Route Encapsulation tunnel interface

ip

IP-over-IP encapsulation tunnel interface

lo

Loopback interface

so

SONET/SDH interface

t1

T1 interface (including Channelized DS-3)

t3

T3 interface (including Channelized OC-12 interfaces)

 

FPC Slot Numbers

 

The FPC slots in a Juniper Networks router begin at 0. Each router model contains a specific number of slots that range from 1 to 8. The slot number is printed directly on the router chassis. Figure below shows the FPC slots on the M40, M40e, M160, T320, and T640 platforms. These are numbered 0 through 7 in a left-to-right fashion.

 

FPC Slot Numbers

 

Four-slot chassis

 

 

 FPC Slot Numbers

 

The remaining router platforms, M5 and M10, share the same chassis platform, with each model supporting a different number of slots. The M5 has a single slot, numbered 0, while theM10 has two slots, numbered 0 and 1.

 

M5 and M10 chassis platforms

 

 

 

 

 

PIC Slot Numbers

 

PIC slot numbers also begin at 0 and have a maximum value of 3. They are physically printed on the FPC and represent the location of the PIC on the FPC module. The numbering schemefollows the physical layout of the Juniper Networks platforms. The vertical FPC slots use the same numbering, while the horizontal slots use a different one.

 

PIC slot numbering

 

 

PIC Port Numbers

 

The physical media cable in your network (for example, Ethernet or SONET) actually connects to a port on the PIC. These ports are also numbered and represent a portion of the interface naming structure. The number of ports on a PIC varies, as does the numbering pattern on the PIC itself.

 

PIC port numbering

 

 

Logical Unit and Channel Numbers

 

The logical unit portion of the interface name corresponds to unit number assigned within the interface configuration hierarchy. This value is a number in the range of 0 to 16384. Interfaces within the JUNOS software always contain a logical configuration, so some value is always present in the naming scheme. 

 

Some physical interfaces use a channel number instead of a unit number to represent their logical configuration. For example, a nonconcatenated (that is, channelized) SONET/SDH OC-48interface has four OC-12 channels, numbered 0 through 3. A channelized OC-12 interface has 12 DS-3 channels, numbered 0 through 11.

 

Interface Naming Examples

 

Suppose a router has two OC-3 PICs in slots 0 and 1 on an FPC in slot 1. Each of the PICs contains two ports. The names of these interfaces are:

 

so-1/0/0.0

so-1/0/1.0

so-1/1/0.0

so-1/1/1.0

 

Interface Properties

 

Interfaces in the JUNOS software contain both physical and logical properties. The actual media type (such as Ethernet or SONET) often determines the physical properties of the interface. An interface’s logical properties represent the Layer 3 routing and Layer 2 transmission parameters needed to operate the interface in a network. 

 

Physical Properties

Each interface in the router inherits certain default values for its physical properties. When the JUNOS software activates an interface, it assigns these values. 

 

Interface Properties

 

 

Connecting to another Vendor’s Router

 

The physical interface defaults do not always match

the operational parameters of another vendor. The default encapsulation type for a SONET link within the JUNOS software is the Point to- Point Protocol (PPP). A Cisco Systems router, on the other hand, uses a Cisco proprietary format of the High-Level Data Link Control (HDLC) protocol. The JUNOS software supports this HDLC format on point-to-point interfaces using the keyword cisco-hdlc.

 

 

Logical Properties

 

Each and every interface within the JUNOS software requires at least one logical interface, called a unit. This is where all addressing and protocol information is configured. Some physical encapsulations allow only a single logical unit. PPP and Cisco-HDLC fall into this category.

 

Logical interfaces, such as the loopback, and non-VLAN Ethernet also provide for only one logical unit. In both situations, the logical interface is assigned a unit value of 0.

 

Multiple logical interface units are often used in ATM, Frame Relay, and VLAN tagged Ethernet networks. In these cases, each logical unit is assigned a Virtual Circuit Identifier (VCI), Data-Link Connection Identifier (DLCI), or Virtual Local Area Network (VLAN) number, respectively.

 

Common logical interface properties include a protocol family, logical Layer 3 addressing, MTU, and virtual circuit (Layer 2) addressing information.

 

Logical Properties

 

Protocol MTU

An MTU value can be configured for each logical unit in the router. The default values vary for each physical media type as well as for the protocol family configured.

 

Point-to-point interfaces When you’re using an encapsulation type of PPP, Cisco-HDLC, ATM, or Frame Relay, the default MTU for the inet and iso protocols is 4470 bytes. The mpls protocol family uses a value of 4458 bytes.

 

Broadcast interfaces Both a Gigabit Ethernet and a Fast Ethernet interface share the same properties for protocol MTU sizes. The inet family uses 1500 bytes, the iso family uses 1497 bytes, and the mpls family uses 1488 bytes.

 

The interface MTU is the largest size packet able to be sent on the physical media. This value includes all Layer 2 overhead information, such as the destination MAC address on Ethernet, or the labels in an MPLS environment. The Cyclic Redundancy Check (CRC) information is not included in this value, however.

 

 

Protocol Families

 

Each logical interface in the JUNOS software has the ability to support one or more protocol families. These families enable the logical interface to accept and process data packets for therouter. Without their configuration, the interface drops any unknown transmissions. 

 

Currently four possible protocol families are available for use:

 

inet Supports IP version 4 (IPv4) packets.

 

inet6 Supports IP version 6 (IPv6) data packets

 

iso The Intermediate System to Intermediate System (IS-IS) routing protocol uses a data link encapsulation defined by the International Standards Organization (ISO). The iso protocol family allows the processing of these packet types. 

 

mpls Support for processing packets encoded with a Multiprotocol Label Switching (MPLS) label. This label information allows the router to forward the data packet. 

 

Protocol Addresses

 

A protocol address is a logical Layer 3 value used to route user packets in a network. For example, an IPv4 address of 192.168.1.1 /24 is a protocol address. The JUNOS software allows addressing for the inet, inet6, and iso protocol families. 

 

The inet family provides the capability to assign multiple addresses to each logical unit, with each address equally represented on the interface. 

 

primary address and the preferred address

A single primary address is assigned to each interface. By default, it is the lowest numerical IP address configured.Primary address is used as the source address of a packet when the destination address is not local to a configured subnet. 

 

Unlike the primary address, a logical unit may have multiple preferred addresses at the same time. The preferred address is used when an interface has two addresses configured within thesame subnet. The default selection of the preferred address is similar to the primary address in that the lowest numerical prefix is selected. 

 

Disabling/Deactivating Interface

 

Interfaces within the JUNOS software are automatically enabled for operation when configured in the router. To stop the operation of a particular interface, we may use one of two CLI commands—disable or deactivate. Both halt an interface without removing the current configuration in the router. This allows us to easily restart the interface when needed.

 

The difference between the commands is how the JUNOS software uses the configuration when the commit command is issued. 

 

Using the disable command at the [edit interfaces

interface-name] hierarchy level allows the router to use the interface configuration. Operationally, the interface is viewed as down, or administratively disabled.

 

The deactivate command places an inactive tag next to the configuration in the router. As the commit command is issued, the JUNOS software completely ignores the configuration. Operationally, the interface has no configuration

 

7.           CLI COMMANDS FOR INTERFACES

 

 

Useful Interface Commands

 

show interfaces int_name extensive

The show interfaces extensive command displays L1,L2 & L3 information about an interface

 

show interfaces int_name terse

The show interfaces terse command displays Admin & Link Status information about an interface

 

monitor interface int_name

The monitor interface interface-name command displays per-second real-time statistics for a physical interface. The output of this command shows how often each field has changed since the command was executed. We can also view common interface failures, such as alarms, errors, or loopback settings.

 

clear interfaces statistics int_name

Clears the counters on the interface

 

Configuration Examples

 

All interface configurations are completed at the [edit interfaces] hierarchy level. A generic interface configuration looks like this:

interfaces {

    interface-name {

      physical-properties;

      unit unit-number {

         logical-properties;

      }

    }

}

 

The Logical properties of the connections will be configured under the Family Unit

 

Adding IP Address for ATM Connection at-0/2/0.100

 

[edit interfaces at-0/2/0.100]

user@Juniper# set unit 100 family inet address 10.0.1.1/24

 

Deleting IP Address for ATM Connection at-0/2/0.100

 

[edit interfaces at-0/2/0.100]

user@Juniper# deletes unit 100 family inet address 10.0.1.1/24

 

Operational Changes

 

Deactivating an interface

 

user@Juniper> show interfaces so-2/0/0 terse

Interface   Admin  Link    Proto  Local          Remote

so-2/0/0    up       up

so-2/0/0.0 up       up       inet  10.0.2.1/30

 

[edit interfaces]

user@Juniper# deactivate so-2/0/0

[edit interfaces]

user@Juniper# show

inactive: so-2/0/0 {

 

[edit interfaces]

user@Juniper# activate so-2/0/0

[edit interfaces]

user@Juniper# show

so-2/0/0 {

 

 

Operational Changes

 

Disabling an interface

 

[edit interfaces]

user@Juniper# set fxp0 disable

[edit interfaces]

 

user@Juniper> show interfaces fxp0 terse

Interface  Admin  Link   Proto  Local Remote

fxp0        down    up

fxp0.0     down    down  inet   172.16.1.1/24

 

[edit interfaces]

user@Juniper# delete fxp0 disable

 

user@Juniper> show interfaces fxp0 terse

Interface  Admin  Link   Proto  Local Remote

fxp0        down    up

fxp0.0     down    up  inet   172.16.1.1/24

 

 

8.           CLI COMMANDS FOR STATIC ROUTES

 

 

   Configuration Examples

 

Adding Static Route for 192.168.16.0 /24 network 1.1.1.1

[edit routing-options]

user@Juniper# set static route 192.168.16/24 next-hop 1.1.1.1

[edit routing-options]

user@Juniper# show

static {

route 192.168.16.0/24 next-hop 1.1.1.1;

}

 

user@Juniper> show route protocol static | match 192.168.16.0

 

inet.0: 10 destinations, 15 routes (10 active, 0 holddown,

+ = Active Route, – = Last Active, * = Both

192.168.16.0/24 *[Static/5] 00:02:28

to 1.1.1.1 via fe-0/0/0.0

 

 

Configuration Examples

 

Deleting Static Route for 192.168.16.0 /24 network 1.1.1.1

 

[edit routing-options]

user@Juniper# delete static route 192.168.16/24 next-hop 1.1.1.1

[edit routing-options]

 

Adding Static Null Route for 192.168.16.1 /32

 

user@Juniper# set static route 192.168.16.1/32 discard / reject

[edit routing-options]

 

A reject next hop will prompt the local router to send an ICMP message of “Destination Host Unreachable” to the source of the IP packet. This message notifies the remote router that the data packet was dropped from the network. A discard next hop does not send an ICMP message back to the source; it silently drops the packet from the network.

 

 

9.           CLI COMMANDS FOR BGP

 

Configuration Examples

 

Configuration of the BGP Peer on Juniper Router for the below listed parameters:

 

Neighbor IP: 192.168.1.2

 

Neighbor AS::65000

 

Peer Type: External (EBGP)

 

Peer Description: Customer

 

Peer Import:: Peer-Neighbor IP—Standard Syntax

                                                (peer-192.168.1.2)

 

remove-private—Standard (Removes Private AS 

                                        Information in   

                                        the BGP Advertisements)

 

 

Configuration Examples

 

User@Juniper # set protocols bgp group peer-192.168.1.2 neighbor 192.168.1.2

 

User@Juniper # set protocols bgp group peer-192.168.1.2 peer-as 65000

 

User@Juniper# set protocols bgp group peer-192.168.1.2 type external

 

User@Juniper# set protocols bgp group peer-192.168.1.2 description Customer

 

User@Juniper# set protocols bgp group peer-192.168.1.2 import peer-192.168.1.2

 

User@Juniper# set protocols bgp group peer-192.168.1.2 remove-private

 

Mandatory to add BGP Peer IP in the Juniper Firewall Filter to protect BGP routing Engine in case of attack

 

[edit]

User@Juniper#

User@Juniper # set firewall filter internet term bgp from source-address 192.168.1.2 /32

 

Configuration Examples

 

Verifying BGP Peer Config

 

user@Juiper> show configuration protocols bgp group peer-192.168.1.2

 

type external;

import [ peer-192.168.1.2 customer bb2 ];

family inet {

    any {

        prefix-limit {

            maximum 116;

            teardown 90;

        }

    }

}

export [ send-default none ];

remove-private;

peer-as 65000;

neighbor 192.168.1.2;

[edit]

 

Cheat Sheet

 

FOR VERIFYING BGP STATE & RECEIVED PREFIXES STATS

 

user@Juniper> show bgp neighbor 172.16.1.1

Peer: 172.16.1.1+179 AS 10 Local: 172.16.1.2+1028 AS 20

Type: External State: Established Flags: <>

Last State: OpenConfirm Last Event: RecvKeepAlive

Last Error: None

Options: <Preference HoldTime PeerAS Refresh>

Holdtime: 90 Preference: 170

Number of flaps: 0

Peer ID: 192.168.2.2 Local ID: 192.168.5.5 Active Holdtime: 90

Keepalive Interval: 30

Local Interface: so-0/0/1.0

NLRI advertised by peer: inet-unicast

NLRI for this session: inet-unicast

Peer supports Refresh capability (2)

Table inet.0 Bit: 10000

Send state: in sync

Active prefixes: 4

Received prefixes: 4

Suppressed due to damping: 0

Last traffic (seconds): Received 13 Sent 13 Checked 13

Input messages: Total 438 Updates 4 Refreshes 0 Octets 8473

Output messages: Total 440 Updates 4 Refreshes 0 Octets 8526

Output Queue[0]: 0

 

If Active Prefixes not equal to Received Prefixes then need to check Route Filter for why few prefixes dropped

 

Cheat Sheet

 

FOR VERIFYING ROUTES PLACED IN ROUTING TABLE ADVERTISED FROM A PEER

 

user@Juniper> show route receive-protocol bgp 192.168.7.7

inet.0: 26 destinations, 27 routes (26 active, 0 holddown, 0 hidden)

+ = Active Route, – = Last Active, * = Both

10.20.3.0/24

192.168.7.7 0 100 I

10.20.4.0/24

192.168.7.7 0 100 I

 

FOR VERIFYING ROUTES RECEIVED FROM A PEER BUT NOT PLACED IN ROUTING TABLE

 

user@Juniper> show route receive-protocol bgp 192.168.5.5

inet.0: 21 destinations, 22 routes (13 active, 0 holddown, 8 hidden)

user@Juniper>

 

user@Juniper> show route hidden | match 192.168.5.5

 

inet.0: 21 destinations, 22 routes (13 active, 0 holddown, 8 hidden)

+ = Active Route, – = Last Active, * = Both

10.10.1.0/24 [BGP/170] 01:04:41, MED 0, localpref 100, from 192.168.5.5

AS path: 10 I

Unusable

 

user@Juniper> show route inactive-prefixes | match 192.168.5.5

 

Cheat Sheet

 

FOR VERIFYING ROUTES ADVERTISED TO A PEER

 

user@Juniper> show route advertising-protocol bgp 192.168.5.5

inet.0: 21 destinations, 22 routes (13 active, 0 holddown, 8 hidden)

+ = Active Route, – = Last Active, * = Both

10.20.3.0/24

Self 0 100 I

10.20.4.0/24

Self 0 100 I

 

FOR VERIFYING ROUTE-FILTER FOR A PEER

 

user@Juniper> show policy peer-192.168.5.5

 

FOR VERIFYING ROUTES IN THE ROUTING TABLE WITH A PARTICULAR AS-NO IN THE AS-PATH FOR A PEER

 

user@Juniper> show route aspath-regex “.*AS-NO.*” | match 192.168.5.5

 

FOR VERIFYING ROUTES IN THE ROUTING TABLE FOR A PARTICULAR ROUTING-INSTANCE

 

user@Juniper>show route table instance_name.inet.0

 

Cheat Sheet

 

FOR CLEARING BGP PEER SESSION

 

Hard Clear: Restarting BGP Peer Session

 

user@Juniper> clear bgp neighbor 192.168.5.5

 

 

Soft Clear: Refreshing Inbound & Outbound Routes

 

user@Juniper> clear bgp neighbor 192.168.5.5 soft

 

 

Verifying CLI Commands History

user@Juniper> show log cli-commands | match interface_name / interface_IP

 

Tags: , , , , , , ,
Posted in Technical | No Comments »

Oracle Stored Procedures – Tips

Wednesday, March 11th, 2009

Oracle Stored Procedures – Tips

 

 

 

Table of Contents

 

 

  1. Usage of %TYPE and %ROWTYPE in declaration of variables.
  2. Usage of CASE statement and Expression.
  3. Usage of DECODE expression.
  4. Creating and Compiling a Package.
  5. Returning a cursor from a stored procedure.
  6. Error Handling in Stored Procedures.
  7. Difference between SQLCODE 1403 and SQLCODE 100.
  8. Usage of Global Temporary tables.
  9. Others

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


 

 

 

 

The following is a collection of some useful tips for writing oracle 8i Stored Procedures. This is directed towards new users of oracle for quick learning.

 

 

 

 

Pre-requisites

 

Users should have a fair knowledge of RDBMS concepts and should know how to write a simple stored procedure.

Preliminary knowledge of Oracle is desirable.

 

 

 

Usage of %TYPE and %ROWTYPE in declaration of variables

 

 

It is considered a good programming practice to use the %TYPE while declaring variables in PL/SQL programming.

 

Syntax:  v_<variable_name>        <table_name>.<column_name>%TYPE

Example    v_assign_number      R3000_nw_control.assign_number%TYPE.

 

This will take the data type of the mapped column and automatically cascades the changes to the variables if the parent column changes.

The %ROWTYPE attribute provides a record type that represents a row in a database table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. Fields in a record and corresponding columns in a row have the same names and datatypes.

You can use the %ROWTYPE attribute in variable declarations as a datatype specifier. Variables declared using %ROWTYPE are treated like those declared using a datatype name.

In the example below, you use %ROWTYPE to declare two records. The first record stores a row selected from the emp table. The second record stores a row fetched from the c1 cursor.

DECLARE

   emp_rec   emp%ROWTYPE;

   CURSOR c1 IS SELECT deptno, dname, loc FROM dept;

   dept_rec  c1%ROWTYPE;

 

In the next example, you select a row from the emp table into a %ROWTYPE record:

DECLARE

   emp_rec  emp%ROWTYPE;

   …

BEGIN

   SELECT * INTO emp_rec FROM emp WHERE empno = my_empno;

   IF (emp_rec.deptno = 20) AND (emp_rec.sal > 2000) THEN

      …

   END IF;

END;

 

 

Usage of CASE statement and Expression

 

CASE statement is similar to the normal IF…THEN…ELSE loop, but is more efficient, easy to understand and maintain.

 

CASE statement takes many forms depending on the exact requirements.

 

The following are different forms of CASE expression and statement.

Value Match CASE Expression

The CASE expression is a more flexible version of the DECODE function. In its simplest form it is used to return a value when a match is found:

 

SELECT ename, empno,

  (CASE deptno

     WHEN 10 THEN ‘Accounting’

     WHEN 20 THEN ‘Research’

     WHEN 30 THEN ‘Sales’

     WHEN 40 THEN ‘Operations’

     ELSE ‘Unknown’

   END) department

FROM emp

ORDER BY ename;

 

The value match CASE expression is also supported in PL/SQL:

 

SET SERVEROUTPUT ON

DECLARE

  deptno     NUMBER := 20;

  dept_desc  VARCHAR2(20);

BEGIN

  dept_desc := CASE deptno

                 WHEN 10 THEN ‘Accounting’

                 WHEN 20 THEN ‘Research’

                 WHEN 30 THEN ‘Sales’

                 WHEN 40 THEN ‘Operations’

                 ELSE ‘Unknown’

               END;

  DBMS_OUTPUT.PUT_LINE(dept_desc);

END;

/

 

Searched CASE Expression

A more complex version is the searched CASE expression where a comparison expression is used to find a match. In this form the comparison is not limited to a single column:

 

SELECT ename, empno,

  (CASE

     WHEN sal < 1000 THEN ‘Low’

     WHEN sal BETWEEN 1000 AND 3000 THEN ‘Medium’

     WHEN sal > 3000 THEN ‘High’

     ELSE ‘N/A’

  END) salary

FROM emp

ORDER BY ename;

           

The searched CASE expression is also supported in PL/SQL:

 

SET SERVEROUTPUT ON

DECLARE

  sal       NUMBER := 2000;

  sal_desc  VARCHAR2(20);

BEGIN

  sal_desc := CASE

                 WHEN sal < 1000 THEN ‘Low’

                 WHEN sal BETWEEN 1000 AND 3000 THEN ‘Medium’

                 WHEN sal > 3000 THEN ‘High’

                 ELSE ‘N/A’

              END;

  DBMS_OUTPUT.PUT_LINE(sal_desc);

END;

/

Value Match CASE Statement

The CASE statement supported by PL/SQL is very similar to the CASE expression. The main difference is that the statement is finished with an END CASE statement rather than just END. The PL/SQL statements are essentially an alternative to lists of IF .. THEN .. ELSIF statements:

 

SET SERVEROUTPUT ON

BEGIN

  FOR cur_rec IN (SELECT ename, empno, deptno FROM emp ORDER BY ename) LOOP

    DBMS_OUTPUT.PUT(cur_rec.ename || ‘ : ‘ || cur_rec.empno || ‘ : ‘);

    CASE cur_rec.deptno

      WHEN 10 THEN

        DBMS_OUTPUT.PUT_LINE(’Accounting’);

      WHEN 20 THEN

        DBMS_OUTPUT.PUT_LINE(’Research’);

      WHEN 30 THEN

        DBMS_OUTPUT.PUT_LINE(’Sales’);

      WHEN 40 THEN

        DBMS_OUTPUT.PUT_LINE(’Operations’);

      ELSE

        DBMS_OUTPUT.PUT_LINE(’Unknown’);

    END CASE;

  END LOOP;

END;

/

Searched CASE Statement

As with its expression counterpart, the searched CASE statement allows multiple comparisons using multiple variables:

 

SET SERVEROUTPUT ON

BEGIN

  FOR cur_rec IN (SELECT ename, empno, sal FROM emp ORDER BY ename) LOOP

    DBMS_OUTPUT.PUT(cur_rec.ename || ‘ : ‘ || cur_rec.empno || ‘ : ‘);

    CASE

      WHEN cur_rec.sal < 1000 THEN

        DBMS_OUTPUT.PUT_LINE(’Low’);

      WHEN cur_rec.sal BETWEEN 1000 AND 3000 THEN

        DBMS_OUTPUT.PUT_LINE(’Medium’);

      WHEN cur_rec.sal > 3000 THEN

        DBMS_OUTPUT.PUT_LINE(’High’);

      ELSE

        DBMS_OUTPUT.PUT_LINE(’Unknown’);

    END CASE;

  END LOOP;

END;

/

 

* NOTE: The CASE Expression is available from Oracle 9i onwards.

 

 

Usage of DECODE expression

 

DECODE is same as IF…THEN…ELSE statement, but is to code and maintain.

It is used to assign a value to variable basing on the value of some other variable.

Syntax: decode (expression, search, result [, search, result]… [, default])

Expression is the value to compare.

Search is the value that is compared against expression.

Result is the value returned, if expression is equal to search.

Default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).

 

Example: You could use the decode function in an SQL statement as follows:

SELECT supplier_name,

decode(supplier_id,

10000,

‘IBM’,

 

10001,

‘Microsoft’,

 

10002,

‘Hewlett Packard’,

 

 

‘Gateway’) result

FROM suppliers;

 

     Nested DECODE can be used to evaluate some complex conditions.

 

(DECODE(NVL(C.PCT_100_DATE,0),0,DECODE(NVL(C.PCT_75_DATE,0),0,DECODE(NVL(C.PCT_50_DATE,0),0,DECODE(NVL(C.PCT_25_DATE,0),0,0,25),50),75),100)) AS TAST_COMPLETION_PCT

 

 

Creating and Compiling a Package

 

The following is the syntax used to create a Package.

 

CREATE PACKAGE <package_name> AS

 

<signature of the Procedures/Functions>

 

END <package_name>;

 

CREATE PACKAGE BODY <package_name> AS

 

<declaration of variables/types/cursors/procedures/functions/error handling>

 

END <package_name>;

 

   To compile a package in PL/SQL F8 key is used.

   

Returning a cursor from a stored procedure

 

At times it is required to return multiple rows from a Stored Procedure. This could be achieved through VArrays, Nested Tables and Cursors.

 

Cursors are the simple to implement.

 

The following steps are to be followed in order to return a cursor from a SP.

 

  1. Declare the CURSOR type in the SP.

 

  TYPE v_cursor IS REF CURSOR;

 

2. Declare a cursor variable of type v_cursor as IN OUT parameter in the SP.

 

  PROCEDURE <procedure_name>

        (<var 1>                     IN        <xxx.xxx>%TYPE,

         <cursor_variable>           IN OUT    v_cursor,

         v_error_message             OUT       VARCHAR2,

         v_error_code                OUT       NUMBER );

 

3. Define a cursor as per the requirement.

    

     OPEN <cursor_name> FOR

        SELECT <col1>,

              …<col2>,

               ……

          FROM  <table 1>

               <table 2>

         WHERE  <cond1>

         AND <cond2>;

 

4. Assign the cursor to the cursor variable.

    

   <cursor_variable> :=  <cursor_name>;

5. Since the cursor is not fetched, we are not sure whether it empty or contains records. This can be handled either in the front-end application or by using a singleton select on the primary key column of the tables in the WHERE condition of the cursor.

 

Error Handling in Stored Procedures

 

A good stored procedure is one which takes care of all the exceptions that occur.

At times we need to raise exceptions for wrong input values; SQLCODES and some times we don’t want to raise exceptions.

 

In order to raise an exception,

 

Define the exception

 

<exception1>   EXCEPTION;

 

ERROR_IN_INPUT EXCEPTION;

 

This has to be raised at the time of error.

 

IF <input_vairable> IS NULL

      THEN

         RAISE ERROR_IN_INPUT;

      END IF;

 

This transfers the control to the EXCEPTION block of the Procedure. Here the required processing should be done.

 

EXCEPTION

         WHEN ERROR_IN_INPUT

         THEN

             v_error_code   := <value>;

             v_error_message:= <message>;

 

END;

 

 

If a stored procedure calls another stored procedure, then

 

Case 1: If OTHERS exception is not handled in the called SP, it has to handled in the calling SP to take the required action and this stops the further processing.

 

Case 2: If OTHERS exception is handled in the called SP, Depending on the requirements, the return values from the SP has to compared and required action should be taken.

 

It is strongly recommended to have each SQL statement with in BEGIN – EXCEPTION – END block to control the exceptions locally.

 

 

  • Always handle all the specific exceptions locally.

 

Example:

 

      BEGIN

            …

            …

            …

            …

            BEGIN

                  …

                  …

                  …

            EXCEPTION

                  WHEN EXP1a

                        ….

                  WHEN EXP2a

                        ….

            END

            …

            …

            BEGIN

                  …

                  …

                  …

            EXCEPTION

                  WHEN EXP1b

                        ….

                  WHEN EXP2b

                        ….

            END

            …

            …

      EXCEPTION

            WHEN EXP1g

                  ….

            WHEN EXP2g

                  ….

            WHEN OTHERS

                  …

END

 

  • Never handle generic exceptions like ‘WHEN OTHERS’ locally because this will continue with further processing even when you need to stop and exit. There is no way to transfer the control back from the EXCPETION block.

 

BEGIN

            …

            …

            …

            …

            BEGIN

                  …

                  …

                  …

            EXCEPTION

                  WHEN EXP1a

                        ….

                  WHEN EXP2a

                        ….

                  WHEN OTHERS       /* Not Suggestible */

                        …

 

            END

 

      EXCEPTION

            WHEN EXP1g

                  ….

            WHEN EXP2g

                  ….

            WHEN OTHERS

                  …

END

 

BEGIN

            …

            …

            …

            …

            BEGIN

                  …

                  …

                  …

            EXCEPTION

                  WHEN EXP1a

                        ….

                  WHEN EXP2a

                        ….

            END

 

      EXCEPTION

            WHEN EXP1g

                  ….

            WHEN EXP2g

                  ….

            WHEN OTHERS

                  …

END

 

The SQLCODE condition checks after a SQL query in a stored procedure will never get executed when an exception occurs

 

Consider the following piece of the code

 

      BEGIN

      SELECT fieldA

      INTO fldA

      FROM tableA

      WHERE field1 = fld1;

           

      IF  SQLCODE <> 0 AND <> 100

      THEN

        <SOME STATEMENTS>

      END-IF

 

  EXCEPTION

     WHEN NO_DATA_FOUND

     THEN

        v_error_code := 0;

   END;

 

The above condition will not get executed if there is an exception for the SELECT query. It will jump to the outer exception WHEN OTHERS block.

 

To handle such cases, you can use the following logic

 

  BEGIN

      SELECT fieldA

      INTO fldA

      FROM tableA

      WHERE field1 = fld1;

 

  EXCEPTION

     WHEN NO_DATA_FOUND

     THEN

        v_error_code := 0;

        v_error_message:= ‘ ‘;

     WHEN  OTHERS

      IF   SQLCODE <> 0 AND SQLCODE <> 100

      THEN

            <SOME STATEMENTS>

      END IF;

   END;

 

 

Difference between SQLCODE 1403 and SQLCODE 100

 

ORA-01403:

no data found

Cause:

In a host language program, all records have been fetched. The return code from the fetch was +4, indicating that all records have been returned from the SQL query.

Action:

Terminate processing for the SELECT statement.

 

ORA-00100:

no data found

Cause:

An application made a reference to unknown or inaccessible data.

Action:

Handle this condition within the application or make appropriate modifications to the application code.

 

Usage of Global Temporary tables

 

Temporary Tables

Oracle 8i introduces a new concept called global temporary tables. This version only provides global temporary tables and local tables are expected in the future. These are like normal tables but the segment is created only when the data is inserted and hence temporary in nature.

create global temporary table dbatest
(c1 number, c2 number);

The above statement creates a table whose definition is visible to all the sessions but the data is visible to only the session that has created it. Temporary tables can be used by developers to store session / transaction specific data that can be discarded as soon as the session/transaction ends. When a truncate is issued on this table, only data related to that particular session is truncated.

The above code creates a table named dbatest. When data is inserted into this table the data persists either at the session or transaction level based on how “On Commit” parameter is specified. On commit can be specified as “delete rows” or “preserve rows”. Delete Rows seems to be the default.

Create global temporary table dbatest
(
c1 number,
c2 number
) on commit delete rows;

In the above table, as soon as the user ends the transaction by issuing a commit statement, the data in the temporary table is deleted (truncated).

Create global temporary table dbatest
(
c1 number,
c2 number
) on commit preserve rows;

The above statement causes the creation of a table that will keep its rows even after the transaction is committed. If the user inserts rows into the above table and then commits the data, the data that was inserted will exist until the session is ended.

 

        

                       

 

Global Temporary Tables        

 

USER

Temporary Tablespace

 

Default Tablespace

Transaction Specific data

(ON COMMIT DELETE ROWS)

or

 

Session specific data

(ON COMMIT PRESERVE ROWS)

                       

 

Normal

Tables

 

                 

 

                                                                                                                                                                                                        

US-A       

 

T

E

M

P

O

R

A

R

Y

 

T

A

B

L

E

 

(T)

 

 


 

 

TEMPORARY SEGMENT  (S1)

 

 

 

 

 

US-B

US-C

 

USER SESSIONS/TRANSACTIONS

TEMPORARY SEGMENT (S2)

 

 

 

 


TEMPORARY SEGMENT (S3)

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

 

 

Advantages of Temporary Tables

 

  1. Used as buffer tables to increase performance while replacing complex joins.
  2. Used as scratch tables to handle voluminous data for sort and other operations.
  3. Supports Multi-session/Transaction.
  4. Only the definition is stored in the database.
  5. The data is deleted at the end of the Session/Transaction.

 

Others

 

  1. Update queries in ORACLE does not return an SQLCODE of 1403/100 when the where clause of the query does not return any rows. This can be handled in two ways

 

  1.  
    1. Usage of SQL%ROWCOUNT: SQL%ROWCOUNT returns the number of rows updated by the query. If SQL%ROWCOUNT returns zero that means the where clause of the query does not return any rows.

 

  1.  
    1. Usage of SELECT: A normal select or select for update or select count (*) with the same where clause can be used to check whether the records exist for the given condition before UPDATE query.

 

  1. Oracle 8i does not have utility functions such as IS NUMBER, IS CHARACTER, et al.

 

  1. When an aggregate function like SUM, AVG e.t.c is used in a select statement, the SQLCODE 100 is returned when no records are found. This can be handled by using NVL(SUM(<parameter>),0) to avoid SQLCODE 100

 

 

 

Tags: , , , , , , , , ,
Posted in Technical | No Comments »

« Older Entries