 |  |  |
 |  |  |
These shirts will be the must-have item at #oow09, so get yours now...I can’t wait to see action shots from the conference.I love this idea. What would be awesome, besides the natural "In The Wild" shots, would be a group shot...perhaps at the Blogger Meet-Up?
When Drew beats Cancer we hope to have sponsors that will donate a dollar for every participant to our partner, LIVESTRONG.It reminded me of myself post Kate incident. I'd jokingly try to get away with all kinds of stuff saying something to the effect of, "But my daughter is in the hospital, she almost died."
SELECT OWNER, TABLE_NAME FROM DBA_TABLES...that's pretty rare. I find myself backspacing to fix it
SELECT owner, table_nameThat might not be the best example (I have a script for that anyway), but you get the point.
FROM dba_tables
CREATE TABLE tand a procedure (which does nothing obviously)
(
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
);
CREATE OR REPLACEWhat's the best way to integrate the procedure into your code? I've seen this:
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;
CREATE OR REPLACESince I default the input parameters to 0, I didn't have to specify each individual parameter every time I called it. I like that.
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;
CREATE OR REPLACENot 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.
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;
DECLARESo I began to use them (despite the bad feeling I get...).
l_table MY_TABLE%ROWTYPE;
BEGIN
INSERT INTO my_table
VALUES l_table;
END;
DECLARE
l_table MY_TABLE%ROWTYPE;
BEGIN
UPDATE my_table
SET ROW = l_table
WHERE my_table_id = l_table.my_table_id;
END;
CREATE TABLE my_tableSee the column DEFAULTs?
(
my_table_id NUMBER(10)
CONSTRAINT pk_mytableid PRIMARY KEY,
status VARCHAR2(1) DEFAULT 'A'
CONSTRAINT nn_status_mytable NOT NULL
CONSTRAINT ck_aori_status_mytable CHECK ( status IN ( 'I', 'A' ) ),
start_date DATE DEFAULT SYSDATE
CONSTRAINT nn_startdate_mytable NOT NULL
);
l_table.status := 'A';you are hosed. Plus, what if the calling application had already set those, now you have this:
l_table.start_date := SYSDATE;
IF l_table.status IS NULL THENSo where is the savings in that?
l_table.status := 'A';
END IF;
IF l_table.start_date IS NULL THEN
l_table.start_date := SYSDATE;
END IF;
CREATE OR REPLACE TYPE r_key_value_record IS OBJECTNothing 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.
(
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
/
FUNCTION parse_urlI 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 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;
ISNothing special here.
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
IF p_line_start IS NOT NULL THENGetting rid of any start or end characters. Next up, my kludge:
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;
IF SUBSTR( l_string, -1, l_token_delimiter_length ) != p_token_delimiter THENJust adding the delimiter to the end of the string. Apparently I'm too lazy to figure out a better way.
l_string := l_string || p_token_delimiter;
END IF;
LOOPWhat does all that do? Let's see.
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;
SET DEFINE OFFHow about a different flavor of URL string?
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
BEGINOK, one more. For this one, there will be no key/value pair, it's simply ordered (yes, you need the API to decipher it).
: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!
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
CREATE OR REPLACE TYPE r_key_value_record IS OBJECTThe simple function, just for demonstration purposes:
(
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
/
CREATE OR REPLACEThis 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.
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
[[:digit:]]{13,16}That's it. That's all there is too it and it's applicable to each and every gateway. You could have some false positives if you're passing through 13 to 16 digit numbers, but this is essentially a logging table, so who cares. If I need to see the exact credit card number, I can look it up through the usual process (access, decrypting, etc).VAR C VARCHAR2(4000);I'm starting with the easy cases, numbers, 13 to 16 digits long:
SELECT regexp_replace( :c, '[[:digit:]]{13,16}', 'X' ) r
FROM dual;
CJUSTICE@TESTING>EXEC :C := 1234123412341234;Now some of the negative cases, still just numbers:
CJUSTICE@TESTING>/
R
----------------------------------------------
X
CJUSTICE@TESTING>--15
CJUSTICE@TESTING>EXEC :C := 123412341234123;
CJUSTICE@TESTING>/
R
----------------------------------------------
X
CJUSTICE@TESTING>--14
CJUSTICE@TESTING>EXEC :C := 12341234123412;
CJUSTICE@TESTING>/
R
----------------------------------------------
X
CJUSTICE@TESTING>--13
CJUSTICE@TESTING>EXEC :C := 1234123412341;
CJUSTICE@TESTING>/
R
----------------------------------------------
X
CJUSTICE@TESTING>--17So any number over 16 is not masked. For my purposes, I don't much care.
CJUSTICE@TESTING>EXEC :C := 12341234123412341;
CJUSTICE@TESTING>/
R
------------------------------------------------------------
X1
CJUSTICE@TESTING>--18
CJUSTICE@TESTING>EXEC :C := 123412341234123412;
CJUSTICE@TESTING>/
R
------------------------------------------------------------
X12
CJUSTICE@TESTING>--19
CJUSTICE@TESTING>EXEC :C := 1234123412341234123;
CJUSTICE@TESTING>/
R
------------------------------------------------------------
X123
CJUSTICE@TESTING>--20
CJUSTICE@TESTING>EXEC :C := 12341234123412341234;
CJUSTICE@TESTING>/
R
------------------------------------------------------------
X1234
CJUSTICE@TESTING>--30
CJUSTICE@TESTING>EXEC :C := 123412341234123412341234123412;
CJUSTICE@TESTING>/
R
------------------------------------------------------------
XX
CJUSTICE@TESTING>SET DEFINE OFFThis is more for me than it is for you (another reason you should blog). I always forget how to use Regular Expressions since I encounter the opportunity to use them so rarely.
CJUSTICE@TESTING>EXEC :C := 'card_number=1234123412341234';
CJUSTICE@TESTING>/
R
----------------------------------------------------------------------------
card_number=X
CJUSTICE@TESTING>EXEC :C := 'test=bollocks&card_number=1234123412341234';
CJUSTICE@TESTING>/
R
----------------------------------------------------------------------------
test=bollocks&card_number=X
CJUSTICE@TESTING>EXEC :C := 'card_number=1234123412341234&test=bollocks';
CJUSTICE@TESTING>/
R
----------------------------------------------------------------------------
card_number=X&test=bollocks
CJUSTICE@TESTING>SET DEFINE ON
FUNCTION is_authorizedand I found it didn't work too well.
( 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;
FUNCTION is_authorizedI'll also be updating the other post to point to this one.
( 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;
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 *.
PROCEDURE update_person_nameYou could have
( 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
...
PROCEDURE update_person_name( p_personname_row IN PERSON_NAME%ROWTYPE )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:
IS
BEGIN
...
DECLAREAs opposed to the other way
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;
BEGINHere's where I get a little scared though. Inside the procedure which accepts the PERSON_NAME%ROWTYPE, my UPDATE looks like this:
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;
UPDATE person_nameAll the NVL make me feel dirty for some reason. I'm not saying it makes sense, I'm just saying.
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;
Foreign keys can be defined as multiple columns. However, a composite foreign key must reference a composite primary or unique key with the same number of columns and the same datatypes. Because composite primary and unique keys are limited to 32 columns, a composite foreign key is also limited to 32 columns.Not really hard to miss I guess.
CREATE TABLE my_codesI want to add a surrogate key to the MY_CODES table, per this discussion. I made a compromise, essentially anything that will or could be user entered will have a surrogate key (either SYS_GUID or sequence generated).
(
mycode VARCHAR2(20)
CONSTRAINT pk_mycode PRIMARY KEY
);
CREATE TABLE t
(
tid NUMBER(10)
CONSTRAINT pk_tid PRIMARY KEY,
mycode
CONSTRAINT fk_mycode_t REFERENCES my_codes( mycode )
CONSTRAINT nn_mycode_t NOT NULL
);
ALTER TABLE t DROP CONSTRAINT fk_mycode_t;Since I'm changing the meaning, I want to make sure that someone doesn't enter the same code twice, so I add a UNIQUE constraint on the table.
ALTER TABLE my_codes DROP CONSTRAINT pk_mycode;
ALTER TABLE my_codes ADD ( mycodeid NUMBER(10) );
ALTER TABLE my_codes
ADD CONSTRAINT pk_mycodeid
PRIMARY KEY ( mycodeid );
ALTER TABLE my_codesI rebuild my tables, without referencing the new PK.
ADD CONSTRAINT uq_mycode
UNIQUE ( mycode );
CJUSTICE@TESTING>CREATE TABLE my_codesWait a minute. I didn't change the FK to point to the PK.
2 (
3 mycodeid NUMBER(10)
4 CONSTRAINT pk_mycodeid PRIMARY KEY,
5 mycode VARCHAR2(20)
6 CONSTRAINT uq_mycode UNIQUE
7 CONSTRAINT nn_mycode NOT NULL
8 );
Table created.
CJUSTICE@TESTING>CREATE TABLE t
2 (
3 tid NUMBER(10)
4 CONSTRAINT pk_tid PRIMARY KEY,
5 mycode
6 CONSTRAINT fk_mycode_t REFERENCES my_codes( mycode )
7 CONSTRAINT nn_mycode_t NOT NULL
8 );
Table created.
CREATE TABLE tDo a describe on that table:
(
x INTEGER
);
CJUSTICE@TESTING>@DESC TRunning a query on USER_TAB_COLUMNS results in this:
Name Null? Type
-------------------------------------- -------- ------------
X NUMBER(38)
SELECTThe describe says NUMBER(38) yet USER_TAB_COLUMNS says NUMBER(22). Weird.
data_type,
data_type_mod,
data_length,
data_precision,
data_scale,
default_length
FROM user_tab_columns
WHERE table_name = 'T'
AND column_name = 'X';
DATA_TYPE DAT DATA_LENGTH DATA_PRECISION DATA_SCALE DEFAULT_LENGTH
---------- --- ----------- -------------- ---------- --------------
NUMBER 22 0
CREATE TABLE "CJUSTICE"."T"So Oracle converts it somewhere along the way?
(
"X" NUMBER(*,0)
);
The following prizes will be awarded for the APEX Developer Competition based on the judges rankings:You have until August 24th to get your application complete and submitted. So hurry.
- First Place - An Unconference Session and One complimentary ticket to Oracle Openworld 2009* in San Francisco October 11 - 15.
- Second Place - One complimentary ticket to Oracle Openworld 2009* in San Francisco October 11 - 15.
- Third Place - One complimentary ticket to Oracle Openworld 2009* in San Francisco October 11 - 15.
- Fourth through Twelfth Place - One copy of the book Pro Oracle Application Express, Apress Publishing, written by John Scott & Scott Spendolini.
Just make a 30-second video describing why you want to go to Oracle OpenWorld 2009.Use any camera you have close at hand—Webcam, cell phone, handheld. Give us your best, most creative, most innovative pitch, and post your video as a response to ours above on the Oracle Web Video YouTube channel before September 30.The post is here. Good luck!