Using VARIABLE instead of CREATE TABLE


A application support person tried to argue for the CREATE TABLE privilege today in a production environment. I asked them why like a good two year-old. And their answer was a script similar to the following:

CREATE TABLE tmp_grandparent_id AS (SELECT rec_seq.nextval AS record_id FROM dual);
CREATE TABLE tmp_parent_id AS (SELECT rec_seq.nextval AS record_id FROM dual);
CREATE TABLE tmp_child_id AS (SELECT rec_seq.nextval AS record_id FROM dual);

INSERT INTO grandparent_table (id, data1)
    VALUES((SELECT record_id FROM tmp_grandparent_id), 'GRANDPARENT');
INSERT INTO parent_table (id, parent, data1)
    VALUES((SELECT record_id FROM tmp_parent_id), (SELECT record_id FROM tmp_grandparent_id), 'PARENT');
INSERT INTO child_table (id, parent, data1)
    VALUES((SELECT record_id FROM tmp_child_id), (SELECT record_id FROM tmp_parent_id), 'CHILD');
	
COMMIT;

DROP TABLE tmp_grandparent_id;
DROP TABLE tmp_parent_id;
DROP TABLE tmp_child_id;

The application support person needs to repair data corruption. But granting CREATE TABLE system privilege is just going too far.

I suggested that bind variables be used via the SQL*Plus command, VARIABLE, along with the RETURNING INTO clause for INSERT, as shown by the following script:

VARIABLE grandparent_id NUMBER
VARIABLE parent_id      NUMBER 

INSERT INTO grandparent_table (id, data1)
    VALUES(rec_seq.nextval, 'GRANDPARENT')
	RETURNING id INTO :grandparent_id;
INSERT INTO parent_table (id, parent, data1)
    VALUES(rec_seq.nextval, :grandparent_id, 'PARENT')
	RETURNING id INTO :parent_id;
INSERT INTO child_table (id, parent, data1)
    VALUES(rec_seq.nextval, :parent_id, 'CHILD');
	
COMMIT;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s