Script with Oracle DROP TABLE in 11gR2

Question

I am little bit confused about my query. I am getting error but it works. I am using ORACLE 11gR2 in command prompt. I have created 2 tables dp (department) and em (employee) with Primary key and foreign key. If I run the script couple of times, the DROP TABLE function attempts to drop the table 2 times but it should try only once. Could you please give me any idea of why it is happening. My script is given below.

cl scr

DROP TABLE dp CASCADE CONSTRAINT ;
L
// DROP TABLE dp PURGE ;
L
CREATE TABLE dp (
    dpid    NUMBER(2),
    dname   VARCHAR2(20));

DROP TABLE em PURGE ;
CREATE TABLE em (
    emid    NUMBER(2),
    ename   VARCHAR2(20),
    dpid    NUMBER(2));

ALTER TABLE dp
ADD CONSTRAINT pk_dp_dpid PRIMARY KEY (dpid) ;

ALTER TABLE em
ADD CONSTRAINT fk_em_dp_dpid FOREIGN KEY (dpid)
REFERENCES dp (dpid) ;

Hi Mat, I have used a script to run those commands in command prompt. In command prompt, if i run script it shows only the result. So I have used 'L' so that it shows what it is reading here. (L shows the lines it just executed.) — Murshed, Dec 25 '14 at 19:24

score 1 · Answer 1 · answered Dec 25 '14 at 19:32

1

Rather than using L to print lines after they're run, use set echo on at the top of your script.

// is not a comment marker in SQL*Plus, quite the contrary: / re-executes the previous command you entered (and apparently what comes after it is ignored). So your script re-executes the first drop.

Use -- for comments.

answered Dec 25 '14 at 19:32

Mat

202,337
40
393
406

Thank you for your reply. I tried this (--) way. But it is still trying to drop twice. is there any way that i can send you a screenshot ? – Murshed Dec 25 '14 at 19:36
Update your question with your updated script & the exact output you're getting. – Mat Dec 25 '14 at 19:37
It happened because I wrote 'L' twice. – Murshed Dec 25 '14 at 19:38

score 0 · Answer 2 · edited Dec 25 '14 at 20:12

It was a silly mistake and my misunderstanding. ORACLE did not attempt to DROP twice. It dropped only once (as it should be) but in the script I wrote 'L' twice so it was showing the attempt twice. Anyway, the updated codes are given below.

cl scr

DROP TABLE dp CASCADE CONSTRAINT ;
L
CREATE TABLE dp (
    dpid    NUMBER(2),
    dname   VARCHAR2(20));

DROP TABLE em PURGE ;
CREATE TABLE em (
    emid    NUMBER(2),
    ename   VARCHAR2(20),
    dpid    NUMBER(2));

ALTER TABLE dp
ADD CONSTRAINT pk_dp_dpid PRIMARY KEY (dpid) ;

ALTER TABLE em
ADD CONSTRAINT fk_em_dp_dpid FOREIGN KEY (dpid)
REFERENCES dp (dpid) ;

Thanks to Mat and all.

Script with Oracle DROP TABLE in 11gR2

2 Answers2