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). Using the TEMP table, we have to do a Join o
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

Database Startup Script-Oracle

vi db_start.sh    1 #!/bin/bash   2 ##################################   3 ##Database startup script   4 ##################################   5 #   6 #   7 ##################################   8     9 ##########################   10 ## Script Usage check   11 ##########################   12     13 Script_Usage()   14 {   15 if [[ $# -ne 1 ]] ; then   16   echo " Usage: sh db_start.sh db_name "   17   exit ;   18 fi   19 }   20     21 ###############################   22 ## Define globals   23 ##############################   24     25 Define_Globals()   26 {   27 export db_name= $1   28 export log_dir= /u01/logs   29 export LOG_FILE= $log_dir /db_start.log   30 export script_dir= /u01/scripts/database   31 export script_name= $0   32     33 export UPPER_DB_NAME= ` echo " ${db_name} " | tr ' [a-z] ' ' [A-Z] ' `   34 }  35     36 ###################################
Post a Comment
Read more