Context Navigation

Useful Oracle Tidbits

Useful Oracle Tidbits

Setup a Real User (Developer)

CREATE USER <username> IDENTIFIED BY "<password>"
  DEFAULT TABLESPACE USERTBS
  TEMPORARY TABLESPACE TEMP
  QUOTA UNLIMITED ON USERTBS 
  QUOTA UNLIMITED ON USERIDX 
  PROFILE DEFAULT
GO
GRANT BIOREADER, DEVELOPER, UMLSUSER, CREATE ANY VIEW TO <username>

Refresh Read Permissions for BIOREADER and UMLSREADER

When new tables are created (including DROP-CREATE) Oracle will have no permissions for those roles as BIOREADER and UMLSREADER. One can't set permissions for an entire schema. So one has to use the generate SQL with SQL trick:

select 'GRANT SELECT ON '||owner||'.'||'"'||table_name||'"'||' TO BIOREADER'||CHR(10)||'GO'
  from dba_tables 
  where owner='AKOLETI'
    and table_name NOT LIKE '%AQUA%'
    and table_name NOT LIKE '%TEST%'
    and table_name NOT LIKE '%JAVA%'
    and table_name NOT LIKE '%JAVA%'
    and length(table_name) > 5

Then copy and paste the result and execute. The same for UMLS:

select 'GRANT SELECT ON '||owner||'.'||'"'||table_name||'"'||' TO UMLSUSER'||CHR(10)||'GO'
  from dba_tables 
  where owner='UMLS'
    and table_name NOT LIKE '%AQUA%'
    and table_name NOT LIKE '%TEST%'
    and table_name NOT LIKE '%JAVA%'
    and table_name NOT LIKE '%JAVA%'
    and length(table_name) > 5

Setup an Application User

This is how I set up an application user to access RMRS schema (or any other schema) while adding certain filters:

CREATE USER rmrsviews IDENTIFIED BY ...
	DEFAULT TABLESPACE USERTBS
	TEMPORARY TABLESPACE TEMP
	PROFILE DEFAULT
    QUOTA UNLIMITED ON USERTBS
GO
GRANT SCHEMA TO rmrsviews

select 'GRANT SELECT ON '||owner||'.'||table_name||' TO rmrsviews WITH GRANT OPTION'||CHR(10)||'GO'
  from dba_tables 
  where owner='SJOTHIRAJAH'
    and table_name NOT LIKE '%AQUA%'
    and table_name NOT LIKE '%TEST%'
    and table_name NOT LIKE '%JAVA%'
    and table_name NOT LIKE '%JAVA%'
    and length(table_name) > 5

select 'CREATE VIEW rmrsviews.'||table_name||' AS SELECT * FROM '||owner||'.'||table_name||CHR(10)||'GO'
  from dba_tables 
  where owner='SJOTHIRAJAH'
    and table_name NOT LIKE '%AQUA%'
    and table_name NOT LIKE '%TEST%'
    and table_name NOT LIKE '%JAVA%'
    and table_name NOT LIKE '%JAVA%'
    and length(table_name) > 5

CREATE USER r4iapp IDENTIFIED BY ...
	DEFAULT TABLESPACE USERTBS
	TEMPORARY TABLESPACE TEMP
	PROFILE DEFAULT
    QUOTA UNLIMITED ON USERTBS
GO
GRANT AGENT TO r4iapp

select 'GRANT SELECT ON '||owner||'.'||view_name||' TO r4iapp'||CHR(10)||'GO'
  from dba_views
  where owner='RMRSVIEWS'

select 'CREATE SYNONYM r4iapp.'||view_name||' FOR '||owner||'.'||view_name||CHR(10)||'GO'
  from dba_views
  where owner='RMRSVIEWS'

Define an Aggregate Function

String Join

Sometimes you need to create a string joining data from subordinate tuples as you do GROUP BY. This function joins it all together. Example:

SELECT SUBSTR(ingredients,LEN(' and ')) 
  FROM (
    SELECT drug, STRINGJOIN(' and '||ingredient) AS ingredients
      GROUP BY drug
  )

CREATE OR REPLACE TYPE STRINGJOINIMPL AS OBJECT (
  value VARCHAR2(4000),
  len NUMBER,
  available NUMBER,
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT StringJoinImpl) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT StringJoinImpl, value IN varchar2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate(self IN StringJoinImpl, returnValue OUT  varchar2, flags IN number) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT StringJoinImpl, ctx2 IN StringJoinImpl) RETURN NUMBER
);
GO
CREATE OR REPLACE TYPE BODY STRINGJOINIMPL IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT StringJoinImpl) RETURN NUMBER IS
  BEGIN
    sctx := StringJoinImpl(null,0,3800);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT StringJoinImpl, value IN varchar2) RETURN NUMBER IS
    len NUMBER := LENGTH(value);
  BEGIN
    IF len < self.available THEN
      self.value := self.value||value;
      self.len := self.len + len;
      self.available := self.available - len;
    ELSE
      self.value := self.value||SUBSTR(value,1,self.available-2)||'~';
      self.len := 4000;
      self.available := 0;
    END IF;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self IN StringJoinImpl, returnValue OUT varchar2, flags IN number) RETURN NUMBER IS
  BEGIN
    returnValue := SUBSTR(self.value,1,4000);
    return ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT StringJoinImpl, ctx2 IN StringJoinImpl) RETURN NUMBER IS
  BEGIN
    IF ctx2.len <= self.available THEN
      self.value := self.value||ctx2.value;
      self.len := self.len + ctx2.len;
      self.available := self.available - ctx2.len;
    ELSE
      self.value := self.value||SUBSTR(ctx2.value,1,self.available-1)||'~';
      self.len := 4000;
      self.available := 0;
    END IF;   
    RETURN ODCIConst.Success;
  END;
END;
GO
CREATE OR REPLACE FUNCTION stringjoin(value VARCHAR2) RETURN VARCHAR2
  PARALLEL_ENABLE AGGREGATE USING StringJoinImpl;
GO

Longest String

This is like MAX, but instead of the biggest number it returns the longest string.

CREATE OR REPLACE TYPE LONGESTIMPL AS OBJECT (
  value VARCHAR2(4000),
  len NUMBER,
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT LongestImpl ) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT LongestImpl, value IN varchar2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate(self IN LongestImpl, returnValue OUT  varchar2, flags IN number) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT LongestImpl, ctx2 IN LongestImpl) RETURN NUMBER
);
GO
CREATE OR REPLACE TYPE BODY LONGESTIMPL IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT LongestImpl) RETURN NUMBER IS
  BEGIN
      sctx := LongestImpl( null, -1 );
      RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT LongestImpl, value IN varchar2) RETURN NUMBER IS
    len NUMBER := LENGTH(value);
  BEGIN
    IF len > self.len THEN
      self.value := value;
      self.len := len;
    END IF;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self IN LongestImpl, returnValue OUT varchar2, flags IN number) RETURN NUMBER IS
  BEGIN
      returnValue := self.value;
      return ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT LongestImpl, ctx2 IN LongestImpl) RETURN NUMBER IS
  BEGIN
      IF ctx2.len > self.len THEN
        self.value := ctx2.value;
        self.len := ctx2.len;
      END IF;
      RETURN ODCIConst.Success;
  END;
END;
GO
CREATE OR REPLACE FUNCTION GSCHADOW.LONGEST(value VARCHAR2) RETURN VARCHAR2
  PARALLEL_ENABLE AGGREGATE USING LongestImpl;
GO

Bitset Operations

I think bitsets are great ways to store information and sometimes one needs to do aggregate bitset operations.

CREATE OR REPLACE TYPE BITUNIONIMPL                                                                          AS OBJECT (
  value NUMBER,
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT BitUnionImpl ) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT BitUnionImpl, value IN NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate(self IN BitUnionImpl, returnValue OUT  NUMBER, flags IN NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT BitUnionImpl, ctx2 IN BitUnionImpl) RETURN NUMBER
);
GO
CREATE OR REPLACE TYPE BODY BITUNIONIMPL IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT BitUnionImpl) RETURN NUMBER IS
  BEGIN
    sctx := BitUnionImpl(UTL_RAW.CAST_FROM_NUMBER(0),0);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT BitUnionImpl, value IN NUMBER) RETURN NUMBER IS
  BEGIN
    self.value := UTL_RAW.CAST_TO_NUMBER(
                    UTL_RAW.BIT_OR(
                      UTL_RAW.CAST_FROM_NUMBER(self.value),
                      UTL_RAW.CAST_FROM_NUMBER(POWER(2,value))));
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self IN BitUnionImpl, returnValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER IS
  BEGIN
    returnValue := self.value;
    return ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT BitUnionImpl, ctx2 IN BitUnionImpl) RETURN NUMBER IS
  BEGIN
    self.value := UTL_RAW.CAST_TO_NUMBER(
                    UTL_RAW.BIT_OR(
                      UTL_RAW.CAST_FROM_NUMBER(self.value),
                      UTL_RAW.CAST_FROM_NUMBER(ctx2.value)));
    RETURN ODCIConst.Success;
  END;
END;
GO

Download in other formats:

Plain Text