Techie: Sabarinathan Arthanari

The primary purpose of a Parameterized View lets you retrieve PL/SQL record collection structures in a SQL context. As there is nothing like parameterized view in Oracle, but you can use parameterize view in oracle following ways

1. You can use the User Environment variable to manage a session variable (dbms_application_info package),

2. another way is write a package to get and set value parameterize view in the global variable or sys_context, later on a view can use the value from, which called so called parameterized view.

http://tamimdba.wordpress.com/2010/04/10/parameterized-view-in-oracle/

Other options to implement parameterized view are as below.

3. Table Function

4. Pipelined functions in oracle 9i

In 9i Release 1 (9.0), Oracle has introduced pipelined table functions (known simply as pipelined functions). These build on the table function principle but with some critical differences, three of which stand out in particular:

• first, data is piped (or streamed) to the caller in small arrays of prepared data, rather than fully materialised as with original table functions;

• second, pipelined functions can be parallelised by Oracle which means that PL/SQL can be executed by multiple slaves for the first time; and

• third, pipelined functions make it easy to convert PL/SQL procedures into rowsources for bulk SQL operations, combining complex transformation logic with the benefits of SQL.

This article introduces the fundamentals of pipelined functions, starting with a simple example.

http://www.oracle-developer.net/display.php?id=207

Cons of PL/SQL Table & Pipelined table functions

There’s a nightmare of strong opinion about the term PL/SQL Table because their more aptly called index-by or Associative Array collections. Unfortunately, it was Oracle’s documentation that gave the community the term. Its unfortunate that it stuck because it can be misleading. A PL/SQL Table isn’t a table in any sense of the word because it doesn’t have a SQL context. A PL/SQL table, index-by table, or Associative Array is a two-dimensional memory structure in the SGA, and it is accessible in a PL/SQL scope or through external programming languages that use the OCI8 libraries.

Pipelined table functions are slower than other approaches, but they are easy to implement. That’s why a number of folks use them. The alternative to a pipelined function is typically a complex query.

We selected the method of using Session Variables in Package method after the analysis of the following,

1. Variables in Package are DB session proof & concurrent proof.

2. The output will be direct SQL query result

3. We can use it both in SQL and PL/SQL

Here is How to Unpersist Your Persistent PL/SQL Package Data

How do I declare session variables ?

You create a package level variable. This is a minimal example:

CREATE OR REPLACE PACKAGE my_package
AS
    FUNCTION get_a RETURN NUMBER;
END my_package;
/
CREATE OR REPLACE PACKAGE BODY my_package
AS
    a  NUMBER(20);
    FUNCTION get_a
    RETURN NUMBER
    IS
    BEGIN
      RETURN a;
    END get_a;
END my_package;
/

If you do this you should read up on (and handle correctly) ORA-04068 errors. Each database session will have it’s own value for a. You can try this with:

SELECT my_package.get_a FROM DUAL;

http://stackoverflow.com/questions/301369/pl-sql-how-do-i-declare-session-variables

The scope of a db-package variable is per database-session. So different forms-session or even different DB-session inside one forms-session will not "overwrite" each other.

http://forums.oracle.com/forums/thread.jspa?threadID=1043252&tstart=270

Connection pool and Sessions

http://stackoverflow.com/questions/1039637/relation-between-oracle-session-and-connection-pool

Please check

• Web Applications and connection related challenges section
• OCI Connection pool section

http://books.google.com/books?id=9MsOpXrElhUC&pg=PA529&lpg=PA529&dq=oracle%2B%22sessions%2Bvs%2Bconnections%22&source=bl&ots=mBwnWKBDQl&sig=XPkoqteZ%5FB%5FC8NT1tHh4rS4UKwI&hl=en&ei=s2BCSoKFHI3SMNbyvMAH&sa=X&oi=book%5Fresult&ct=result&resnum=1

PHP & Database Resident Connection Pooling

Oracle has several transaction monitor capabilities such as the fine-grained management of database sessions and connections. This is done by separating the notion of database sessions (user handles) from connections (server handles). Using these OCI calls for session switching and session migration, it is possible for an application server or transaction monitor to multiplex several sessions over fewer physical connections, thus achieving a high degree of scalability by pooling of connections and back-end Oracle server processes.

http://www.di.unipi.it/~ghelli/didattica/bdldoc/B19306_01/appdev.102/b14250/oci09adv.htm

http://php.net/manual/en/oci8.connection.php

DRCP is especially relevant for architectures with multi-process single threaded application servers (such as PHP/Apache) that cannot perform middle-tier connection pooling. The database can still scale to tens of thousands of simultaneous connections with DRCP.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/manproc002.htm

Overview of User Processes

When a user runs an application program (such as a Pro*C program) or an Oracle tool (such as Enterprise Manager or SQL*Plus), Oracle creates a user process to run the user’s application.

Connections and Sessions

Connection and session are closely related to user process but are very different in meaning.

A connection is a communication pathway between a user process and an Oracle instance. A communication pathway is established using available interprocess communication mechanisms (on a computer that runs both the user process and Oracle) or network software (when different computers run the database application and Oracle, and communicate through a network).

A session is a specific connection of a user to an Oracle instance through a user process. For example, when a user starts SQL*Plus, the user must provide a valid user name and password, and then a session is established for that user. A session lasts from the time the user connects until the time the user disconnects or exits the database application.

Multiple sessions can be created and exist concurrently for a single Oracle user using the same user name. For example, a user with the user name/password of SCOTT/TIGER can connect to the same Oracle instance several times.

In configurations without the shared server, Oracle creates a server process on behalf of each user session. However, with the shared server, many user sessions can share a single server process.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/process.htm#i19036

Server and Client Sessions

A server session manages the server side of client/server communications, providing shared resources, including a shared object cache and connection pools to a single data source.

A client session is a client-side communications mechanism that works together with the server session to provide the client/server connection. You acquire client sessions from a server session at run time as required. By default, a client session shares the session cache of its parent server session. Each client session serves one client. A client session communicates with the server session on behalf of the client application.

Each client session can have only one associated server session, but a server session can support any number of client sessions.

http://download.oracle.com/docs/cd/B25221_04/web.1013/b13593/sesun003.htm#i1123047

CodeProject