Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2013
    Posts
    41

    Unanswered: How to load .sql scripts?

    Hi,

    I have two files namely, departmentExamples.sql and classExample.sql as mentioned below. Could you tell me how would I load it
    into an oracle database instead of typing everything at the SQL PLUS prompt? Please clarify:

    classExamples.sql

    Code:
    DROP TABLE courses;
    DROP TABLE students;
    DROP TABLE instructors;
    DROP TABLE sections;
    DROP TABLE registration;
    
    CREATE TABLE courses (
    	cid varchar2(9) NOT NULL PRIMARY KEY,
    	cname varchar2(50) NOT NULL,
    	credits number(1) DEFAULT 3,
    	prereq varchar2(9)
    );
    
    INSERT INTO courses VALUES ('IS 201','Java Programming',3,null);
    INSERT INTO courses VALUES ('IS 202','C++ Programming',3,'IS 201');
    INSERT INTO courses VALUES ('IS 301','Web Design',3,null);
    INSERT INTO courses VALUES ('IS 331','Business Applications',3,null);
    INSERT INTO courses VALUES ('IS 401','Database Design',3,'IS 331');
    INSERT INTO courses VALUES ('IS 413','SQL Programming',3,'IS 401');
    
    
    CREATE TABLE students (
    	sid char(9)   NOT NULL PRIMARY KEY,
    	lname varchar(30)  NOT NULL ,
    	fname varchar2(30) NOT NULL ,
    	gender char(1) NOT NULL ,
    	addr varchar2(50) NOT NULL ,
    	city varchar2(20) NOT NULL ,
    	state char(2) NOT NULL ,
    	zip varchar2(10) NOT NULL ,
    	phone varchar2(14) NULL ,
    	birthdate date NULL ,
    	tuitionRate number(7, 2) NOT NULL ,
    	creditsEarned number(3) NOT NULL 
    ); 
    
    INSERT INTO students VALUES ('100000001','Lee','George','M','15 Merchant Street','Honolulu','HI','96818','808-524-3333','01-MAY-1965',5000.00,47);
    INSERT INTO students VALUES ('100000002','Yamamoto','Bill','M','3432 Birch Street','Honolulu','HI','96814','808-522-2212','03-JUN-1958',5000.00,12);
    INSERT INTO students VALUES ('100000003','Carver','Hillary','F','22 Aardvark Avenue','Washington','DC','10101','800-212-3246','23-AUG-1981',5000.00,69);
    INSERT INTO students VALUES ('100000004','King','Linda','F','341 Kaapahu Road','Paauilo','HI','96776',NULL,'01-SEP-1990',4399.00,6);
    INSERT INTO students VALUES ('100000005','Rollings','Willie','M','1221 Ala Moana Blvd','Honolulu','HI','96814',NULL,NULL,4888.00,0);
    INSERT INTO students VALUES ('100000006','Alexander','Wanda','F','93-123 Old Mill Road','Honokaa','HI','96727','808-776-2313','02-OCT-1987',5000.00,99);
    INSERT INTO students VALUES ('100000007','Carver','Bill','M','33 Richards Street','Honolulu','HI','96813',NULL,'22-OCT-1990',5000.00,0);
    INSERT INTO students VALUES ('100000008','DeLuz','Bob','M','102 Orleans Ave','San Francisco','CA','97745','808-555-3324','01-MAR-1988',5000.00,14);
    INSERT INTO students VALUES ('100000009','Lee','Lisa','F','45 Fong Avenue','San Francisco','CA','97767','808-333-3432','21-APR-1987',5000.00,26);
    INSERT INTO students VALUES ('100000010','Garcia','Sherrie','F','2 S. Beretania','Honolulu','HI','96817','808-663-4453','03-DEC-1990',5000.00,29);
    INSERT INTO students VALUES ('100000011','Kamaka','Oscar','M','34 Kapolani Blvd','Honolulu','HI','96813','808-533-3332','12-FEB-1988',5000.00,0);
    
    CREATE TABLE instructors (
    	inId char(9) NOT NULL PRIMARY KEY,
    	iLname varchar2(30) NOT NULL,
    	iFname varchar2(30) NOT NULL,
    	rank varchar2(10) NOT NULL,
    	office varchar2(10) NULL,
    	phone varchar2(20) NULL,
    	salary number(8,2) DEFAULT 0
    );
    
    INSERT INTO instructors VALUES ('200000001','Souza','Edward','Professor','LM101','808-533-4241',5000.00);
    INSERT INTO instructors VALUES ('200000002','Tenzer','Laurie','Professor','LM102','808-533-4244',5000.00);
    INSERT INTO instructors VALUES ('200000003','Otake','Bill','Assistant','MR101','808-533-4247',3800.00);
    
    CREATE TABLE sections (
    	crn char(4) NOT NULL PRIMARY KEY,
    	cid varchar2(9) NOT NULL,
    	section char DEFAULT 'A',
    	inId char(9) NOT NULL,
    	days varchar2(10) DEFAULT 'TBA',
    	time varchar2(16) DEFAULT 'TBA',
    	room varchar2(10) NULL
    );
    
    INSERT INTO sections VALUES ('1000','IS 201','A','200000003','MWF','08:00 - 08:50','CL100');
    INSERT INTO sections VALUES ('1001','IS 201','B','200000003','MWF','09:00 - 09:50','CL100');
    INSERT INTO sections VALUES ('1002','IS 201','C','200000001','TTh','08:00 - 09:15','CL102');
    INSERT INTO sections VALUES ('1003','IS 301','A','200000002','TTh','09:30 - 10:45','CL340');
    INSERT INTO sections VALUES ('1004','IS 301','B','200000002','MWF','09:00 - 09:50','CL340');
    INSERT INTO sections VALUES ('1005','IS 413','A','200000001','MWF','09:00 - 09:50','CL230');
    
    
    CREATE TABLE registration (
    	crn char(4) NOT NULL,
    	sid char(9) NOT NULL,
    	CONSTRAINT pk_registration PRIMARY KEY (crn,sid)
    );
    
    INSERT INTO registration VALUES ('1000','100000001');
    INSERT INTO registration VALUES ('1003','100000001');
    INSERT INTO registration VALUES ('1005','100000001');
    INSERT INTO registration VALUES ('1001','100000002');
    INSERT INTO registration VALUES ('1004','100000002');
    INSERT INTO registration VALUES ('1005','100000003');
    INSERT INTO registration VALUES ('1002','100000004');
    INSERT INTO registration VALUES ('1003','100000004');
    INSERT INTO registration VALUES ('1005','100000004');
    INSERT INTO registration VALUES ('1000','100000005');
    INSERT INTO registration VALUES ('1003','100000005');
    INSERT INTO registration VALUES ('1002','100000008');
    INSERT INTO registration VALUES ('1004','100000008');
    INSERT INTO registration VALUES ('1002','100000009');
    INSERT INTO registration VALUES ('1005','100000009');
    INSERT INTO registration VALUES ('1002','100000010');
    INSERT INTO registration VALUES ('1005','100000010');
    INSERT INTO registration VALUES ('1000','100000011');
    INSERT INTO registration VALUES ('1003','100000011');
    INSERT INTO registration VALUES ('1005','100000011');
    commit;
    Here is departmentExamples.sql

    Code:
    DROP TABLE EMP;
    DROP TABLE DEPT;
    
    CREATE TABLE DEPT
           (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT_DEPTNO PRIMARY KEY,
            DNAME VARCHAR2(14),
            LOC VARCHAR2(13) );
    
    INSERT INTO DEPT VALUES
            (10,'ACCOUNTING','NEW YORK');
    INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
    INSERT INTO DEPT VALUES
            (30,'SALES','CHICAGO');
    INSERT INTO DEPT VALUES
            (40,'OPERATIONS','BOSTON');
    
    CREATE TABLE EMP
           (EMPNO NUMBER(4) CONSTRAINT PK_EMP_EMPNO PRIMARY KEY,
            ENAME VARCHAR2(10),
            JOB VARCHAR2(9),
            MGR NUMBER(4),
            HIREDATE DATE,
            SAL NUMBER(7,2),
            COMM NUMBER(7,2),
            DEPTNO NUMBER(2) CONSTRAINT FK_EMP_DEPTNO REFERENCES DEPT(DEPTNO));
    
    INSERT INTO EMP VALUES
            (7369,'SMITH','CLERK',7902,'17-DEC-2009',1950,NULL,20);
    INSERT INTO EMP VALUES
            (7499,'ALLEN','SALES',7698,'20-FEB-2009',2600,3000.79,30);
    INSERT INTO EMP VALUES
            (7521,'WARD','SALES',7698,'22-FEB-2008',2950,5051.85,30);
    INSERT INTO EMP VALUES
            (7566,'JONES','MANAGER',7839,'2-APR-2008',7975,NULL,20);
    INSERT INTO EMP VALUES
            (7654,'MARTIN','SALES',7698,'28-SEP-2008',2250,2853.51,30);
    INSERT INTO EMP VALUES
            (7698,'BLAKE','MANAGER',7839,'1-MAY-2009',7850,NULL,30);
    INSERT INTO EMP VALUES
            (7782,'CLARK','MANAGER',7839,'9-JUN-2008',8450,NULL,10);
    INSERT INTO EMP VALUES
            (7788,'SCOTT','ANALYST',7566,'09-DEC-2010',6000,NULL,20);
    INSERT INTO EMP VALUES
            (7839,'KING','PRESIDENT',NULL,'17-NOV-2008',10500,NULL,10);
    INSERT INTO EMP VALUES
            (7844,'TURNER','SALES',7698,'8-SEP-2009',2500,0,30);
    INSERT INTO EMP VALUES
            (7876,'ADAMS','CLERK',7788,'12-JAN-2011',1700,NULL,20);
    INSERT INTO EMP VALUES
            (7900,'JAMES','CLERK',7698,'3-DEC-2008',2250,NULL,30);
    INSERT INTO EMP VALUES
            (7902,'FORD','ANALYST',7566,'3-DEC-2009',6000,NULL,20);
    INSERT INTO EMP VALUES
            (7934,'MILLER','CLERK',7782,'01-JAN-2010',2000,NULL,10);
    
    COMMIT;
    Please let me know how can I do this?

    Thanks

  2. #2
    Join Date
    Apr 2013
    Posts
    1
    have you tried from sqlplus running (obviously this assumes your file is on the desktop):

    @C:\users\<username>\Desktop\filename.sql

    from the prompt?

  3. #3
    Join Date
    Apr 2013
    Posts
    41
    Yes, after trying that, I'm getting following message:

    SP2-0024: Nothing to change

    In addition to that I'm also unable to see the database inside which I'm working. I used the following command:

    Code:
    select name from v$database;
    and message says, error at line 1, table or view does not exists.

    Quote Originally Posted by gwenky View Post
    have you tried from sqlplus running (obviously this assumes your file is on the desktop):

    @C:\users\<username>\Desktop\filename.sql

    from the prompt?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    sqlplus scott/tiger @departmentExamples.sql
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2013
    Posts
    41
    Thanks it worked. I forgot to use @ symbol before letter "C"

    But still I would like to know how can I find the database I'm working on.

    Quote Originally Posted by Jack_Tauson_Sr View Post
    Yes, after trying that, I'm getting following message:

    SP2-0024: Nothing to change

    In addition to that I'm also unable to see the database inside which I'm working. I used the following command:

    Code:
    select name from v$database;
    and message says, error at line 1, table or view does not exists.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT ora_database_name FROM dual;
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >and message says, error at line 1, table or view does not exists.
    it is because which ever USER issued "select name from v$database;" did not own v$database view

    V$DATABASE view is owned by SYS user.
    it will succeed when you do as below.

    sqlplus
    / as sysdba
    select name from v$database;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •