STEP *1* - FIRST DEFINE THE TABLE SPACE YOU WILL BE ACCESSING AND INSERT VALUES ============================================================================== A sample table schema and values have been defined for you in ~oracle/sql/embedded/t2.sql % source /home/1/oracle/source.oracle % rehash % sqlplus SQL*Plus: Release 3.3.2.0.0 - Production on Mon Nov 23 22:26:39 1998 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. Enter user-name: yarowsky Enter password: Connected to: Oracle7 Server Release 7.3.2.2.0 - Production Release PL/SQL Release 2.3.2.2.0 - Production SQL> start /home/oracle/oracle/sql/embedded/t2.sql; This will define and load the tables "customers" and "zipcodes"; ------------------------------------------------------------------------------ STEP *2* - CREATE A C PROGRAM WITH EMBEDDED SQL AND COMPILE IT ============================================================== Several additional sample programs and makefiles have been created for you in the directory /home/oracle/oracle/sql/embedded. You should copy these to your home directory. For example: % mkdir embedded % cd embedded % cp -p /home/oracle/oracle/sql/embedded/* . A Sample C program with embedded SQL (from the Sunderraman text) is located in the file t2.pc. You can compile this file as follows with the following csh script c.csh provided by Woosung as follows. This will run the preprocessor over t2.pc in the current directory and then compile it using gcc. For some reason, this script must be run twice to achieve its desired effect. For example: % csh c.csh gcc: t2.c: No such file or directory gcc: No input files gcc: t2.o: No such file or directory proc iname=t2.pc Pro*C/C++: Release 2.2.2.0.0 - Production on Fri Nov 20 13:01:27 1998 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. System default option values taken from: /home/1/oracle/app/oracle/product/7.3.2/precomp/admin/pcscfg.h gcc -xO4 -Xc -xstrconst -xcg92 -xF -mr -K PIC -DSLXMX_ENABLE -DSLTS_ENABLE -D_REENTRANT -I. -I/home/1/oracle/app/oracle/product/7.3.2/precomp/public -c t2.c gcc: PIC: No such file or directory gcc: unrecognized option `-Xc' gcc: unrecognized option `-K' gcc: language F not recognized cc1: Invalid option `r' *** Error code 1 make: Fatal error: Command failed for target `t2.o' gcc: t2.o: No such file or directory % csh c.csh ld: fatal: library -l/home/1/oracle/app/oracle/product/7.3.2/precomp/lib/: not found ld: fatal: File processing errors. No output written to t2 gcc -o t2 t2.o -L/home/1/oracle/app/oracle/product/7.3.2/lib -lclntsh -lsql -lsqlnet -lncr -lsqlnet -lclient -lcommon -lgeneric -lsqlnet -lncr -lsqlnet -lclient -lcommon -lgeneric -lepc -lnlsrtl3 -lc3v6 -lcore3 -lnlsrtl3 -lcore3 -lnlsrtl3 `cat /home/1/oracle/app/oracle/product/7.3.2/rdbms/lib/sysliblist` -lm -lthread Ignore these error messages. This execution of the two shell scripts has created the stand-alone executable program 't2' that will connect to Oracle and modify data. ------------------------------------------------------------------------------ STEP *3* - RUN THE NEWLY GENERATED C PROGRAM ============================================ Before running the newly generated C program, you should make sure your environment is set up properly. You should execute the following 3 setenv commands before running 't2'. When you run 't2' the program will prompt you for your oracle USERID and PASSWORD (*not* your unix password) and pass these to Oracle when creating the connection. A sample run of the program is given below: % setenv ORACLE_HOME /home/1/oracle/app/oracle/product/7.3.2 % setenv ORACLE_SID hops % setenv ORACLE_TERM xsun5 % t2 Enter your USERID: woosung Enter your PASSWORD: ************************************************ <1> Add a new customer <2> Print all customers <3> Remove a customer Quit *********************************************** Type in your option: 2 1111 Charles 123 Main St. 67226 316-636-5555 2222 Bertram 237 Ash Avenue 67226 316-689-5555 3333 Barbara 111 Inwood St. 60606 316-111-1234 ************************************************ <1> Add a new customer <2> Print all customers <3> Remove a customer Quit *********************************************** Type in your option: 1 Customer ID: 1234 Customer Name: Kim Street : 123 St. Paul St. Zip Code : 60606 Phone Number : 123-45-6789 ************************************************ <1> Add a new customer <2> Print all customers <3> Remove a customer Quit *********************************************** Type in your option: 2 1111 Charles 123 Main St. 67226 316-636-5555 2222 Bertram 237 Ash Avenue 67226 316-689-5555 3333 Barbara 111 Inwood St. 60606 316-111-1234 1234 Kim 123 St. Paul St. 60606 123-45-6789 ------------------------------------------------------------------------------ STEP *4* - NOTES ON MODIFYING THIS PROGRAM FOR YOUR HOMEWORK 3 ============================================================== You should be able to modify the program t2.pc to handle the goals of your homeowrk 3 problem h. Note that you should not prompt the user for your oracle userid and password. Because many other people besides the owner of the grades tablespace will need to access this data, they should not all need to know your Oracle password. But all you need to do is *hardwire* your Oracle Userid and Password into the C program. Rather than requesting them from the user as in t2.pc, you should just pass ordinary C strings with your Oracle userid and password. These will be invisible to your users and will allow them to access your tablespace without knowing your password. There are other ways of doing this, but this is the easiest. You *should* request the TA password, however, and compare this with the table "CurPasswords", as in the PL/SQL parts of the assignment. Note that this password is not the same as that of the tablespace owner (you). If you have trouble making this comparison, partial credit will be given for simply comparing the users password with a string hardwired into the program. Although not ideal, this is the minimal functionality that will be accepted. Otherwise, the process of requesting and changing values via C should be very similar to that in the sample program t2.pc.