Recently, I came across one of those situations where you need to get some data from a Production instance and transform it in some way. The catch is that you only have read access on the database in question. All of the usual methods of extracting data are blocked by either an extremely long and tedious battle to get the required access or are prohibited by various security policies.
I will need to do this quite regularly, so it looks like I’ll have to write some slave SQL to extract the data and then load it into a non-production database, where I have more privileges.
Once again, I’m going to have to struggle to remember the new Oracle Q quote syntax.
But before all that, a brief history of quotes.
In the beginning, there was the escape character. In SQL*Plus, this happens to be a single quote …
select 'It''s only a game' from dual;
Things got a bit more complicated when you needed to build a string that included column values…
SELECT 'This is '||USER||'''s session' FROM dual;
There was however an alternative to runaway punctuation.
SELECT 'This is '||USER||CHR(39)||'s session' FROM dual;
Good old ASCII.
Now we come bang up to date with Oracle’s new quote syntax ….
SELECT 'This is '||USER||q'['s session]' FROM dual;
Note to self – the syntax is :
q'[Hey, that’s my quoted string]’
The number of times I get those brackets in the wrong place !
So, back to my current predicament.
Let’s take a topical example.
I’ve got a table of teams in the Cricket World Cup ( going on in India and Bangladesh as I write). Included in the table is the time and date of each team’s first game in the tournament.
Anyway, here’s the DDL for the table and the DML to populate it so you can play along if you’re so inclined.
CREATE TABLE teams (
country_code VARCHAR2(3),
country_name VARCHAR2(30),
ranking NUMBER(2),
first_match DATE)
/
DECLARE
PROCEDURE ins( p_code VARCHAR2, p_name VARCHAR2, p_rank NUMBER,
p_date DATE) IS
BEGIN
INSERT INTO teams( country_code, country_name, ranking,
first_match)
VALUES( p_code, p_name, p_rank,
p_date);
END ins;
BEGIN
ins( 'AUS', 'AUSTRALIA', 1, TO_DATE('21/02/11 14:30', 'DD/MM/RR HH24:MI'));
ins( 'IND', 'INDIA', 2, TO_DATE( '19/02/11 14:30', 'DD/MM/RR HH24:MI'));
ins( 'SL', 'SRI LANKA', 3, TO_DATE( '20/02/11 14:30', 'DD/MM/RR HH24:MI'));
ins( 'SA', 'SOUTH AFRICA', 4, TO_DATE( '24/02/11 14:30', 'DD/MM/RR HH24:MI'));
ins( 'ENG', 'ENGLAND', 5, TO_DATE( '22/02/11 14:30', 'DD/MM/RR HH24:MI'));
ins( 'PAK', 'PAKISTAN', 6, TO_DATE( '23/02/11 14:30', 'DD/MM/RR HH24:MI'));
ins( 'NZ', 'NEW ZEALAND', 7, TO_DATE( '20/02/11 09:30', 'DD/MM/RR HH24:MI'));
ins( 'BAN', 'BANGLADESH', 8, TO_DATE( '19/02/11 14:30', 'DD/MM/RR HH24:MI'));
ins( 'WI', 'WEST INDIES', 9, TO_DATE( '24/02/11 14:30', 'DD/MM/RR HH24:MI'));
ins( 'IRE', 'IRELAND' ,10, TO_DATE( '25/02/11 14:30', 'DD/MM/RR HH24:MI'));
ins( 'ZIM', 'ZIMBABWE', 11, TO_DATE( '21/02/11 14:30', 'DD/MM/RR HH24:MI'));
ins( 'NET', 'NETHERLANDS', 12, TO_DATE( '22/02/11 14:30', 'DD/MM/RR HH24:MI'));
ins( 'KEN', 'KENYA', 13, TO_DATE( '20/02/11 09:30', 'DD/MM/RR HH24:MI'));
ins( 'CAN', 'CANADA', 14, TO_DATE( '20/02/11 14:30', 'DD/MM/RR HH24:MI'));
END;
/
I’ll want to capture this data regularly so I need to write a bit of slave SQL so that I can generate insert statements for these records to drop into another database.
At this point, the usual dire warnings about the dangers of SQL Injection apply.
So, if I’m not completely sure that there are no nasty surprises in the data I’m extracting, I can always strip out any dodgy characters. All the SQL injection attack methods I know of require some sort of punctuation character, so by stripping those out, I should guard against that particular problem. I know that I shouldn’t have any such characters in the VARCHAR columns I’m selecting from so I won’t corrupt any of the data by transforming it in this way.
As for the other columns, well, I want to retain the time element of the date anyway, and explicitly stating the date format is always a good move if you want to avoid vulnerability to an injection via NLS_DATE_FORMAT.
So, with the q syntax, the query is going to look something like this ( running in SQL*Plus):
set heading off
set lines 300
set pages 100
set feedback off
spool old_quote_ins.sql
SELECT 'INSERT INTO remote_teams( country_code, country_name, ranking, first_match) '
||' VALUES ( '
||q'[']'||REGEXP_REPLACE(country_code,'[[:punct:]]')||q'[',]'
||q'[']'||REGEXP_REPLACE(country_name,'[[:punct:]]')||q'[',]'
||ranking||','
||'TO_DATE('||q'[']'||TO_CHAR( first_match, 'DD-MM-RR HH24:MI')||q'[','DD-MM-RR HH24:MI'));]'
FROM teams
/
spool off
set feedback on
set heading on
Yep, we sill need 3 quotes to print out a single quote. However, by mixing the ASCII character approach with the quotation syntax we get this :
set heading off
set lines 300
set pages 100
set feedback off
spool mixed_quote_ins.sql
SELECT 'INSERT INTO remote_teams( country_code, country_name, ranking, first_match) '
||' VALUES( '
||CHR(39)||REGEXP_REPLACE(country_code,'[[:punct:]]')||CHR(39)||','
||CHR(39)||REGEXP_REPLACE(country_name,'[[:punct:]]')||CHR(39)||','
||ranking||','
||'TO_DATE( '||CHR(39)||TO_CHAR( first_match, 'DD-MM-RR HH24:MI')|| q'[', 'DD-MM-RR HH24:MI'));]'
FROM teams
/
spool off
set feedback on
set heading on
Anyway, I now have a series of insert statements that I can point at the table in my remote database
INSERT INTO remote_teams( country_code, country_name, ranking, first_match) VALUES( 'AUS','AUSTRALIA',1,TO_DATE( '21-02-11 14:30', 'DD-MM-RR HH24:MI')); INSERT INTO remote_teams( country_code, country_name, ranking, first_match) VALUES( 'IND','INDIA',2,TO_DATE( '19-02-11 14:30', 'DD-MM-RR HH24:MI')); INSERT INTO remote_teams( country_code, country_name, ranking, first_match) VALUES( 'SL','SRI LANKA',3,TO_DATE( '20-02-11 14:30', 'DD-MM-RR HH24:MI')); INSERT INTO remote_teams( country_code, country_name, ranking, first_match) VALUES( 'SA','SOUTH AFRICA',4,TO_DATE( '24-02-11 14:30', 'DD-MM-RR HH24:MI')); INSERT INTO remote_teams( country_code, country_name, ranking, first_match) VALUES( 'ENG','ENGLAND',5,TO_DATE( '22-02-11 14:30', 'DD-MM-RR HH24:MI')); INSERT INTO remote_teams( country_code, country_name, ranking, first_match) VALUES( 'PAK','PAKISTAN',6,TO_DATE( '23-02-11 14:30', 'DD-MM-RR HH24:MI')); INSERT INTO remote_teams( country_code, country_name, ranking, first_match) VALUES( 'NZ','NEW ZEALAND',7,TO_DATE( '20-02-11 09:30', 'DD-MM-RR HH24:MI')); INSERT INTO remote_teams( country_code, country_name, ranking, first_match) VALUES( 'BAN','BANGLADESH',8,TO_DATE( '19-02-11 14:30', 'DD-MM-RR HH24:MI')); INSERT INTO remote_teams( country_code, country_name, ranking, first_match) VALUES( 'WI','WEST INDIES',9,TO_DATE( '24-02-11 14:30', 'DD-MM-RR HH24:MI')); INSERT INTO remote_teams( country_code, country_name, ranking, first_match) VALUES( 'IRE','IRELAND',10,TO_DATE( '25-02-11 14:30', 'DD-MM-RR HH24:MI')); INSERT INTO remote_teams( country_code, country_name, ranking, first_match) VALUES( 'ZIM','ZIMBABWE',11,TO_DATE( '21-02-11 14:30', 'DD-MM-RR HH24:MI')); INSERT INTO remote_teams( country_code, country_name, ranking, first_match) VALUES( 'NET','NETHERLANDS',12,TO_DATE( '22-02-11 14:30', 'DD-MM-RR HH24:MI')); INSERT INTO remote_teams( country_code, country_name, ranking, first_match) VALUES( 'KEN','KENYA',13,TO_DATE( '20-02-11 09:30', 'DD-MM-RR HH24:MI')); INSERT INTO remote_teams( country_code, country_name, ranking, first_match) VALUES( 'CAN','CANADA',14,TO_DATE( '20-02-11 14:30', 'DD-MM-RR HH24:MI'));
Oh well, that should keep me occupied whilst I wait to see just how the giants of World Cricket will fare. You know, India, Australia, South Africa…Ireland. Honestly, I think it’d be better if England just didn’t bother turning up to any tournament calling itself “World Cup”. It’d just save time.
