Jeff Holoman's Blog

Archive for April, 2009

Interesting Video

Posted by jholoman on April 9, 2009

I’m not one of those people who spends an inordinate amount of time on YouTube, but my Mom sent me a video that I thought was pretty interesting.

One thing it really underscores is that staying current is very important, and as “other stuff” comes up in life, I’m making it a personal goal to continue learning…

Posted in Uncategorized | Leave a Comment »

Arrays over DB Link

Posted by jholoman on April 8, 2009

I recently had a problem come up where we needed to return a resultset via database link. I was aware that the data types allowed over DB link were somewhat restricted: no ref-cursors, no object types (including XMLType) to name a few.

I wasn’t aware that you could use PL/SQL Associative arrays (index-by tables) via db link. Here’s a small example:

On the remote db:

SQL> create or replace package aa_test
    as
    type my_emp_type is table of emp%rowtype index by pls_integer;
    function get_emp(p_empno in number) return my_emp_type;
    end;
    /

Package created.
SQL> create or replace package body aa_test
    as
    function get_emp(p_empno in number) return my_emp_type
    as
    l_emp_rec my_emp_type;
    begin
     select * bulk collect into l_emp_rec from emp where empno = p_empno;
    return l_emp_rec;
    exception when no_data_found then
    null;
   end get_emp;
   end aa_test;
  /

Package body created.

And Locally:

SQL> declare
    l_local_emp_rec aa_test.my_emp_type@holoman;
    begin
    l_local_emp_rec := aa_test.get_emp@holoman(7654);
    dbms_output.put_line(l_local_emp_rec(1).ename);
    dbms_output.put_line(l_local_emp_rec(1).job);
    end;
    /
MARTIN
SALESMAN

PL/SQL procedure successfully completed.

Always nice to learn something new.

Posted in Oracle, PL/SQL | Leave a Comment »

Show / Hide Report Columns with jQuery

Posted by jholoman on April 7, 2009

I have been a benefactor of others work regarding the Hide/Show columns functionality that I believe Carl first implemented here: http://apex.oracle.com/pls/otn/f?p=11933:46

Vikas and  Denes also have examples. The one thing that always kind of bothered me about the example was that in order to save the user’s preference, you needed to click a button. This doesn’t seem so intuitive to me, so I decided to revamp the example a bit and save the preference based on a cookie.

You can see this in action here:

http://apex.oracle.com/pls/otn/f?p=18507:5

I rewrote the code with jQuery and strayed a bit from the original implementation, but I think it works ok

Posted in APEX | Tagged: , , | Leave a Comment »

Franklin Street

Posted by jholoman on April 7, 2009

Last night my brother and a bunch of other friends made the trip to Chapel Hill to watch the Tarheels crush Michigan St. I couldn’t make it up there, but it looked crazy:

http://vimeo.com/4039511

Posted in Personal | Leave a Comment »