Oracle_SQL_Stuff




Track progress/status of your long running jobs (via DBMS_APPLICATION package

declare    modout varchar2(48);  actout varchar2(48); begin    dbms_application_info.set_module('MY_COOL_MODULE_1', 'Starting at '|| to_char(systimestamp, 'yyyymmdd_HH24:mi'));       DBMS_APPLICATION_INFO.READ_MODULE (modout, actout);       dbms_output.put_line(modout||'-'||actout);    dbms_application_info.set_module('MY_COOL_MODULE_1', 'STEP_UNO');       DBMS_APPLICATION_INFO.READ_MODULE (modout, actout);       dbms_output.put_line(modout||'-'||actout);    dbms_application_info.set_module('MY_COOL_MOD1', 'STEP_DOS');       DBMS_APPLICATION_INFO.READ_MODULE (modout, actout);       dbms_output.put_line(modout||'-'||actout);end;

oracle hash table with index


set serveroutput on;
declare
    type varchar4000_type_table is table of VARCHAR2(4000) index by PLS_INTEGER;
    l_strings  varchar4000_type_table;
    l_string   varchar2(4000);
    idx BINARY_INTEGER := 1;
   begin
   -- assign values to some strings
     l_strings(01)    := 'one';
     l_strings(02)    := 'two';
     l_strings(03)    := 'three';

   -- iterate/print the strings above
   idx := l_strings.FIRST;  -- get index of first element
   WHILE idx IS NOT NULL LOOP
   dbms_output.PUT_LINE(idx||'='||l_strings(idx)); 
   idx := l_strings.NEXT(idx);  -- get index of next element
END LOOP;
  end;
/

Rank

select JURISDICTION, JURISDICTION_NAME, CNT ROAD_SEGMENT_COUNT, RANK() OVER (PARTITION BY AGENCY ORDER BY CNT DESC) RANKfrom (select 'WIDGETS_CO' AGENCY, JURISDICTION, max(JURISDICTION_NAME) JURISDICTION_NAME, count(*) CNT from dw_rahgeo_countylog_vw group by JURISDICTION) ORDER BY RANK;

PERCENT_RANK

select count(*) from user_tables; --473
select distinct num_rows from user_tables order by num_rows; --372 rows
select table_name, num_rows from user_tables order by num_rows;
select table_name, num_rows from user_tables where num_rows>10000000; --8 rows
select table_name, num_rows from user_tables where num_rows>1000000; --53 rows

 Aggregate Form
--(rank of any table with 1 million rows)
SELECT PERCENT_RANK(1000000) WITHIN GROUP (ORDER BY num_rows DESC) PctRank FROM user_tables; --aggregate form 

 Analytic Form
--(show all ranks)
SELECT table_name, num_rows, PERCENT_RANK() OVER (ORDER BY num_rows DESC) PctRank FROM user_tables; --analytic form


Call a function using named parms (see usage below)


 create or replace FUNCTION NONWHEELPATH_Cracking_PCT_Long(  pFeetLow IN number,  pFeetMed IN number,  pFeetHigh IN number,  pRoadSegment_LenFeet IN number) RETURN numberis  PCT NUMBER;BEGIN  /* Get the percentage of Longitudinal NONWHEELPATH_Cracking for given segment.     Note: there are 5280 feet in a mile       Usage: select NONWHEELPATH_Cracking_PCT_Long(         pFeetLow=>50,          pFeetMed=>70,          pFeetHigh=>130,          pRoadSegment_LenFeet => 500)  from dual; */  PCT := (pFeetLow +pFeetMed +pFeetHigh)/(2*pRoadSegment_LenFeet *5280)*100;  return round(PCT,3);END;

Quickly generate a table containing all the calendar dates for a specified date range


create table DATES(dt date);

insert into dates

select to_date('01jan2014') + rownum -1 dt from all_objects 

where rownum <= to_date('31dec2030')-to_date('01jan2014')+1;

commit;


PLSQL (PL-SQL PL/SQL PL SQL)

create or replace function getDateKeyFromDate(p_datein date)
   RETURN number is
   /*********************************************************************************
   Author....: Michael Dockery
   Created...: 10FEB2017
   Purpose...: Return yyyymmdd or 99999999 (as this is an OBIEE date key standard)
   Usage.....: SELECT mydate, getDateKeyFromDate(mydate) mydatekey FROM mytable 
   Note......: This is the equivalent of :
               case when(m.INACTIVE_DATE is null) then to_number(to_char(m.INACTIVE_DATE,'YYYYMMDD')) 
                    else 99999999 end INACTIVE_DATE_KEY 
   **********************************************************************************/
   dateKeyNumberResult number;
  BEGIN
  IF p_datein is null THEN
   dateKeyNumberResult := 99999999;
  ELSE
   dateKeyNumberResult := to_number(to_char(p_datein, 'YYYYMMDD'));
  END IF;
   return dateKeyNumberResult;
END getDateKeyFromDate;
--Top N from each group
select * from (
  SELECT ROUTE_ALIAS||'_'||DIRECTION rte, START_MM, VEH_DELAYED_UNDER_85PCT, 
  ROW_NUMBER() OVER (PARTITION BY ROUTE_ALIAS||'_'||DIRECTION
                         ORDER BY VEH_DELAYED_UNDER_85PCT desc) rn
  FROM STG_MOBILITY_RTEMETRICS 
  where START_MM is not null and VEH_DELAYED_UNDER_45MPH>0
) where rn<=3;

--get fy from date
select                                  date '2016-07-01',
case when(EXTRACT(month FROM            date '2016-07-01')>6)
    then EXTRACT(YEAR FROM   ADD_MONTHS(date '2016-07-01', +7))
    else EXTRACT(YEAR FROM              date '2016-07-01') end fy1,
                                        date '2016-06-30',
case when(EXTRACT(month FROM            date '2016-06-30')>6)
    then EXTRACT(YEAR FROM   ADD_MONTHS(date '2016-06-30', +7))
    else EXTRACT(YEAR FROM              date '2016-06-30') end fy2
  FROM dual;



or via a simple scalar function:
create or replace FUNCTION get_SFY_from_date( pDate IN DATE) RETURN number
IS 
  sfy number;
BEGIN
  /*********************************************
   * Author...: Michael Dockery
   * Created..: 03/14/2017
   * Purpose..: Convenience function to get numeric state FY from a date column
   * Usage....: select get_SFY_from_date( sysdate ) from dual;
   *********************************************/
  sfy := 
  case when(EXTRACT(month FROM pDate)>6) 
    then EXTRACT(YEAR FROM   ADD_MONTHS(date '2016-07-01', +7))
    else EXTRACT(YEAR FROM              pDate) 
  end;
  RETURN sfy;
END;
select get_SFY_from_date( sysdate ) from dual;
select get_SFY_from_date( date '2016-06-30') from dual;
select get_SFY_from_date( date '2016-07-01') from dual;

create or replace FUNCTION FUNCTION_WITH_ERRORHANDLING( pEMAIL_ADDR IN VARCHAR2 ) RETURN VARCHAR2 AS
  user_account varchar2(100);
BEGIN  /*0000000000000000000000000000000000000000000000000000000000000000000000000000000000
           Author.......: mdockery
           Purpose......: Function to find login name from email address from table DW_PS_ACTIVE_DIR_FACT
           Permissions..: grant EXECUTE on "DWTARGET"."GET_USERACCOUNTNAME_FROM_EMAIL" to "DWSTAGE" ;
           Usage........: select GET_USERACCOUNTNAME_FROM_EMAIL('MdOCKERY1@INDOT.IN.GOV') from dual;
           Note.........: to see DBMS_OUTPUT.PUT_LINE()...  in toad or sql developer ensure you first run:  "set serveroutput on"
                          this funciton was formerly called GET_USERACCOUNTNAME_FROM_EMAIL
                       ||| Mike |||                                ____==========_______
                       | |  __  | |                     _--____   |    | ""  " "|       \
        |-|_____-----/   |_|  |_|   \-----_____|-|     /  )8}  ^^^| 0  |  =     |  o  0  |
        |_|_________{   }|  (^) |{  }__________|_|   </_ +-==B vvv|""  |  =     | '  "" "|
         ||          |_| |   ^  | |_|          ||       \_____/   |____|________|________|
         |              \|  /\  |/              |                (_(  )\________/___(  )__)
         |               \ |--| /               |                  |\  \            /  /\   
         =               \ |__| /               =                  | \  \ DOCKERY  /  /\ \       
         +               \      /               +                  | |\  \        /  /  \ \   
                          \    /                                   (  )(  )       (  \   (  )
                          \    /                                    \  / /        \  \   \  \     
                           |  |                                      \|  |\        \  \  |  |
                           |  |                                       |  | )____    \  \ \  )___     
                           |  |                                       (  )  /  /    (  )  (/  /
                           \  /                                      /___\ /__/     /___\ /__/
                          3 \/ 0               

 If you want two or more exceptions to execute the same sequence of statements:
    EXCEPTION
      WHEN invalid_number OR STANDARD.INVALID_NUMBER THEN ...

 123456789012345678901234567890000000000000000000000000000000000000000000000000000000000000000*/
  DBMS_OUTPUT.PUT_LINE( 'Getting windows login account name for email ' || pEMAIL_ADDR || ' from table DW_PS_ACTIVE_DIR_FACT');
  --THIS WORKS ALSO
  --   execute immediate 'select ACCOUNT from DW_PS_ACTIVE_DIR_FACT where upper(email) = '''|| pEMAIL_ADDR ||'''' into user_account;
  select upper(ACCOUNT) into user_account  from DW_PS_ACTIVE_DIR_FACT where upper(email) = upper(pEMAIL_ADDR);
  DBMS_OUTPUT.PUT_LINE( 'USER_ACCOUNT: ' || user_account);
  RETURN user_account;
  exception
    when NO_DATA_FOUND then
         DBMS_OUTPUT.PUT_LINE( 'Could not find login account name for email ' || pEMAIL_ADDR);
         return 'EMAIL_NOT_FOUND : '||pEMAIL_ADDR;
    when TOO_MANY_ROWS then
         DBMS_OUTPUT.PUT_LINE( 'More than one login account name found for email ' || pEMAIL_ADDR);
         return 'TOO_MANY_ROWS_FOR '|| pEMAIL_ADDR;
    WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE('------------------------');
         DBMS_OUTPUT.PUT_LINE('Error finding email:'||pEMAIL_ADDR);
         DBMS_OUTPUT.PUT_LINE(SQLERRM);
         DBMS_OUTPUT.PUT_LINE('------------------------');
         return 'SQLERRM'; --raise_application_error(-20011,'Email not found');
END FUNCTION_WITH_ERRORHANDLING;

==================================================================
https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/errors.htm#LNPLS00704

Defining Your Own PL/SQL Exceptions
  notice below the scope/nesting matters!!!!

DECLARE
   past_due EXCEPTION;
   acct_num NUMBER;
BEGIN
   DECLARE  ---------- sub-block begins
      past_due EXCEPTION;  -- this declaration prevails
      acct_num NUMBER;
     due_date DATE := SYSDATE - 1;
     todays_date DATE := SYSDATE;
   BEGIN
      IF due_date < todays_date THEN
         RAISE past_due;  -- this is not handled
      END IF;
   END;  ------------- sub-block ends
EXCEPTION
  -- Does not handle raised exception
  WHEN past_due THEN
    DBMS_OUTPUT.PUT_LINE
      ('Handling PAST_DUE exception.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE
      ('Could not recognize PAST_DUE_EXCEPTION in this scope.');
END;
/
==================================================================
https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/errors.htm#i1863

Associating a PL/SQL Exception with a Number (EXCEPTION_INIT Pragma)

To handle error conditions (typically ORA-n messages) that have no predefined name,
   you must use the OTHERS handler or the pragma EXCEPTION_INIT.

note: A pragma is a compiler directive that is processed at compile time, not at run time.

the pragma EXCEPTION_INIT tells the compiler to associate an exception name with error number.
 so you can refer to any internal exception by name and to write a specific handler for it.

When you see an error stack, or sequence of error messages, the one on top is the one that you can trap and handle.

You code the pragma EXCEPTION_INIT in the declarative part
of a PL/SQL block, subprogram, or package using the following syntax:

PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);


for example

DECLARE
   deadlock_detected EXCEPTION;
   PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
   NULL; -- Some operation that causes an ORA-00060 error
EXCEPTION
   WHEN deadlock_detected THEN
      NULL; -- handle the error
END;
/

==================================================================
Defining Your Own Error Messages (RAISE_APPLICATION_ERROR Procedure)
The RAISE_APPLICATION_ERROR procedure lets you issue user-defined ORA-n error messages from stored subprograms. That way, you can report errors to your application and avoid returning unhandled exceptions.

To invoke RAISE_APPLICATION_ERROR, use the following syntax:

raise_application_error(
      error_number, message[, {TRUE | FALSE}]);

where error_number is a negative integer in the range -20000..-20999 and message is a character string up to 2048 bytes long.

example:
DECLARE
   num_tables NUMBER;
BEGIN
   SELECT COUNT(*) INTO num_tables FROM USER_TABLES;
   IF num_tables < 1000 THEN
      /* Issue your own error code (ORA-20101)
         with your own error message. You need not
          qualify RAISE_APPLICATION_ERROR with
          DBMS_STANDARD */
      RAISE_APPLICATION_ERROR
        (-20101, 'Expecting at least 1000 tables');
   ELSE
      -- Do rest of processing (for nonerror case)
      NULL;
   END IF;
END;
/

==================================================================
Example 11-6 Using RAISE to Raise a User-Defined Exception

DECLARE
   out_of_stock   EXCEPTION;
   number_on_hand NUMBER := 0;
BEGIN
   IF number_on_hand < 1 THEN
      RAISE out_of_stock; -- raise an exception that you defined
   END IF;
EXCEPTION
   WHEN out_of_stock THEN
      -- handle the error
      DBMS_OUTPUT.PUT_LINE('Encountered out-of-stock error.');
END;
/

another example
Example 11-9 Reraising a PL/SQL Exception

DECLARE
  salary_too_high  EXCEPTION;
  current_salary NUMBER := 20000;
  max_salary NUMBER := 10000;
  erroneous_salary NUMBER;
BEGIN
  BEGIN  ---------- sub-block begins
    IF current_salary > max_salary THEN
      RAISE salary_too_high;  -- raise the exception
    END IF;
  EXCEPTION
    WHEN salary_too_high THEN
      -- first step in handling the error
      DBMS_OUTPUT.PUT_LINE('Salary ' || erroneous_salary ||
      ' is out of range.');
      DBMS_OUTPUT.PUT_LINE
        ('Maximum salary is ' || max_salary || '.');
      RAISE;  -- reraise the current exception
  END;  ------------ sub-block ends
EXCEPTION
  WHEN salary_too_high THEN
    -- handle the error more thoroughly
    erroneous_salary := current_salary;
    current_salary := max_salary;
    DBMS_OUTPUT.PUT_LINE('Revising salary from ' || erroneous_salary ||
       ' to ' || current_salary || '.');
END;
/

==================================================================
ORA-00942: table or view does not exist

SQL>  DECLARE
  2      cnt NUMBER;
  3  BEGIN
  4      SELECT Count(*)
  5      INTO   cnt
  6      FROM   user_tables
  7      WHERE  table_name = 'TBL_COMP_ASSESSMENT';
  8
  9      IF cnt = 1 THEN
 10        BEGIN
 11            EXECUTE IMMEDIATE 'DROP TABLE TBL_COMP_ASSESSMENT';
 12        EXCEPTION
 13            WHEN OTHERS THEN
 14              IF SQLCODE != -942 THEN
 15                RAISE;
 16              END IF;
 17        END;
 18      END IF;
 19  END;
 20
 21  /



create or replace FUNCTION VERIFY_OBJECT_EXISTS( pObjName IN VARCHAR2 ) RETURN VARCHAR2 AS
  cnt number;
BEGIN  /******************
  author mdockery
  purpose test for object existance
  usage: SELECT VERIFY_OBJECT_EXISTS('dual3') FROM dual;
  ******************/
  DBMS_OUTPUT.PUT_LINE( 'Getting count for ' || pObjName);
  execute immediate 'select count(*) from '||pObjName into cnt;
  --execute immediate `select `||pColName||` from `||pTbl||` where `||pColID||`=`||pIDVal into retName;
  DBMS_OUTPUT.PUT_LINE( 'all is well ');
  RETURN 'count is '||cnt;
  exception
    WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         DBMS_OUTPUT.PUT_LINE('obj not found! ' ||SQLERRM);
      end if;
      return SQLERRM;
END VERIFY_OBJECT_EXISTS;

SQL PLUS 

The -silent option can come in handy

 csv generation
set markup csv | on
SET COLSEP '|'
set feedback off
set verify   off
set echo     off
set wrap     off
SET TRIMSPOOL ON
SET pagesize 0
SET ARRAYSIZE 100

spool STG_WMSOB_ASSET_DIM.csv
clear screen
select * from STG_WMSOB_ASSET_DIM where Loc_IDENT != -1;
spool off



also
--produces a comma-separated csv file form a simple qry from a table or view
set pagesize 50000
set linesize 10000  
set trimspool on 
set underline off
set colsep ,

set feedback off


--produces a tilde-separated csv file form a simple qry from a table or view
set pagesize 50000 --50k is the max as of 12c
set linesize 10000   
set trimspool on  --remove trailing blankspaces
set underline off --remove the dashes/underlines under the col headers
set colsep ~
set feedback off

select * from DW_TMC_PROJECT_VW;

quit

 also
select 'replace the comma with comma-like unicoode char    '|| replace('doc, mike',',',unistr( '\201A' )) from dual;
Unicode!
select 'The copyright symbol is unicode hex A9 (dec 169): '|| unistr( '\00A9' ) from dual;

Oracle Objects

Create object type (and define useful functions for that object)

CREATE OR REPLACE TYPE PERSON AS OBJECT(
  person_ID number,
  first_name varchar2(100),
  last_name varchar2(100),
  dob timestamp,
  dept varchar2(100),
  email varchar2(100),
  phone varchar2(100),
  status varchar2(1),
  member function getFullName return VARCHAR2,
  MEMBER FUNCTION getAge RETURN NUMBER
);

Body

create or replace TYPE BODY Person AS
  MEMBER FUNCTION getAge RETURN NUMBER AS
  BEGIN
    RETURN Trunc(Months_Between(Sysdate, dob)/12);
  END getAge;
  MEMBER FUNCTION getfullname RETURN varchar2 AS
  BEGIN
    RETURN first_name ||' '||last_name;
  END getfullname;
END;

Use/view/create object (and call its functions)

Simple way to construct/view a dynamic instance of the object

select person(1,'f','l','27-mar-2015','d','e','317','A') from dual;

Simple way to call the awesome/useful methods/functions

select
  person(1,'f','l','27-mar-2015','d','e','317','A').first_name fnm,
  person(1,'f','l','27-mar-2015','d','e','317','A').getAge() age
from dual;

-- create a table to use oracle object

create table stg_test_people(person person);

-- insert a record with an oracle object

insert into stg_test_people values(
 person(1,'Jim','Smith','27-mar-1995','Finance','jims@us.com','3172332097','A')
);

-- view the record

select peeps.person,
       peeps.person.getfullname() nam,
       peeps.person.getage() age,
       peeps.person.email,
       peeps.person.phone
from STG_TEST_PEOPLE peeps;

-- update a record with an oracle object

update STG_TEST_PEOPLE peeps set peeps.person.phone='3172332099'

where peeps.person.email='jims@us.com';



Add/Subtract minutes or seconds

select DISTRICTNUM,
 to_char(to_Date('01jan2001'), 'DD-MON-YYYY HH24:MIss') dt,
 to_char(to_Date('01jan2001') + ((interval '1' minute) * DISTRICTNUM)  , 'DD-MON-YYYY HH24:MIss') dt_min_added, --add x minutes based on a data column value
 to_char(to_Date('01jan2001') + interval '30' second, 'DD-MON-YYYY HH24:MIss') dt_secs_added --add 30 seconds

from geo_tbl1;

SQL Developer stuff

Add SQL Server driver for accessing Microsoft DB's

http://sourceforge.net/projects/jtds/postdownload?source=dlp


Note: A bit tricky, in the port field, enter:   port/dbname;instance=instance_name
    ex:  1433/myDb;instance=myInstance
    or:   1433/myDb;    ...to connect to the default instance

Errors



Extension "EDITOR" to view "All_Errors"




Code Templates
just type the letters on the left
 and code completion will finish it for you



Modeler


Path for Exports/Imports and SQL Scripts


Cool extensions
A handy little extension i just wrote   (if you use sql developer)

<items>
  <item type="editor" node="ViewNode" vertical="true">
     <title>VIEW_ALL_ERRORS</title>
     <query><sql>SELECT LINE||'_'||POSITION LINE_POS, TEXT FROM All_Errors 
                 WHERE type = 'VIEW' AND owner = :OBJECT_OWNER AND name = :OBJECT_NAME
     </sql></query>
  </item>
</items>

After an attempt to compile a view, this extension shows a tab with any view source errors.


Avoid the annoying variable binding popup prompt
  set define off;





I found this to improve perf for my sqldeveloper (Startup and qry running)

Simply add the start and max java vm memory settings to the product.conf file  (note: I am using v17 of developer)

================================================================== 
Increase sql developer memory
 C:\Users\mdockery\AppData\Roaming\sqldeveloper\17.4.0\product.conf

change it to use min of 512K and max 2GB! (instead of max 800MB)
AddVMOption -Xms512m
AddVMOption -Xmx2048m

also to increase speed add this option (if you have many dataabases)
AddVMOption -Sqldev.tnsping=false



##############################################################################
#
# The format of this file is:
#
# Directive  Value
#
# with one or more spaces between the directive and the value. This file
# can be in either UNIX or DOS format for end of line terminators. Use UNIX
# style '/' path separators, although on Windows some directives, such as
# SetJavaHome, can take '\' path separators.
#
##############################################################################
SetJavaHome C:\Program Files\Java\jdk1.8.0_121\

AddVMOption -Xms512m
AddVMOption -Xmx2048m

AddVMOption -Sqldev.tnsping=false


Comments

Post a Comment

Popular Posts