Sunday, March 8, 2015

PRAGMA RESTRICT_REFERENCES for Restricting Package security.

PRAGMA RESTRICT_REFERENCES is an instruction or a hint to the compiler which is processed at compile time, not at runtime, it used to control the side effects of PL/SQL Subprograms. Every PL/SQL Subprograms must follow some rules in terms of transaction control and security. 


PRAGMA RESTRICT_REFERENCES mainly used in PL/SQL Package and it ensures that a subprogram does not read or write database tables or package variables. 

PRAGMA RESTRICT_REFERENCES can appear only in a package specification or object type specification. Typically, this pragma is specified for functions. If a function calls procedures, specify the pragma for those procedures also.

From Oracle 11g, PRAGMA RESTRICT REFERENCES is deprecated. Oracle recommends using DETERMINISTIC and PARALLEL_ENABLE instead of RESTRICT REFERENCES.

  • RNDS – Read No Database State. function not to read or query tables
  • RNPS – Read No Package State. function not to read or reference package variables
  • WNDS – Write No Database State. function not modify database tables
  • WNPS – Write No Package State. function not modify package variables
  • TRUST – function can be trusted not to violate one or more rules. Used only when C or JAVA routines are called from PL/SQL.
Without using pragma in package, package successfully complied but it may result runtime error, and developers need to change the code.

ORA-14551: cannot perform a DML operation inside a query

Using pragma in package, package will be successfully complied with compile time error, which leads developers need to change the code.

PLS-00452: Subprogram ‘send_emp_alloc’ violates its associated pragma
  
CREATE OR REPLACE PACKAGE pkg_transfergoods
IS
function send_emp_alloc(p_empno integer) return number;    
PRAGMA restrict_references(send_emp_alloc, RNDS, RNPS, WNDS, WNPS);
END pkg_transfergoods;
/

To apply PRAGMA restrict_references functionalities on all package functions – sub programs by using Keyword = DEFAULT to all of the sub programs inside the package. It applies (WNDS – Write No Database State to all package not modify database tables) to all database tables which are used in package.

CREATE OR REPLACE PACKAGE pkg_emp_alloc
IS
function send_emp_alloc(p_empno integer) return number;
function get_emp_name(p_empno integer) return varchar2;
PRAGMA restrict_references(DEFAULT, WNDS);
END pkg_emp_alloc;
/


What are major difference between Traditional Import-Export and Datapump Import-Export?


Traditional EXP/IMP both are separately executing.
Data Pump EXPDP and IMPDP are executed as DATAPUMP jobs. DB server processes access db files and db objects to perform EXP/IMP on respective directory objects of DB. This enforces a security model for DBAs.
Single data stream execution of  Traditional IMP/EXP.
EXPDP/IMPDP can take advantages of server’s parallel process to read or write multiple data streams simultaneously.
EXP/IMP not have very interactive command line mode.
EXPDP/IMPDP have very powerful interactive command line mode, allows user to monitor and control the process.
EXP/IMP cannot have disconnect and reconnect feature. Once started it need to be finish.
DATAPUMP jobs entirely running on server hence we can disconnect, detach and reconnect like, PAUSE and RESUME.
No data transfer between two databases in EXP/IMP.
DATAPUMP has ability to pass data between two databases over a database link, without creating a dump file on disk.
EXP/IMP uses sequential path access method.
DATAPUMP uses Direct Path access method.
EXP/IMP operates on single file.
DATAPUMP operates on group of files.
EXP/IMP can access files of server and client.
DATAPUMP exports/imports files using database directories.
Traditional EXP/IMP supports sequential media like tape drive and others.
DATAPUMP not supports on export/import from/to on Sequential media like tape drives.
EXP/IMP uses Byte by Byte mode.
DUMP Files can be Compressed, Encrypted and uses BLOCK by BLOCK mode.
EXP/IMP not supports for XML Schemas and XML Type data.
DATAPUMP Can support XML schemas and XML Type data.
EXP/IMP works with all oracle versions.
DATAPUMP works with oracle 10g or later versions.