Wednesday, April 30, 2008
TIBCO BusinessWorks Sample Questions
-->what is tibco and where to we use tibco?
TIBCO -> THE INFORMATON BUS
tibco is used for
to integrate application system .
tibco is integration tool or it is a middleware tool
-->Explian the TIBCO architecture?
TIBCO’s main architecture is based on an information-bus oriented system concept. Whereas all the information that is used by the multiple systems passes through one information-bus. Tibco commonly refers to this information-bus as the “TIB”.
--> Business Works (B W)
The TIBCO BusinessWorks integration platform enables companiesto rapidly integrate systems and automate business processes.TIBCO BusinessWorks supports your integration projectthroughout the project lifecycle. It includes a commongraphical user interface for adapter configuration, processdesign, and deployment. User administration, componentmanagement, and processmonitoring are available via a webbrowser based GUI.
BusinessWorks components
-->BusinessWorks consists of TIBCO Administrator, TIBCO Runtime Agent, BusinessWorks Engine, and TIBCO Designer
-->TIBCO Designer
TIBCO Designer is a GUI tool for designing your business process integration. It is used for adapter configuration, process design, test, and deployment. TIBCO Designer is a component of TIBCO BusinessWorks. --
-->Overview of Variables
There are several types of variables in TIBCO Business Works, each with their own purpose and usage. TIBCO Business Works provides the following types of variables:
Global Variables — these variables allow you to specify constants that can be used throughout the project. The constants can be specified and changed while designing and testing your project.
Process Variables — these variables allow you to access various data in your project.
Shared Variables — these variables allow you to specify data for use across multiple process instances. Because multiple process instances can access the same variable, you can also synchronize access across processes when setting or retrieving the shared variable
-->What is an activity?
An activity is an individual unit of work within a process definition in the TIBCO Designer. Activities are operations that interface with external systems or perform internal processing. Activities are available on the various palettes in the TIBCO Designer, e.g. an AE adapter palette contains AE publish activity, AE subscription process starter activity, and AE respond activity.
-->What is TIBCO Designer?
TIBCO Designer is a GUI (graphical user interface) tool for designing your business process integration. It is used for adapter configuration, process design, test, and deployment. TIBCO Designer is a component of TIBCO BusinessWorks.
-->What is a transition?
A transition is an arrow on a process definition between activities. Transitions describe the flow of processing. An activity in a process definition must have a transition to it, or the activity is not executed when the process executes.
-->What is TIBCO Administrator?
TIBCO Administrator is a browser-based interface used for monitoring and managing resources in a TIBCO Domain.
-->What is a TIBCO Domain?
Domain is a collection of hardware and software components that are used for business process integration. The domain defines the TIBCO BusinessWorks environment. Each domain must contain one and only one administration server. Each domain must have a unique domain name. Each domain may contain one or more machines but no single machine can belong to multiple domains. Each machine may have more than one type of software component (BusinessWorks process engine, ActiveEnterprise 4.2 Adapters, etc.) installed. By definition, software components can only be deployed in the same domain that the machine belongs to.
1.What are the modes of TIBCO BW Installations ?
* GUI mode
* Console mode
* Silent mode
2.If you have installed a particular version of TIBCO software e.g. TIBCO BW X.Y.Z, What are X, Y and Z number stands for?
Integration can be at different application layers:
* X:Patch
* Y:Major
* Z:Minor
3.What is the role of TRA?
TRA stands for TIBCO Runtime Agent.
The TRA has two main functions:
* Supplies an agent that is running in the background on each machine.
1. The agent is responsible for starting and stopping processes that run on a machine according to the deployment information.
2. The agent monitors the machine. That information is then visible via TIBCO Administrator.
* Supplies the run-time environment, that is, all shared libraries including third-party libraries.
4.What are the resources that gets included in the EAR file, created by the TIBCO Designer?
An EAR file can contain local project resources, LibraryBuilder resources, and files as specified in AliasLibrary resources. In addition, the TIBCO Designer classpath may include references to other files that are included in the EAR file.
TIBCO EAR file
5.What are the revision control system options available in TIBCO designer?
* File sharing
* Perforce
* XML Canon
* ClearCase
* iPlanet
5.1 What are the 4 main panels of the Designer window ?
The 4 main panels of the Designer window are
* Project panel
* Palette panel
* Design panel
* Configuration panel
6.What are the different modes of service invocation?
Services can be invoked in several ways.
* A one-way operation is executed once and does not wait for a response.
* A request-response operation is executed once and waits for one response. In a request-response service, communication flows in both directions. The complete interaction consists of two point-to-point messages—a request and a response. The interaction is only considered complete after the response has arrived.
* Publication (notification) means an operation sends information on an as-needed basis, potentially multiple times.
* Subscription means incoming information is processed on an as-needed basis, potentially multiple times.
6.1 How do you determine if there are broken references in the project?
Project -> Validate for deployment
7.What is vcrepo.dat?
TIBCO Designer creates a file named vcrepo.dat in the project root directory when you first save the project. This file is used to store properties such as display name, TIBCO Rendezvous encoding, and description. This file can be used for identification in place of the project root directory and can be used as the repository locator string (repoUrl).
7.1 What are the uses of grouping activities ?
Uses of grouping activities are:
* Create a set of activities having a common error transition.
* Repeat group of activities based on a condition.
1. - Iterate over a list.
2. - Repeat until condition true.
3. - Repeat on Error until condition true.
* Group activities into a transaction.
* To create a critical section area that synchronizes process instances.
* A 'Pick First Group' allows you to wait for the occurence of multiple events and proceed along a path following the first event to occur.
8.What are the TIBCO BW activities that can participate in transactions?
Not all TIBCO BusinessWorks activities can participate in a transaction. Only the following types of activities have transactional capabilities:
* JDBC activities
* JMS activities
* ActiveEnterprise Adapter activities that use JMS transports
* EJB activities
* TIBCO iProcess BusinessWorks Connector activities
8.1 What activity can be used to set the value of a 'User defined process variable' ?
The 'Assign' activity can be used to set the value of a 'User defined process variable'.
9.What are the different types of Transactions TIBCO provides?
TIBCO BusinessWorks offers a variety of types of transactions that can be used in different situations. You can use the type of transaction that suits the needs of your integration project. When you create a transaction group, you must specify the type of transaction. TIBCO BusinessWorks supports the following types of transactions:
* JDBC
* Java Transaction API (JTA) UserTransaction
* XA Transaction
10.What activities are supported in JTA Transaction?
The Java Transaction API (JTA) UserTransaction type allows:
* JDBC
* JMS
* ActiveEnterprise Adapter (using JMS transports)
* EJB activities
to participate in transactions.
12.What are the possible Error output's of Read File activity?
Integration can be at different application layers:
* FileNotFoundException :Thrown when yhe file does not exist.
* UnsupportedEncodingException:Thrown when the text file’s encoding is not valid and the content of the file is read into process data.
* FileIOException :Thrown when an I/O exception occurred when trying to read the file.
13.What are the encodings supported by designer ?
Encodings supported by designer are
* ISO8859-1(Latin-1)
* UTF-8
14. What is the scope of user defined process variables ?
The scope of user defined process variables is only the process in which it is defined.
15. What is difference between shared variable and job shared variable ?
*
Both of them can be manipulated via the palette resources 'Get shared variable' and 'Set shared variable'.
*
A job shared variable is private to one instance of job or in other words each job has a fresh copy. In the case of shared variable the same copy is shared across all job instances. It can even be persisted and can survive BW engine restarts and even shared across multiple BW engines(when deployed using DB persistence).
17. Question:
What is the difference between Tibco Rendezvous and TIB EMS when to prefer which one?
Answer: tibco rv is mostly used where u got to send the data at a higher speed,u may consider the example of stocks where u need to update the data at a faster pace,but in the case where performance is give a priority, EMS is mostly used.in the case of financial services where the data has to be accurate ems is used
Answer: Tibco RV is a bus kind of architecture and TIbco EMS is a Hub and spoke architecture....in ems every msg passes from server...drawback is if server fails all the transactions will be stops..and RV is a bus architecture, and it is subject based messaging...receiver need not know the sender location....Drawback is in RVCM some times duplications will be done...u can use any one..but most of the people prefer EMS becoz its cheap and good in security purpose..RV also good but its costlier than EMS and maintaince also costlier....than ems...
18. is there catch activity in bw?
This activity is available in general activities palette. This catch activity receives control of execution when an unhandled exception occurs. You can select a specific exception type to catch or you can specify that this activity should catch all unhandled exceptions. You can have more than one Catch activity in each exception scope, but each Catch activity must have a unique exception type.
19.what is a pallete?
Pallete is a container for Business works resources & activities which are used to design BW processes. Palletes are categorised and named on the basis of the functionality of the enclosed activities & resources, e.g., Rendezvous pallete, SOAP pallete, HTTP pallete, General activities pallete etc. The resources & activities from each pallete can be dragged from pallete panel to design pannel in the TIBCO designer.
20.How many types of messaging services in RV?
1. reliable
2.certified
3.distributed queue
21.What are the three properties appear for JMS Message?
SEN,REPLAY & SEND REQUEST AND REPLY
22.Difference between Topic and Queue?
queue is point to point
topic is publish-subscribe type
-->queue:- is like sender and reciver(listener). if you send a message on a particular subject to multiple queues. the queues will be listening in roundrobin fashion. ""i.e., one queue can receive or listen at an instance of time"
Topic:- its like publish and subscribe. many can publish and many can reviever. it at all many subscribers subscribe to the same topic they will all listen at the same time there will not be any roundrobin fashion her
Tuesday, April 29, 2008
Software Development Life Cycle
Software development life cycle model is also called as waterfall model which is followed by majority of systems. This software development life cycle process has the following seven stages in it namely
1. System Requirements Analysis
2. Feasibility study
3. Systems Analysis and Design
4. Code Generation
5. Testing
6. Maintenance
7. Implementation
Let us discuss each of these to have an overview about teach of the following steps in software development life cycle.
1. System Requirements Analysis:
The first essential or vital thing required for any software development is system. Also the system requirement may vary based on the software product that is going to get developed. So a careful analysis has to be made about the system requirement needed for the development of the product. After the analysis and design of the system requirement phase the system required for the development would be complete and the concentration can be on the software development process.
2. Feasibility study:
After making an analysis in the system requirement the next step is to make analysis of the software requirement. In other words feasibility study is also called as software requirement analysis. In this phase development team has to make communication with customers and make analysis of their requirement and analyze the system. By making analysis this way it would be possible to make a report of identified area of problem. By making a detailed analysis on this area a detailed document or report is prepared in this phase which has details like project plan or schedule of the project, the cost estimated for developing and executing the system, target dates for each phase of delivery of system developed and so on. This phase is the base of software development process since further steps taken in software development life cycle would be based on the analysis made on this phase and so careful analysis has to be made in this phase.
3. Systems Analysis and Design:
This is an important phase in system development .Here analysis is made on the design of the system that is going to be developed. In other words database design, the design of the architecture chosen, functional specification design, low level design documents, high level design documents and so on takes place. Care must be taken to prepare these design documents because the next phases namely the development phase is based on these design documents. If a well structured and analyzed design document is prepared it would reduce the time taken in the coming steps namely development and testing phases of the software development life cycle.
4. Code Generation:
This is the phase where actual development of the system takes place. That is based on the design documents prepared in the earlier phase code is written in the programming technology chosen. After the code is developed generation of code also takes place in this phase. In other words the code is converted into executables in this phase after code generation.
5. Testing:
A software or system which is not tested would be of poor quality. This is because this is the phase where system developed would be tested and reports are prepared about bugs or errors in system. To do this testing phase there are different levels and methods of testing like unit testing, system test and so on. Based on the need the testing methods are chosen and reports are prepared about bugs. After this process the system again goes to development phase for correction of errors and again tested. This process continues until the system is found to be error free. To ease the testing process debuggers or testing tools are also available.
6. Maintanence:
After the testing phase is completely through and the system is found to be error free it is delivered to the customer. But no real system would be error free even then. This is because when the system gets executed in real user system scenarios there would be possibilities of lot of bugs and errors. This phase is called the maintenance phase and the errors in this are recorded and changes has to be made in the system accordingly and again testing phase comes into picture.
7. Implementation:
This is the final stage when the system gets into live.
The above software development process are all vital for a system to get developed with quality and thus to achieve customer satisfaction which is the main objective of any software development process.
Traning & FAQ'S
Object-oriented programming (OOP) is a computer science term used to characterize a programming language that began development in the 1960’s. The term ‘object-oriented programming’ was originally coined by Xerox PARC to designate a computer application that describes the methodology of using objects as the foundation for computation. By the 1980’s, OOP rose to prominence as the programming language of choice, exemplified by the success of C++. Currently, OOPs such as Java, J2EE, C++, C#, Visual Basic.NET, Python and JavaScript are popular OOP programming languages that any career-oriented Software Engineer or developer should be familiar with.
Java FAQ'S:
1.What is Java?
Java was originally developed as an implementation of a programming language that would compile and complete its directive functions regardless of platform and language version by James Gosling, a software developer employed by Sun Microsystems in June of 1991.
Gosling's main goals in developing the Java language were: implementing object oriented programming methodology containing built in support for the use of computer networks, the ability to execute remote sources of code in a secure fashion, and of course cross platform compatibility.
2.What is JNI?
JNI is the Java Native Interface.
JNI is an interface between Java and applications and libraries written in other languages.
As an example, JNI enables Java programs to use C libraries and also enables C programs to use Java classes.
JNI is distributed as part of the JDK.
3.What is the JDK?
The JDK is the Java Development Kit.
The JDK consists of the Java compiler and related tools which enable users to create applications in Java.
4.What is a Java compiler?
A Java compiler is a program which converts Java source code into Java bytecode.
A basic Java compiler is included as part of the JDK (Java Development Kit). This Java compiler is called "javac".
5.What is Java source code?
Java source code is code that you write in the Java programming language.
Java source code is converted to Java bytecode by the Java compiler.
Java source code files usually have the .java extension.
Sun recommends that Java source code files be no longer than two thouand lines. Larger source code files should be split up into multiple smaller files.
6.What is Java bytecode?
Java bytecode is produced by the Java compiler and executed by the JVM (Java Virtual Machine).
Java bytecode isn't exactly source code, and it isn't exactly compiled code, it's something in the middle.
Java bytecode is one of the things that make it possible for Java code to run on many different platforms.
7.What is a Java Virtual Machine?
A Java Virtual Machine is quite simply a piece of software that enables Java technology to be recognized and successfully executed on a vast array of hardware platforms. Java virtual machines are so named because they provide a necessary environment for the Java bytecode to be executed. The flexibility of a JVM allows a Java applet to be written only once, but able to be run on virtually any operating system.
8.What is a Java package?
A Java package is a set of classes which are grouped together.
This grouping helps to organize Java classes and codevent multiple Java classes with the same name.
9.What is the JRE?
The JRE is the Java Runtime Environment.
The JRE consists of the Java Virtual Machine, the Java libraries, and all other components necessary to run Java applications and applets.
10.What is a jar file?
A jar file is a Java Archive.
A jar file is a collection of class files.
The jar file standard is based upon PKZip. In fact, you can open .jar files with a normal unzip program!
A jar archive should contain a file called Manifest.mf which tells the Java Virtual Machine which class file contains the main method.
11.What is a Java servlet?A servlet is a Java program which is run on a web server.
A Java servlet is much like a Java applet, except that servlets are run on the web server instead of being downloaded to the web client.
12.What is a Java class?
A Java class is a group of Java methods and variables.
Each Java source code file can contain one public class. The name of this public class must match the name of the Java source code file. If the public class is called "ballistics", then the filename would be "ballistics.java".
13.What is a Java method?
A Java method is a set of Java statements which can be included inside a Java class.
Java methods are similar to functions or procedures in other programming languages.
Every Java program must have one main() method.
14.What is the main method?
Every Java program must have one main method. The main method is the first method which the Java Virtual Machine executes. The main method then calls the other methods, as they are needed.
Main method is often written as main() method.
It is good programming practice to maintain a very small main method and to move all possible code into other methods.
15.What is a static method?
A static method is a method which accepts arguments instead of using instance variables.
The opposite of a static method is an instance method.
The default for a method is to be an instance method. Methods must be explicitly defined as static methods.
A static method is also referred to as a class method.
16.What is a constructor?
A constructor is a special method for initializing a new instance of a class.
The constructor method for a class will have the same name as the class.
17.What are JavaBeans?
JavaBeans are reusable software components which can be used to develop larger applications.
About EAI
EAI
Enterprise Application Integration:
Definition:
EAI is a conceptual framework for an integration environment based on service-oriented
various services provided by business applications and other enterprise IT resources to enable the end-to-end business process capability. Seamlessness of the EAI
framework comes from its ability to allow various technologies and platforms to talk to each other and enable business applications to understand each other’s data formats. view of enterprise architecture. It allows seamless integration of
After reading so many complicated definitions of EAI on the web I conclude that EAI is the use of middleware to integrate the application programs, databases, and legacy systems involved in an organization's critical business processes.
Now what does middleware mean? It is software that mediates between an application program and a network. It manages the interaction between disparate
applications across the heterogeneous computing platforms.
The Object Request Broker (ORB), software that manages communication between objects, is an example of a middleware program.
Why EAI?
Now the question arises why do we need EAI at all? Well, to answer this my understanding is that in the past, many organizations built their systems based on the
most popular technology of the times without realizing that if there is a need to share information, how would it be achieved? There are a number of organizations
having different types of open and proprietary systems. Each with its own development tools, database, networking and operating system resulting in a heterogeneous
environment.
Modern Integration Approach:
With the use of EAI, an organization’s disparate internal applications should appear as one single unified application for the user with high performance.
Below mentioned are few of many advantages of EAI:
It helps in developing new applications by integrating the existing legacy and packaged applications of an organization.
To satisfy the business requirements of an organization and utilize the existing information system to the maximum.
EAI is best for easily integrating the disparate systems of the enterprise.
To utilize the real power of web that enables collaborative business-to-business e-commerce solutions.
It helps to share data and integrate business processes.
To increase the productivity.
To increase the user experience by implementing uniform user interface
The Challenges in EAI:
The task of integration is not as easy as one may think. There are a number of challenges to integration:
Different Application Development Tools.
Different Operating Systems.
Different Communication Protocols.
Knowledge of off-the-shelf products.
Multiple and inconsistent versions of common business data.
Different Computing Architectures.
Tight-coupling of application components.
Distributed databases and applications.
Legacy icebergs.
Resistance to change.
Types of Integration
An enterprise system comprises of business processes and data. So it becomes necessary to understand how these business processes are automated and the
importance of all business processes. This understanding will bring out a lot of useful hints for determining the amount of work needed, how much time it will take, which
business processes and data are to be integrated etc. Apart from this initial and first task of exploration, the primary knowledge needed is at what level, the integration process has to be performed in an enterprise application as there are mainly four levels, such as data level, application level, platform level, and Business Process level in an application
Platform Integration:is the integration on heterogeneous platforms. The platform integration can be achieved by synchronous and asynchronous messaging.
Data-level Integration : is the process and the techniques and technology of transferring data between data stores. This can be described as extracting information from one database, if need, processing that information, and updating the same in another database.Extraction is done through custom extraction and import programs and is used in application which do not require real time information.
Application-level Integration: custom or packaged applications expose some interfaces. Developers make use of these interfaces to access both business processes and simple information. Using these interfaces, we can bring many applications together, allowing them to share business logic and information. The only limitations of the application interfaces are the specific features and functions. This type of integration is most applicable for ERP applications, such as SAP, PeopleSoft and Bann, which will expose interfaces into their processes and data, the most preferred EAI technology for this type is message brokers as these can extract the information from one application, put it in a format understandable by the target application and transmit the information.
Business Process Integration:is designing business processes that synchronize internal operations of an organization. The business processes are not bypassed. The state of the Business Processes is maintained (e.g. workflow approvals etc.).A Business Process is viewed, monitored and controlled.
Publish-Subscribe EAI:Publish-subscribe is a system whereby messages generated by publishers are sent to a central messaging hub or broker that in
turn sends these messages to subscribers that have previously subscribed to receive some or all of these messages. This method is similar to hub-and-spoke, and
the main conceptual difference is that spokes in a hub-and-spoke system typically know the destination of the message, whereas in publish-subscribe, the publishers do not
have any knowledge of the subscribers.
Advantages:
Complete decoupling of source and target. Senders (Publishers) have no idea who subscribes to their messages; Receivers (Subscribers) have no idea who
generates their messages.Less complexity of integration. Applications on either side of the hub can be modified independently of each other and the hub performs any mapping
of documents between different application formats.More dynamicSubscribers can choose to subscribe and unsubscribe to messages and change their subscription properties at any time.
Disadvantages:
Two-way communications is harder. The hub has to correlate messages flowing between both parties.
Applications on both sides of the hub have to work well in a decoupled, event-driven fashion.
This is not it about the world of SOA and Middleware however, this is the first step to enter in it and there is lot more to learn about it.
Where SOA comes into picture?
Suppose you want to get your address changed in this bank. The process for the same would be to go to the bank, or maybe send them an e-mail to get this done. This process would take time. Suppose the bank extended a part of its internal CRM application directly to the customer over a thin client or even the Web and allowed the change to happen directly, after authentication of course. Adding such a capability into your CRM application would require you to update it, which could take plenty of time. That's where SOA comes into the picture. This would allow you to easily extend your existing business applications' capabilities to the customer, without ripping through the application's code. It would do this by providing a loosely coupled service between the application and the user interface.
So the bottomline is that every organization has various business applications running. SOA allows you to extend their capabilities or integrate them without disturbing them too much. This allows an organization to expand its business and offer new services to customers faster than traditional methods. Another area where SOA shines is in company mergers. When a merger happens, chances are that the merging companies would have different applications, platforms, etc. These could be performing redundant or completely different processes. SOA can help integrate these platforms without bringing in major changes to the existing infrastructure.
Monday, April 28, 2008
TIBCO
ActiveEnterprise is Tibco's integration server package that supports both message-bus and hub-and-spoke integration server models. The message-bus model connects the different applications to a common backbone using application adapters. The hub-and-spoke model connects all applications to a central server. Tibco's latest addition to ActiveEnterprise is BusinessWorks, which uses Web services technology.
About SOA
Service-oriented architecture (SOA) definition
| A service-oriented architecture is essentially a collection of services. These services communicate with each other. The communication can involve either simple data passing or it could involve two or more services coordinating some activity. Some means of connecting services to each other is needed. Service-oriented architectures are not a new thing. The first service-oriented architecture for many people in the past was with the use DCOM or Object Request Brokers (ORBs) based on the CORBA specification. For more on DCOM and CORBA, see Prior service-oriented architectures (new window). ServicesA service-oriented architecture is essentially a collection of services.If a service-oriented architecture is to be effective, we need a clear understanding of the term service. A service is a function that is well-defined, self-contained, and does not depend on the context or state of other services. See Service (new window). ConnectionsThe technology of Web services (new window) is the most likely connection technology of service-oriented architectures. Web services essentially use XML (new window) to create a robust connection. The following figure illustrates a basic service-oriented architecture. It shows a service consumer at the right sending a service request message to a service provider at the left. The service provider returns a response message to the service consumer. The request and subsequent response connections are defined in some way that is understandable to both the service consumer and service provider. How those connections are defined is explained in Web Services explained (new window). A service provider can also be a service consumer.
|
Tuesday, April 1, 2008
Techonology
want to know about technologies like TIBCO & JAVA then....u r on the correct way....
MIDDLEWARE & EAI:
In the computer industry, middleware is a general term for any programming that serves to "glue together" or mediate between two separate and often already existing programs. A common application of middleware is to allow programs written for access to a particular database to access other databases.
Typically, middleware programs provide messaging services so that different applications can communicate. The systematic tying together of disparate applications, often through the use of middleware, is known as enterprise application integration (EAI).
PURPOSE OF MIDDLE WARE:
Every type of middleware has the same general purpose: to allow multiple computers to do multiple things across a network, or to allow one computer to do many things or one complicated thing across a network. For example, some middleware is used to link a database system to a Web server, allowing users to access the database via a Web browser. Certain complicated computer systems require middleware in order to run their demanding applications. A perfect example of a large form of middleware is an application server, which is a server that is dedicated to a single application or a single type of application.
At its most basic, middleware is invisible, allowing computers to connect and communicate with one another and with servers. Without middleware, certain kinds of network activity would be impossible. The more powerful applications become, the more middleware will be needed.
Rocking Naresh........
comon lets have fun ......want entertainment.....differently.....then u r on correct way
know more........wat u want.....
Naresh
N-Naughty
A-Angry
R-Royal
E-Energetic
S-Self-organised
H-Humanity
Im Naresh working as a software eng in a MNC company.im cool ,simple person with lots of energy to entertainment ,i like to live always simply with fun...and now u wil know......about me...
Oracle Stored Procedures
| Oracle Stored Procedures |
| |||||
| So what's this business with Oracle allowing programmers to put programs in databases? That's right. They're called Oracle stored procedures, and they're quite useful. Mooh the Cow walks you through writing, creating, debugging, and deleting a procedure. What you will learn An Oracle stored procedure is a program stored in an Oracle database. Now this is unusual – we expect data in a database, but not programs! However, you will soon see how useful this can be. We often refer to an Oracle stored procedure as a procedure. You will learn to:
Next, you'll find out what you need to know before you start. Now, to get you started, let's learn how to write a procedure. Procedures are written in Oracle's PL/SQL programming language. Let’s write a harmless procedure that does nothing, so we can compile and run it with no fear of damage to your database. In Notepad, type:
Save your file as skeleton.sql. Let’s go through the procedure line by line. The lines are numbered for your convenience:
Line 1: CREATE OR REPLACE PROCEDURE is an SQL statement that instructs Oracle to create a procedure called skeleton, and to overwrite it if it exists. Line 2: The IS keyword signals that a PL/SQL body will follow. Line 3: The BEGIN keyword signals the start of a PL/SQL body. Line 4: The NULL PL/SQL statement indicates that no action should be performed. We cannot just leave it out, because at least one statement is required in a PL/SQL body. Line 5: The END keyword signals the end of the PL/SQL block. Now let's move on to creating a procedure. The SQL statement CREATE OR REPLACE PROCEDURE creates, compiles and saves a procedure on an Oracle database. You need the CREATE PROCEDURE system privilege to create a procedure in your own schema. Open SQL*Plus from Windows and log on to your database. From SQL*Plus, open your skeleton.sql file.
Execute the contents of the SQL*Plus buffer. Type a front slash and press
SQL*Plus informs you the procedure has been created successfully and presents the SQL command prompt:
Now your procedure is created, compiled and saved on your Oracle database. We have a procedure now, so let's run it. Run your procedure from the SQL*Plus command prompt with the EXECUTE command like this:
The EXECUTE statement is easy and fast to type. You can also run your procedure from within an unnamed PL/SQL block. At the SQL*Plus command prompt, it looks like this:
By calling your procedure from within an unnamed PL/SQL block, you can even call your procedure twice, like this:
SQL*Plus refers to the unnamed PL/SQL block when it says "PL/SQL procedure successfully completed." So even though we called the skeleton procedure twice, we only get one message back. So much for going the extra mile! Now that we've run our procedure, what do we need to do if we want to change it? Let's write a procedure that outputs the string "Hello World!". Open your skeleton.sql file in Notepad. Replace the NULL statement iswith the DBMS_OUTPUT.PUT_LINE procedure call. Your program should look like
Save your file as skeleton.sql. From SQL*Plus, open your skeleton.sql file.
Execute the contents of the SQL*Plus buffer. Type a front slash and press
SQL*Plus informs you the procedure has been created successfully and presents the SQL command prompt:
Run your procedure from the SQL*Plus command prompt with the EXECUTE command like this:
SQL*Plus assures you the procedure executed successfully:
But wait a minute! We wanted to see the string "Hello World!" Where did that go? SQL*Plus is quirky. A SET command is needed before output is shown from the DBMS_OUTPUT.PUT_LINE procedure. So let's do that. At the SQL*Plus command prompt, type:
SQL*Plus remains secretive and provides no feedback. Execute your procedure again. From the SQL*Plus command prompt, type:
Now it works! SQL*Plus rewards you with:
Suppose there is a problem with our procedure. Then what? We debug it, and that's what we're going to do next. Let’s introduce a compilation error into your procedure declaration. Open your skeleton.sql file in Notepad. Replace the DBMS_OUTPUT.PUT_LINE procedure call with the NULLL statement (notice the three "l"s!), an invalid PL/SQL statement. Your program should look like this:
From SQL*Plus, open your skeleton.sql file. SQL*Plus loads the contents of your skeleton.sql file into the SQL*Plus buffer or memory area and presents the SQL*Plus command prompt:
Execute the contents of the SQL*Plus buffer. Type a front slash and press
Your procedure is compiled and saved on the database. However, SQL*Plus warns us of compilation errors:
Let’s see the compilation errors. First, we need to run two SET commands to ensure the SQL*Plus buffer does not overflow. At the SQL*Plus command prompt, type:
Again, SQL*Plus remains secretive of the result. Let's see the errors. At the SQL*Plus command prompt, type:
You should see the compilation error:
Oracle doesn't recognize the NULLL statement with the three "l"s. But Oracle won't hold it against you. Change your procedure declaration in Notepad by inserting the proper NULL statement, and follow the steps to create your procedure again on the Oracle database. What if you want to completely remove a procedure from your database? That's what we'll cover next. If you no longer want a procedure in your database, you can remove it. The SQL statement DROP PROCEDURE removes a procedure from a database. Please use caution, because the effect is permanent! DROP PROCEDURE is classified in SQL as a Data Definition Language (DDL) statement. Other examples of SQL statements in this category include CREATE, ALTER, RENAME and TRUNCATE. Oracle issues an automatic COMMIT after a DDL statement is executed. Therefore, no rollback is possible after executing a DDL statement. You need the CREATE PROCEDURE system privilege to create a procedure in your own schema. At the SQL*Plus command prompt, issue the DROP PROCEDURE SQL statement to remove your procedure called skeleton:
SQL*Plus assures us the procedure has been removed:
Now let's sum up it all up. An Oracle stored procedure is a program stored in an Oracle database. Procedures are written in the PL/SQL programming language. You can use the Notepad text editor to write a procedure. Save each procedure with a .sql file name extension. Log on to an Oracle database with Oracle's SQL*Plus tool. It submits SQL and PL/SQL statements to the Oracle database. The SQL statement CREATE OR REPLACE PROCEDURE creates, compiles and saves a procedure on an Oracle database, regardless of whether the procedure contained compilation errors or not. Run a procedure stored on an Oracle database from the SQL*Plus tool. Use the EXECUTE statement, or an anonymous PL/SQL block to call your procedure. The clause OR REPLACE from the SQL statement CREATE OR REPLACE PROCEDURE overwrites an existing stored procedure on the Oracle database. View a procedure's compilation errors with the SQL*Plus command SHOW ERRORS. Would you like to find out more? Keep reading. You can read more about Oracle stored procedures in the Oracle documentation. The following topics are covered:
cheers, |









