Re: why administrator refuse to give permission on PLUSTRACE

On Fri, 02 Nov 2007 23:30:48 -0700, DA Morgan <damorgan_at_psoug.org> wrote:

What a bunch of crap:

>
>CREATE TABLE parent (
>part_num NUMBER,
>part_name VARCHAR2(15));
>

no primary key declared, I wouls suspect one on part_num

no alternate key declared, I would suspect one on part_name

no NOT NULL attributes, I would suspect one on part_num and part name

>CREATE TABLE child AS
>SELECT *
>FROM parent;

same as above, plus

no foreign key declared, I would suspect one because of the table's name

>
>DECLARE
> j PLS_INTEGER := 1;
> k parent.part_name%TYPE := 'Transducer';
>BEGIN
> FOR i IN 1 .. 200000
> LOOP
> SELECT DECODE(k, 'Transducer', 'Rectifier',
> 'Rectifier', 'Capacitor',
> 'Capacitor', 'Knob',
> 'Knob', 'Chassis',
> 'Chassis', 'Transducer')
> INTO k
> FROM dual;
>
> INSERT INTO parent VALUES (j+i, k);
> END LOOP;
> COMMIT;
>END;
>/

Select decode? we got CASE for doing decoding in PL/SQL

INSERT INTO without qualifiing the target columns? this code won't pass any review

beside, this could be easily done in in one INSERT INTO...SELECT FROM, I leave it to you as an exercise

>
>CREATE OR REPLACE PROCEDURE test IS
>BEGIN
> FOR r IN (SELECT * FROM parent)
> LOOP
> r.part_num := r.part_num * 10;
> INSERT INTO child
> VALUES
> (r.part_num, r.part_name);
> END LOOP;
> COMMIT;
>END test;
>/

again, INSERT without qualifiing the columns, bad habits, bad habits

again, this could be done with one statement

why you are shifting the part_num remains a mystery, due to the crappy data model without any constraints, you receive no exception and have to live with corupted data

*sigh* Received on Sat Nov 03 2007 - 09:43:12 CDT