`Frequently Asked Questions

Subject: sqlplus?

Q: Is anyone successful is starting sqlplus, the oraenv script runs but then 

nothing, no new path or env vars or anything. I was just wondering if I

might not have permissions.

A: Make sure you put the '. ' before the 'oraenv' when you run the

script. The '. ' tells the shell to run the script in the

environment of the shell.

Subject: Alternative to Dynamic SQL

:conditionals in the WHERE clause. (something about using: LIKE '%')

A: What I said was that for the various conjunctive

clauses in the books query, you could use the LIKE clause

to avoid using dynamic SQL.

The user can specify Author, Title, Price, etc. or leave them

blank -- in which case, you want to ignore that condition.

So, what you can do is to put a check in your code: if the

user enters a string for Author, then your SQL should read:

WHERE author LIKE 'Stephen King' AND ....

If the user leaves author blank, then your SQL should read:

WHERE author LIKE '%' AND ....

so that Oracle will match all authors.

In Pro*C, your code would read: 

WHERE author LIKE :in_author AND ...

and your C code above it would populate the host variable 'in_author'

with '%' if the user left the author field blank.

For non-string data types (like quantity), if the user leaves

the value blank, then your C code should replace it with the

maximum or minimum value allowed for that data type -- so that

it would read:

WHERE qty <= :max_qty AND qty >= :min_qty

and your code would set min_qty = 0 and max_qty to the maximum

allowed integer value allowed by Oracle (or the corresponding data type)

if the user left the values blank.

Subject: document description?

part of "Identifying tasks". (?) What are these two things and we don't

have any clue what these mean. We have followed the class notes very

closely and we are unclear on these things. Could you please give us some

help.

A: The DB methodology distinguishes between the analysis

and specification phases -- the analysis phase is where

you analyze the real-world information that is needed

by the application. That is, you identify all the documents 

and tasks that are necessary for the correct operation

of your system.

The specification phase is where you understand the

relationships between the different tasks and documents,

and then represent this understanding using ER-diagrams

and task forms.

That is why in the requirements for phase 1, identifying

the documents and tasks in the analysis phase were given 

some points and the task forms in the specification phase 

were given some points.

Subject: Sales by Specific Clerk

why do i need to print the name/id of the clerk on each line of output?

Can't I just mention it once at the top and then enlist all the

sales-qty and revenue?

A: 'All' the sales-qty and revenue? From what you have written,

it seems to me that this is for a specific clerk. Therefore,

this means that there would only be ONE line in the output.

The reason that the output contains both ID and name is

that if the query was on the clerk's ID then the result

should show the clerk's name. If the query was on the clerk's

name, then you need to show the clerk's ID.

Subject: Querying Orders II

for orders, if we enter book information, will it return ALL orders that

contain the conjoined info that is provided? Or have we been misled by the examples to believe one order contains

different books? Does each order focus on one particular ISBN?

A: "A book may appear on several orders and an order may 

include several books. However, a given order will go to 

only one publisher." -- this means that an order can only

contain books from the same publisher (but it can contain

multiple books).

When you query orders, you might want to have a

column for the Order numbers that match the query. And yes,

all orders that match the query conditions must be returned.

You might want to only show those orders that have not yet

been received...

I will confirm the format of the query result with the

professors (I agree that it is a little inconsistent) and

I will post how the final output should look later on.

Continued:

Now that Phase 1 is over, I wanted to clarify the Query Order

task a little more.

** When you query for a specific ISBN, you should list out:

1. Book Information:

ISBN

Author

Title

Publisher

2. Orders containing the book:

Order#PriceQtyNamePhone

So, each order that is printed out should only contain the price

and quantity for the book you are looking for (there may be other 

books in that order, but you don't have to display them).

** When you query for Author/Publisher/Customer Name/Phone number,

you will be doing the conjunction of all these conditions.

Therefore, every line in the result will contain Orders that

are for that customer (if specified) and that contain books

for that publisher or author. This means that if you don't

enter an author, books by all authors must match. If you 

don't give a publisher, orders containing books by any publisher

will match.

The output must contain:

Order#ISBNAuthorTitlePublisher Price QtyNamePhone

The customer Name and Phone might be empty for regular orders.

Once again, every line will contain only those orders (and books

& customers) that match all the query conditions.

Hope this helps. If you have any requests for presenting this

data differently, get it cleared with the TA for your group.

Subject: multiple fields

"Query Order Info", "Place Order", and "Sell Books" can we

assume that values(strings) *must* be entered in all fields,

or should we handle situations where only a subset of the fields

could provide correct functionality? 

Example: "Query Order Info" - If only ISBN was entered,

the number of matching items could very likely be much larger

than the number of matching items for a query specifying

all fields.

I hope that your answer is that all fields must be 

specified.The case of a "Query Order Info", 

as an example, might then be split into

"Query Regular Order Info" and "Query Special Order Info".

What if we wanted to query books by subject alone?

This could easily be remedied in a similar fashion with

additional control menus to add specification.

A: Unfortunately, I don't agree completely -- when you 

change a book, you are most likely correcting only one 

attribute of the book. So, you should allow the user to 

enter a new value for all the attributes, but if the new 

value is blank, the update should default to the original value.

There is a problem with the Query Order menu and I will

be addressing that problem after Phase 1 (we will make

it much simpler). So don't worry too much about that one 

specifically...

Take each task/document individually and evaluate

whether or not all fields are necessary to perform the 

task. If a field is not necessary, then make a decision 

about whether you want to make a control decision for 

it or you want to let the user simply leave that field blank.

Subject: Eds

Q: In the input field of the Task Forms, what exactly does EDs mean?

A: External Documents

Subject: Unit of Measurement

Does this mean that I represent the statistic: Add 4 new Clerks per year

as: 0.011/day since my UoM is /day??? It seems absurd.

A: You can say < 1/day -- anything less than your lowest unit

of measurement should be considered to be negligible.

You could consider increasing the number of times clerks

are added :) Think of all the temporary hires that are

done during Christmas...

Subject: Relational Schema

to have ISBN, QTY Ordered and Order # as attributes of the

relationship.Such as:

Book-Order

Order#, ISBN, Qty

From my understanding, there will be a huge list of all the books ever

ordered.Is there a better way of doing this?Could you create a

separate table for each order number which lists all of the books on

that particular order?

A: It could potentially be a large list -- but that's why

you want to use a DBMS :)

As far as your idea for creating a separate table for

each order and then listing books for that order: the downside

is that the number of tables would become so large that 

maintaining the table meta-information would be a high overhead. 

On the other hand, the DBMS is designed to handle lots of 

tuples in one table rather than few tuples in a large number

of tables.

You are correct that individually there are duplicates of 

Order# and ISBN in the relation (though when taken together 

there are no duplicates). But to take an example, if an order

contains 10 books on average and there are around 30 orders

per day -- that is 300 tuples per day or 

300 * 365 = ~100000 tuples/year

This isn't much for a DBMS like Oracle. Also, most DBMSs will 

archive old information on a regular basis to reduce storage costs 

Subject: constraints

slides (slide: 51)), or just in psuedocode.

A: Preferably you should write SQL. In Phase 3, you will need

to write SQL scripts to create your tables. So the constraints

you define now should be easily taken forward to phase 3.

(Note that defining constraints is optional but recommended for Phase 2)

Subject: Query Conditions

specified above in tabular format"

So do we need to AND all the conditions together or OR them together

2.] On Slide 52 of DB Methodology, what happens if the first select

statement returns "NOT FOUND". According to the code, it will goto

endloop: and at endloop: there is the SQL statement for closing the

cursor... but since a cursor wasn't opened to begin with, how can we

close it?

Shouldn't the code me modified somewhat to reflect "where" the NOT-FOUND

was encountered?

A: Yes, it should be the conjunction of the terms:

author = "someone" AND subject = "some subject" etc.

The code first declares a cursor for the specified select

statement. Just declaring the cursor will not actually detect the

NOT FOUND condition.

The first fetch occurs only when the cursor is opened

and the first fetch on that cursor is performed.

When 'endloop' is reached, the FETCH should have

already been executed at least once.

Subject: Functional Dependencies

those that are implied with primary keys?if so, there would be

many of them...IF A is a primary key and B is a field, is this a valid

dependency that needs listing, or is it implied?

Or, are we supposed to document those dependencies that are inherent

in our db that can't be changed, i.e. that a ssn functionally

determines a first name...etc.

A: You should identify all the functional dependencies that

you can conceive of -- including the FDs on the primary

keys. 

The reason for this is that -- especially when you

have a composite primary key -- you might find a FD

from an attribute on ONE attribute of the composite

primary key. The FD on singular primary keys might be 

trivial, but its also so easy to express:

{PK} -> {A1, A2, A3, ... }

Frankly, if you follow the ER to relational conversion

algorithm, you should have very few normalization problems

(and very few FDs to worry about!), but we want 

you to understand the issues involved in normalization.

Subject: make files

Also, I created the table directly at the SQL> prompt in acme. Is this

the way we are supposed to do this?

A: Makefile is available at: http://www.cc.gatech.edu/classes/AY2002/cs4400a_spring/proc.mk

You can create all the tables by just putting all the create table

commands in a file called tables.sql and then run the whole file by typing

START tables.sql at the SQL> prompt.

Subject: Other "make" people...

Q: OK, I've used and manipulated make files before, but the "make" 

on AcmeX is being troublesome.I'd prefer to define my own 

make file based on the proc.mk in the $ORACLE_HOME directory, 

but Sun's make does not seem to follow dependencies properly.

To simplify to try to try to find the problem, I used the 

following (yes, it's ridiculous, but it does well for 

test purposes).

#-------------------------------------------

# START

all: ok

echo "Done."

.SUFFIXES:

.SUFFIXES:.o .c .pc

.c.o:

echo "Making .o from .c."

cp $(*).c $(*).o

.pc.c:

echo "Making .c from .pc."

cp $(*).pc $(*).c

ok: ok.o

echo "Making OK."

cp ok.o OK

#END

#------------------------------------

Now, with this Makefile, with a dummy ok.pc file created, if I 

type "make" I get the following message:

make: Fatal error: Don't know how to make target `ok.o'

If I do the same with the same file under Linux it works just fine.

Yes, I know I could use a ".pc.o" rule to solve this, but this is 

the type of brain-dead behavior make is supposed to allow you to 

avoid, and it can be very useful to have the .c file available to 

track down problems.Now, does the Sun "make" just do poor 

dependency checking, or am I missing a switch somewhere?I 

did a quick sweep of the man pages, but have not found 

anything yet.

A: one of the students had a problem running the sample program

that I have put up on the web page. The program was

dumping core when executing the first SQL statement.

The C compiler turned out to be the problem.

Pro*C programs should only be compiled using the

SUNWspro C compiler. Do not try to use GNU C or /usr/ucb/cc!

You should see the following when you do:

bash$ which cc

/opt/SUNWspro/bin/cc

If it shows you some other version of cc, then make sure you

do the following every time you login:

bash$ export PATH=/opt/SUNWspro/bin:$PATH

Subject: make on proc.mk

Fatal error in reader: proc.mk, line 197: Unexpected end of line seen

I copied it off of the web and got all of he kinks out of it except this

one. Any ideas?

195 .y.c:

196$(SLAX) -p -d -v $*.y

197

198 SQLHOME= etc...

A: Don't copy-paste off the webpage. It adds "invisible" control characters.

Save the file and transfer it to your acme account. I had the same problem.

Subject: Make error 2

to use the sqlca feature?I'm getting:

"sales.c", line 477: undefined struct/union member: SQLCODE

"sales.c", line 477: warning: left operand of "." must be struct/union

object

"sales.c", line 646: undefined symbol: sqlca

I have the include line in each function I use.

A: You don't need the include line in each function. You only

need it at the top of each .pc file.

If you still face a problem, post the snippet of code

at the line numbers in the .c file that are causing a problem.

Take a look at the sample.pc file to see how sqlca.sqlcode is

used.

Subject: create domain?

CREATE DOMAIN ISBNNumber CHAR(13);

*

ERROR at line 1:

ORA-00901: invalid CREATE command

A: Oracle does not conform to the SQL standard completely. It

only conforms to level 1 and some portions of level 2 and 3

of the standard. Basically, it doesn't support domains.

I would recommend taking a good look at the following

documentation available from the link:

http://aistweb.gtri.gatech.edu/oracle/DOC/server/doc/SQL73/toc.htm

1. http://aistweb.gtri.gatech.edu/oracle/DOC/server/doc/SQL73/ch2.htm#toc015

(the section "Elements of Oracle7 SQL") and

2. http://aistweb.gtri.gatech.edu/oracle/DOC/server/doc/SQL73/ch4a.htm#toc040

(the section "Commands")

as a more correct reference for the third phase -- the slides

and textbook refer to the SQL-92 standard and since Oracle

supports some things differently, you shouldn't waste time with

the wrong information...

Subject: core dump

A: Usually bugs in accessing memory -- for example, you have

a character string:

char str[10];

and in your code, you do:

str[100] = '\0';

This might dump core because the assignment is writing

memory that does not belong to 'str'.

You can use a debugger to locate the problem, but rather than

learning to use a debugger at this stage, it might be easier to

put printf statements in your code to locate at which statement

your program is crashing. Then by printing values of the data

being referenced by the statement you can figure out which

variable is invalid.

Subject: Create Schema command

later try to insert into the tables, I am told they don't exist. Here is what

I'm doing:

CREATE SCHEMA Bookstore AUTHORIZATION publ

CREATE TABLE Bookstore.book

(

...

)

I have several questions. Should there be a ';' after the last parenthesis?

Should I be using 'publ' as my authorization? I'm not putting a ';' after my

last ) because when I do I get an error. Help! Yes, I am looking at the online

documentation, but frankly, it's not that helpful on basic syntax, which

appears to be the problem here.

A: Try the following:

SQL> CREATE SCHEMA authorization ops$gte916j

2CREATE TABLE book(isbn char(11))

3CREATE TABLE publisher(id integer)

4;

replacing ops$gte916j with your username (ops$gtxxxxx)

As you can see, you only need a semi-colon at the end

of the schema-creation statement. Apparently you can't 

give names to your schemas in Oracle (if anyone knows

a way to do this, please post it).

The advantage of using the CREATE SCHEMA statement is

that all the objects are created in one transaction --

this allows you to define foreign keys into tables that

have not yet been created.

Q2: Thanks for the help on my previous post! I have a new problem. Here is a

summary of my code:

CREATE SCHEMA AUTHORIZATION ops$gt5262b

CREATE TABLE Book

(ISBNCHAR(13)NOT NULL,

...

)

CREATE TABLE Author

(ISBNCHAR(13)NOT NULL,

...

FOREIGN KEY (ISBN)

REFERENCES Book(ISBN)

);

I am getting this as an error:

----

REFERENCES Book(ISBN),

*

ERROR at line 8:

ORA-00942: table or view does not exist

------

That doesn't make sense to me - I am creating the Book table immediately

before the Author table. So even if placement mattered (it isn't

supposed to, when you create all of your tables in the create schema

command), this reference should work.

A: Does it say "Table created" for the book table?

If not, then that might be the reason why you can't create the foreign key

in author. Because i've got very similar code as yours and it works fine.

Q3: The only feedback I'm getting from my create schema command is: '11' I

assume this refers to the number of tables I create - which is 11.

That's all it tells me! What should I get, and why aren't I getting it?

A1: ok .. I am supposing that you are entering the create schema/ table scripts

from a file using "START <script file>" from the sqlplus interactive

screen.

If so, then after the file is run, try typing "desc book" at the

interactive sqlplus screen.

It should give u a description of the books table looking something like

this:

NameNull?Type

------------------------------- -------- ----

ISBNNOT NULL CHAR(10)

AUTHORVARCHAR2(30)

TITLEVARCHAR2(30)

SUBJECTVARCHAR2(30)

PRICENUMBER(6,2)

QUANTITYNUMBER(38)

PUBLISHER_NAMENOT NULL VARCHAR2(30)

If it says, "Object does not exist. " that means that your book table was

not created. and that's why you cannot make the foreign key isbn in your

author table.

A2: I think your problem is that you haven't defined

ISBN to be a primary key... I can't be sure about this,

but try defining ISBN to be the primary key for Book.

I would recommend first testing your SQL statements

without the CREATE SCHEMA statement (It might introduce 

some unexpected errors).

Subject: Functionality Question...

Q1: The problem description document shown on the web page explicitly states 

the requirement of some function of our final product (IE, Add Book, 

Place Order, Reports), but it could be interpreted to imply others.

For example, I would assume that we would need to be able to tell 

the difference between a special order that has been picked up and 

one that has not.This implies a new screen.Is such "added" 

functionality required of our finished product, or just those 

functions described in the problem description document?

A: OK -- first point is that the project description on

the web page is a simple one. It is sort of what a DB designer

would be given at the time of analysis. It is NOT what your

finished product would definitely look like. So to make things

work correctly, you should add any intermediate screens

that you need (within reason).

I don't see why you need a separate screen to distinguish

between a regular order and special order. Given an order

number, your schema should let you determine whether an

order is regular or special and based on that your code

should do the appropriate actions.

Q2: I was not saying that two different screens would be required to receive 

special and regular orders; however, while regular orders are only 

received, special orders are received and then picked up by the customer.

Ideally, the database would show the difference between a special order 

that has ONLY BEEN RECEIVED, and a special order that has been 

RECEIVED AND PICKED UP by the customer.Since it makes sense to 

increase the inventory apporpriately when receiving an order, a seperate 

action would make sense to decrement it at the time of the pick up.While 

we could only increase inventory for regular orders, this solution would 

still leaves us unable to tell the difference between a special order 

that has been picked up and one that has not.This seems distasteful, 

it leaves the store vulnerable to an unscrupulous customer attempting 

multiple pick-ups.If you can't tell the difference between a special 

order that has been picked up and one that has not, you have little 

basis on which to argue the attempted second pick-up, or to even notice it.

A: I see your point. Feel free to add cool features if you think they are justified

-- just make sure not to miss out on the important (and more basic) tasks already in 

the requirements.

Subject: Order #s:

Q: How do i go about generating sequence numbers? For eg, while placing an

order, a DBMS generated order number is returned which is supposed to be

my Primary Key that I use as reference keys in other tables. How do i

create this sequence number and once created how do i use it as a

foreign key in other tables?

A: Say you create a sequence

The format is

create sequence <seq-name>

[increment by integer]

[start with integer]

[maxvalue integer]

[cycle/nocycle]

For example:

create sequence custseq start with 1000;

you would

insert into customers

values (custseq.nextval, 'Jones', '123 main', . . . . );

When createing the table you should declare it as type int or number(x).

A2: (Following A1) That is correct -- you create the sequence just like any

other database object in your .sql file. You can then

use nextval to get the next unique sequence number and 

currval to get the current sequence number.

A3: If I understand your question, you want to know how to use the same key 

values in two tables.Remember, you don't always have to use nextval; 

there is always currval.In other words use sequence.nextval for the 

first table, and sequence.currval for the next table.If you really 

need to get at the value of the sequence for debugging purposes, you can 

use 

SELECT

sequence.currval

INTO

:variable

FROM 

DUAL;

DUAL is a dummy table you can use to get to things like sequence numbers 

and SYSDATE that are not normally easily accessible for assignment in C.

Q2: Ok.. so now I've created the sequence number. I just realised that I somehow

I managed to increment the sequence number accidently. How would I reset the

value of the sequence number to, say, 0?

A: Just drop the sequence and recreate it. I don't think you

are allowed to reset the sequence numbers (for a very good

reason).

Subject: SQL table errors

of the file.They were working fine and then I started to get these

errors (multiple times) pertaining to the first table I got to work.Am

I making a mistake in dropping the table?

DROP TABLE PUBLISHER_INFO

*

ERROR at line 1:

ORA-02266: unique/primary keys in table referenced by enabled foreign

keys

CREATE TABLE PUBLISHER_INFO

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

A1: try to drop table publisher _info cascade;

that'll get rid of all constraints that apply to that table.

A2: The syntax is:

DROP TABLE publisher_info CASCADE CONSTRAINTS;

(refer to the Oracle documentation for syntax problems...)

A simple solution is to drop the tables that reference publisher_info

before dropping publisher_info. So for example, if the table 'book'

references publisher_info, drop book first and then publisher_info.

This works in most cases -- it will only fail if two tables

reference each other (circular references)

Subject: Cursor Problem...

Q: OK, I've created a cursor in ProC, but it does not retreive the rows 

it should.This cursor is used for Query Order Information.As a 

test, I copied the SQL select statement out of the code and into 

a sqlplus session.I then replaced any variables from the C with 

their values for a given problem case (obtained by print statements 

run immediately before opening the cursor).While my compiled function 

finds no rows, sqlplus finds the one I expect it to.Any suggestions?

A: Is sqlca.sqlcode indicating the 'no rows found' error code? Or do 

you have the WHENEVER NOT FOUND clause correctly specified?

If it is, then check the data types of the variables in

your where clause. If any one of them is a CHAR, then you

need to pad it with spaces. If any of them is a VARCHAR,

you need to set the "len" variable to the length of the

value.

Also check the case of the data that you are querying: Oracle 

is case-sensitive.

(Post a snippet of the code if you are still facing a problem.)

Subject: Problem Inserting

insert into book

values("0201357534","Oracle

Programming","CS","Sunderraman","Rajshekhar",35.00,50,10,1);

SQL> start initbook.sql

values("0201357534","Oracle

Programming","CS","Sunderraman","Rajshekhar",35.00,5

0,10,1)

*

ERROR at line 2:

ORA-00984: column not allowed here

A: Do you have "Oracle" and "Programming" on different lines?

Try putting them on the same line.

Subject: Reports

does it need to be in the books report?Likewise for a publisher, if

a book has not been sold from a specific publisher, does it need to be

in the report.

A: Yes, even books that have not been sold should show up in the

sales report.

Subject: JOINS on oracle?

I'm trying

SELECT stuff

FROM (Table1 NATURAL JOIN Table2) NATURAL JOIN Table3

and it's dying.

A: Some people have asked me about using Left outer joins

on Oracle since the JOIN keywords in the notes don't work,

From the Oracle documentations:

"

Outer Joins 

The outer join extends the result of a simple join. An outer

join returns all rows that satisfy the join condition and those 

rows from one table for which no rows from the other satisfy

the join condition. Such rows are not returned by a simple join. 

To write a query that performs an outer join of tables A and B 

and returns all rows from A, apply the outer join operator (+) to 

all columns of B in the join condition. For all rows in A that 

have no matching rows in B, Oracle7 returns NULL

for any select list expressions containing columns of B. 

This is the basic syntax of an outer join of two tables:

....

"

The rest of it includes images so I am not showing it here. But

take a look at the documentation for the SELECT statement and

the sub-section on "Outer Joins".

Subject: Queries

not the options that they didn't enter any data for?

We are thinking about using the LIKE clause, but how do we search for

price > and price LIKE?

A1: Basically, if the user doesn't enter anything for the prices (or qty),

assign them the max values. For example, if the user enters nothing for

PriceGreaterThan, then make it PriceGreaterThan=0. If they enter nothing

for PriceLessThan, then make it PriceLessThan=9999999999.99. Or

something of that nature.

However, I have not determined how to see if the user enters nothing.

If you use scanf to get user input, the users have to enter something.

If you use fgets to get user input, the value input is assigned as a

string. I don't know how to change a string to a float. (You can use

stoi to convert a string to an int). Any thoughts?

A2: You can use sscanf to scan the float from the string. Its

almost equivalent to scanf, but it lets you handle the

spaces in user input. You should do the gets, check that

the string contains some data and then try to sscanf it

into the float/integer variable.

Use man to find out more about sscanf.

Subject: varchar

because the varchar field that holds the string is unsigned.i can get

around it, but i get a warning for doing it my way.

gets((unsigned char)title.arr);gives warning for removing sign bit.

A: You should use (unsigned char *) and not (unsigned char).

If it still gives you a warning ignore it, but it shouldn't...

Subject: Select where one value is NULL

values. As stated in a previous post, if the user doesn't enter any data for a

specific column, then that column is replaced with a %. For example, if I ask

the user what is the Middle Name of the author that wrote the book the user is

querying for, and the user just hits return, then the middle name is replaced

with a % in the select value. ie, select * from book where middlename='%'.

Here is my problem. This method works for every field which is NOT null.

However, if middle name is null for one of my books, then the search will

never find that book. % does not match null. How do I do this search?

I tried having a space as my default value for middle name, but that didn't

work. % does not match a space. Even if it did work, it is a horrible hack

solution that I don't want to use.

Any ideas?

A1:I think the easiest way is to modify your cursor to grab any row that 

matches your 'LIKE' condition for the middle name and OR that clause 

(in parenthesis) with a clause selecting middle name if it is null.

I would then add logic in you code to pitch rows with a null middle 

name if a middle name was specified.Be sure to use an indicator 

variable to determine whether or not the middle name column was null.

By the way, your space hack should work; try the following query in 

sqlplus:

select count(*) from dual where ' ' like '%';

I get back one row (as I should); if the like did not work, I would 

get back 0.

If you are like me, you added code to trim the white space off the 

beginning and end of user entries before using them with the database 

to make sure a user-entered 'DOG' matchesa user entered 'DOG'.

This code, if present, may be reducing your space to the null string.

Last, I would not feel too bad about the space hack.A similar hack 

is just now being phased out of a production Oracle product.

A2: Why not just insert an empty string rather than a NULL 

(middlename.len = 0). That should work. My suggestion is not

to stress out on solving this problem because the specification 

made the query more complicated than was originally intended.

Secondly, in your posting you have said "where middlename='%'"

which should be "where middlename like '%'". If thats just

a type, then no big deal, but otherwise, that could be your

problem.

Subject: space quota exceeded?

create my tables.The script runs fine up till the 10th thru 13th table,

then at some point I get the error:

ORA-01536: space quota exceeded for tablespace 'USERS'

I'm only creating 15 tables and they aren't what I imagine to be large from

a RDMS standpoint.Anyone know if there is some limit we need to adhere to

and what that may be?

On a related note, I'm wondering if in my trials I created some test tables

that are still hanging around.Is there a command to view all objects in my

tablespace?

A1: to check all the tables owned by you try sthg like:

select * from all_tables where owner='OPS$GTXXXX';

That should give you all ur tables and show you which ones to delete.

You might want to check what that error code stands for (from the oracle doc

pages -- the solutions are also listed there)

A2: I think we're all using the 'USERS' tablespace (the default); I know I am.

It may be that there is not enough free space for all of us to have our 

tables out there at once, and there are currently enough tables out there 

to prevent you from adding more.You might want to keep trying until you 

snag the space--of course this will come at someone else's expense.In the 

meantime, it'd be nice if some who are done would take down their tables 

so others can use the space.The faculty could also look into securing more 

space for the 'USERS' tablespace, so this is no longer a problem.

A3: You must have some old tables hanging around.

Do:

SELECT table_name FROM user_tables;

to get all the tables in your name.

A4: or you can do

select table_name from user_tables;

Subject: INSERT error

SQL> desc PUBLISHER

NameNull?Type

------------------------------- -------- ----

PUBLISHER#NOT NULL NUMBER(38)

COMPANY_NAMENOT NULL VARCHAR2(20)

PHONENOT NULL VARCHAR2(15)

ADDRESSVARCHAR2(20)

CITYVARCHAR2(15)

STATECHAR(2)

ZIP_CODENUMBER(38)

SQL> INSERT INTO PUBLISHER

2VALUES (1, "Al's Books", "8006661234", "1 Main St", "Peabody",

"NH", 33333)

;

VALUES (1, "Al's Books", "8006661234", "1 Main St", "Peabody", "NH",

33333)

*

ERROR at line 2:

ORA-00984: column not allowed here

A1: I don't know if you did a typo, but you are supposed to use 'single

quotes' and not "double quotes" to delimit strings. That might be your

problem.

Subject: database

prompt.when i'm using my program with embedded sql, changes take effect

for as long as the program is running, but then when the program exits, the

database reverts to it's original state before i ran the program.for

instance i run my program and add a book.the book is in the database as

long as the program is running, but once i exit, it's gone because i didn't

add the book from the sql prompt.

is that ok?it's good to have it that way for debugging purposes, but i

don't want to loose points if we are supposed to have it set so our program

can make permament changes.

A: The changes you make in your program are supposed to

be permanent. (That is the whole point of a database, right!?)

You probably are not doing EXEC SQL COMMIT WORK at the

end of your functions.

Subject: scanf problems

A: scanf() will only read up to the first space in your string, which i'm

guessing is your problem.if you use scanf() to read and type in a multiple

word string, it will read in up to the first space, and the next scanf()

call will read up to the second space and so on...use gets() to read a

string with spaces in it.

Q2: Okay, now I'm using fgets (safer than gets). To get an int, I use fgets

than stoi. How do I get a float? I'm using fgets even for numbers because I want to

terminate if someone doesn't enter a value and just hits enter. scanf requires the

user enter something.

A: you have to fflush(stdin) in order for gets() to work if you have used

scanf() before it, or else at your call to gets(), nothing will happen.