Oracle For Fun

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Mon, 26 Jan 2004 23:36:46 -0800
Message-ID: <1075188942.399322@yasure>

At a class I taught this last weekend I brought up the fact that most PL/SQL programmers are still writing v7 code. I gave everyone there a challenge and thought I'd share it with the group for any of you looking for a challenge on which to sharpen your skills.

CREATE TABLE t1 AS
SELECT *
FROM all_objects
WHERE 1=0; CREATE OR REPLACE PROCEDURE test_proc IS

BEGIN
  FOR x IN (SELECT * FROM all_objects)
  LOOP
    INSERT INTO t1
(owner, object_name, subobject_name, object_id,

     data_object_id, object_type, created, last_ddl_time,
     timestamp, status, temporary, generated, secondary)

    VALUES
(x.owner, x.object_name, x.subobject_name, x.object_id,

    x.data_object_id, x.object_type, x.created,
    x.last_ddl_time, x.timestamp, x.status, x.temporary,
    x.generated, x.secondary);

  END LOOP;
  COMMIT;
END test_proc;
/

set timing on
exec test_proc;
set timing off

Everyone using 8i+ features should be able to improve the performance of this by at least 5X.

I'll post a solution in a week or so.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Tue Jan 27 2004 - 01:36:46 CST