Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

Space Saved With Exadata Hybrid Columnar Compression

Posted by Tyler Muth on June 8, 2010

I’ve been working with Exadata lately so I thought I’d share some numbers on HCC and Advanced Compression using ALL_OBJECTS.  This is a virtualized environment so I don’t have any real performance numbers to post, but the compression ratio is still impressive.  Most people think of compression in terms of saving space, but it also has a performance benefit for large table scans since it reduces physical IO.

create table all_objs as select a.* from all_objects a;

create table all_objs_compressed_oltp compress for all operations as select a.* from all_objects a;

create table all_objs_compressed_query compress for query as select a.* from all_objects a;

create table all_objs_compressed_archive compress for archive as select a.* from all_objects a;


select segment_name,round(bytes/1024/1024,3) size_mb
  from user_segments 
 where segment_name like 'ALL_OBJS%'
 order by 2 desc nulls last;

SEGMENT_NAME                 SIZE_MB                
---------------------------- ---------------------- 
ALL_OBJS                     7                      
ALL_OBJS_COMPRESSED_OLTP     2                      
ALL_OBJS_COMPRESSED_QUERY    0.375                  
ALL_OBJS_COMPRESSED_ARCHIVE  0.375 

So, for Advanced / OLTP Compression (compress for all operations) we achieve a compression factor of 3.5x. For HCC our compression factor jumps to 18.7x! Extrapolating a bit, a 1 TB table becomes a 55 GB table! Not sure why the compression ratio for Query and Archive is the same…

Posted in Exadata, Oracle | 1 Comment »

Logger 1.3.0 Released

Posted by Tyler Muth on April 16, 2010

I just posted version 1.3.0 of Logger. This version includes the following:

  • Fixed major flaw in time calculation used in time_start/time_stop
  • Changed implementation of LOG_APEX_ITEMS to use the APEX views so explicit privs on wwv_flow_data are not required.

Enjoy!

Posted in PLSQL | 8 Comments »

jQuery Autocomplete for APEX – Update 1.2

Posted by Tyler Muth on March 16, 2010

A while back I modified this outstanding  jQuery Autocomplete plugin to work with APEX. Lately there’s been a lot of interest in a jQuery autocomplete plugin on the APEX forum so I put together a few examples and cleaned up the code a bit. Here’s how it works:

  • When the page is loaded the plugin adds an event listener to one or more items specified by jQuery selector.  For example $(‘P1_SEARCH’).autocomplete… will enable autocomplete for the P1_SEARCH item.
  • As a user enters text into this item, the plugin sends the text back to an APEX Application Process as an http get request.
  • The Application Process queries a table using the text it receives in one or more query predicates.
  • The process sends back a pipe (|) delimited list of results via htp.p
  • The user can use one or more columns from the result to populate one or more items on the page.

Take a look at the APEX examples here.  You should also explore the original author’s examples here as he demonstrates a lot more functionality.  The APEX version is capable of producing any of those examples as well.

Update: As requested in the comments, I’ve added parameters x02 – x10 to allow extra page items to be passed back to the application process. I’ve updated page 2 and the following example code to show this.

Here is the JavaScript in the page header of the “Multiple Fields” example:

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<script type="text/javascript" src="#WORKSPACE_IMAGES#jquery.autocompleteApex1.1.js"></script>
<link rel="stylesheet" type="text/css" href="#WORKSPACE_IMAGES#jquery.autocomplete.css" />

<script type="text/javascript">

$(document).ready( function() {
    $("#P2_SEARCH").autocomplete('APEX', {
            apexProcess: 'EMPLOYEES_EXTENDED',
            width: 400,
            multiple: false,
            matchContains: true,
            cacheLength: 1,
            max: 100,
            delay: 150,
            minChars: 1,
            matchSubset: false,
            x02: 'foo',
            x03: $('#P2_DEPARTMENT').val()
        });
    // the following statement sends the result to multiple items on the page.
    $("#P2_SEARCH").result(function(event, data, formatted) {
        if (data){
            $("#P2_FIRST_NAME").val(data[1]);
            $("#P2_LAST_NAME").val(data[2]);
            $("#P2_DEPARTMENT").val(data[3]);
            $("#P2_EMAIL").val(data[4]);
        }
    });
});

</script>

And here’s the application process that drives the search results. Note the 2 alter session statements to make the search case-insensitive:

-- This Application Process is named EMPLOYEES_EXTENDED
declare
	l_search varchar2(255);
begin
    execute immediate 'alter session set NLS_SORT=BINARY_CI';
    execute immediate 'alter session set NLS_COMP=LINGUISTIC';

    l_search := replace(wwv_flow.g_x01,'*','%');

    for c1 in (select first_name||' '||last_name name,last_name,first_name,department_name,email
                 from employees_rollup
                where first_name like '%'||l_search||'%'
                   or last_name like '%'||l_search||'%')
    loop
        htp.p(c1.name||'|'||c1.first_name||'|'||c1.last_name||'|'||c1.department_name||'|'||c1.email);
    end loop;
end;

Update: If you want the loading animation, you need to upload the image and add the following in the head of your page template. Thanks Dean Attewell for commenting on this omission.

<style type="text/css">
.ac_loading {
	background: white url('#WORKSPACE_IMAGES#indicator.gif') right center no-repeat;
}
</style>

You can download the updated version 1.2 JavaScript, CSS, and image files here.

Posted in JavaScript / AJAX | 28 Comments »

Logger 1.2.2 Released

Posted by Tyler Muth on February 19, 2010

I justed commited Logger 1.2.2 which addresses several bugs in the previous release. The change log at the bottom of the project page has more details. Thanks to John Flack and Bill Wheeling for taking the time to find and “log” these issues.

Posted in PLSQL | 6 Comments »

Employees Sample Data Simplified

Posted by Tyler Muth on February 16, 2010

I use the HR schema sample data for a lot of examples or when I just need to work through a problem.  It’s great data, but many times I want to use the EMPLOYEES table without joining in DEPARTMENTS and JOBS as well as a self join of EMPLOYEES to get the manager name.  So, I used the following CTAS to create a simplified EMPLOYEES_ROLLUP table. I then exported the data as insert statements using SQL Developer (love that feature).

create table employees_rollup as
select e.employee_id,e.first_name, e.last_name, lower(e.email)||'@nowhere.com' email, e.phone_number, e.hire_date,e.salary,
       j.job_title,
       e2.first_name ||' '|| e2.last_name manager,e.manager_id,
       d.department_name
  from hr.employees e, hr.departments d, hr.employees e2, hr.jobs j
 where e.department_id = d.department_id
   and e.manager_id = e2.employee_id
   and e.job_id = j.job_id
/

The result is:

select * from employees_rollup;

EMPLOYEE_ID            FIRST_NAME           LAST_NAME                 EMAIL                                 PHONE_NUMBER         HIRE_DATE                 SALARY                 JOB_TITLE                           MANAGER                                        MANAGER_ID             DEPARTMENT_NAME
---------------------- -------------------- ------------------------- ------------------------------------- -------------------- ------------------------- ---------------------- ----------------------------------- ---------------------------------------------- ---------------------- ------------------------------
200                    Jennifer             Whalen                    jwhalen@nowhere.com                      515.123.4444                  17-SEP-87                 4400                   Administration Assistant            Neena Kochhar                                  101                    Administration
201                    Michael              Hartstein                 mhartste@nowhere.com                  515.123.5555         17-FEB-96                 13000                  Marketing Manager                   Steven King                                    100                    Marketing
202                    Pat                  Fay                       pfay@nowhere.com                      603.123.6666         17-AUG-97                 6000                   Marketing Representative            Michael Hartstein                              201                    Marketing

Unroll the following code block to the full DDL including insert statements (hint: you can use “View Source” or “Copy to Clipboard” on the overlay panel to save it locally):

create table employees_rollup (
	employee_id number(6,0), 
	first_name varchar2(35), 
	last_name varchar2(35), 
	email varchar2(100), 
	phone_number varchar2(20), 
	hire_date date, 
	salary number(8,2), 
	job_title varchar2(50), 
	manager varchar2(70), 
	manager_id number(6,0), 
	department_name varchar2(50) not null enable, 
	 constraint employees_rollup_pk primary key (employee_id) enable
)
/

create index employees_rollup_idx1 on employees_rollup(first_name,last_name)
/
create index employees_rollup_idx2 on employees_rollup(department_name)
/
create index employees_rollup_idx3 on employees_rollup(hire_date)
/
create index employees_rollup_idx4 on employees_rollup(employee_id,manager_id)
/
 

Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (200,'Jennifer','Whalen','jwhalen@nowhere.com','515.123.4444',to_timestamp('17-SEP-87 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),4400,'Administration Assistant','Neena Kochhar',101,'Administration')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (201,'Michael','Hartstein','mhartste@nowhere.com','515.123.5555',to_timestamp('17-FEB-96 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),13000,'Marketing Manager','Steven King',100,'Marketing')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (202,'Pat','Fay','pfay@nowhere.com','603.123.6666',to_timestamp('17-AUG-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),6000,'Marketing Representative','Michael Hartstein',201,'Marketing')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (115,'Alexander','Khoo','akhoo@nowhere.com','515.127.4562',to_timestamp('18-MAY-95 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),3100,'Purchasing Clerk','Den Raphaely',114,'Purchasing')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (116,'Shelli','Baida','sbaida@nowhere.com','515.127.4563',to_timestamp('24-DEC-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2900,'Purchasing Clerk','Den Raphaely',114,'Purchasing')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (117,'Sigal','Tobias','stobias@nowhere.com','515.127.4564',to_timestamp('24-JUL-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2800,'Purchasing Clerk','Den Raphaely',114,'Purchasing')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (118,'Guy','Himuro','ghimuro@nowhere.com','515.127.4565',to_timestamp('15-NOV-98 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2600,'Purchasing Clerk','Den Raphaely',114,'Purchasing')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (119,'Karen','Colmenares','kcolmena@nowhere.com','515.127.4566',to_timestamp('10-AUG-99 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2500,'Purchasing Clerk','Den Raphaely',114,'Purchasing')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (114,'Den','Raphaely','drapheal@nowhere.com','515.127.4561',to_timestamp('07-DEC-94 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),11000,'Purchasing Manager','Steven King',100,'Purchasing')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (203,'Susan','Mavris','smavris@nowhere.com','515.123.7777',to_timestamp('07-JUN-94 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),6500,'Human Resources Representative','Neena Kochhar',101,'Human Resources')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (198,'Donald','OConnell','doconnel@nowhere.com','650.507.9833',to_timestamp('21-JUN-99 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2600,'Shipping Clerk','Kevin Mourgos',124,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (199,'Douglas','Grant','dgrant@nowhere.com','650.507.9844',to_timestamp('13-JAN-00 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2600,'Shipping Clerk','Kevin Mourgos',124,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (141,'Trenna','Rajs','trajs@nowhere.com','650.121.8009',to_timestamp('17-OCT-95 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),3500,'Stock Clerk','Kevin Mourgos',124,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (142,'Curtis','Davies','cdavies@nowhere.com','650.121.2994',to_timestamp('29-JAN-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),3100,'Stock Clerk','Kevin Mourgos',124,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (143,'Randall','Matos','rmatos@nowhere.com','650.121.2874',to_timestamp('15-MAR-98 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2600,'Stock Clerk','Kevin Mourgos',124,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (144,'Peter','Vargas','pvargas@nowhere.com','650.121.2004',to_timestamp('09-JUL-98 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2500,'Stock Clerk','Kevin Mourgos',124,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (137,'Renske','Ladwig','rladwig@nowhere.com','650.121.1234',to_timestamp('14-JUL-95 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),3600,'Stock Clerk','Shanta Vollman',123,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (138,'Stephen','Stiles','sstiles@nowhere.com','650.121.2034',to_timestamp('26-OCT-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),3200,'Stock Clerk','Shanta Vollman',123,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (139,'John','Seo','jseo@nowhere.com','650.121.2019',to_timestamp('12-FEB-98 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2700,'Stock Clerk','Shanta Vollman',123,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (140,'Joshua','Patel','jpatel@nowhere.com','650.121.1834',to_timestamp('06-APR-98 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2500,'Stock Clerk','Shanta Vollman',123,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (133,'Jason','Mallin','jmallin@nowhere.com','650.127.1934',to_timestamp('14-JUN-96 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),3300,'Stock Clerk','Payam Kaufling',122,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (134,'Michael','Rogers','mrogers@nowhere.com','650.127.1834',to_timestamp('26-AUG-98 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2900,'Stock Clerk','Payam Kaufling',122,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (135,'Ki','Gee','kgee@nowhere.com','650.127.1734',to_timestamp('12-DEC-99 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2400,'Stock Clerk','Payam Kaufling',122,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (136,'Hazel','Philtanker','hphiltan@nowhere.com','650.127.1634',to_timestamp('06-FEB-00 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2200,'Stock Clerk','Payam Kaufling',122,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (129,'Laura','Bissot','lbissot@nowhere.com','650.124.5234',to_timestamp('20-AUG-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),3300,'Stock Clerk','Adam Fripp',121,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (130,'Mozhe','Atkinson','matkinso@nowhere.com','650.124.6234',to_timestamp('30-OCT-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2800,'Stock Clerk','Adam Fripp',121,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (131,'James','Marlow','jamrlow@nowhere.com','650.124.7234',to_timestamp('16-FEB-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2500,'Stock Clerk','Adam Fripp',121,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (132,'TJ','Olson','tjolson@nowhere.com','650.124.8234',to_timestamp('10-APR-99 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2100,'Stock Clerk','Adam Fripp',121,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (125,'Julia','Nayer','jnayer@nowhere.com','650.124.1214',to_timestamp('16-JUL-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),3200,'Stock Clerk','Matthew Weiss',120,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (126,'Irene','Mikkilineni','imikkili@nowhere.com','650.124.1224',to_timestamp('28-SEP-98 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2700,'Stock Clerk','Matthew Weiss',120,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (127,'James','Landry','jlandry@nowhere.com','650.124.1334',to_timestamp('14-JAN-99 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2400,'Stock Clerk','Matthew Weiss',120,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (128,'Steven','Markle','smarkle@nowhere.com','650.124.1434',to_timestamp('08-MAR-00 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),2200,'Stock Clerk','Matthew Weiss',120,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (120,'Matthew','Weiss','mweiss@nowhere.com','650.123.1234',to_timestamp('18-JUL-96 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),8000,'Stock Manager','Steven King',100,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (121,'Adam','Fripp','afripp@nowhere.com','650.123.2234',to_timestamp('10-APR-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),8200,'Stock Manager','Steven King',100,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (122,'Payam','Kaufling','pkauflin@nowhere.com','650.123.3234',to_timestamp('01-MAY-95 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),7900,'Stock Manager','Steven King',100,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (123,'Shanta','Vollman','svollman@nowhere.com','650.123.4234',to_timestamp('10-OCT-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),6500,'Stock Manager','Steven King',100,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (124,'Kevin','Mourgos','kmourgos@nowhere.com','650.123.5234',to_timestamp('16-NOV-99 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),5800,'Stock Manager','Steven King',100,'Shipping')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (104,'Bruce','Ernst','bernst@nowhere.com','590.423.4568',to_timestamp('21-MAY-91 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),6000,'Programmer','Alexander Hunold',103,'IT')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (105,'David','Austin','daustin@nowhere.com','590.423.4569',to_timestamp('25-JUN-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),4800,'Programmer','Alexander Hunold',103,'IT')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (106,'Valli','Pataballa','vpatabal@nowhere.com','590.423.4560',to_timestamp('05-FEB-98 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),4800,'Programmer','Alexander Hunold',103,'IT')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (107,'Diana','Lorentz','dlorentz@nowhere.com','590.423.5567',to_timestamp('07-FEB-99 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),4200,'Programmer','Alexander Hunold',103,'IT')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (103,'Alexander','Hunold','ahunold@nowhere.com','590.423.4567',to_timestamp('03-JAN-90 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),9000,'Programmer','Lex De Haan',102,'IT')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (204,'Hermann','Baer','hbaer@nowhere.com','515.123.8888',to_timestamp('07-JUN-94 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),10000,'Public Relations Representative','Neena Kochhar',101,'Public Relations')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (145,'John','Russell','jrussel@nowhere.com','011.44.1344.429268',to_timestamp('01-OCT-96 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),14000,'Sales Manager','Steven King',100,'Sales')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (101,'Neena','Kochhar','nkochhar@nowhere.com','515.123.4568',to_timestamp('21-SEP-89 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),17000,'Administration Vice President','Steven King',100,'Executive')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (102,'Lex','De Haan','ldehaan@nowhere.com','515.123.4569',to_timestamp('13-JAN-93 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),17000,'Administration Vice President','Steven King',100,'Executive')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (109,'Daniel','Faviet','dfaviet@nowhere.com','515.124.4169',to_timestamp('16-AUG-94 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),9000,'Accountant','Nancy Greenberg',108,'Finance')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (110,'John','Chen','jchen@nowhere.com','515.124.4269',to_timestamp('28-SEP-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),8200,'Accountant','Nancy Greenberg',108,'Finance')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (111,'Ismael','Sciarra','isciarra@nowhere.com','515.124.4369',to_timestamp('30-SEP-97 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),7700,'Accountant','Nancy Greenberg',108,'Finance')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (112,'Jose Manuel','Urman','jmurman@nowhere.com','515.124.4469',to_timestamp('07-MAR-98 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),7800,'Accountant','Nancy Greenberg',108,'Finance')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (113,'Luis','Popp','lpopp@nowhere.com','515.124.4567',to_timestamp('07-DEC-99 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),6900,'Accountant','Nancy Greenberg',108,'Finance')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (108,'Nancy','Greenberg','ngreenbe@nowhere.com','515.124.4569',to_timestamp('17-AUG-94 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),12000,'Finance Manager','Neena Kochhar',101,'Finance')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (205,'Shelley','Higgins','shiggins@nowhere.com','515.123.8080',to_timestamp('07-JUN-94 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),12000,'Accounting Manager','Neena Kochhar',101,'Accounting')
/
Insert into EMPLOYEES_ROLLUP (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,SALARY,JOB_TITLE,MANAGER,MANAGER_ID,DEPARTMENT_NAME) values (206,'William','Gietz','wgietz@nowhere.com','515.123.8181',to_timestamp('07-JUN-94 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),8300,'Public Accountant','Shelley Higgins',205,'Accounting')
/

Posted in Oracle, Uncategorized | Leave a Comment »

APEX Packager Utility

Posted by Tyler Muth on February 2, 2010

I’ve been working on a pretty substantial APEX application with Jason Straub and Sharon Kennedy for the last x months (where x > estimated time). It will likely be an APEX Packaged application which consists of the APEX Application, DDL scripts for all schema objects, and install scripts for ~20 image / JavaScript / CSS files.  The process of creating a build is just painful.  It involves way too much point and click and way too much time. I’m not faulting the APEX team for this, as very few people create packaged applications and their time is much better spent on other features (and there are a ton of cool features in 4.0). So, I’ve always had it in the back of mind that I should have learned Perl as it’s very versatile language and really shines where my primary skill-set of SQL, PL/SQL, and JavaScript falls flat.  Perl was designed to work with files and this problem was all about files. There’s no better way to learn a new skill than to have a real project to apply it to…

So, I set aside some evenings a few hours on the weekend here and there and wrote the APEX Packager.  Here’s the description from the project page in case you don’t have an OTN account:

APEX Packager is a utility written in Perl to create APEX Packaged Applications (examples, documentation). It’s goal is to automate the process of adding supporting object scripts such as PL/SQL packages and table DDL, and to automate the process of adding images / JavaScript / CSS to an application. The manual process for each of these steps requires the developer to upload each script and image individually. In the case of images, an additional step is required to generate the hex-encoded version of the image as a script, then upload that script as a supporting object. Once properly configured APEX Packager will automatically perform both of these steps offline without uploading any files. A test-case of APEX Packager was to take a moderately sized APEX Application, 25 DDL scripts, and 1000 images and package them into a single application in 7 seconds.

APEX Packager WILL NOT generate your DDL scripts for you or reverse engineer a schema. This process is better suited for tools like SQL Developer or Data Pump Export.

This utility consists of 2 perl files: apex-packager.pl and config.pl. apex-packager.pl takes in 1 argument which is the config file. This allows you to create different config files for different applications / scenarios and simply pass in the config file when you run the script.

It’s definitely not for everyone. I suspect the primary audience will be the APEX Team and any of the APEX experts that are producing example applications or applications delivered to customers. If you’re new to APEX, this is not for you.  There are a million other resources and utilities you should focus on first.

As a final thought, keep in mind this was my first adventure in Perl. Please don’t use the code as an example of best practices. If you know Perl, I’d love to get your feedback and suggestions.  Even better, please join the project and contribute! I can honestly say this will not be my last Perl utility.  It was such an easy language to learn and there are so many CPAN modules I’d love to explore…

Posted in APEX | 5 Comments »

The Best Unix-like Windows Command Prompt

Posted by Tyler Muth on November 30, 2009

If you follow this blog you know that my laptop is Windows 7 but I live my life in Linux Servers.  As such, I’m a big fan of the bash shell.  I’ve been running Cygwin on my laptops for as long as I can remember to get all of my favorite Linux commands on Windows.  However, the Windows Command Prompt has always felt a little clunky.  I’ve been using Console2 for a while, which I really like but when you combine it with Cygwin on Windows 7, it hangs a lot which is NOT what I want from a shell. I love the feel of Putty when ssh-ing to a Linux / Unix machine, but something about running sshd on my laptop felt like overkill.

Then I discovered PuttyCyg. Essentially it’s a one-off port of Putty that in addition to ssh, lets you connect to your local installation of Cygwin.  Make sure you read the FAQ on that page.  Note the additional option highlighted in yellow:

The main quirk I ran into was with sqlplus and rman.  Just like on Linux / Unix, it lacks the up-arrow functionality which I can’t live without.  So, make sure you also install rlwrap when installing Cygwin.  Then, in your ~/.bashrc file add the following aliases:

alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
To summarize:
  1. Download and install Cygwin.  Make sure you include the rlwrap package.
  2. Create the 2 aliases in your ~/.bashrc file so sqlplus and RMAN use rlwrap.
  3. Download and unzip PuttyCyg.
  4. Optionally create a shortcut to PuttyCyg in your start menu with the following syntax (substituting your actual directories):
"C:\Program Files\puttycyg-20071202\putty.exe" -cygterm -

Now when you run PuttyCyg you have a Putty client connected to your local Windows machine.  Command Prompt bliss…

Posted in Uncategorized | Tagged: , , | 6 Comments »

Logger 1.2.0 Released

Posted by Tyler Muth on November 23, 2009

I just posted the latest version of logger, my PL/SQL logging utility to https://logger.samplecode.oracle.com/.  There are a number of new features as well as bug fixes, many of which were suggested by other people in the comments of my previous blog post on logger.  You can view the new features and fixes in the “Change Log” section at the bottom of the logger home page. Thanks again for your feedback.

On a related note, to post to the discussion forums for logger you have to request the role of “observer” for the project.  I’ve set this to automatically approve everyone so it’s instantaneous.  It’s not my choice and I’m trying to get this speed-bump removed, but for now it’s just the way it is… sorry.  I have an Announcements forum which is locked-down so only I can post to.  My thought was that if you were interested you could subscribe to that Discussion forum to receive emails when a new version is released or a major bug is identified, yet not get a bunch of “noise” from other people posting questions.

Posted in PLSQL | 3 Comments »

Oracle 11g DB on Windows 7 – Success

Posted by Tyler Muth on November 15, 2009

Short Version

Tried to install Oracle Database 11.1.0.7 32-bit on Windows 7 32-bit. Failed at Network Configuration section. I found that in my case, the issue was that I installed in a path with spaces in it (c:\program files\oracle\….). Un-installed. Reinstalled in c:\oracle\product\11.1.0.7. Success!!!

Longer Version

OK, I admit it, I’ve been running Windows 7 since the first beta was released and I love it. Yes, my blog title has “Linux” in it, and I love Linux too, but I’ve had a few issues with Linux on a laptop in front of customers and that’s something I can’t afford to do. See, my laptop needs to be really agile. I plug it into a different networks, projectors, multiple monitors, I put a Verizon Air card in it, I plug lots of peripherals into it. Linux has failed me in each of those areas on more than one occasion. On the other hand, the fact that Linux is (in my mind) very consistent, stable, and almost completely unwilling to change is EXACTLY why I LOVE it for servers. Every “system” I currently manage is running Linux (which I chose from the start). It’s my appliance OS. I have scripted, nightly RMAN backups and auto-start scripts so these systems are 99% hands off. The only time they’ve been down is when one of pseudo-data centers has gone completely dark for network and power upgrades. They’re all internal, so I’m a bit more relaxed about OS patches, which means I basically ssh into them once every few months to apply patches. That’s it. OK, that was waaaaaayyyyy too much info to qualify why I’m running Windows on my laptop. On a related note, hey Apple, I’m happy to blog about my experience switching to say, a MacBook Pro….

Anyway, I got a bit off topic. Sorry. So I’m running Windows 7 RC1, build 7100, 32-bit. I downloaded the Windows 2008 Server version of Oracle, v. 11.1.0.7 since Server 2k8 is a similar code base to Windows 7. After I got stuck on the network configuration, I checked the install logs, but there wasn’t anything helpful there. So, I ran netca from the command-line and got error that said something about “error at c:\program blah blah blah”. It was obvious that it truncated the path at the first space. So, once I installed in a path with no spaces, everything went fine.

OHS 10.1.3.3 Had Issues Too

I downloaded Oracle HTTP Server 10.1.3.3 as well since that’s my preferred APEX configuration (no, I haven’t tried the new listener on Windows 7 yet, just Linux). The installation got stuck on the configuration section, so I eventually killed the process. However, it appeared that everything installed correctly so I created a DAD, ran opmnctl startall, and presto, APEX was ready to go. OK, I left out the part about dropping the default 11.1 APEX version and installing APEX 3.2.1, but that’s not really important here.

Hopefully this helps someone out there. I’ll take a look and see if there are bugs filed on these issues and file them if I don’t find anything. Windows 7 isn’t supported by Oracle yet, but it can’t hurt to let them know…

Posted in 11g, Oracle, Windows 7 | 15 Comments »

Logger, A PL/SQL Logging and Debugging Utility

Posted by Tyler Muth on November 3, 2009

I’ve been gradually building out a logging utility for PL/SQL over the last year or so. It’s been a huge help on some complicated projects, especially in APEX Applications with a lot of AJAX. I’m sure most people reading this have been stuck on a problem, created a logging table, and sprinkled a few inserts in their code to see what’s going on and when. It’s amazing how much faster you resolve these issues if the table is already there and the inserts are now just a short procedure call. Anyone that’s ever used Firebug probably can’t imagine it without console.log() or console.dir(). Yes, you can use dbms_output.put_line or htp.p, but those fall over pretty quick in a web environment with HTTP Posts or worse, a lot of AJAX Posts / Gets. Additionally, if you’re a Tom Kyte groupie, you’ve probably heard him talk about the value of instrumentation more than once.

Now that you know the “why”, lets talk about what Logger is.  It’s just a couple of tables and a PL/SQL package that make all of this easier for you.  I’ll give you a brief summary of features here, but for full details you should go to the project page at https://logger.samplecode.oracle.com.  So, the super short demo is:

logger@orcl> exec logger.log('hello world');

PL/SQL procedure successfully completed.

logger@orcl> select * from logger_logs_terse;

     ID LOGGER_LEVEL TIME_AGO             TEXT
------- ------------ -------------------- ---------------------
     48           16 35 seconds ago       hello world

Features Include:

  • Easily Enable / Disable all logging
  • Minimal Overhead.  There’s even a NO-OP version of the package that you can use for production if you’re really paranoid. It’s just a stub of the logger package that doesn’t write to or depend on any tables (or any other objects).
  • Automatically purges debug messages older than 7 days.  This is completely configurable
  • Ability to time blocks of code
  • Log sys_context(‘userenv’,”) variables
  • Log APEX Item Names and Values
  • Automatically captures key session information such as module, action, client_identifier including APEX session ID, timestamp, call stack.

I’d like to thank all of the community members that tested and contributed ideas for logger including:

I would love to get more feedback from the community as well.  You can either comment here, or use one of the 3 discussion forums I created for logger listed on the project page just above the download link.

The Fastest Way to Store / Retrieve a Cross Session Variable

Posted in APEX, Oracle, PLSQL | Tagged: , , | 44 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 87 other followers