Implementing DDL Scripts and Manipulating Data
Once you have designed all of your tables and constraints in Designer/2000 you will need to implement them on an Oracle instance in order to begin populating the tables with data. The following information explains how to do this.
SQL Plus is a command line interpreter that allows you to interactively use SQL commands to create, drop or modify objects on an Oracle instance. In addition, you can use SQL Plus to manually insert, update and delete records from tables that have already been created. There are two ways to connect to Oracle via SQL Plus. First, you can click on the "SQL Plus" icon in the Designer/2000 main menu. Doing so will open up an MS-DOS window and you will be instantly connected to the Oracle instance that Designer is installed on.
Alternatively, you can use the start menu in NT or Windows 95 to connect. Go to the Oracle for NT program group and click on SQL Plus. A dialogue window will pop up asking you for your login id, password and the connect string. The connect string is used to specify which Oracle instance you would like to connect to. For our class, the name of the Oracle instance is oracle-student. Click on OK and you will be connected. This alternative method is especially useful if you don't want to go through Designer and if, for example, you want to create tables on an instance that is different from the one where Designer is installed. In the real world, this is usually the case.
Once you are in SQL Plus you can use any SQL statements to manipulate the database. In addition, Oracle allows you to use any PL/SQL extensions. When you are finished with your SQL Plus session, simply type "exit" to return to the operating system.
Once you have designed all of your tables, constraints, etc., and generated them using the server generator in Designer/2000 you will want to run the script to implement all of your work. Let's say that the server generator created a file called "mytables.sql" which is stored in the root directory of the c drive. To run the script, connect to Oracle using SQL Plus and type in the following command:
@c:\mytables.sql
While the SQL statements in the script are being executed you will see a series of messages pop up on the screen which show whether each statement in the script was executed successfully or whether there were any errors. Usually, this information scrolls too fast to see. You can view what happened at your leisure using WordPad or NotePad. All scripts generated by the server generator spool the output to a file with the same name as the script, except with a .lst extension. In our example, that file would be "mytables.lst". Alternatively, you can view the file using the default SQL Plus editor (usually NotePad) by issuing the following command:
Edit c:\mytables.lst
If you are using SQL Plus for purposes other than running a script, you can create your own log file by issuing the following command:
Spool c:\mylog.lst
Where c:\mylog.lst is the name and drive of the log file you want to create. When you are done, simply issue the following command to end logging:
Spool off
Sometimes SQL Plus will issue error messages when running scripts. Usually this happens when you are trying to create an object that already exists or when you try to create an illegal object. In addition, errors will occur when you try to insert invalid data into a table, for example. You can view these error messages by examining the .lst log files.
SQL scripts consist of a series of SQL statements that are executed when you run the script. A script will not stop running as soon as an error is encountered. You can use this fact to your advantage. For example, if, when you first ran the script, all of the tables were created successfully, but some of the constraints were not because of errors, you do not have to drop all of the tables before you can run the script again. Simply go back into Designer, correct the errors, generate the script again, and then run it again. This time around you will get error messages on the table creation statements saying that they already exist. You can ignore those because they didn't affect the (hopefully, this time) successful creation of your constraints.
Once your script has run successfully you can begin populating the tables with data. Let's say that one of your tables is called tbl_customer and consists of the following columns: name (varchar2(40)), cust_id (number), address (varchar2(50)) and status (varchar2). To insert a row of data into this table, use the insert command:
Insert into Tbl_Customer(name, cust_id, address, status)
Values('Fred Jones',12,'100 Main St.','A');
To delete the row for Tom Smith, you would use the following command:
Delete from Tbl_Customer where name = 'Tom Smith';
Let's say you wanted to update the data for customer Fred Jones. Specifically, you want to change the address to 34 East James St. You do so by typing the following:
Update Tbl_Customer set address = '34 East James St.' where
Name = 'Fred Jones';
Remember that any changes you make to the database are not permanent until you issue the "commit" command. If you make a mistake and would like to undo your changes, simply issue the "rollback" command. This will undo anything done after the last commit.
If you try to do anything illegal Oracle will let you know. For example, if you violate the primary key constraint on Tbl_Customer by attempting to insert another customer with the same cust_id as that of Fred Jones, you will get the following error message:
insert into tbl_customer(name, address, status, cust_id)
values('John Brown','12 Park Ave.','B',12)
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C001296) violated
The exact name of the constraint being violated depends on the one assigned to it when it was created. This means that if you violate the primary key constraint you will not always get SYSTEM.SYS_C001296.
When inserting, updating or deleting records from tables remember that Oracle is case sensitive for strings, i.e., 'Fred Jones' is not the same as 'FRED JONES".
Return to Main Page