Posts

SYS_REFCURSOR

A cursor variable is a  variable  that references to a  cursor . Different from implicit and explicit cursors, a cursor variable is not tied to any specific  query . Meaning that a cursor variable can be opened for any query. The most important benefit of a cursor variable is that it enables passing the result of a query between PL/SQL programs. Without a cursor variable, you have to fetch all data from a cursor, store it in a variable e.g., a collection, and pass this variable as an argument. With a cursor variable, you simply pass the reference to that cursor. Starting from Oracle 9i, you can use  SYS_REFCURSOR, which is a predefined weak typed  REF CURSOR , to declare a weak  REF CURSOR  as follows: Eg: Declare C_Customer  SYS_REFCURSOR; Requirement: Business will send us the Data in the .CSV file and  we need to load in to the TEMP table(every time we have to create a new TEMP table and column structure and naming will be different...
Post a Comment
Read more

Oracle Database Logon Trigger

CREATING SCHEMA and OBJECTS required to capture USER successful and failed logins: Schema Creation: create user DB_SEC identified by XXXXXX; grant resource to db_sec; grant unlimited tablespace to db_sec; Table to Capture User's SUCCESSFUL login info: create table DB_SEC.LAST_LOGIN_INFO ( DBUSER Varchar2(30), OSUSER Varchar2(30), MACHINE Varchar2(64), LAST_LOGIN DATE); Table to Capture User's FAILED login info: Create table DB_SEC.FAILED_LOGIN_INFO ( DBUSER Varchar2(30), OSUSER Varchar2(30), MACHINE Varchar2(64), FAILED_TIME DATE); Whitelisting Table to allow User login: Create table DB_SEC.USER_AUTH ( DBUSER Varchar2(30), OSUSER Varchar2(30), MACHINE Varchar2(64)); DB_SEC.USER_AUTH: This table holds the user's info as below and the trigger will authenticate against this table to allow the user to login. SQL> select * from db_sec.user_auth; DBUSER          OSUSER        MACHINE -----------...
Post a Comment
Read more

PL/SQL STORED PROCEDURE for DATA Insert and Capture activity in the Log table:

Simple stored procedure to insert data into the EMP table in SCOTT schema and each successful insert will be logged into the log table( scott.procedure_logtable ) and the data in the log table  records will be purged if the  Create_timestamp of  Log table  is 180 days old by scheduling the Purge Procedure " Purge_scott_logtable"  with the schedular Job " Run_Purge_scott_logtable_job" Also, the log table will capture the user info on who changed data and from which machine the SQL was executed. LOG TABLE: SQL> Create table scott.procedure_logtable( Activity Varchar2(40), Created_by Varchar(10), Create_timestamp Date, From_machine Varchar(50) ) tablespace users; EMP TABLE: Name     Null?    Type          -------- -------- ------------  EMPNO    NOT NULL NUMBER(4)     ENAME             VARCHAR2(10)...
Post a Comment
Read more