Jun 14, 2010


The fewer side-effects a function has, the better it can be optimized within a query, particular when the PARALLEL_ENABLE or
DETERMINISTIC  hints are used. The same rules that apply to the function itself also apply to any functions or procedures that  it calls.If any SQL statement inside the function body violates a rule, you get an error at run time (when the statement is
parsed).To check for violations of the rules at compile time, you can use the compiler directive PRAGMA RESTRICT_REFERENCES.

Pragma RESTRICT_REFERENCES is a compiler directive.
It makes sure that the function maintains the purity rules and code in fuction creation satisfy the purity rules.
There are 4 values for it.WNDS,RNDS,WNPS,RNPS.

WNDS - Write No Database state

RNDS - Read No Database state

WNPS - Write No Package state

RNPS - Read No Package state

TRUST - the function body is not checked for violations of the constraints listed in the pragma

You can declare the pragma RESTRICT_REFERENCES only in a package spec or object type spec.You can specify up to four
constraints (RNDS, RNPS, WNDS, WNPS,TRUST) in any order. To call a function from parallel queries, you must specify all four
constraints. No constraint implies another.

When you specify TRUST, the function body is not checked for violations of the constraints listed in the pragma. The function
is trusted not to violate them. Skipping these checks can improve performance.
If you specify DEFAULT instead of a subprogram name, the pragma applies to all subprograms in the package spec or object
type spec (including the system-defined constructor for object types). You can still declare the pragma for individual
subprograms, overriding the default pragma.A RESTRICT_REFERENCES pragma can apply to only one subprogram declaration. A pragma that references the name of overloaded
subprograms always applies to the most recent subprogram declaration.
Typically, you only specify this pragma for functions. If a function calls procedures, then you need to specify the pragma for
those procedures as well.


This example asserts that the function BALANCE writes no database state (WNDS) and reads no package state (RNPS). That is, it does not issue any DDL or DML statements, and does not refer to any package variables, and neither do any procedures or functions that it calls. It might issue queries or assign values to package variables.

END loans;

Jun 9, 2010

ORACLE NetworkTopology

    The Oracle network architecture encompasses many components - all of which neatly corresponds to the OSI networking model (see below Figure oraclenw1.jpg). This architecture enables Oracle client and server applications to transparently communicate over protocols such as TCP/IP. The session protocol that interfaces between the applications (Oracle Call Interface, or OCI, on the client and Oracle Program Interface, or OPI, on the server) and the network layer is known as Net8 (Net9), and before that SQL*Net. Between the OCI/OPI and Net8 layer is a presentation protocol called Two-Task Common (TTC) that is responsible for character set and data type conversion differences between the client and the server. The Net8 session protocol has three components - the Net Foundation and Routing/Naming/Auth and TNS - the last two making up Protocol Support. Supported transport protocols include TCP/IP, with or without TCP, Named Pipes and Sockets Direct Protocol (SDP), which enables communication over Infiband high-speed networks. Underpinning all of this is the Transparent Network Substrate protocol, also known as TNS. The task of TNS is to select the Oracle Protocol Adapter, wrapping the communication in one of the supported transport protocols.

           Main data dictionary view of network model is USER_SDO_NETWORK_METADATA which is in the user MDSYS", where "MD" stands for "Multi Dimensional" .

There are two  types of Network


 Among Spatial Netwrok there are 3 sub classification
  a) SDO Geometry
  b) LRS Geometry
  c) Topology Geometry

A network data model for representing capabilities of objects (modeled as nodes and links) in a network.

The network data model provides PL/SQL procedures (package SDO_NET) to simplify network creation and management.
The default values for network table names, column names, and metadata are as follows
Node table name: _NODE$
Link table name: _LINK$
Path table name: _PATH$ (only if the network contains paths)
Path Link table name: _PLINK$ (only if a path table is created; contains a row for
each link in each path in the network)

 Step 1:

  , 1 -- no of hierarchy level
  , FALSE  -- directed link?
  , FALSE  -- no with cost?
 Step 2:

  Populate the node and link tables.
Step 3:

  Validate the network. select  SDO_NET.VALIDATE_NETWORK('NET_TEST') from dual ; 

During network creation if we  set cost parameter as TRUE ie :(  EXECUTE SDO_NET.CREATE_LOGICAL_NETWORK('NET_TEST', 1, FALSE , TRUE);  )
 Then in  _NODE$ table COST named  field will be created , and if  directlink parameter is set as TRUE then in _LINK$ table Biridected named field will be created .