Category Archives: Script

The next half hour

I have a job in Oracle that runs every 30 minutes (during work hours), and wanted to schedule the first run at the next “xx:30” mark on the clock. Nothing all that exciting, but took me a few minutes to figure out the logic and I thought I would share.

Line of interest from my call to the scheduler:

 start_date      =>   CEIL( (SYSDATE - trunc(SYSDATE)) * 48 )/ 48 + trunc( SYSDATE )
 

Basic demonstration of the calculation:

SQL> ALTER SESSION SET NLS_DATE_FORMAT = "MM/DD/YYYY HH:MI AM";
  
Session altered.
  
SQL> SELECT SYSDATE FROM DUAL;
  
SYSDATE
-------------------
04/02/2014 12:11 PM
  
SQL> SELECT CEIL( (SYSDATE - trunc(SYSDATE)) * 48 )/ 48 + trunc( SYSDATE ) FROM DUAL;
  
CEIL((SYSDATE-TRUNC
-------------------
04/02/2014 12:30 PM

Comparing lists in Oracle

While Oracle is incredibly powerful, there are some things it generally isn’t asked to do. One of those things is compare a list of values. You can compare recordsets, tables, strings, etc… But trying to compare two separate lists of values can be a challenge.  Other languages such has ColdFusion have quite a few LIST functions baked in, but there are times I don’t want to (or can’t) switch from Oracle to CF for processing.

Below is a package I wrote with two functions I have found I need from time to time. If you are needing them every day, you should probably re-evaluate your data model… but there are times you have to deal with the data you got.

First, declare a datatype you will need.


CREATE TYPE LIST_PACK_TYPE AS TABLE OF VARCHAR2(4000);
/

Compile the package

CREATE OR REPLACE PACKAGE LIST_PACK 
AS
  FUNCTION LIST_CONVERT_FUNC
   ( P_LIST IN VARCHAR2,
     P_DELIM IN VARCHAR2 DEFAULT ',' )
   RETURN LIST_PACK_TYPE
   PIPELINED;
 --
 FUNCTION LIST_SINGLE_MATCH_FUNC
 ( P_LIST1 IN VARCHAR2,
   P_LIST2 IN VARCHAR2,
   P_DELIM IN VARCHAR2 DEFAULT ','
 )
 RETURN NUMBER;
 --
END LIST_PACK;
/

CREATE OR REPLACE PACKAGE BODY LIST_PACK 
AS
 FUNCTION LIST_CONVERT_FUNC
 ( P_LIST IN VARCHAR2,
   P_DELIM IN VARCHAR2 DEFAULT ',' 
 )
 RETURN LIST_PACK_TYPE
 PIPELINED
 AS
   L_STRING LONG := P_LIST ||P_DELIM;
   L_DELIM_INDEX PLS_INTEGER;
   L_INDEX PLS_INTEGER := 1;
 BEGIN
 LOOP
   L_DELIM_INDEX := INSTR(L_STRING, P_DELIM, L_INDEX);
   EXIT WHEN L_DELIM_INDEX = 0;
   PIPE ROW (SUBSTR(L_STRING, L_INDEX, L_DELIM_INDEX - L_INDEX));
   L_INDEX := L_DELIM_INDEX + 1;
 END LOOP;
 RETURN;
 END LIST_CONVERT_FUNC;
 --
 FUNCTION LIST_SINGLE_MATCH_FUNC
 ( P_LIST1 IN VARCHAR2,
   P_LIST2 IN VARCHAR2,
   P_DELIM IN VARCHAR2 DEFAULT ','
 )
 RETURN NUMBER
 IS
   v_MATCHES NUMBER;
   GET_OUT EXCEPTION;
 BEGIN
   IF TRIM(P_LIST1) IS NULL AND TRIM(P_LIST2) IS NULL THEN
     v_MATCHES := 0;
     RAISE GET_OUT;
   END IF;
   SELECT COUNT(*) INTO v_MATCHES 
   FROM ( SELECT COLUMN_VALUE 
          FROM TABLE(LIST_CONVERT_FUNC(P_LIST1,P_DELIM))
          INTERSECT 
          SELECT COLUMN_VALUE 
          FROM TABLE(LIST_CONVERT_FUNC(P_LIST2,P_DELIM))
        );
   IF v_MATCHES >= 1 THEN
     RETURN 1;
   ELSE 
     RETURN 0;
   END IF;
 EXCEPTION
   WHEN GET_OUT THEN
     RETURN v_MATCHES;
 END LIST_SINGLE_MATCH_FUNC;
 --
END LIST_PACK;
/

Examples:

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','3,4,5') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','3,4,5')
-------------------------------------------------
 1

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','4,5,6') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','4,5,6')
-------------------------------------------------
 0

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','3,4,5',',') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','3,4,5',',')
-----------------------------------------------------
 1

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','4,5,6',',') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','4,5,6',',')
-----------------------------------------------------
 0

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC('1|2|3','3|4|5','|') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC('1|2|3','3|4|5','|')
-----------------------------------------------------
 1

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC('1|2|3','4|5|6','|') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC('1|2|3','4|5|6','|')
-----------------------------------------------------
 0

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC('1|2|3','4|5|6',',') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC('1|2|3','4|5|6',',')
-----------------------------------------------------
 0

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC(NULL,NULL,',') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC(NULL,NULL,',')
-----------------------------------------------
 0

Manage your SQL Plus Window

Update your glogin.sql file to contain the code below to automatically modify your sql plus window to reflect database environment and logged in user.  My glogin.sql file is located in the following directory:

C:\oracle\Ora11gR2x64\product\11.2.0\client_1\sqlplus\admin


set term off
set sqlprompt "_user > "
define sql_prompt= '&_user @ &_connect_identifier'

COLUMN host_cmd_col NEW_VALUE host_cmd

SELECT ( CASE
when upper('&_CONNECT_IDENTIFIER') = 'PROD' THEN 'COLOR F0'
when upper('&_CONNECT_IDENTIFIER') = 'DEV' THEN 'COLOR 1F'
when upper('&_CONNECT_IDENTIFIER') = 'STAGE' THEN 'COLOR 0F'
when upper('&_CONNECT_IDENTIFIER') = 'QA' THEN 'COLOR 4F'
else 'COLOR 0E'
END) host_cmd_col
FROM dual;

HOST &host_cmd
set term on
HOST title &sql_prompt

/* Color attributes are specified by TWO hex digits -- the first corresponds to the
background; the second the foreground. Each digit can be any of the below values.

0 = Black      8 = Gray
1 = Blue       9 = Light Blue
2 = Green      A = Light Green
3 = Aqua       B = Light Aqua
4 = Red        C = Light Red
5 = Purple     D = Light Purple
6 = Yellow     E = Light Yellow
7 = White      F = Bright White
*/

One source I used heavily for writing my own script:  http://steveharville.wordpress.com/2010/01/29/change-sqlplus-color-according-to-database-name/

An updated sqlprompt script

Basically, the meat of this post is this command (in sql plus):

set sqlprompt "_user '@' _connect_identifier > "

If you run the above command, your login prompt will update automatically even when you switch connections.  Previous methods had to be run manually over and over with every new connection… unless you wrote your own “connect” script.  This way… run once and it follows you throughout your session.

You can also update your glogin.sql file to contain the command above.  My glogin.sql file is located in the following directory:

$ORACLE_HOME\product\11.2.0\client_1\sqlplus\admin

An Oracle “roles and privs” script

As I mentioned in my “An Oracle ‘who’ script” (https://hagoodc.wordpress.com/2013/10/26/8/) post, I have several scripts I always drop into the oracle bin directory when I start working somewhere.

Next on the list… @rolesprivs.sql

This script quickly displays the roles and privs granted to an Oracle user. The script below displays both normal roles as well as system privs. Nothing special going here, but running @rolesprivs is quicker than running each query seperately.

</pre>
REM ########################################################################
REM ## Author : Chris Hagood
REM ##
REM ## This script will display role/priv information for the username
REM ## provided at the prompt
REM ##
REM #######################################################################

ACCEPT v_username PROMPT 'Enter USERNAME> '
set pages 100 lines 100 verify off feedback off;

col grantee heading 'USERNAME' format a15;
col granted_role heading 'Role' format a35;
col admin_option heading 'Admin|Option?' format a7
col default_role heading 'Default|Role?' format a7

break on grantee;

select grantee, granted_role, admin_option, default_role
from dba_role_privs
WHERE GRANTEE = UPPER('&v_username') or GRANTEE LIKE UPPER('%&v_username%')
order by grantee,granted_role;

COL grantee heading 'USERNAME' format a15
col privilege heading 'PRIVILEGE' format a35
col admin_option heading 'Admin|Option?' format a7

select grantee, privilege, admin_option
from dba_sys_privs
WHERE GRANTEE = UPPER('&v_username') or GRANTEE LIKE UPPER('%&v_username%')
order by grantee, privilege;

clear columns;
clear breaks;
set verify on;
set feedback on;

A couple of other notes:

  • The script doesn’t have to go in your bin directory.  You can tell SQL Plus where to look.  I just use the bin directory.
  • I have been using this script for years.  When I started, I grabbed it from somebody and have modified it for my use.  I would like to give credit to who started it… But I have no idea who they are.

Download: rolesprivs.sql

Screenshot:

rolesprivs screenshot

An Oracle “who” script

Since I am not sure where to start, I thought I would begin by posting some utility scripts that I always use in Oracle. When I start a new project, one of the first things I do is drop my scripts (starting with this one) into the bin directory.

If you work in an environment that has more than one instance… And honestly… there should always be at least a Development and Production environment; Knowing where you are before you run anything is REALLY important.  Figuring out where you are isn’t really that hard, but typing @who (my script) just makes it a little bit quicker.

The script below determines who you are connected as and where you are connected.  It then sets the SQL> prompt to reflect that information.

REM ########################################################################
REM ## Author : Chris Hagood
REM ##
REM ## This script when run will change the SQL> prompt to display
REM ## the current user logged in and the db connected to.
REM ##
REM #######################################################################

set pause off
set termout off
column the_db_name new_value the_db_name
SELECT USER||'@'||INSTANCE_NAME AS THE_DB_NAME FROM V$INSTANCE;
set sqlp "&the_db_name.> "
set termout on
set serveroutput on

A couple of other notes:

  • The scripts don’t have to go in your bin directory.  You can tell SQL Plus where to look.
  • I prefer to always run @who before I make any data/structure changes, however you could easily write your own connect script that would set the prompt every time you log in.

Download: Who.sql

Screenshot:

who screenshot