Parameterized View in Oracle

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.

Tamim DBA's Blog

Other options to implement parameterized view are as below.

3. Table Function

A table function is a piece of procedural code that can produce a result which can be understood by the SQL engine — that is, a relation! A table function can have a parameter, so that the output relation depends on it. For all practical purposes it looks like a Parameterized view, and it is even called this in the SQL Server world.

Table Function - SQL Tips by Burleson Consulting

Since Oracle 8.0, it has been possible to select from a collection of data (usually loaded via a function) as a "virtual table". This technique became popular in the 8i timeframe thanks to Tom Kyte and numerous web forums where "SELECT FROM TABLE(CAST(plsql_function AS collection_type))" became a common technique for binding user-generated lists of data. However, as a technique for processing large volumes of data, "table functions" of this sort are limited due to their potentially large memory footprints.

pipelined functions in oracle 9i

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.

pipelined functions in oracle 9i

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;

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.

Thread: scope of global & pl/sql variable in open_form with new session

Connection pool and Sessions

Relation between Oracle session and connection pool

Please check

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

Expert oracle JDBC programming By R. M. Menon

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.

9 OCI Programming Advanced Topics

Connection Handling

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.

About Database Resident Connection Pooling

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.

Shared Server Architecture

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