Cursors in Oracle


CURSORS:

A cursor is a mechanism by which you can assign a name to a “select statement” and manipulate the information within that SQL statement. Cursor is a pointer, which works on active set, i.e. which points to only one row at a time in the context area’s ACTIVE SET. A cursor is a construct of pl/sql, used to process multiple rows using a pl/sql block.

Implicit and Explicit cursors:

Oracle automatically declares an implicit cursor every time a SQL statement is executed. The user is unaware of this and cannot control or process the information in an implicit cursor.

We define an explicit cursor for any query that returns more than one row of data. This means that the programmer has declared the cursor within the PL/SQL code block. This declaration allows the application to sequentially process each row of data as the cursor returns it.

Difference between implicit and explicit cursors:

An explicit cursor is declared opened and fetched from in the program block where as an implicit cursor is automatically generated for SQL statements that process a single row only.

How an Implicit cursor works?

  1. Any given PL/SQL block issues an implicit cursor whenever a SQL statement is executed, as long as an explicit cursor does not exist for that SQL statement.
  2. A cursor is automatically associated with every DML statement (UPDATE, DELETE, INSERT).
  3. All UPDATE and DELETE statements have cursors that identify the set of rows that will be affected by the operation.
  4. An INSERT statement needs a place to receive the data that is to be inserted into the database; the implicit cursor fulfills this need.
  5. The most recently opened cursor is called the SQL cursor.

How an Explicit cursor works? 

The process of working with an explicit cursor consists of the following steps:                                                                                                                                                         

  1. Declaring the cursor: This initializes the cursor into memory.
  2. Opening the cursor: The declared cursor is opened, and memory is allotted.
  3. Fetching the cursor: The declared and opened cursor can now retrieve data.
  4. Closing the cursor: The declared, opened, and fetched cursor must be closed to release the memory allocation.

 Declaring a Cursor:

  • Cursor without parameters (simplest)
                          CURSOR cursor_name
                          IS  
SELECT_statement;
  • Cursor with parameters

                          CURSOR cursor_name (parameter_list)
                          IS
                          SELECT_statement;

  • Cursor with return clause

                          CURSOR cursor_name
                          RETURN field%ROWTYPE
                          IS
SELECT_statement;

 Opening the Cursor:

When a cursor is opened the active set is determined, the rows satisfying the where clause in the select statement are added to the active set. A pointer is established and points to the first row in the active set.

                          OPEN cursor_name;

Fetching the Data:

The purpose of using a cursor, in most cases, is to retrieve the rows from your cursor so that some type of operation can be performed on the data. After declaring and opening your cursor, the next step is to FETCH the rows from your cursor.

                          FETCH cursor_name INTO <list of variables>;

 Closing the Cursor:

The final step of working with cursors is to close the cursor once you have finished using it.

                         CLOSE cursor_name;

Example:

Declare

            Vno emp.empno%type;

            Vname emp.ename %type;

            Cursor emp_cursor is Select empno,ename From emp;

Begin

            Open cursor;

            For I in 1..10 loop

            Fetch emp_cursor into vno,vname;

            dbms_output.put_line(to_char(vno) ||’ ‘||vname);

            End loop;
End;

Implicit & explicit cursor attributes:

Both Implicit and Explicit cursor have 4 attributes:

Implicit Cursor:

1. %ISOPEN: The Oracle engine automatically opens and closed the SQL cursor after executing its associated select, insert, update or delete SQL statement has been processed in case of implicit cursor. Thus the SQL%ISOPEN attribute of an implicit cursor cannot be referenced outside of its SQL statement. As a result, SQL%ISOPEN always evaluates to FALSE.

2. %FOUND: Evaluates to TRUE, if an insert, update or delete affected one or more rows, or a single-row select returned one or more rows. Otherwise it evaluates to FALSE. The syntax for accessing this attribute is SQL%FOUND.

3. %NOTFOUND: It is logical opposite of %FOUND. It evaluates to TRUE, if an insert , update or delete affected no rows, or otherwise it evaluates to FALSE. The syntax for accessing this attribute is SQL%NOTFOUND.

4. %ROWCOUNT: Returns the numbers of rows affected by an insert , update or delete, or select into statement. The syntax for accessing this attribute is SQL%ROWCOUNT.

Explicit Cursor:

1. %ISOPEN: Return TRUE if cursor is open, FALSE otherwise.

2. %FOUND: Return TRUE if record was fetched successfully, FALSE otherwise.

3. %NOTFOUND: Return TRUE if record was not fetched successfully, FALSE otherwise.

4. %ROWCOUNT: Returns number of records processed from the cursor.

Example:

Begin

         Open emp_cursor;

         Loop

         Fetch when emp_cursor % rowcount &gt;10 or Emp_curor % not found;

         dbms_output.put_line(to_char(vno)||’ ‘|| vname);

         End loop;

         Close emp_cursor;
End;

Select For Update statement:

The Select For Update statement allows you to lock the records in the cursor result set. You are not required to make changes to the records in order to use this statement. The record locks are released when the next commit or rollback statement is issued.

The syntax for the Select For Update is:

                 CURSOR cursor_name
                 IS
                 select_statement
                 FOR UPDATE [of column_list] [NOWAIT];

If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.

Where Current Of statement:

If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.

The syntax for the Where Current Of statement is either:

                  UPDATE table_name
                  SET set_clause
                  WHERE CURRENT OF cursor_name;

        OR

                  DELETE FROM table_name
                  WHERE CURRENT OF cursor_name;

The Where Current Of statement allows you to update or delete the record that was last fetched by the cursor.

Few PL/SQL Cursor Exceptions:

  • Cursor_Already_Open
  • Invalid_Cursor

What is REF Cursor?

A REF CURSOR is basically a data type.  A variable created based on such a data type is generally called a cursor variable.  A cursor variable can be associated with different queries at run-time. 

To execute a multi-row query, oracle opens an unnamed work area that stores processing information. To access the information, an explicit cursor, which names the work area or, a cursor variable, which points to the work area can be used.

Whereas a cursor always refers to the same query work area,  a cursor variable can refer to a different work areas. Cursor variables are like ‘c’ pointers, which hold the memory location(address) of some object instead of the object itself.

Describe a cursor for loop

You would use a CURSOR FOR Loop when you want to fetch and process every record in a cursor. The CURSOR FOR Loop will terminate when all of the records in the cursor have been fetched.

The syntax for the CURSOR FOR Loop is:

                         FOR record_index in cursor_name
                         LOOP
                         {.statements.}
                         END LOOP;

Example:

Declare

         Cursor emp_cursor is

         Select empno,ename From emp;

         Begin

            For emp_record in emp_cursor loop

                  Dbms_output.put_line(emp_record.empno);

                  Dbms_output.put_line(emp_record.ename) 

         End loop;       

End;
Advertisements

The Runtime Process of a Oracle Form


The Runtime Process of a Oracle Form

In this article we will discuss how a Oracle Form runs in a server and how the client machines access them.

Oracle Forms applications are deployed to the web through a three-tier architecture. Application logic and the Forms Services Runtime Engine reside on the middle-tier application server. All trigger processing occurs on database and application servers, while user interface processing occurs on the Forms client. End users can run Forms Developer applications in a Web browser.

Before discussing the runtime process, lets go through the Forms Services Architecture in brief. Forms Services consists of four major components: the Forms Servlet, the Java client (Forms Client), the Forms Listener Servlet, and the Forms Runtime Engine.

Forms Services Architecture

The Forms Servlet is a Java servlet(class) that is capable of creating a dynamic HTML file in the client browser.

The Java client or Forms client(applet) is nothing but a set of Java classes that are downloaded to the client machine when any Form application is called in the client machine for the first time. This component is only responsible to display the Form in the Browser and send the user interactions to the middle tier. You do not have to deploy a separate Java applet for each application. The same generic applet is used to run any Forms Services application, regardless of its size and complexity.

The Forms Listener Servlet is a Java servlet that runs in a Web server equipped with a servlet engine, such as OC4J. The Forms Listener Servlet is in charge of managing the creation of the Forms Runtime process for each client and managing the network communications that occur between the client and its associated Forms Runtime process, through the Web server.

The Forms Runtime Engine is a process on the Application Server that is started by the Forms Listener Servlet. The Forms Runtime Engine handles all the application logic and Forms functionality and executes the code written into the application.

Users request a Form application in their Web browsers by entering a URL that points to the application. Forms Services then generates an HTML file that downloads a Java applet to the client machine. This small applet is capable of displaying the user interface of any form, while the application logic is executed on the middle tier.

The URL to invoke an application must have the following format:

http://host%5B:port%5D/forms servlet or html file[parameters] (optional portions of URL enclosed in brackets)

For Example:http://dummysite.com:8888/forms90/f90servlet

Where http is a protocol, dummysite.com is the host, 8888 is the port number and forms90/f90servlet is forms servlet alias.

We should have a Java Runtime Environment(JRE) in the client machine to run our Forms. We can use JInitiator, a plug-in that provides a JRE capable of running the Forms applet.

The below is the complete process how we can access a Form application through web:

Oracle Forms Runtime

1] The user accesses the Forms application through a URL in the web browser.

http://dummysite.com:8888/forms90/f90servlet

2] In the middle tier, Oracle HTTP Server or OC4J receives an HTTP request from the browser client and contacts the Forms Servlet.

3] The Forms Servlet dynamically creates an HTML page in the client machine containing all the information to start the Forms session.

Oracle Forms Runtime

4] The Oracle HTTP Server or OC4J downloads a generic applet to the client machine if the Form is called for the first time. The client machine caches the applet so that it can run future Forms applications without downloading it again.

5] The applet in the client machine contacts the Forms Listener Servlet in the middle tier to start the session. The Forms Listener Servlet starts an instance of the Forms Runtime Engine on the Forms Server (middle tier).

6] The Forms Listener Servlet establishes a connection with the Runtime Engine, which connects to the database if needed and loads application executable files.

Oracle Forms Runtime

7] The Forms applet displays the Form in the main window of the user’s Web browser.

8] The Forms Listener Servlet, working through OC4J or the HTTP Server, manages communication between the Forms applet and the Runtime Engine.

Anger vs. Love


Anger vs. Love

While a man was washing his new car, his 4-year-old son picked stone & scratched lines on the side of the car.

In anger, the man took the child’s hand & hit it many times, not realizing he was using a wrench.

At the hospital, the child lost all his fingers due to multiple fractures.  When the child saw his father….With painful eyes he asked ‘Dad when will my fingers grow back?’

Man was so hurt and speechless. He went back to car and kicked it a lot of times. Devastated by his own actions….. . Sitting in front of that car he looked at the scratches, child wrote ‘LOVE YOU DAD’. The next day that man committed suicide.

 Anger and Love have no limits, Choose the later to have a beautiful & lovely life….

Value of Time


Value of Time — A nice story of a father and son

A man came home from work late, tired and irritated, to find his 5-year old son waiting for him at the door.

SON: ‘Daddy, may I ask you a question?’

DAD: ‘Yeah sure, what it is?’ replied the man.

SON: ‘Daddy, how much do you make an hour?’

DAD: ‘That ‘ s none of your business. Why do you ask such a thing?’ the man said angrily.

SON: ‘I just want to know. Please tell me, how much do you make an hour?’

DAD: ‘If you must know, I make 100 an hour.’

SON: ‘ Oh, ‘the little boy replied, with his head down.’Daddy, may I please borrow 20?

‘The father was furious, ‘If the only reason you asked that is so you can borrow some money to buy a silly toy or some other nonsense, then you march yourself straight to your room and go to bed. Think about why you are being so selfish. I don’ t work hard every day for such childish frivolities.’

The little boy quietly went to his room and shut the door. The man sat down and started to get even angrier about the little boy’ s questions. How dare he ask such questions only to get some money?

After about an hour or so, the man had calmed down , and started to think: Maybe there was something he really needed to buy with that 20.00 and he really didn’t ‘ t ask for money very often.

The man went to the door of the little boy’ s room and opened the door.’Are you asleep, son?’ He asked.

‘No daddy, I’ m awake, ‘replied the boy.’ I’ve been thinking, maybe I was too hard on you earlier ‘ said the man. ‘It’s been a long day and I took out my aggravation on you. Here’s the 20.00 you asked for. ‘

The little boy sat straight up, smiling. ‘Oh, thank you daddy!’ he yelled.

Then, reaching under his pillow he pulled out some crumpled up notes. The man saw that the boy already had money, started to get angry again. The little boy slowly counted out his money, and then looked up at his father.

‘Why do you want more money if you already have some? ‘ the father grumbled.’

Because I didn ‘ t have enough, but now I do, ‘ the little boy replied.’ Daddy, I have 100 now. Can I buy an hour of your time? Please come home early tomorrow. I would like to have dinner with you. ‘

The father was crushed. He put his arms around his little son, and he begged for his forgiveness.

We should not let time slip through our fingers without having spent some time with those who really matter to us, those close to our hearts.

Think from the others side


Think from the others side–A nice story

The train has started moving. It is packed with people of all ages, mostly with the working men and women and young college guys and gals. Near the window, seated a old man with his 30 year old son. As the train moves by, the son is overwhelmed with joy as he was thrilled with the scenery outside..

” See dad, the scenery of green trees moving away is very beautiful”

This behavior from a thirty year old son made the other people feel strange about him. Every one started murmuring something or other about this son.”This guy seems to be a krack..” newly married Anup whispered to his wife.

Suddenly it started raining… Rain drops fell on the travellers through the opened window. The Thirty year old son , filled with joy ” see dad, how beautiful the rain is ..”

Anup’s wife got irritated with the rain drops spoiling her new suit.

Anup ,” cant you see its raining, you old man, if ur son is not feeling well get him soon to a mental asylum..and dont disturb public henceforth”

The old man hesitated first and then in a low tone replied ” we are on the way back from hospital, my son got discharged today morning , he was a blind by birth,last week only he got his vision, these rain and nature are new to his eyes.. Please forgive us for the inconvenience caused…”

The things we see may be right from our perspective until we know the truth. Bt when we know the truth our reaction to that will hurt even us. So try to understand the problem better before taking a harsh action.

Oracle Apps Concurrent Processing:An Introduction


This article gives an introduction of  Concurrent Processing in Oracle Application.

Concurrent Program:

An instance of an execution file, along with parameter definitions and incompatibilities. Several concurrent programs may use the same execution file to perform their specific tasks, each having different parameter defaults and incompatibilities.

Concurrent Program Executable:

An executable file that performs a specific task. The file may be a program written in a standard language, a reporting tool or an operating system language.

An execution method can be a PL/SQL Stored Procedure, an Oracle Tool such as Oracle Reports or SQL*Plus, a spawned process, or an operating system host language.

Concurrent Request:

A request to run a concurrent program as a concurrent process.

Concurrent Process:

An instance of a running concurrent program that runs simultaneously with other concurrent processes.

Concurrent Manager:

A program that processes user’s requests and runs concurrent programs. System Administrators define concurrent managers to run different kinds of requests.

There are many concurrent managers each monitoring the flow within each apps area.

But there are 3 MASTER CONCURRENT MANAGERS:

1. Internal Concurrent Manager (ICM): This is the one which monitors all other CMs

2. Standard Manager (SM) : This takes care of report running and batch jobs

3. Conflict Resolution Manager (CRM): checks concurrent program definitions for incompatibility checks.

We cannot delete a concurrent manager… but we can disable it… but it’s not recommended.

Concurrent Queue:

List of concurrent requests awaiting to be processed by a concurrent manager.

Phases and Statuses through which a concurrent request runs:

A concurrent request proceeds through three, possibly four, life cycle stages or phases: 

  • Pending                                       Request is waiting to be run
  • Running                                       Request is running
  • Completed                                   Request has finished
  • Inactive                                       Request cannot be run

Within each phase, a request’s condition or status may change.  Below appears a listing of each phase and the various states that a concurrent request can go through. 

Concurrent Request Phase and Status   

Phase: PENDING        

  • Normal: Request is waiting for the next available manager.
  • Standby: Program to run request is incompatible with other program(s) currently running.
  • Scheduled: Request is scheduled to start at a future time or date.
  • Waiting: A child request is waiting for its Parent request to mark it ready to run.   

Phase:RUNNING         

  • Normal: Request is running normally.
  • Paused: Parent request pauses for all its child requests to complete. 
  • Resuming: All requests submitted by the same parent request have completed running.  The  Parent   request is waiting to be restarted.
  • Terminating: Running request is terminated, by selecting Terminate in the Status field of the Request Details zone.

Phase:COMPLETED      

  • Normal: Request completes normally.
  • Error: Request failed to complete successfully.
  • Warning: Request completes with warnings.  For example, a report is generated successfully but fails to print.
  • Cancelled: Pending or Inactive request is cancelled, by selecting Cancel in the Status field of the Request Details zone.
  • Terminated: Running request is terminated, by selecting Terminate in the Status field of the Request Details zone.

 Phase:INACTIVE            

  • Disabled: Program to run request is not enabled.  Contact your system administrator.
  • On Hold: Pending request is placed on hold, by selecting Hold in the Status field of the Request Details zone.
  • No Manager: No manager is defined to run the request.  Check with your system administrator.

 Different execution methods of executabls:

  • FlexRpt                             The execution file is written using the FlexReport API.
  • FlexSql                             The execution file is written using the FlexSql API.
  • Host                                 The execution file is a host script.
  • Oracle Reports                  The execution file is an Oracle Reports file.
  • PL/SQL Stored Procedure   The execution file is a stored procedure.
  • SQL*Loader                      The execution file is a SQL script.
  • SQL*Plus                          The execution file is a SQL*Plus script.
  • SQL*Report                      The execution file is a SQL*Report script.
  • Spawned                          The execution file is a C or Pro*C program.
  • Immediate                       The execution file is a program written to run as a subroutine of the concurrent manager.

 Output formats of a concurrent program:

  • HTML
  • PDF
  • TEXT
  • PS (Post Script)
  • PCL(HP’s Printer Control Language)

Complete forms deployment process in Oracle Apps


Form Name: Customers.fmb
Deployed Application: Custom AR Application
Responsibility: US Receivables Super User

Step 1: COPYING FILES FROM THE SERVER

a) Copy TEMPLATE.fmb file from $AU_TOP/forms/US folder to a local directory. Also download APPSTAND.fmb file and place it in D:\DevSuiteHome_1\forms

b) Copy All PL/SQL Libraries from $AU_TOP/resource/US folder (one time only). Download all the .pll and .plx files to D:\DevSuiteHome_1\forms

Step 2: DESIGN THE FROM IN ORACLE FORMS BUILDER

a) Remove the Defaults
                        >Open Oracle Forms Builder
                        >Open the form TEMPLATE.fmb
                        >Rename the form (ex XXARCUST_1)
                        >Delete the followings from object nevigator.
                                    >Go to Data Blocks and delete BLOCKNAME, DETAILBLOCK
                                    >Go to Windows and delete BLOCKNAME
                                    >Go to Canvases and delete BLOCKNAME
b) Create a new Window (ex WINDOW10)
c) Create a new Canvas (ex CANVAS10) and attached it to the new Window via Property Palate
d) Create a new Datablock (ex CUSTOMERS) wth items  from a table.
e) Create a frame in the Canvas and attached the items
f) Modify the PRE-FORM Trigger
                        >Go to triggers—PREFORM
                        >Original Code:
                    

FND_STANDARD.FORM_INFO(‘$Revision: 120.0 $’, ‘Template Form’, ‘FND’,
‘$Date: 2005/05/06 23:25  $’, ‘$Author: appldev $’);
 app_standard.event(‘PRE-FORM’);
 app_window.set_window_position(‘BLOCKNAME’, ‘FIRST_WINDOW’);
                       
                        >Modified Code:
                       
  FND_STANDARD.FORM_INFO(‘$Revision: 1.0 $’, ‘XXARCUST_1’,’CUST_FORM’,
  ‘$Date: 2010/01/06 16:25  $’, ‘$Author: Dibyajyoti $’);
   app_standard.event(‘PRE-FORM’);
    app_window.set_window_position(‘WINDOW10’, ‘FIRST_WINDOW’);
                       
                        > Compile the code

g) Modification for Program unit
                        > Go to APP_CUSTOM*(Package Body)
                        >Type your First window name in place of <your first window>
                        > Compile the code
h) If any Item in the Datablock is of Date type and you want to attach a standard calender to it
    (ex Orderdate item of Customers datablock), do the following
                        > Go to Orderdate > Property palate > Subclass Information > Property Class
                        > Give property class name as TEXT_ITEM_DATE
                        > Attach LOV as ENABLE_LIST_LAMP
                        > Create KEY-LISTVAL item level trigger & add following code into it
                           calendar.show; and compile the trigger.

Step 3: DEPLOY THE FORM IN THE SERVER

                         > Upload your .fmb file

Step 4: CREATE THE .fmx FILE IN THE SERVER

>Type the code
$ORACLE_HOME/bin/frmcmp_batch module=$XXAR_TOP/forms/US/CUSTOMERS.fmb         userid=<username>/<password>  output_file=$XXAR_TOP/forms/US/ CUSTOMERS.fmx module_type=form  compile_all=special

Step 5: REGISTARING THE FORM IN ORACLE APPS

a) Registaring the FORM in Oracle Apps
                        > Go to Application Developer  —> Application —> Form
                        > Give the details:
                       
                         FORM: CUSTOMERS(name of the custom form)
                         APPLICATION: Custom AR Application
                         User Form Name: CUSTOMERS_DETAIL
                         Description: Customer Detail form
                        > save

b) Registaring the FORM to a form function
                        > System Administrator —->Application —-> Function
                        > Give the details:

                         Form—->form:CUSTOMERS_DETAIL
                         Function:XX_CUSTOMER_DETAIL
                         APPLICATION: Custom AR Application
                         Properties —> Type: Form
                         User Function Name: CUSTOMERS_DETAIL_FUNCTION

                        > save
c) Finding the menu to which the above form function is to be attached. Again the menu is attached to a responsibilty. So we have go in the reverse order to find the menu name.
                        > System Administrator —->Security —-> Responsibility —->Define
                        > Press F11
                        > Responsibility Name: %US%Rec (searching for US Receivables Super User)
                        > Press Ctrl + F11

                        > This things will come in the form
                         Responsibility Name: US Receivables Super User
                          Application:Receivables (will come automatically )

                        >Take the Menu name   —>AR_NAVIGATE_GUI
                       
d) Attaching the function to a Submenu of the above Main menu
                        > System Administrator —->Application —> Menu
                        > Press F11
                        > Menu: AR_NAVIGATE_GUI
                        > Press Ctrl+F11

                        > Promt: Custom Interfaces
                        > Submenu: XXAR_CUSTOM
                       

                        >System Administrator —->Application —> Menu
                        > Press F11
                        > Menu: XXAR_CUSTOM
                        > Press Ctrl+F11
                        > Create a new
                        > Promt: Customer Detail Form (It will display in the nevigator)
                        >Function: CUSTOMERS_DETAIL_FUNCTION (Attach the function to the submenu)
                        > save
                        >One request has been submitted to recompile your menus in the database
e) Viewing the request submitted in the background to recompile the menus to attach the function
                        > Go to View——–>Requests———>Find
                        >The status should be :Compiled  Normal

Step 6: VIEWING THE FORM IN ORACLE APPLICATION:

                        >Go to Oracle apps front end.
                        >Login with username and password
                        >Go to US Receivables Super User—>Custom Interfaces —>Customer Detail Form

Views:Basic Concepts


Views
1.A view is a predefined query on one or more tables.
2.Retrieving information from a view is done in the same manner as retrieving from a table.
3.With some views you can also perform DML operations (delete, insert, update) on the base tables.
4.Views don’t store data, they only access rows in the base tables.
5.user_tables, user_sequences, and user_indexes are all views.
6.View Only allows a user to retrieve data.
7.view can hide the underlying base tables.
8.By writing complex queries as a view, we can hide complexity from an end user.
9.View only allows a user to access certain rows in the base tables.

Advantages of Views

• To restrict data access
• To make complex queries easy
• To provide data independence
• To present different views of the same data

Creating and Using a View

You create a view using CREATE VIEW , which has the following simplified syntax:

CREATE [OR REPLACE][{FORCE | NOFORCE}] VIEW view_name
[(alias_name[, alias_name...])] AS subquery
[WITH {CHECK OPTION | READ ONLY} CONSTRAINT constraint_name];

where
1.OR REPLACE specifies the view is to replace an existing view if present.
2.FORCE specifies the view is to be created even if the base tables don’t exist.
3.NOFORCE specifies the view is not to be created if the base tables don’t exist; NOFORCE is the default.
4.alias_name specifies the name of an alias for an expression in the subquery.
5.There must be the same number of aliases as there are expressions in the subquery.
6.subquery specifies the subquery that retrieves from the base tables.
7.If you’ve supplied aliases, you can use those aliases in the list after the SELECT clause.
8.WITH CHECK OPTION specifies that only the rows that would be retrieved by the subquery can be inserted, updated, or deleted.
9.By default, rows are not checked that they are retrievable by the subquery before they are inserted, updated, or deleted.
10.constraint_name specifies the name of the WITH CHECK OPTION or READ ONLY constraint.
11.WITH READ ONLY specifies that rows may only read from the base tables.

There are two basic types of views:

Simple views:
–Derives data from only one table
–Contains no functions or groups of data
–Can perform DML operations through the view

Complex views:
–Derives data from many tables
–Contains functions or groups of data
–Does not always allow DML operations through the view

Creating a View with a CHECK OPTION Constraint

You can specify that DML operations on a view must satisfy the subquery by adding a CHECK OPTION constraint to the view.

CREATE VIEW myView AS
   SELECT *
   FROM employee
   WHERE id  INSERT INTO myView (id) VALUES (0);

1 row created.

INSERT INTO myView (id) VALUES (7);
INSERT INTO myView (id) VALUES (7)
            *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

Creating a View with a READ ONLY Constraint

You can make a view read only by adding a READ ONLY constraint to the view.

CREATE VIEW myView AS
  SELECT *
  FROM employee
  WITH READ ONLY CONSTRAINT my_view_read_only;

View created.

INSERT INTO myView (id) VALUES (1);
INSERT INTO myView (id) VALUES (1)
                    *
ERROR at line 1:
ORA-01733: virtual column not allowed here

Performing an INSERT Using a View

You can only perform DML operations with simple views.Complex views don’t support DML.

CREATE VIEW employee_view AS
  SELECT id, first_name, last_name
  FROM employee;

View created.

INSERT INTO employee_view (id, first_name, last_name) VALUES (
                             13, 'New','Western');

1 row created.

1 row is created in the employee table.

Modifying a View

You can completely replace a view using CREATE OR REPLACE. You can alter the constraints on a view using ALTER VIEW.

CREATE VIEW myView AS
  SELECT *
  FROM employee
  WITH READ ONLY CONSTRAINT my_view_read_only;

View created.

ALTER VIEW myview
DROP CONSTRAINT my_view_read_only;

View altered.

Create a complex view with group by and having clause

CREATE VIEW myview AS
  SELECT city, AVG(salary) average_salary
  FROM employee
  GROUP BY city
  HAVING AVG(salary)>50000;

Removing a View
You can remove a view without losing data because a view is based on underlying tables in the database.

DROP VIEW myview;

Inline Views

• An inline view is a subquery with an alias (or correlation name) that you can use within a SQL statement.
• A named subquery in the FROM clause of the main query is an example of an inline view.

Materialized Views

A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses “master tables” for consistency. The databases containing the master tables are called the master databases.

When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.

Oracle Forms..An Introduction and Brief History


Oracle Forms…What is it?

•A software product for creating screens that interact with an Oracle Database.
•Has a typical IDE including an object navigator, property sheet and code editor that uses PL/SQL.
•Primary focus of Forms is to create data entry systems that access an Oracle database.
•Originally sold as standalone product , now bundled into a package suite called Oracle Developer Suite.

History

•Interactive Application Facility (IAF)
-Block Mode
-Oracle Database 2

•FastForms
-Character Mode
-Oracle Database 4

•SQL*Forms version 2, 2.3
-Character Mode
-Oracle Database 5

•SQL*Forms 3
-Character Mode
-Oracle Database 6
-New IDE, PL/SQL

•Oracle Forms 4.0/4.5
-4.0 was the first “true” GUI based version
-Oracle Database 6/7
-Optimized for client server.
-4.0 interface was slow, buggy and un-popular with client base.
-4.5 was a popular one with enhanced GUI.

•Oracle Forms 5
-Oracle Database 7

•Oracle Forms 6/6i
-Forms server and web forms were introduced.
-Oracle Database 8/8i

•Forms 7 to 8 did not exist. These numbers were jumped over in order to allow the Oracle Forms version number to match the database version.

•Oracle Forms 9i
-Oracle Database 9i
-Stable one with many bug fixes.
-Three-tier, browser based delivery

•Oracle Forms 10g
-Oracle Database 10g
-Actually a Forms release (9.0.4.0.19)

•Oracle Forms 11g
-Oracle Database 11g
-Latest One.

The Rain


The Rain

The Rain

I will remember you in these rainy days
And will wet alone…
Will express my joy welcoming the new season.
today I miss your presence
… miss those walking we made through these streets..
….miss your face in those banners surrounding my town..
….miss all the slogans in the walls.

I will thank you for that…
you once stopped these rains from entering into my town with your umbrella..
I was getting the sweetest touch of my dream under it..
If you are pretending at that time..
If you are showing me the wrong path..
If you are laughing silently hearing the words of my heart..
Wish those rains will never come again..
Good bye those rains…Good bye those rainy days.

I know all the paths to become happy are closed now
Happiness will never come my way any more
..because of the torture your feelings give me
..because of the loneliness exists in every corner of my heart
Your absence will make me sad forever…

Today I am the lonely stranger of this sad town..
Will welcome the rainy days and remember you under my umbrella..
removing the happiness of wetting in the rains from my minds
Actually those mad rainy days no longer belongs to my town.
They went with you…in the search of a new umbrella!