Dynamic Single Row Query Once in awhile doing analysis, I'd like to see the count from each table, just to get an idea of how much data I am working with. Of course this doesn't measure width, but it is a metric.

Of course I could go through and write a SELECT COUNT(*) from every table, that works if there are like 4 tables. Anything more and...well it sucks.

Yes, I could gather stats on the schema and then reference NUM_ROWS, but this is an occasion where I don't have the necessary privileges.

I've been trying to do this for years, with no luck, until yesterday. Typically I would do something like this:

DECLARE
  l_count INTEGER;
  l_sql VARCHAR2(200);
BEGIN
  FOR i IN ( SELECT table_name
             FROM dba_tables
             WHERE owner = 'SYSTEM' ) 
  LOOP
    l_sql := 'SELECT COUNT(*) INTO :1 FROM SYSTEM.' || i.table_name;
    EXECUTE IMMEDIATE l_sql USING l_count, i.table_name;
    dbms_output.put_line( i.table_name || '-' || l_count );         
  END LOOP;
END;
/

Which would of course would give me this:

DECLARE
*
ERROR at line 1:
ORA-01745: invalid host/bind variable name
ORA-06512: at line 10

Only now did I realize a fatal flaw with that...I was trying to bind the variable into the string, which would obviously never work.

For whatever reason, this time I decided to research it. I came across Flavio Cassetta's site and this post, SQL Error: ORA-01745: invalid host/bind variable namI'll skip a few iterations and get to the final product (because I went through this exercise on Friday night and quite honestly, don't remember all the permutations).

SET SERVEROUTPUT ON

DECLARE
  l_count INTEGER;
  l_table VARCHAR2(61);
BEGIN
  FOR i IN ( SELECT table_name
             FROM dba_tables
             WHERE owner = 'SYSTEM' ) 
  LOOP
    l_table := 'SYSTEM.' || i.table_name;
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || l_table INTO l_count;
    dbms_output.put_line( l_table || '-' || l_count );         
  END LOOP;
END;
/

...snip
SYSTEM.LOGMNR_USER$-0
SYSTEM.LOGMNR_OBJ$-0
SYSTEM.LOGMNR_DICTIONARY$-0
SYSTEM.LOGMNR_DICTSTATE$-0
SYSTEM.OL$NODES-0
SYSTEM.OL$HINTS-0
SYSTEM.OL$-0

PL/SQL procedure successfully completed.

Done.

Labels: plsql, sql

  ¶ 1/10/2010 09:36:00 PM   0 Comments Links to this post

  Free Oracle Developer/dba There is a serious lack of work in the Tampa market and desperate times call for desperate measures.

Now, I've always wanted to do this, but was never in a position to do so financially...I'm still not, but something is way better than nothing.

I'm going to offer my services for free.

Not forever mind you, somewhere between 2 and 4 weeks. It is negotiable.

If it works out, i.e. you like what I can bring to your organization, I prove that I pick new systems up quickly and I fit in well with your team; and I like working in your organization, then you pay me for that time worked and I sign up full-time either as a permanent employee or some sort of contract.

If it doesn't work out, we part ways and no one is worse for wear. You get free work from me and I get to experience a new environment (i.e. meet new people, expand my network, etc).

You can view my resume here (permanent link up top coming soon).

Some basic highlights:
PL/SQL: Expert (I don't like that term)
DBA: Junior to Mid-level (or DBA in lowercase, "dba")
APEX: Worked with up to version 3.2, admittedly rusty, but passionate about the product.
Design: Love modeling data. Model just about everything in my head (in normal life). Use Visio extensively for visual representations. Experience with SQL Developer Data Modeler, ERwin, etc.
Documentation: Give me a wiki or something similar and I'll document just about everything that I do.

That's it. Contact info is on my resume or up top on the "email" icon.

It's now posted on craigslist as well.

Labels: database, dba, development, jobs, plsql, work

  ¶ 10/27/2009 12:25:00 PM   3 Comments Links to this post

  APEXposed 2009 and "..you down with OPP(2009)?". Sorry, couldn't resist. Was it 1991 that song came out? Were freshmen in college even born yet? You know you're old when you start saying stuff like that...

The event takes place in Atlanta, GA, November 10-11th.

Join PL/SQL and Application Express experts Steven Feuerstein and Scott Spendolini for two days of Seriously Practical training that can immediately be applied to your daily duties at work.

Are you interested in optimization, PL/SQL tools, or writing maintainable code? OPP 2009 is the place for you.

Are you interested in tuning, debugging, or APEX and the Oracle Database? Sign up now for APEXposed 2009.

Want to learn a little of both? Sign up for one conference and attend sessions at either conference. This provides you with a great opportunity for cross-over training.

Admittedly, I've been out of the Application Express community for a couple of years now, but I still use the tool on occasion for smallish (personal) applications and I still follow blogs of those I do know. I still try to sell the tool at every organization I join (maybe APEX is the reason I keep losing (actually, I haven't lost any of them, I know right where they are) my job?). And yes, that list is long...especially recently.

My point is, I wouldn't have remembered this even if I weren't neighbors (so to speak) with Dan McGhan, the Tampa APEX guru. Am I not plugged in to the right communities? Half of this event is PL/SQL...I should know about events related to that. I think they need more press to get the word out...

Of course I want to go and since I have free time now, all I need is a pass. Now...wait for it.



You knew I was going to ask someone didn't you? I still don't qualify as a new media douchebag though.

It's funny, I always thought that this blogging thing would one day pay off in terms of a job or conference attendance. The latter was realized last May when I attended COLLABORATE 09 and almost realized for OOW earlier this month. No job yet though. I've asked John and Dimitri. I've asked Scott too. No takers yet. They've probably read my history. :)

Labels: apex, plsql

  ¶ 10/26/2009 01:33:00 AM   0 Comments Links to this post

  PL/SQL: Exceptions I'm not really sure what I learned today, yet. Here's what I went through though.

I'm rebuilding/redesigning/refactoring a payment processing platform. It's complete with WHEN others...there is logging after the WHEN others, but no RAISE.

I was taught to use exceptions, which to me, meant using

raise_application_error( -20001, 'something went wrong' )

which meant that my calling PL/SQL had to use the PRAGMA EXCEPTION_INIT declaration. Not a big deal when it's 1 or 2 layers deep, but that's part of today's lesson (for me).

Exceptions were used in the code, they were slightly different though, just the

DECLARE
  some_exception EXCEPTION;
BEGIN
...

variety. It's way better than nothing and I believe they were headed in the right direction.

As I peel away the layers though, far too many errors are being caught with OTHERS. Bad. Bad. Bad.

Payment processing, being at the center of most everything, should, ney, must, blow up loudly if something unknown goes wrong. Before that ever goes live you should know about the vast majority of exceptions. Vast Majority to me means 99.9%.

By blowing up loudly, you don't have to rely on looking through error logs and you are far less likely to encounter strange behavior. If one pops up that you didn't account for, it's a quick code change to add that handling.

Of course much of this is predicated on having unit tests or other testing means available. Once of the first things I did was build about 80 test cases with SQLUnit. So I am fairly confident when I make changes that I haven't affected (much) the underlying code.

Finally, on to the exceptions.

There were 4 or 5 generic exceptions (other than OTHERS) defined. I wanted more though. So I began adding them in. Currently the code travels through about 5 levels of the candy cane forest, I mean, PL/SQL. In the lower most level, I used

raise_application_error( -20001, 'invalid card number (gateway)' );

Reran the tests and nothing showed up. I added an internal function to capture the error stack.

  FUNCTION error_stack RETURN VARCHAR2
  IS
    l_error_stack VARCHAR2(32767);
  BEGIN
    l_error_stack := dbms_utility.format_call_stack;
    l_error_stack := l_error_stack || dbms_utility.format_error_backtrace;
    l_error_stack := l_error_stack || dbms_utility.format_error_stack;
    
    RETURN l_error_stack;
  END error_stack;

so I wouldn't have to rewrite those 3 (long) lines over and over. I realize that you get an extra line in there, but I'll know to ignore it.

Rerun the tests and I can see the call stack with a reference to ORA-20001. I'm getting somewhere. That's when I realized that even if you throw an exception in that manner, if you have an exception block in the same block of code and a WHEN others, WHEN others will catch it. For some reason, I always thought it bypassed that current block of code, but then again, I've rarely used WHEN others.

One by one I began to remove the WHEN others from the calling layers. I created global exceptions:

invalid_card EXCEPTION;
PRAGMA EXCEPTION_INIT( invalid_card, -20001 );

, removed WHEN others and created a new exception block in the top-most procedure. Perfect!

I rerun the tests and the error propogates all the way to the top (as it should, I just wasn't used to it). Tests begin to work again and I'm all set to go. Win!

For more on exception handling in PL/SQL, go here for 10gR2, here for 11gR1, and here for 11gR2.

Labels: plsql, sqlunit, testing

  ¶ 9/15/2009 05:02:00 PM   0 Comments Links to this post

  PL/SQL: Coding Practices I've struggled this week to write. Lately I've had lots of technical content and not much philosophical content. I have lots of philosophical content now, but the blog isn't the place for it...at least not now.

The big project I'm working on now is refactoring our payment processing system. We interface with multiple gateways for redundancy purposes. The code is comprised of 3 stand-alone procedures. One of those procedures has (had) 17 private procedures/functions in it. On one hand, it made sense, since everything was a stand-alone procedure or function, you didn't want all these dependencies on other objects. On the other hand, testing was virtually impossible. If you needed to make a change to private procedure #13, you had to run through an almost infinite number of test cases to ensure that you covered that particular case.

I've now moved those 3 procedures into a package. Theoretically, it's hot deployable now as long as we don't change the package signature. That's a big win in my book. I've also moved those 17 private functions into their own individual procedures and functions that can be exposed via the package specification for unit testing purposes (in production they will be private as nothing else needs access). The hardest part of that effort was there were no parameters being passed to the procedure, it just relied on the declared variables. So for each and every one of those I had to figure out what it relied on to work and what variables it set. No small task.

Here is the table:

CREATE TABLE t
(
  id NUMBER PRIMARY KEY,
  col_1 NUMBER(1) DEFAULT 0 NOT NULL,
  col_2 NUMBER(1) DEFAULT 0 NOT NULL,
  col_3 NUMBER(1) DEFAULT 0 NOT NULL,
  start_date DATE DEFAULT SYSDATE NOT NULL,
  end_date DATE
);

and a procedure (which does nothing obviously)

CREATE OR REPLACE
PROCEDURE update_t
  ( p_id IN NUMBER,
    p_col_1 IN INTEGER DEFAULT 0,
    p_col_2 IN INTEGER DEFAULT 0,
    p_col_3 IN INTEGER DEFAULT 0 )
IS
BEGIN
  NULL;
END update_t;

What's the best way to integrate the procedure into your code? I've seen this:

CREATE OR REPLACE
PROCEDURE some_other_procedure
  ( p_id NUMBER,
    p_variable VARCHAR2(1) )
IS
BEGIN
  IF p_variable = 'A' THEN
    update_t
      ( p_id => p_id,
        p_col_3 => 1 );
  ELSIF p_variable = 'B' THEN
    update_t
      ( p_id => p_id,
        p_col_2 => 1,
        p_col_3 => 1 );
  ELSIF p_variable = 'C' THEN
    update_t
      ( p_id => p_id,
        p_col_1 => 1 );
  ELSIF p_variable = 'D' THEN
    update_t
      ( p_id => p_id,
        p_col_1 => 1,
        p_col_3 => 1 );
  END IF;
END some_other_procedures;

Since I default the input parameters to 0, I didn't have to specify each individual parameter every time I called it. I like that.

I don't much like having 4 separate calls to UPATE_T though.

1. It makes it difficult (without further logging), to determine where exactly it's being called in the control statement.
2. Seems like a waste of space. P_ID is always going to be the same, why set it 4 times?

I decided to make just one call to UPDATE_T. I create local variables, then set them in the control statement, and then make the call to UPDATE_T.

CREATE OR REPLACE
PROCEDURE some_other_procedure
  ( p_id NUMBER,
    p_variable VARCHAR2(1) )
IS
  l_col_1 INTEGER := 0;
  l_col_2 INTEGER := 0;
  l_col_3 INTEGER := 0;
BEGIN
  IF p_variable = 'A' THEN
    l_col_3 := 1;
  ELSIF p_variable = 'B' THEN
    l_col_2 := 1;
    l_col_3 := 1;
  ELSIF p_variable = 'C' THEN
    l_col_1 := 1;
  ELSIF p_variable = 'D' THEN
    l_col_1 := 1;
    l_col_3 := 1;
  END IF;

  update_t
    ( p_id => p_id,
      p_col_1 => l_col_1,
      p_col_2 => l_col_2,
      p_col_3 => l_col_3 );
  
END some_other_procedures;

Not much savings in space (and sometimes you'll actually have more), but for me, this is much easier to read. If I have to debug this, it feels a lot easier to concentrate on the control statement without the calls to UPDATE_T.

What do you do in these kinds of situations? Same as me? Different? Think I'm off my rocker (yeah, I know some of you do)?

Labels: design, development, plsql

  ¶ 8/19/2009 10:04:00 PM   2 Comments Links to this post

  PL/SQL: Parse URL Strings Finally, I can put it all together now.

PL/SQL: Split URL Parameters

PL/SQL: Split Key-Value Pairs

REGEXP_REPLACE - Credit Card (CC) Numbers

PL/SQL: Pipelined Function

It all culminates in this (hopefully final) post.

The goal was to be able to take in a URL string and parse it out accordingly. I ultimately decided that persisting that data was not of importance, so I built a fairly flexible function which returns a user-defined-type (UDT), also known as a SQL object.

We'll start with the UDTs:

CREATE OR REPLACE TYPE r_key_value_record IS OBJECT
(
  orderof NUMBER(5),
  key_string VARCHAR2(1000),
  value_string VARCHAR2(1000)
)
/

CREATE OR REPLACE TYPE t_key_value_table AS TABLE OF R_KEY_VALUE_RECORD
/

Nothing fancy there. I'm not exactly sure why I added the ORDEROF column, other than I think it will come in good use down the road.

  FUNCTION parse_url
    ( p_url IN VARCHAR2,
      p_token_delimiter IN VARCHAR2,
      p_keyvalue_delimiter IN VARCHAR2,
      p_enclosed_by IN VARCHAR2 DEFAULT NULL,
      p_line_start IN VARCHAR2 DEFAULT NULL,
      p_line_end IN VARCHAR2 DEFAULT NULL ) RETURN T_KEY_VALUE_TABLE PIPELINED;

I tried to make this as flexible as possible. Different payment gateways return different response strings so this seemed necessary. I suppose I could build one for each...but that wouldn't be as fun.

P_URL - self-explanatory
P_TOKEN_DELIMITER - For most URL strings, this will be the ampersand (&) that separates the key/value pairs.
P_KEYVALUE_DELIMITER - Usually the equals (=) sign, but can vary.
P_ENCLOSED_BY - occasionally a string will be enclosed by quotes (")
P_LINE_START - much more rare, the string has one or more characters at the beginning of the line
P_LINE_END - much more rare, the string has one or more characters at the end of the line

The declaration:

  IS
    l_table T_KEY_VALUE_TABLE := T_KEY_VALUE_TABLE();
    l_string VARCHAR2(4000) := p_url;
    l_token_delimiter_length INTEGER := LENGTH( p_token_delimiter );
    l_key_value_delimiter_length INTEGER := LENGTH( p_keyvalue_delimiter );
    l_key VARCHAR2(100);
    l_value VARCHAR2(100);
    l_keyvalue VARCHAR2(200);
    l_counter INTEGER := 0;
    l_new VARCHAR2(4000);
    l_start INTEGER := 1;
    l_end INTEGER := 0;
  BEGIN

Nothing special here.

    IF p_line_start IS NOT NULL THEN
      l_string := SUBSTR( l_string, LENGTH( p_line_start ) + 1 );
    END IF;

    IF p_line_end IS NOT NULL THEN
      l_string := SUBSTR( l_string, 1, INSTR( l_string, p_line_end, -1 ) - 1 );
    END IF;

Getting rid of any start or end characters. Next up, my kludge:

    IF SUBSTR( l_string, -1, l_token_delimiter_length ) != p_token_delimiter THEN
      l_string := l_string || p_token_delimiter;
    END IF;

Just adding the delimiter to the end of the string. Apparently I'm too lazy to figure out a better way.

Finally, the meat of the process:

    LOOP
      l_counter := l_counter + 1;
      IF p_keyvalue_delimiter IS NOT NULL THEN
        l_end := INSTR( l_new, p_token_delimiter, 1 );
        l_keyvalue := SUBSTR( l_new, 1, l_end - 1 );
        EXIT WHEN l_keyvalue IS NULL;
        l_key := SUBSTR( l_keyvalue, 1, INSTR( l_keyvalue, p_keyvalue_delimiter ) - 1 );
        l_value := SUBSTR( l_keyvalue, INSTR( l_keyvalue, p_keyvalue_delimiter ) + 1 );
        l_start := l_start + ( l_end + ( l_token_delimiter_length - 1 ) );
        l_new := SUBSTR( l_string, l_start );
      ELSE
        l_end := INSTR( l_new, p_token_delimiter, 1 );
        l_keyvalue := SUBSTR( l_new, 1, l_end - 1 );
        EXIT WHEN l_new = p_token_delimiter;
        l_key := l_counter;
        l_value := l_keyvalue;
        l_start := l_start + l_end ;
        l_new := SUBSTR( l_string, l_start );
      END IF;
  
      l_table.EXTEND(1);
      l_table( l_counter ) := R_KEY_VALUE_RECORD( l_counter, l_key, l_value );
      PIPE ROW ( R_KEY_VALUE_RECORD( l_counter, l_key, l_value ) );
    END LOOP;
    RETURN;
  END parse_url;

What does all that do? Let's see.

SET DEFINE OFF
BEGIN
:C := '&&AUTH_CODE XXXXXX;&AVS_CODE N;&CMRCL_FLAG 5;&CMRCL_TYPE 3;&INTRN_SEQ_NUM 
9999999;&PAYMENT_MEDIA AMEX;&REFERENCE 9211258897;&RESPONSE_TEXT PERFECT MATCH;&RESULT 
SUCCESS;&RESULT_CODE 3;&TERMINATION_STATUS SUCCESS;&TRANS_DATE 2009.08.10;&TRANS_SEQ_NUM 
999;&TRANS_TIME 15:24:51;&TROUTD 99999999;';
END;
/

SELECT *
FROM TABLE( pkg_payment_processing.parse_url( :c, ';&', ' ', NULL, '&&', ';' ) );

ORDEROF KEY_STRING           VALUE_STRING
------- -------------------- ------------------------
      1 AUTH_CODE            XXXXXX
      2 AVS_CODE             N
      3 CMRCL_FLAG           5
      4 CMRCL_TYPE           3
      5 INTRN_SEQ_NUM        9999999
      6 PAYMENT_MEDIA        AMEX
      7 REFERENCE            9211258897
      8 RESPONSE_TEXT        PERFECT MATCH
      9 RESULT               SUCCESS
     10 RESULT_CODE          3
     11 TERMINATION_STATUS   SUCCESS
     12 TRANS_DATE           2009.08.10
     13 TRANS_SEQ_NUM        999
     14 TRANS_TIME           15:24:51
     15 TROUTD               99999999

How about a different flavor of URL string?

BEGIN
  :C := 'transaction_id=0983450982340985sada0384098098234&error_code=999&auth_response_text=Nice Job!';
END;
/

SELECT *
FROM TABLE( pkg_payment_processing.parse_url( :c, '&', '=' ) );

ORDEROF KEY_STRING           VALUE_STRING
------- -------------------- ------------------------------------
      1 transaction_id       0983450982340985sada0384098098234
      2 error_code           999
      3 auth_response_text   Nice Job!

OK, one more. For this one, there will be no key/value pair, it's simply ordered (yes, you need the API to decipher it).

BEGIN
  :c := '1,1,1,This is a test transaction.,999999,Y,999999999,,,1.00,CC,authorization,,Jake,Kuramoto,,123 Main 
Street,Somewhere,CA,93063,US,,,,,,,,,,,,,,,,,XXXXXXXXXXXXXXXX
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,';
END;
/

SELECT *
FROM TABLE( pkg_payment_processing.parse_url( :c, ',', NULL ) );
ORDEROF KEY_STRING           VALUE_STRING
------- -------------------- -------------------------------------------
      1 1                    1
      2 2                    1
      3 3                    1
      4 4                    This is a test transaction.
      5 5                    999999
      6 6                    Y
      7 7                    999999999
      8 8
      9 9
     10 10                   1.00
     11 11                   CC
     12 12                   authorization
     13 13
     14 14                   Jake
     15 15                   Kuramoto
     16 16
     17 17                   123 Main Street
     18 18                   Somewhere
     19 19                   CA
     20 20                   93063
     21 21                   US
     22 22
        ...SNIP
     37 37
     38 38                   XXXXXXXXXXXXXXXX
        ...SNIP
     63 63
     64 64
     65 65
     66 66

Labels: development, plsql

  ¶ 8/10/2009 09:47:00 PM   0 Comments Links to this post

  PL/SQL: Pipelined Function As with Friday's post, pipelined functions are something I use rarely, thus end up looking up how to do it.

So with a little help from Tim Hall [ blog | twitter ], I shall do a quick example.

I'm trying to integrate these 2 posts, PL/SQL: Split URL Parameters and PL/SQL: Split Key-Value Pairs. I don't want to store the parsed response or request string just yet, just the string. But in the off chance I want it parsed, I don't want to load it up into excel or something and begin the arduous task of breaking it down. I will (just remembered) have to parse the response string when I receive it though to figure out whether or not the transactions was Approved or Declined.

Let's start with the types:

CREATE OR REPLACE TYPE r_key_value_record IS OBJECT
(
  orderof NUMBER(5),
  key_string VARCHAR2(1000),
  value_string VARCHAR2(1000)
)
/

CREATE OR REPLACE TYPE t_key_value_table AS TABLE OF R_KEY_VALUE_RECORD
/

The simple function, just for demonstration purposes:

CREATE OR REPLACE
FUNCTION get_key_value_pairs RETURN T_KEY_VALUE_TABLE PIPELINED
IS
BEGIN
  FOR i IN 1..10 LOOP
    PIPE ROW ( R_KEY_VALUE_RECORD( i, i, i ) );
  END LOOP;
  RETURN;
END get_key_value_pairs;
/
show errors

This will definitely be expanded in the days to come to include those previous posts mentioned above. I will post the final result and link it up here as well.

Labels: plsql, sql, udt

  ¶ 8/09/2009 06:27:00 PM   3 Comments Links to this post

  Users and Roles - Revisited In a previous post, How To: Users and Roles (Simple), I went through and demonstrated one way to build a simple security system using users and roles.

I'm using the Database Authentication security scheme in Apex. Patrick Wolf [ blog ] pointed out that I should create database roles instead of using my own. That is the end game of course as I've limited the role_name column to VARCHAR2(30). But for now, this will have to do.

38 lines is now 27

The previous code looked like this:

FUNCTION is_authorized
  ( p_username IN VARCHAR2,
    p_rolename IN VARCHAR2 ) RETURN BOOLEAN
IS
  l_count INTEGER;
  l_rolename VARCHAR2(30);
BEGIN
  SELECT COUNT(*)
  INTO l_count
  FROM v_active_user_roles
  WHERE user_name = p_username
    AND role_name = p_rolename
    AND rownum < 2;
  
  IF l_count = 1 THEN
    --user has this role assigned
    RETURN TRUE;
  ELSE
  --user does not have this role assigned
  --check to see if user's role inherits these privs
    l_count := NULL;

    SELECT COUNT( DISTINCT role_name )
    INTO l_count
    FROM v_roles
    WHERE status = 'A'
      AND end_date IS NULL
      AND INSTR( role_path, p_rolename ) > 0
      AND INSTR( role_path, p_rolename ) < INSTR( role_path, role_name );

    IF l_count > 0 THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
    
  END IF;
END is_authorized;

and I found it didn't work too well.

Specifically, the person with the least privileges, TAB_ADMIN, could see everything that only the ADMIN could see.

There was another small problem, using INSTR was screwed up to. Can you guess why?

'ADMIN'?

That would return a hit back for 3 roles: ADMIN, SECTION_ADMIN and TAB_ADMIN.

Bad developer, bad. (Another great reason you should NOT roll your own security!)

What was the problem again?

Let me quickly recap what I am trying to do with a "pretty" picture:



I'm going to try and articulate this with words, please don't laugh. ADMIN is the all powerful, it should be able to do anything it wants to do. There are 5 authorization schemes, one for each role; placed on different objects (pages, items, tabs, etc).

If a button has the TAB_ADMIN authorization schema, ADMIN should be able to run it through inheritance. The picture above is trying to depict that with those red arrows.

So, what's the point of this post? I've forgotten, so I'm skipping to the end.

I refactored the IS_AUTHORIZED procedure from 38 lines to 27 and I believe it's a bit more intuitive than it was. The key for me was included ROLE_LEVEL which is just an alias for the pseudo-column LEVEL.

If the ROLE_LEVEL, of the enabled roles for a user, is less than the ROLE_LEVEL where ROLE_NAME is part of the ROLE_PATH (SYS_CONNECT_BY_PATH), you're in.

Or something like that.

Here's the final product:

FUNCTION is_authorized
  ( p_username IN VARCHAR2,
    p_rolename IN VARCHAR2 ) RETURN BOOLEAN
IS
  l_count INTEGER;
BEGIN
  FOR i IN ( SELECT role_name, role_path, role_level
             FROM v_active_user_roles
             WHERE user_name = p_username )
  LOOP
    IF p_rolename = i.role_name THEN
      RETURN TRUE;
    ELSE
      SELECT COUNT(*)
      INTO l_count
      FROM v_roles
      WHERE INSTR( role_path, i.role_name, -1 ) > 0
        AND role_level > i.role_level;
      
      IF l_count > 0 THEN
        RETURN TRUE;
      ELSE
        RETURN FALSE;
      END IF;
    END IF;
  END LOOP;
END is_authorized;

I'll also be updating the other post to point to this one.

Small reminder: TESTING, TESTING, TESTING

Labels: development, plsql, security

  ¶ 8/06/2009 06:15:00 PM   2 Comments Links to this post

  %ROWTYPE

The %ROWTYPE attribute provides a record type that represents a row in a table (or view). The record can store an entire row of data selected from the table, or fetched from a cursor or strongly typed cursor variable...

I have never been fond of using %ROWTYPE, for me, it feels to much like SELECT *.

The last 2 years or so I've worked on legacy systems though, and that's forced me to reconsider %ROWTYPE.

In particular, you have various INSERT/UPDATE statements on a given table all over the place. I have typically written stored procedures to handle discrete, logical, business requirements. For example, you need to update a person's name. There are a couple of ways to do this.

1. Create a discrete procedure like update_person_name that accepts the appropriate input.
2. Create a generic procedure like update_person which will take all the possible inputs and possibly change them all (the data is UPDATEd, even if the data doesn't change).
3. Create a super generic (fancy term) procedure that accepts a row from a table, PERSON%ROWTYPE.

One advantage of using %ROWTYPE, is that your input list has shrunk considerably. Instead of

PROCEDURE update_person_name
  ( p_personnameid IN NUMBER,
    p_namecodeid IN NUMBER,
    p_titleid IN NUMBER,
    p_firstname IN VARCHAR2,
    p_middlename IN VARCHAR2,
    p_lastname IN VARCHAR2,
    p_suffixid IN NUMBER )
IS
BEGIN
...

You could have

PROCEDURE update_person_name( p_personname_row IN PERSON_NAME%ROWTYPE )
IS
BEGIN
...

Ultimately, it doesn't really save on typing because you have to assign the variables somewhere (it's just shifted from one location to another). Here's how you would call the second example:

DECLARE
  l_row PERSON_NAME%ROWTYPE;
BEGIN
  l_row.personnameid := 22;
  l_row.namecodeid := 2;
  l_row.titleid := 4;
  l_row.firstname := 'Jake';
  l_row.middlename := NULL;
  l_row.lastname := 'Kuramoto';
  l_row.suffxid := 'VI';

  package_name.update_person_name( l_row );
  --OR (BETTER)
  package_name.update_person_name( p_personname_row => l_row );
END;

As opposed to the other way

BEGIN
  package_name.update_person_name
    ( p_personnameid => 22,
      p_namecodeid => 2,
      p_titleid => 4,
      p_firstname => 'Jake',
      p_middlename => NULL,
      p_lastname => 'Kuramoto',
      p_suffixid => 'VI' );
END;

Here's where I get a little scared though. Inside the procedure which accepts the PERSON_NAME%ROWTYPE, my UPDATE looks like this:

UPDATE person_name
  SET namecodeid = NVL( p_personname_row.namecodeid, namecodeid ),
      titleid = NVL( p_personname_row.titleid, titleid ),
      firstname = NVL( p_personname_row.firstname, firstname ),
      middlename = NVL( p_personname_row.middlename, middlename ),
      lastname = NVL( p_personname_row.lastname, lastname ),
      suffixid = NVL( p_personname_row.suffixid, suffixid )
WHERE personnameid = p_personname_row.personnameid;

All the NVL make me feel dirty for some reason. I'm not saying it makes sense, I'm just saying.

However, it does serve a purpose, in a legacy system anyway. If you need to quickly and easily get a handle on things, without knowing all the rules of a given system, it works well...as a first step.

Thoughts? Opinions? Does anyone else out there use this? For similar reasons or completely different? Should I feel guilty about this? Or is this an acceptable method you have (and do) use?

Labels: %ROWTYPE, plsql

  ¶ 8/04/2009 08:31:00 PM   9 Comments Links to this post

  Classic: Application Developers vs. Database Developers II The original (with all the fun comments) can be found here. Originally posted on December 9, 2008. This is the "followup" to yesterday's post.

You can read the first article here. My application developer friend, Mr. M, emailed me and another fine gentleman this little blurb recently:

Mr. M:

OH YEAH BABY!!! TEN TIMES FASTER!!!! YEAH!!!!!!!!

Hey seriously, what a tub of shit Oracle is. Where does this myth come from that it's such a great platform? Their client tools suck balls and it's generally just a pain in the ass to work with from a developer's point of view. But devs for some reason are under this impression that from thew server perspective it's rock solid and performant. Well, it may be solid, but it's a fucking turd. Our dba here - definitely an Oracle guy - has been tasked with looking into moving us onto an oss db. He basically refuses to even look at MySQL, stating that it's a mickey mouse worthless pile of shit (can't really argue with him there lol), so that basically leaves Postgres. So it's been a few weeks now, and he will basically admit now that Postgres completely waxes Oracle as far as performance goes. LOL We run RAC in production too. He's looking at failover, replication, blah blah blah now, we'll see what the verdict on that is. Oh, and Oracle AQ? That's a worthless pile of shit too. Why do they tack a fucking message queue that doesn't play nice with jms onto the fucking database, which is probably already overworked? Oh wait, that's right, they're in the business of selling per cpu licenses! Cocksuckers.

This was prompted by a recent Oracle email blast about the Exadata storage system/Warehouse.

As I did before, I'll just put the email here.

Me:

Agreed, their client tools aren't all the great. Which ones are you using?

I use SQL*Plus (naturally), SQL Developer and JDeveloper. The latter 2 tie in very nicely with Subversion. With JDeveloper, when I want to try out Java, it's got a pretty good suite of tools.

Oracle starting out catering to businesses, Microsoft started with the consumer. Oracle has made pretty good strides in making their software more usable while Microsoft has made theirs more scalable.

I haven't used AQ a whole lot and definitely not with Java. I do know that it's billed as JMS compliant.

Postgres has it's place and so does Oracle. It is a great platform if you know how to leverage it. PL/SQL is a 3GL (or is it 4) and you can write entire applications using it...and if you do that right, an incredibly robust and scalable application at that.

Mr. M:

"It is a great platform if you know how to leverage it. PL/SQL is a 3GL (or is it 4) and you can write entire applications using it...and if you do that right, an incredibly robust and scalable application at that."

NO!!! NO!!! NOOOOO!!!

I want to beat people like you who say this with a ball pean hammer. There are only a select few individuals on this earth who can write and read application logic written in SQL. AVOID THIS ANTI-PATTERN AT ALL COSTS! What is it with you f_cking database guys??? Why do you always want to stuff as much crap into the db as possible?

DUDE! SQL IS A PILE OF SHIT FOR EXPRESSING APPLICATION LOGIC!!!

It's fine for querying and manipulating sets of data, in a relational database. But it is a worthless sack of shit for expressing application logic!

I'm having to dig through this f_cking abortion right now because some Oracle f_ckhead thought "you can write entire applications using it!" Blog that, mofo!

This was followed by a package he had been working on. I wouldn't say it was the greatest, but it wasn't all bad either.

Me:

goodness gracious.

"DUDE! SQL IS A PILE OF SHIT FOR EXPRESSING APPLICATION LOGIC!!!"

disagree (naturally). It's incredibly easy to do, you just don't know how yet...and it seems even the Oracle professionals out there don't either.

I'll tell you this, the crazier the SQL or PL/SQL needed to derive and manipulate data the poorer the design. Start with a good design and it all becomes very simple.

Of course note the use of "naturally" in my lexicon. Thanks Jake.

Mr. M:

well dude, we are back to our old discussion - you arguing that procedural sql code is perfectly fine for building apps, and by extension, that the last 20 years of computer science have basically been a misguided lost journey down the meandering, fruitless trail of oop. um.....no. select this from that. otherwise keep that sql crap caged up where it belongs.

btw, do the db guys here suck? seriously. i'm not competent enough to judge. (to be fair, apparently that crap i sent you is fairly old stuff, but still....)

Me:

I would say, based on limited knowledge of software development, that the OOP movement was started because the database (specifically Oracle) was not mature enough to do what was needed. Plus, I seem to recall that the OOP movement was supposed to have solved all the world's problems by now.

It's further propogated due to the needs you specified in our discussion that day at WellCare (i.e. performance). I still believe that if application developers better understood how a DB works (Oracle, MySQL, SQLServer, etc) they would end up writing less code. Database constraints alone force you to write less (and better) code simultaneously ensuring good data.

The code that I did look at (first 1000 lines or so) isn't great.
1. With all those IF THEN ELSE statements it's telling me that there's probably a better way to store the data. Looks like they're missing an attribute that should be contained with a track.
2. using Object (PL/SQL) types to store data in the way they seem to be doing it is not the best way. Again, probably a design issue.
3. When you do something like this:

   UPDATE pb_album_metadata
      SET primary_digital_flag = 0
    WHERE album_id IN (SELECT b.album_id
                       FROM (SELECT a.album_id AS album_id,
                                MAX(a.album_id) OVER (PARTITION BY a.standard_upc) AS latest_album_id
                             FROM pb_album_metadata a
                             WHERE a.standard_upc = g_album_tab(1).standard_upc ) b
                       WHERE b.album_id <> b.latest_album_id )
         AND primary_digital_flag <> 0;

They should probably have considered end_date as an attribute of the album metadata. While analytic functions are pretty cool, they're more for analyzing (OLAP) and not OLTP environments.

That's for starters and without table definitions...

Me (again):

oh yeah...and PL/SQL is/was built on top of ADA, FYI.

Mr. M:

"I still believe that if application developers better understood how a DB works (Oracle, MySQL, SQLServer, etc) they would end up writing less code. Database constraints alone force you to write less (and better) code simultaneously ensuring good data."

Huh? What are we not understanding? What would be an example of a constraint that would force us to write less and better code?

Me:

CHECK, NOT NULL (same as CHECK) and FOREIGN KEY constraints all fit into that category.

Instead of having to check if a value is NULL when submitting it to the database, just submit and let the database throw an error, which you'll conveniently capture in your Exception block. Same goes for CHECK constraints, columnA should be either one of three values (test, test1, test3), you won't have to check that in your application code, just catch the exception. FKs insure good data (proper relationships).

A different perspective. If you are going to pay for it, use that damn thing. If you don't care, don't do it. But over the course of an "enterprisey" application, you'll end up writing more code and make maintenance that much more difficult (did I do it there?). Just those constraints will force you and the teams of application developers to put good data into the database.

You can still do it in the application of course (form validation is a good place)...

Mr. M:

Ahh, jeez dude, I wasn't sure if you were referring to the literal "constraint" or not.

Dude, even f_cksticks like redacted I think have a decent enough understanding of when and how to use db constraints. It's when you get into things like cursors or cost plans of subselects and anonymous tables (i think that's the name for it - where I say select something from (select some other crap). Then we defer to db gurus like yourself.

But dude....."you won't have to check that in your application code, just catch the exception".......uh, don't ever repeat that off our little email group. And definitely don't go posting that on your blog. F_ck me man, it's a damn good thing we keep you db folks caged up in that rdbms box....

Me:

So we've had this discussion at work...for a high transaction system, do Java/C/etc handle exceptions well or what?

Why is it bad to deal with exceptions rather than coding to avoid them?

I highly doubt even redacted understood database constraints...him and his cohorts believed all database software would be commoditized and MySQL would be king in short order.

Mr. M:

"for a high transaction system"

Or for any system really....

To use your example of check constraints (is this value one of....) or not null constraints, checking these rules in the Java code and in the database code would seem to violate DRY. But we do that alot, and it is acceptable in certain cases. For instance, we also probably violate DRY if we're checking this same rule say in Javascript on the front end. But we accept this tiny violation of DRY because it tends to make for a better user experience and as a performance gain too, for we avoid the round trip to the server. Now, what your advocating here is close to the same thing. You're basically saying, don't check for the not null constraint in Java code, just go ahead and hit the database, let the database throw back an exception to the Java code, presumably correct the problem, and then make another roundtrip to the database again. Dude, what are you thinking?!? This to say nothing of the fact that this also could be considered a violation of Fail Fast, and a violation of Defensive Programming - what happens if the dba forgot to add the not null constraint in production?

Dude, listen to this guy. For a "high transaction system" basically you want to treat the database, as much as you can, as just a dumb data holder. A f_cking dumpster, that you just throw sh_t into and pull shit back out, using no vendor-specific features if at all possible.

Again, for we've had this discussion, but even in everyday apps, not just on Wall Street, the database is the bottleneck. And the database is the hardest layer to scale. So given those facts, you should design your app to do as little work in the database as possible.

I was laughing at this point because the link above points to one of our consulting architects (I'm not really sure what his role is at this point).
Me:

i agree in any application that you want to minimize the number of round trips...

shocker...he's one of our architects. he's spot on in many instances, but...

database is the bottleneck because people don't know how to write SQL. I'll certainly concede the wall street applications (for the time being anyway), but the rest of us with what we do on a daily basis...Oracle will scale far beyond the demands they place. When that bottleneck shows up, 9 times out of 10 some dumb-ass c#/java guy thought he could write a better query than i. besides, what's the idiot doing anything but a simple join anyway? probably poor data model to start with...and we're right back where we started (sort of).

Mr. M:

"database is the bottleneck because people don't know how to write SQL.....some dumb-ass c#/java guy thought he could write a better query than i."

Dude, I'll grant you, people don't know how to write SQL, myself included. But that's not always why the database is the bottleneck. I think it's safe to say that's not even the majority of the reason. Yes, there are apps written by people who were just idiots, and they needlessly pummel the database, but that's definitely not the majority of scenarios. At my work the database is the bottleneck, and we run RAC in production. It's my understanding that even with RAC, there is a limit to how much you can scale that out. But any layer up from the database we are basically unlimited in how much we can scale that out. So it's very easy to stick another Apache box in front, or fire up another Weblogic server. But I can't do that with the database. We have 24 Weblogic servers for redacted. The database is the bottleneck. And we don't have shitty sql code in the app. In fact, we have very few hand-written queries anywhere in the app. Persisting something to a database is really a low-level concern that as an application developer I shouldn't even have to be bothered with, except for the rare corner cases where the persistence abstraction I'm using is just a little bit too generic to handle things effectively. And we don't use these ORMs because we don't know how to write sql. To effectively use an ORM tool requires a deeper understanding of sql and databases than simply being able to write solid SQL code. (Not saying Java devs who use ORMs know SQL better than a dba, just that it requires better sql skills than is required of a Java dev to simply write JDBC/SQL.) Now, before you try to tell me that my ORM library isn't able to write as efficient of sql code as a dba of your caliber, keep in mind that ORM tools are pretty advanced. They're able to intelligently do things like batch sql updates, and let you apply transactional semantics much easier than with raw jdbc/sql. But the overwhelming reason developers have so thoroughly adopted ORM is because Structured Query Language is such a nasty piece of shit for expressing application logic. SQL is a declarative, procedural language. It's totally unsuited for writing application logic! This, more than anything else, is why organizations and dev teams should seek to restrict what is handled within a relational database as much as possible - because the programming interface to it is a fucking ancient backward dying dinosaur.

Mr. V (note, not Mr. M):

My 2 canadian cents:
The polyglot approach "... use different languages for different domain ..."
Database was developed to manipulate data and should remain there.
General purpose language was developed to encapsulate logic and should remain in that domain.
You should not use DB to encapsulate business logic (in my opinion) no more than you would use HTML to create complex logic.

While Java, C#, Python, etc are described as General Purpose languages, they, too, are really domain-constrained. Their expressiveness are confined (and should be) to express low-level, tersed, explicit, verbose, and repetive logic (if that makes any sense). Languages such as these are more suitable for low-level abstraction on top of which, richer, more expressive languages can be built. We are seeing this now with the emergence of languages on the JVM (and arguably on the .Net's CLR).

I think SQL as we know will take a back seat and a smart developer somewhere will create a new domain-specific language that lives on the VM and will push the SQL expression out of the RDBMS and closer to the code that needs it. We are not there yet, but Microsfot is trying (see LINQ and all ORM papers). This is not to say that there won't be isntances where tuning in the SQL-native language won't be necessary. However, 80 to 90% of simple CRUD cases will be handled closer to the logic code that uses the data code.

Again, that's my 2 canadian cents... I could go on. But I have a meeting with redacted.

Mr. V, I believe, is a little bit more sensible. Mr. M on the other hand is just trying to rile (sp?) me up.

Me:

Someone will probably create something like that, but it still gets at the heart of one of my arguments, many developers don't know how to use a database thus will go to any means to circumvent it. Embrace it I say.

Ultimately for me, it comes down to simplicity. I can write an application using PL/SQL that will scale very well for 90% of the solutions. Of course that doesn't include the "fancy" javascript/Ajax stuff needed for the UI. In my ever so humble opinion, this is a much better solution for a business in the long run.
1. You're more likely to change the middle tier than the database tier. Java, asp, Ruby, what's next?
2. Fewer moving parts means lower cost of ownership, even with the CPU costs. One person, one, was able to build and maintain a 350 page website. Be hardpressed to do that with the more expressive languages.

I think all of us are purists and very passionate about what we do. It's probably the main reason we get along. I thoroughly enjoy these conversations because it does force me to think...and that's always a good thing.

Mr. V:

Haaa chet.
You sorta made my point than diverged away. Building an app in all PL/SQL is dangerous. It's no safer than me building an app in all Java. I can build very limited app in all Java. The moment I need to interact with other domain (UI, data, low-level native code, etc), I have to switch to something that is closer to what I am trying to do. If I need to create UI, I will pick a ui-centric environment, when I need to talk to DB, I will pass connect to a db and send in my SQL, and so forth. I will use Java as the glue to bring it all togher.

In the end, we may be saying the same thing, but using different accent. O well.

And that's where it ended. I must say it's always fun. Mr. M and Mr. V are both very smart individuals and I highly respect what they do. We have different perspectives...but I think they listen, if only a little, as I listen to them. Their voices creep up on me especially now...which is definitely a good thing.

Labels: design, development, funny, humility, ideas, java, plsql

  ¶ 7/07/2009 05:15:00 PM   1 Comments Links to this post

  SQL: SYS_CONTEXT In my experience so far, I've seen very few places where SYS_CONTEXT is used. It is typically used in conjunction with CONTEXTs and also Virtual Private Database (VPD).

Oracle has a built in namespace called USERENV and their is a wealth of information you can retrieve from there. I wrote up a quick view to demonstrate the use of SYS_CONTEXT:

SELECT
  SYS_CONTEXT( 'USERENV', 'ACTION' ) action,
  SYS_CONTEXT( 'USERENV', 'AUTHENTICATION_METHOD' ) authentication_method,
  SYS_CONTEXT( 'USERENV', 'BG_JOB_ID' ) bg_job_id,
  SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ) client_identifier,
  SYS_CONTEXT( 'USERENV', 'CLIENT_INFO' ) client_info,
  SYS_CONTEXT( 'USERENV', 'CURRENT_SCHEMAID' ) current_schemaid,
  SYS_CONTEXT( 'USERENV', 'CURRENT_SCHEMA' ) current_schema,
  SYS_CONTEXT( 'USERENV', 'CURRENT_USER' ) current_user,
  SYS_CONTEXT( 'USERENV', 'DB_DOMAIN' ) db_domain,
  SYS_CONTEXT( 'USERENV', 'DB_NAME' ) db_name,
  SYS_CONTEXT( 'USERENV', 'DB_UNIQUE_NAME' ) db_unique_name,
  SYS_CONTEXT( 'USERENV', 'HOST' ) host,
  SYS_CONTEXT( 'USERENV', 'INSTANCE' ) instance,
  SYS_CONTEXT( 'USERENV', 'INSTANCE_NAME' ) instance_name,
  SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) ip_address,
  SYS_CONTEXT( 'USERENV', 'ISDBA' ) isdba,
  SYS_CONTEXT( 'USERENV', 'LANG' ) lang,
  SYS_CONTEXT( 'USERENV', 'LANGUAGE' ) language,
  SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) network_protocol,
  SYS_CONTEXT( 'USERENV', 'NLS_CALENDAR' ) nls_calendar,
  SYS_CONTEXT( 'USERENV', 'NLS_CURRENCY' ) nls_currency,
  SYS_CONTEXT( 'USERENV', 'OS_USER' ) os_user,
  SYS_CONTEXT( 'USERENV', 'SERVER_HOST' ) server_host,
  SYS_CONTEXT( 'USERENV', 'TERMINAL' ) terminal
FROM dual

Which yields the following:

ACTION:  NULL
AUTHENTICATION_METHOD:  PASSWORD
BG_JOB_ID:  NULL
CLIENT_IDENTIFIER:  NULL
CLIENT_INFO:  NULL
CURRENT_SCHEMAID:  88
CURRENT_SCHEMA:  CJUSTICE
CURRENT_USER:  CJUSTICE
DB_DOMAIN:  NULL
DB_NAME:  testing
DB_UNIQUE_NAME:  testing
HOST:  cdj-laptop
INSTANCE:  1
INSTANCE_NAME:  TESTING
IP_ADDRESS:  192.168.1.4
ISDBA:  FALSE
LANG:  US
LANGUAGE:  AMERICAN_AMERICA.WE8MSWIN1252
NETWORK_PROTOCOL:  tcp
NLS_CALENDAR:  GREGORIAN
NLS_CURRENCY:  $
OS_USER:  chet
SERVER_HOST:  oracledb
TERMINAL:  UNKNOWN

Basically, these are global variables that Oracle stores on the current session/user.

You can create a context for just about anything you want. I've used them in the past along with table based security in a stateless environment. In essence, I create a record in a table with a SESSION_ID, I then store that in the context (global variable) and I use that to reconnect to the database each time.

I promise to have an example of using it in the near future.

Labels: database, oracle, plsql, security, sql

  ¶ 7/05/2009 11:02:00 PM   0 Comments Links to this post

  PL/SQL: Split Key-Value Pairs In a continuation from a previous post, PL/SQL: Split URL Parameters, I give you the key/value splitting! (cheese, I know...I'm bored).

In that previous post, I was taking a URL string and splitting it based on the delimiter, which is typically the ampersand &. In ApEx it is the colon :. I'd take a wild guess and say there are others, but I'm not going to look them up. An example string looks like this:

param1=value1¶m2=value2

Etc. Etc. Etc.

The output of that split looks like this:

param1=value1
param2=value2

Now you need that string parsed. Instead of just a string being returned however, you'd like to know the name of the parameter as well, thus key/value. Desired format looks like this:

KEY         VALUE
----------- -----------
param1      value1
param2      value2

First up, I'll create the SQL objects (user defined types):

CREATE TYPE r_key_value AS OBJECT
(
  key VARCHAR2(100),
  value VARCHAR2(100)
)
/

CREATE TYPE t_key_value AS TABLE OF r_key_value
/

I could easily do this using PL/SQL tables, but I have future plans.

DECLARE
  l_string VARCHAR2(1000) := 'param1=value1¶m2=value2';
  l_table T_KEY_VALUE := T_KEY_VALUE();
  l_delimiter VARCHAR2(5) := '&';
  l_delimiter_length INTEGER := LENGTH( l_delimiter );
  l_key VARCHAR2(100);
  l_value VARCHAR2(100);
  l_keyvalue VARCHAR2(200);
  l_counter INTEGER := 0;
  l_new VARCHAR2(4000);
  l_start INTEGER := 1;
  l_end INTEGER := 0;

This will eventually turn into a Function, but I'm just doing some smoke testing now to get the logic right. More extensive testing will be performed in the future with SQLUnit.

BEGIN
  IF SUBSTR( l_string, -1, 1 ) <> l_delimiter THEN
    l_string := l_string || l_delimiter;
  END IF;

  l_new := l_string;
  p( L_NEW );

I still haven't figured out the best way to grab the last token without appending the delimiter onto the end. It feels like a kludge, but for now, it works.

  LOOP
    l_counter := l_counter + 1;
    l_end := INSTR( l_new, l_delimiter, 1 );
    l_keyvalue := SUBSTR( l_new, 1, l_end - 1 );
    EXIT WHEN l_keyvalue IS NULL;
    
    l_table.EXTEND(1);
    l_key := SUBSTR( l_keyvalue, 1, INSTR( l_keyvalue, '=' ) - 1 );
    l_value := SUBSTR( l_keyvalue, INSTR( l_keyvalue, '=' ) + 1 );
    l_table(l_counter) := R_KEY_VALUE( l_key, l_value );
    l_start := l_start + ( l_end + ( l_delimiter_length - 1 ) );
    l_new := SUBSTR( l_string, l_start );
  END LOOP;

Regular expressions would be the best fit here. Until I learn them (yes, I hear you, "Isn't this the perfect opportunity?"), I'm going to do it the hard way.

Finally, just printing out to the console so I can see the results.

  p( 'table counter:  ' || l_table.COUNT );
  FOR I IN 1..l_table.COUNT LOOP
    p( 'key:    ' || l_table(i).key );
    p( 'value:  ' || l_table(i).value );
    p( '' );
  END LOOP;

Run it and I get the following:

CJUSTICE@TESTING>@C:\TEMP\S
table counter:  2
key:    param1
value:  value1
key:    param2
value:  value2

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07

Now I just need to wrap this up into package form and I'm all done. That will be another post with the unit tests provided.

Update
So after rereading this post, I realized that I just confused it with the previous post on splitting URL strings. Probably the best solution would be to combine these 2 functions, or at least pipe out the key/value pairs from the main function (previous post). Did I catch a niner in there?

Labels: howto, plsql

  ¶ 6/23/2009 05:00:00 PM   1 Comments Links to this post

  PL/SQL: Split URL Parameters I've always had this phobia of anything to do with strings. Might be because I find it hard to think that way...and by "that way" I have no idea what I'm talking about.

Whenever I come across this problem, my first instinct is to go through every character and build a string, something like this:

DECLARE
  l_string VARCHAR2(100) := 'THIS_LITTLE_STRING';
  l_character VARCHAR2(1);
BEGIN
  FOR i IN 1..LENGTH( l_string ) LOOP
    l_character := l_character || SUBSTR( l_string, i, 1 );
  END LOOP;
END;
/

Of course that means I have to go through the loop at least 18 times. How could I loop less?

Well, I would need to know the delimiter first, in this case, it will be the underscore character:

DECLARE
  l_string VARCHAR2(100) := 'THIS_LITTLE_STRING';
  l_character VARCHAR2(1);
  l_delimiter VARCHAR2(1) := '_';
BEGIN
...

OK, that helps. So what can I do with that?

DECLARE
  l_string VARCHAR2(100) := 'THIS_LITTLE_STRING';
  l_new_string VARCHAR2(100) := l_string;
  l_token VARCHAR2(30);
  l_character VARCHAR2(1);
  l_delimiter VARCHAR2(1) := '_';
BEGIN
  LOOP
    EXIT WHEN l_new_string IS NULL;
    l_token := SUBSTR( l_new_string, 1, INSTR( l_new_string, '_', 1 ) );
...

OK, now I remember what I always get confused. There seems to be this endless stream of SUBSTR and INSTR involved. It's difficult to follow...for me anyway. So here's what I've come up with for now. I am aware of regular expressions and the DBMS_UTILITY.COMMA_TO_TABLE procedures, but for some reason, I like to reinvent the wheel. No, not really, I just find it challenging. More challenging would be for me to start using regular expressions...Anyway, here goes my attempt at splitting a URL string:

DECLARE
  TYPE my_table IS TABLE OF VARCHAR2(100);
  l_table MY_TABLE := MY_TABLE();
  l_string VARCHAR2(1000) := 'TESTING=YES&&BOLLOCKS=SOMETHING&&&testing=DF';
  l_keyvalue VARCHAR2(100);
  l_delimiter VARCHAR2(5) := '&&';
  l_delimiter_length INTEGER := LENGTH( l_delimiter );
  l_counter INTEGER := 0;
  l_new VARCHAR2(4000);
  l_start INTEGER := 1;
  l_end INTEGER := 0;
BEGIN
  IF SUBSTR( l_string, -1, 1 ) <> l_delimiter THEN
    l_string := l_string || l_delimiter;
  END IF;

  l_new := l_string;

  LOOP
    l_counter := l_counter + 1;
    l_end := INSTR( l_new, l_delimiter, 1 );
    l_keyvalue := SUBSTR( l_new, 1, l_end - 1 );
    EXIT WHEN l_keyvalue IS NULL;
    l_table.EXTEND(1);
    l_table(l_counter) := l_keyvalue;
    l_start := l_start + ( l_end + ( l_delimiter_length - 1 ) );
    l_new := SUBSTR( l_string, l_start );
  END LOOP;
  p( 'table counter:  ' || l_table.COUNT );
  FOR I IN 1..l_table.COUNT LOOP
    p( 'string:  ' || l_table(i) );
  END LOOP;
END;
/

CJUSTICE@TESTING>@T
table counter:  3
string:  TESTING=YES
string:  BOLLOCKS=SOMETHING
string:  &testing=DF

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

Voila! My own URL String Tokenizer.

Now I just have to write something that will split up the key/value pairs...

Labels: howto, plsql

  ¶ 6/16/2009 12:01:00 AM   4 Comments Links to this post

  What is a PL/SQL API? As a follow-on to yesterday's post on PL/SQL APIs, I decided to describe what I consider to be an API in PL/SQL. Actually, it was an after-thought and then John T went into further details in comments on that post. I felt it was more than a comment should be so...

First, what is an API?

As defined by Wikipedia:

application programming interface (API) is a set of routines, data structures, object classes and/or protocols provided by libraries and/or operating system services in order to support the building of applications.

Easy enough. I think the vast majority of readers understand that.

What is an PL/SQL API?

The way that I would define a PL/SQL API is (preferably) one package that provides the interface to one or more underlying tables. Let's take the EMP table for instance. If I were to define an API for that it would be something like this (pseudocode):

• Create Employee
• Update Employee
• Update Employee Department
• Update Employee Salary

All other code would call one of these procedures/functions to perform the specific action. No one else would write a direct update on EMP, no matter how small or seemingly trivial. If something outside of the 4 defined actions was needed, then it would be added to the preceding package.

Why use a PL/SQL API?

In all likeliness, the person who is adding the underlying table or tables is going to be the subject matter expert (SME). They should be the ones to define the actions to be performed against that set of tables.

Security is a factor here as well. Would you give INSERT/UPDATE/DELETE to non application users or would you require them to use the PL/SQL API? If I had my way, no one would have any direct DML to any table in the application.

How about support and maintenance? If you make a change to a table you should only have to change in 1 or 2 locations (say the package and a view), not 10, or 20, or 30. If you have it contained in just a couple of locations most minor changes would be relatively easy and quick to complete. If it's spread out across the database, you end up obscuring or hiding many of your data structures.

Finally, to John T's point, there is encapsulation, which hits on a few points noted above. I'm not going to quote the entire page (though it would be appropriate), but I will come close:

...encapsulation is the hiding of the internal mechanisms and data structures of a software component behind a defined interface, in such a way that users of the component (other pieces of software) only need to know what the component does, and cannot make themselves dependent on the details of how it does it. The purpose is to achieve potential for change: the internal mechanisms of the component can be improved without impact on other components, or the component can be replaced with a different one that supports the same public interface.

Encapsulation also protects the integrity of the component, by preventing users from setting the internal data of the component into an invalid or inconsistent state.

Another benefit of encapsulation is that it reduces system complexity and thus increases robustness, by limiting the interdependencies between software components.

...For example, a simple digital alarm clock is a real-world object that a lay person can use and understand. They can understand what the alarm clock does, and how to use it through the provided interface (buttons and screen), without having to understand every part inside of the clock. Similarly, if you replaced the clock with a different model, the lay person could continue to use it in the same way, provided that the interface works the same.

I think that sums it up nicely.

So how about you? PL/SQL APIs?

Labels: database, design, development, oracle, plsql

  ¶ 4/12/2009 07:02:00 PM   1 Comments Links to this post

  Oracle As a Platform I'm always hearing about this platform and that platform and all the wonderful things you can do with them.

What about Oracle as a platform?

Before you get all crazy eyes on me, I'm not talking about eBay, Google or the like. I'm talking about the other 99% of the applications out there.

Database
Oracle is the market leader in database technology. There is a very good reason for that and I'll let you decide on what that reason might be.

Web Server
Either Oracle's Application Server (Apache), which came on the Companion CD on 10g (not sure about 11g), or Oracle's built in XDB HTTP Server (pre-configured in 11g). MOD_PLSQL allows the web server to communicate with the database.

MOD_PLSQL is an Apache (Web Server) extension module that allows one to create dynamic web pages from PL/SQL packages and stored procedures. It is ideal for developing fast and flexible applications that can run on the Internet or an Intranet. MOD_PLSQL was formerly called the Oracle PL/SQL Cartridge and OWA (Oracle Web Agent).

Front End
You have 2 choices (that I am aware of) for creating web based applications, Application Express and the HTP/HTF packages. The former is built atop the latter...and I'd definitely use ApEx before the packages.

Oracle Application Express (Oracle APEX) is a rapid web application development tool for the Oracle database. Using only a web browser and limited programming experience, you can develop and deploy professional applications that are both fast and secure.

ApEx is, in my experience, one of the most under-used tools out there.

Procedural Language
PL/SQL is a very powerful language. It's loosely based on Ada and is a procedural extension to SQL. PL/SQL has been around since 1992, version 6. It supports arrays, collections, user defined types, and many other data structures. Best of all though, Oracle provides a vast library of PL/SQL code. Sending email? Got it. Lower level stuff? UTL_TCP should do the trick. Queueing? Done.

I could go on and on, but you get the point.

If you want to build powerful, robust applications that are low on complexity and high on performance, Oracle's the way to go.

What's that? Oracle licensing is too expensive? How about Oracle Standard Edition (SE) for $5,000? There's not a whole lot you don't get with SE.

Of course you could disagree, but I'd say you're pretty crazy.

Labels: apex, database, development, oracle, plsql, rant

  ¶ 3/09/2009 11:49:00 PM   7 Comments Links to this post

  SELECT INTO or LOOP? I've always used the SELECT INTO syntax to retrieve data into variables. Lately though, I've seen the LOOP structure used.

Personally, I like the SELECT INTO, it seems clear and concise to me. I didn't want to just dismiss the LOOP offhand so I ran a couple of tests.

First I create my table with random strings:

CREATE TABLE t
AS
SELECT dbms_random.string( 'a', TRUNC( dbms_random.value( 5, 30 ) ) ) x
FROM dual
  CONNECT BY level <= 10000000;

Yes, that's 10 Million rows. I just wanted to have a nice number for total time. Everything small returned a big fat zero.

I picked a string out of the table and performed the same test 5 times with each method searching for the same string.

SELECT INTO

DECLARE
  l_start_time NUMBER;
  l_end_time NUMBER;
  l_total_time NUMBER;
  l_dummy VARCHAR2(1);
BEGIN
  l_start_time := dbms_utility.get_time;

  d( l_start_time );

  SELECT 'Y'
  INTO l_dummy
  FROM t
  WHERE x = 'XFLCTdLXZjwlHBAqOgdddUCu';

  l_end_time := dbms_utility.get_time;

  d( l_end_time );
  l_total_time := l_end_time - l_start_time;
  d( 'Total Time:  ' || l_total_time );
END;
/

Here are the results:

SQL>/
Total Time:  86

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.85
SQL>/
Total Time:  87

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.87
SQL>/
Total Time:  86

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.87
SQL>/
Total Time:  87

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.87
SQL>/
Total Time:  87

PL/SQL procedure successfully completed.

Not bad.

LOOP

DECLARE
  l_start_time NUMBER;
  l_end_time NUMBER;
  l_total_time NUMBER;
  l_dummy VARCHAR2(1);
BEGIN
  l_start_time := dbms_utility.get_time;

  d( l_start_time );

  FOR i IN ( SELECT 'Y' y
             FROM t
             WHERE x = 'XFLCTdLXZjwlHBAqOgdddUCu' )
  LOOP
    l_dummy := i.y;
    EXIT;
  END LOOP;

  l_end_time := dbms_utility.get_time;

  d( l_end_time );
  l_total_time := l_end_time - l_start_time;
  d( 'Total Time:  ' ||  l_total_time );
END;
/

And the results:

SQL>/
Total Time:  86

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.87
SQL>/
Total Time:  88

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.89
SQL>/
Total Time:  88

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.89
SQL>/
Total Time:  88

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.89
SQL>/
Total Time:  87

PL/SQL procedure successfully completed.

Neglibily slower. Nothing to write home about though.

Let's throw an index on there for fun.

CREATE INDEX t_idx
  ON t( x );

SELECT INTO

SQL>DECLARE
  2    l_start_time NUMBER;
  3    l_end_time NUMBER;
  4    l_total_time NUMBER;
  5    l_dummy VARCHAR2(1);
  6  BEGIN
  7    l_start_time := dbms_utility.get_time;
  8  
  9    SELECT 'Y'
 10    INTO l_dummy
 11    FROM t
 12    WHERE x = 'XFLCTdLXZjwlHBAqOgdddUCu';
 13  
 14    l_end_time := dbms_utility.get_time;
 15  
 16    l_total_time := l_end_time - l_start_time;
 17    d( 'Total Time:  ' || l_total_time );
 18  END;
 19  /
Total Time:  1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL>/
Total Time:  0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL>/
Total Time:  0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL>/
Total Time:  1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>/
Total Time:  0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

LOOP

SQL>DECLARE
  2    l_start_time NUMBER;
  3    l_end_time NUMBER;
  4    l_total_time NUMBER;
  5    l_dummy VARCHAR2(1);
  6  BEGIN
  7    l_start_time := dbms_utility.get_time;
  8  
  9    FOR i IN ( SELECT 'Y' y
 10               FROM t
 11               WHERE x = 'XFLCTdLXZjwlHBAqOgdddUCu' )
 12    LOOP
 13      l_dummy := i.y;
 14      EXIT;
 15    END LOOP;
 16  
 17    l_end_time := dbms_utility.get_time;
 18  
 19    l_total_time := l_end_time - l_start_time;
 20    d( 'Total Time:  ' ||  l_total_time );
 21  END;
 22  /
Total Time:  1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL>/
Total Time:  0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL>/
Total Time:  0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>/
Total Time:  0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL>/
Total Time:  0

PL/SQL procedure successfully completed.

OK, there doesn't seem to be much of a difference performance wise.

Then I got to thinking (look out!). What about errors? no_data_found or too_many_rows? With the SELECT INTO those are thrown, with the LOOP, they are not.

SELECT INTO, no_data_found

SQL>DECLARE
  2    l_start_time NUMBER;
  3    l_end_time NUMBER;
  4    l_total_time NUMBER;
  5    l_dummy VARCHAR2(1);
  6  BEGIN
  7    l_start_time := dbms_utility.get_time;
  8  
  9    SELECT 'Y'
 10    INTO l_dummy
 11    FROM t
 12    WHERE x = 'ORACLENERD';
 13  
 14    l_end_time := dbms_utility.get_time;
 15  
 16    l_total_time := l_end_time - l_start_time;
 17    d( 'Total Time:  ' || l_total_time );
 18  END;
 19  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 9

LOOP, no record found

SQL>DECLARE
  2    l_start_time NUMBER;
  3    l_end_time NUMBER;
  4    l_total_time NUMBER;
  5    l_dummy VARCHAR2(1);
  6  BEGIN
  7    l_start_time := dbms_utility.get_time;
  8  
  9    FOR i IN ( SELECT 'Y' y
 10               FROM t
 11               WHERE x = 'ORACLENERD' )
 12    LOOP
 13      l_dummy := i.y;
 14      EXIT;
 15    END LOOP;
 16  
 17    l_end_time := dbms_utility.get_time;
 18  
 19    l_total_time := l_end_time - l_start_time;
 20    d( 'Total Time:  ' ||  l_total_time );
 21  END;
 22  /
Total Time:  0

PL/SQL procedure successfully completed.

SELECT INTO, too_many_rows

SQL>INSERT INTO t ( x ) VALUES ( 'ORACLENERD' );

1 row created.

Elapsed: 00:00:00.04
SQL>INSERT INTO t ( x ) VALUES ( 'ORACLENERD' );

1 row created.

Elapsed: 00:00:00.01
SQL>DECLARE
  2    l_start_time NUMBER;
  3    l_end_time NUMBER;
  4    l_total_time NUMBER;
  5    l_dummy VARCHAR2(1);
  6  BEGIN
  7    l_start_time := dbms_utility.get_time;
  8  
  9    SELECT 'Y'
 10    INTO l_dummy
 11    FROM t
 12    WHERE x = 'ORACLENERD';
 13  
 14    l_end_time := dbms_utility.get_time;
 15  
 16    l_total_time := l_end_time - l_start_time;
 17    d( 'Total Time:  ' || l_total_time );
 18  END;
 19  /
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 9

If choosing between the 2 methods, performance is not necessarily a consideration. However, one method will throw errors and one will not. If you use the LOOP method, you'll have to add a check IF l_dummy IS NOT NULL THEN or something along those lines. If you use the SELECT INTO method, you can either next the statement in a BEGIN END block or let the exception propogate.

I prefer to let it complain loudly with SELECT INTO. I want to create the BEGIN END block knowing why I am doing it or catching no_data_found or too_many_rows.

One more thing about the LOOP method, what if you have more than one row that matches the criteria (input) you specify? You'll get a row but you can't be sure it's the row. That's not good.

I would recommend SELECT INTO vs the LOOP method. It's prettier (naturally) and it will complain loudly if you don't specify all of the proper predicates (input).

Labels: database, design, oracle, plsql

  ¶ 2/12/2009 09:25:00 PM   3 Comments Links to this post

  DBMS_SESSION.IS_ROLE_ENABLED Interesting (to me anyway) thing I learned recently.

DBMS_SESSION.IS_ROLE_ENABLED

The call looks like this:

DBMS_SESSION.IS_ROLE_ENABLED (
   rolename    VARCHAR2) 
  RETURN BOOLEAN;

A few times in the pass I've had need to check the role of the logged in user. I would do that with something like this:

CREATE OR REPLACE
FUNCTION role_enabled( p_role_name IN VARCHAR2 ) RETURN BOOLEAN
IS
  l_dummy VARCHAR2(1);
BEGIN
  SELECT 'Y'
  INTO l_dummy
  FROM dba_role_privs
  WHERE grantee = USER
    AND role = p_role_name;

  RETURN TRUE;
EXCEPTION
  WHEN no_data_found THEN
    RETURN FALSE;
END role_enabled;
/

I did something similar here (and I can't believe no one told me about this function!).

Lesson? Before building something on your own (even something this simple), search through the documentation to see if Oracle has already done it.

Update This was shown to me by my colleage, @serge_a_storms

Labels: dbms_session, oracle, plsql

  ¶ 2/04/2009 10:21:00 PM   3 Comments Links to this post

  Nested Tables One concept of Oracle that I've always been intrigued by is Nested Tables. Nested tables are, basically, a table (dataset) stored in a single column. They are part of the object-relational features of Oracle. Now, I've never found a practical use for them as it's still relational data in reality.

Anyway, I've been playing around with them in regards to the test harness that I've mulling over.

I decided to try them out to store the arguments for each procedure/function. I can certainly string this together at runtime, but again, just trying to learn something new.

I start off with some SQL Objects:

CREATE TYPE r_arguments AS OBJECT
(
  argument_name VARCHAR2(30),
  variable_name VARCHAR2(30),
  data_type VARCHAR2(30),
  defaulted VARCHAR2(1),
  position INTEGER,
  sequence INTEGER,
  in_out VARCHAR2(9)
);
/
show errors

CREATE OR REPLACE
TYPE t_arguments AS TABLE OF R_ARGUMENTS
/

I'll use T_ARGUMENTS as a data type for my table definition:

CREATE TABLE procedures
(
  owner VARCHAR2(30)
    CONSTRAINT nn_owner_procedures NOT NULL,
  package_name VARCHAR2(30),
  procedure_name VARCHAR2(30)
    CONSTRAINT nn_procedurename_procedure NOT NULL,
  function_or_procedure VARCHAR2(1)
    CONSTRAINT forp_forp_procedures CHECK ( function_or_procedure IN ( 'F', 'P' ) )
    CONSTRAINT nn_forp_procedures NOT NULL,
  overload VARCHAR2(40),
  arguments T_ARGUMENTS
)
  NESTED TABLE arguments STORE AS args;

Thanks to Mr. Morgan and his always helpful library I was able to finally figure out the syntax. Specically, I was ommitting the NESTED TABLE clause there at the end.

When you describe the table and set the describe dept to all it looks like this:

CJUSTICE@ELEVEN>set describe depth all

@DESC PROCEDURES
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ----------------
 OWNER                                                 NOT NULL VARCHAR2(30)
 PACKAGE_NAME                                                   VARCHAR2(30)
 PROCEDURE_NAME                                        NOT NULL VARCHAR2(30)
 FUNCTION_OR_PROCEDURE                                 NOT NULL VARCHAR2(1)
 OVERLOAD                                                       VARCHAR2(40)
 ARGUMENTS                                                      T_ARGUMENTS
   ARGUMENT_NAME                                                VARCHAR2(30)
   VARIABLE_NAME                                                VARCHAR2(30)
   DATA_TYPE                                                    VARCHAR2(30)
   DEFAULTED                                                    VARCHAR2(1)
   POSITION                                                     NUMBER(38)
   SEQUENCE                                                     NUMBER(38)
   IN_OUT                                                       VARCHAR2(9)

I got that wrapped up, so how to INSERT?

DECLARE
  l_arguments T_ARGUMENTS;
BEGIN
  l_arguments := T_ARGUMENTS();
  l_arguments.EXTEND(1);
  l_arguments(1) := R_ARGUMENTS
                      ( argument_name => 'TESTING',
                        variable_name => 'L_TESTING',
                        data_type => 'NUMBER',
                        defaulted => 'Y',
                        position => 1,
                        sequence => 1,
                        in_out => 'IN' );

  INSERT INTO procedures
    ( owner,
      package_name,
      procedure_name,
      function_or_procedure,
      overload,
      arguments )
  VALUES
    ( 'TESTING',
      'TESTING',
      'TESTING',
      'F',
      NULL,
      l_arguments );
END;
/

And it looks like this:

OWNER   PACKAGE PROCEDU F OVER ARGUMENTS(ARGUMENT_NAME, VARIABLE_NAME, DATA_TYPE, DEFAULTED, POSITION, SEQUENCE
------- ------- ------- - ---- --------------------------------------------------------------------------------
TESTING TESTING TESTING F      T_ARGUMENTS(R_ARGUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1, 1, 'IN'))

What if you want to be able to select a given value from inside the nested table? I swear in an earlier version of Oracle you just had to qualify, or maybe I'm just crazy, but here's how you do it now:

SELECT variable_name
FROM 
  procedures p,
  TABLE( p.arguments );

VARIABLE_NAME
------------------------------
L_TESTING

You need to CAST your nested table using the TABLE clause. I still haven't figured out how (or if) you ever need to use ARGS (from the NESTED TABLE clause above).

Alright, now I'm going to add 10 records into the nested table. In the relational world, you'd have 2 tables to do this. One for the procedures and one for the arguments. I'll do it in "1."

DECLARE
  l_arguments T_ARGUMENTS;
BEGIN
  l_arguments := T_ARGUMENTS();

  FOR i IN 1..10 LOOP
    l_arguments.EXTEND(1);
    l_arguments(i) := R_ARGUMENTS
                        ( argument_name => 'TESTING',
                          variable_name => 'L_TESTING',
                          data_type => 'NUMBER',
                          defaulted => 'Y',
                          position => 1,
                          sequence => i,
                          in_out => 'IN' );
  END LOOP;

  INSERT INTO procedures
    ( owner,
      package_name,
      procedure_name,
      function_or_procedure,
      overload,
      arguments )
  VALUES
    ( 'TESTING',
      'TESTING',
      'TESTING',
      'F',
      NULL,
      l_arguments );
END;
/

The result of a SQL statements looks like this:

OWNER   PACKAGE PROCEDU F OVER ARGUMENTS(ARGUMENT_NAME, VARIABLE_NAME, DATA_TYPE, DEFAULTED, POSITION, SEQUENCE
------- ------- ------- - ---- --------------------------------------------------------------------------------
TESTING TESTING TESTING F      T_ARGUMENTS(R_ARGUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1, 1, 'IN'), R_AR
                               GUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1, 2, 'IN'), R_ARGUMENTS('TESTING
                               ', 'L_TESTING', 'NUMBER', 'Y', 1, 3, 'IN'), R_ARGUMENTS('TESTING', 'L_TESTING',
                               'NUMBER', 'Y', 1, 4, 'IN'), R_ARGUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1
                               , 5, 'IN'), R_ARGUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1, 6, 'IN'), R_AR
                               GUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1, 7, 'IN'), R_ARGUMENTS('TESTING
                               ', 'L_TESTING', 'NUMBER', 'Y', 1, 8, 'IN'), R_ARGUMENTS('TESTING', 'L_TESTING',
                               'NUMBER', 'Y', 1, 9, 'IN'), R_ARGUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1
                               , 10, 'IN'))

Not too pretty.

I now want to know how many arguments (yes, I already know, it's 10, but humor me):

SELECT 
  owner, 
  package_name, 
  procedure_name, 
  overload,
  COUNT(*) c
FROM 
  procedures p,
  TABLE( p.arguments )
GROUP BY
  owner,
  package_name,
  procedure_name,
  overload;

OWNER   PACKAGE PROCEDU OVER          C
------- ------- ------- ---- ----------
TESTING TESTING TESTING              10

Voila!

I still don't have a practical application for this as this can be done on the fly using PL/SQL collections. Perhaps this in combination with your Middle Tier application (returning UDTs back to the calling application)...

Labels: plsql, udt

  ¶ 1/03/2009 10:52:00 AM   8 Comments Links to this post

  SQL Objects vs. PL/SQL Tables So I was writing a small procedure to loop through a procedure's arguments (all_arguments).

After getting it working, I then began to port it to packaged code, which is where I ran into a little problem.

Here's the initial statement:

DECLARE
  TYPE r_records IS RECORD
  (
    owner VARCHAR2(30),
    package_name VARCHAR2(30),
    procedure_name VARCHAR2(30),
    overload VARCHAR2(40),
    argument_name VARCHAR2(30),
    sequence INTEGER,
    in_out VARCHAR2(9)
  );
  TYPE t_records IS TABLE OF R_RECORDS INDEX BY BINARY_INTEGER;
  l_records T_RECORDS;
BEGIN
  SELECT
    owner, 
    package_name, 
    object_name,
    overload,
    argument_name,
    sequence, 
    in_out 
  BULK COLLECT INTO l_records
  FROM dba_arguments
  WHERE package_name = 'DBMS_UTILITY'
    AND object_name IN ( 'COMPILE_SCHEMA', 'INVALIDATE' )
  ORDER BY owner, package_name, object_name, position;
END;
/

Runs fine.

My first step to refactor was to use the TYPE declaration in the package header. But you can't do that (little rusty on pl/sql tables). So I created SQL Objects or User Defined Types (UDT).

CREATE TYPE r_procedure_arguments AS OBJECT
(
  owner VARCHAR2(30),
  package_name VARCHAR2(30),
  procedure_name VARCHAR2(128),
  overload VARCHAR2(40),
  argument_name VARCHAR2(30),
  position INTEGER,
  sequence INTEGER,
  in_out VARCHAR2(9)
);
/
show errors

CREATE TYPE t_procedure_arguments AS TABLE OF R_PROCEDURE_ARGUMENTS;
/
show errors

Then I rewrote the anonymous block to use the UDT.

CJUSTICE@ELEVEN>DECLARE
  2    l_records T_PROCEDURE_ARGUMENTS;
  3  BEGIN
  4    SELECT
  5      owner,
  6      package_name,
  7      object_name,
  8      overload,
  9      argument_name,
 10      position,
 11      sequence,
 12      in_out 
 13    BULK COLLECT INTO l_records
 14    FROM all_arguments;
 15  END;
 16  /
  FROM all_arguments;
  *
ERROR at line 14:
ORA-06550: line 14, column 3:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

not enough values? OK, let's go through it. There are 8 "columns" in the defined object and 8 in the SELECT clause. Hmmm...I tried adding some extra values at the end:

DECLARE
  l_records T_PROCEDURE_ARGUMENTS;
BEGIN
  SELECT
    owner,
    package_name,
    object_name,
    overload,
    argument_name,
    position,
    sequence,
    in_out, 1, 2, 3, 4
  BULK COLLECT INTO l_records
  FROM all_arguments;
END;
/

Same result, not enough values.

I spent the next hour toggling between gmail, facebook and meebo thinking about the problem.

Wait! You have to "cast" the values from the SELECT clause!

CJUSTICE@ELEVEN>DECLARE
  2    l_records T_PROCEDURE_ARGUMENTS;
  3  BEGIN
  4    SELECT
  5      R_PROCEDURE_ARGUMENTS( owner,
  6      package_name,
  7      object_name,
  8      overload,
  9      argument_name,
 10      position,
 11      sequence,
 12      in_out )
 13    BULK COLLECT INTO l_records
 14    FROM all_arguments;
 15  END;
 16  /

PL/SQL procedure successfully completed.

Duh.

So I post it here so next time I don't waste an hour trying to remember why I can't get it to work.

Labels: development, plsql, sql, udt

  ¶ 12/30/2008 01:32:00 AM   9 Comments Links to this post

  Application Developers vs. Database Developers: Part II You can read the first article here. My application developer friend, Mr. M, emailed me and another fine gentleman this little blurb recently:

Mr. M:

OH YEAH BABY!!! TEN TIMES FASTER!!!! YEAH!!!!!!!!

Hey seriously, what a tub of shit Oracle is. Where does this myth come from that it's such a great platform? Their client tools suck balls and it's generally just a pain in the ass to work with from a developer's point of view. But devs for some reason are under this impression that from thew server perspective it's rock solid and performant. Well, it may be solid, but it's a fucking turd. Our dba here - definitely an Oracle guy - has been tasked with looking into moving us onto an oss db. He basically refuses to even look at MySQL, stating that it's a mickey mouse worthless pile of shit (can't really argue with him there lol), so that basically leaves Postgres. So it's been a few weeks now, and he will basically admit now that Postgres completely waxes Oracle as far as performance goes. LOL We run RAC in production too. He's looking at failover, replication, blah blah blah now, we'll see what the verdict on that is. Oh, and Oracle AQ? That's a worthless pile of shit too. Why do they tack a fucking message queue that doesn't play nice with jms onto the fucking database, which is probably already overworked? Oh wait, that's right, they're in the business of selling per cpu licenses! Cocksuckers.

This was prompted by a recent Oracle email blast about the Exadata storage system/Warehouse.

As I did before, I'll just put the email here.

Me:

Agreed, their client tools aren't all the great. Which ones are you using?

I use SQL*Plus (naturally), SQL Developer and JDeveloper. The latter 2 tie in very nicely with Subversion. With JDeveloper, when I want to try out Java, it's got a pretty good suite of tools.

Oracle starting out catering to businesses, Microsoft started with the consumer. Oracle has made pretty good strides in making their software more usable while Microsoft has made theirs more scalable.

I haven't used AQ a whole lot and definitely not with Java. I do know that it's billed as JMS compliant.

Postgres has it's place and so does Oracle. It is a great platform if you know how to leverage it. PL/SQL is a 3GL (or is it 4) and you can write entire applications using it...and if you do that right, an incredibly robust and scalable application at that.

Mr. M:

"It is a great platform if you know how to leverage it. PL/SQL is a 3GL (or is it 4) and you can write entire applications using it...and if you do that right, an incredibly robust and scalable application at that."

NO!!! NO!!! NOOOOO!!!

I want to beat people like you who say this with a ball pean hammer. There are only a select few individuals on this earth who can write and read application logic written in SQL. AVOID THIS ANTI-PATTERN AT ALL COSTS! What is it with you f_cking database guys??? Why do you always want to stuff as much crap into the db as possible?

DUDE! SQL IS A PILE OF SHIT FOR EXPRESSING APPLICATION LOGIC!!!

It's fine for querying and manipulating sets of data, in a relational database. But it is a worthless sack of shit for expressing application logic!

I'm having to dig through this f_cking abortion right now because some Oracle f_ckhead thought "you can write entire applications using it!" Blog that, mofo!

This was followed by a package he had been working on. I wouldn't say it was the greatest, but it wasn't all bad either.

Me:

goodness gracious.

"DUDE! SQL IS A PILE OF SHIT FOR EXPRESSING APPLICATION LOGIC!!!"

disagree (naturally). It's incredibly easy to do, you just don't know how yet...and it seems even the Oracle professionals out there don't either.

I'll tell you this, the crazier the SQL or PL/SQL needed to derive and manipulate data the poorer the design. Start with a good design and it all becomes very simple.

Of course note the use of "naturally" in my lexicon. Thanks Jake.

Mr. M:

well dude, we are back to our old discussion - you arguing that procedural sql code is perfectly fine for building apps, and by extension, that the last 20 years of computer science have basically been a misguided lost journey down the meandering, fruitless trail of oop. um.....no. select this from that. otherwise keep that sql crap caged up where it belongs.

btw, do the db guys here suck? seriously. i'm not competent enough to judge. (to be fair, apparently that crap i sent you is fairly old stuff, but still....)

Me:

I would say, based on limited knowledge of software development, that the OOP movement was started because the database (specifically Oracle) was not mature enough to do what was needed. Plus, I seem to recall that the OOP movement was supposed to have solved all the world's problems by now.

It's further propogated due to the needs you specified in our discussion that day at WellCare (i.e. performance). I still believe that if application developers better understood how a DB works (Oracle, MySQL, SQLServer, etc) they would end up writing less code. Database constraints alone force you to write less (and better) code simultaneously ensuring good data.

The code that I did look at (first 1000 lines or so) isn't great.
1. With all those IF THEN ELSE statements it's telling me that there's probably a better way to store the data. Looks like they're missing an attribute that should be contained with a track.
2. using Object (PL/SQL) types to store data in the way they seem to be doing it is not the best way. Again, probably a design issue.
3. When you do something like this:

   UPDATE pb_album_metadata
      SET primary_digital_flag = 0
    WHERE album_id IN (SELECT b.album_id
                       FROM (SELECT a.album_id AS album_id,
                                MAX(a.album_id) OVER (PARTITION BY a.standard_upc) AS latest_album_id
                             FROM pb_album_metadata a
                             WHERE a.standard_upc = g_album_tab(1).standard_upc ) b
                       WHERE b.album_id <> b.latest_album_id )
         AND primary_digital_flag <> 0;

They should probably have considered end_date as an attribute of the album metadata. While analytic functions are pretty cool, they're more for analyzing (OLAP) and not OLTP environments.

That's for starters and without table definitions...

Me (again):

oh yeah...and PL/SQL is/was built on top of ADA, FYI.

Mr. M:

"I still believe that if application developers better understood how a DB works (Oracle, MySQL, SQLServer, etc) they would end up writing less code. Database constraints alone force you to write less (and better) code simultaneously ensuring good data."

Huh? What are we not understanding? What would be an example of a constraint that would force us to write less and better code?

Me:

CHECK, NOT NULL (same as CHECK) and FOREIGN KEY constraints all fit into that category.

Instead of having to check if a value is NULL when submitting it to the database, just submit and let the database throw an error, which you'll conveniently capture in your Exception block. Same goes for CHECK constraints, columnA should be either one of three values (test, test1, test3), you won't have to check that in your application code, just catch the exception. FKs insure good data (proper relationships).

A different perspective. If you are going to pay for it, use that damn thing. If you don't care, don't do it. But over the course of an "enterprisey" application, you'll end up writing more code and make maintenance that much more difficult (did I do it there?). Just those constraints will force you and the teams of application developers to put good data into the database.

You can still do it in the application of course (form validation is a good place)...

Mr. M:

Ahh, jeez dude, I wasn't sure if you were referring to the literal "constraint" or not.

Dude, even f_cksticks like redacted I think have a decent enough understanding of when and how to use db constraints. It's when you get into things like cursors or cost plans of subselects and anonymous tables (i think that's the name for it - where I say select something from (select some other crap). Then we defer to db gurus like yourself.

But dude....."you won't have to check that in your application code, just catch the exception".......uh, don't ever repeat that off our little email group. And definitely don't go posting that on your blog. F_ck me man, it's a damn good thing we keep you db folks caged up in that rdbms box....

Me:

So we've had this discussion at work...for a high transaction system, do Java/C/etc handle exceptions well or what?

Why is it bad to deal with exceptions rather than coding to avoid them?

I highly doubt even redacted understood database constraints...him and his cohorts believed all database software would be commoditized and MySQL would be king in short order.

Mr. M:

"for a high transaction system"

Or for any system really....

To use your example of check constraints (is this value one of....) or not null constraints, checking these rules in the Java code and in the database code would seem to violate DRY. But we do that alot, and it is acceptable in certain cases. For instance, we also probably violate DRY if we're checking this same rule say in Javascript on the front end. But we accept this tiny violation of DRY because it tends to make for a better user experience and as a performance gain too, for we avoid the round trip to the server. Now, what your advocating here is close to the same thing. You're basically saying, don't check for the not null constraint in Java code, just go ahead and hit the database, let the database throw back an exception to the Java code, presumably correct the problem, and then make another roundtrip to the database again. Dude, what are you thinking?!? This to say nothing of the fact that this also could be considered a violation of Fail Fast, and a violation of Defensive Programming - what happens if the dba forgot to add the not null constraint in production?

Dude, listen to this guy. For a "high transaction system" basically you want to treat the database, as much as you can, as just a dumb data holder. A f_cking dumpster, that you just throw sh_t into and pull shit back out, using no vendor-specific features if at all possible.

Again, for we've had this discussion, but even in everyday apps, not just on Wall Street, the database is the bottleneck. And the database is the hardest layer to scale. So given those facts, you should design your app to do as little work in the database as possible.

I was laughing at this point because the link above points to one of our consulting architects (I'm not really sure what his role is at this point).
Me:

i agree in any application that you want to minimize the number of round trips...

shocker...he's one of our architects. he's spot on in many instances, but...

database is the bottleneck because people don't know how to write SQL. I'll certainly concede the wall street applications (for the time being anyway), but the rest of us with what we do on a daily basis...Oracle will scale far beyond the demands they place. When that bottleneck shows up, 9 times out of 10 some dumb-ass c#/java guy thought he could write a better query than i. besides, what's the idiot doing anything but a simple join anyway? probably poor data model to start with...and we're right back where we started (sort of).

Mr. M:

"database is the bottleneck because people don't know how to write SQL.....some dumb-ass c#/java guy thought he could write a better query than i."

Dude, I'll grant you, people don't know how to write SQL, myself included. But that's not always why the database is the bottleneck. I think it's safe to say that's not even the majority of the reason. Yes, there are apps written by people who were just idiots, and they needlessly pummel the database, but that's definitely not the majority of scenarios. At my work the database is the bottleneck, and we run RAC in production. It's my understanding that even with RAC, there is a limit to how much you can scale that out. But any layer up from the database we are basically unlimited in how much we can scale that out. So it's very easy to stick another Apache box in front, or fire up another Weblogic server. But I can't do that with the database. We have 24 Weblogic servers for redacted. The database is the bottleneck. And we don't have shitty sql code in the app. In fact, we have very few hand-written queries anywhere in the app. Persisting something to a database is really a low-level concern that as an application developer I shouldn't even have to be bothered with, except for the rare corner cases where the persistence abstraction I'm using is just a little bit too generic to handle things effectively. And we don't use these ORMs because we don't know how to write sql. To effectively use an ORM tool requires a deeper understanding of sql and databases than simply being able to write solid SQL code. (Not saying Java devs who use ORMs know SQL better than a dba, just that it requires better sql skills than is required of a Java dev to simply write JDBC/SQL.) Now, before you try to tell me that my ORM library isn't able to write as efficient of sql code as a dba of your caliber, keep in mind that ORM tools are pretty advanced. They're able to intelligently do things like batch sql updates, and let you apply transactional semantics much easier than with raw jdbc/sql. But the overwhelming reason developers have so thoroughly adopted ORM is because Structured Query Language is such a nasty piece of shit for expressing application logic. SQL is a declarative, procedural language. It's totally unsuited for writing application logic! This, more than anything else, is why organizations and dev teams should seek to restrict what is handled within a relational database as much as possible - because the programming interface to it is a fucking ancient backward dying dinosaur.

Mr. V (note, not Mr. M):

My 2 canadian cents:
The polyglot approach "... use different languages for different domain ..."
Database was developed to manipulate data and should remain there.
General purpose language was developed to encapsulate logic and should remain in that domain.
You should not use DB to encapsulate business logic (in my opinion) no more than you would use HTML to create complex logic.

While Java, C#, Python, etc are described as General Purpose languages, they, too, are really domain-constrained. Their expressiveness are confined (and should be) to express low-level, tersed, explicit, verbose, and repetive logic (if that makes any sense). Languages such as these are more suitable for low-level abstraction on top of which, richer, more expressive languages can be built. We are seeing this now with the emergence of languages on the JVM (and arguably on the .Net's CLR).

I think SQL as we know will take a back seat and a smart developer somewhere will create a new domain-specific language that lives on the VM and will push the SQL expression out of the RDBMS and closer to the code that needs it. We are not there yet, but Microsfot is trying (see LINQ and all ORM papers). This is not to say that there won't be isntances where tuning in the SQL-native language won't be necessary. However, 80 to 90% of simple CRUD cases will be handled closer to the logic code that uses the data code.

Again, that's my 2 canadian cents... I could go on. But I have a meeting with redacted.

Mr. V, I believe, is a little bit more sensible. Mr. M on the other hand is just trying to rile (sp?) me up.

Me:

Someone will probably create something like that, but it still gets at the heart of one of my arguments, many developers don't know how to use a database thus will go to any means to circumvent it. Embrace it I say.

Ultimately for me, it comes down to simplicity. I can write an application using PL/SQL that will scale very well for 90% of the solutions. Of course that doesn't include the "fancy" javascript/Ajax stuff needed for the UI. In my ever so humble opinion, this is a much better solution for a business in the long run.
1. You're more likely to change the middle tier than the database tier. Java, asp, Ruby, what's next?
2. Fewer moving parts means lower cost of ownership, even with the CPU costs. One person, one, was able to build and maintain a 350 page website. Be hardpressed to do that with the more expressive languages.

I think all of us are purists and very passionate about what we do. It's probably the main reason we get along. I thoroughly enjoy these conversations because it does force me to think...and that's always a good thing.

Mr. V:

Haaa chet.
You sorta made my point than diverged away. Building an app in all PL/SQL is dangerous. It's no safer than me building an app in all Java. I can build very limited app in all Java. The moment I need to interact with other domain (UI, data, low-level native code, etc), I have to switch to something that is closer to what I am trying to do. If I need to create UI, I will pick a ui-centric environment, when I need to talk to DB, I will pass connect to a db and send in my SQL, and so forth. I will use Java as the glue to bring it all togher.

In the end, we may be saying the same thing, but using different accent. O well.

And that's where it ended. I must say it's always fun. Mr. M and Mr. V are both very smart individuals and I highly respect what they do. We have different perspectives...but I think they listen, if only a little, as I listen to them. Their voices creep up on me especially now...which is definitely a good thing.

Labels: design, development, funny, humility, ideas, java, plsql

  ¶ 12/09/2008 09:11:00 PM   23 Comments Links to this post