| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Help pls: Access -> Oracle via SQLPlus?
On Wed, 24 May 2000 21:20:26 GMT, duryeaa00_at_my-deja.com wrote:
>1. How do I identify field constraints such as primary and foreign keys
>in a CREATE statement (I have examples of how to do it through ALTER but
>why do all of that schlock twice?) What's the syntax?
CREATE TABLE my_table (
X NUMBER,
Y VARCHAR2(30)
CONSTRAINT my_table_pk
PRIMARY KEY (X),
CONSTRAINT my_table_fk
FOREIGN KEY (Y) REFERENCES your_table );
The two constraints in this case are named my_table_pk and my_table_fk. I recommend naming your constraints rather than letting Oracle name them for you. For multicolumned keys, place a comma-separated list of columns in the paranentheses.
>2. Data Type conversions, please confirm or correct the following:
> Access Oracle
> Yes/No = Raw(1)
> Long Int= Number(7,0), Raw(16)?
> Single = Number(?,?)
> Text(X) = VarChar2(X), X = some number of chars
I'm not sure about all of these, but I would not use RAW for a yes/no field. I would use CHAR(1), with a CHECK constraint to restrict the values to Y and N. For example:
CREATE TABLE my_table (
X CHAR(1),
CONSTRAINT X_YN_ONLY
CHECK (X IN ('Y','N'))
);
To declare an integer in Oracle, use NUMBER(X,0) where X is the number of significant digits and can range from 1 to 38. For a 38 digit integer, use NUMBER(38,0). I'm not sure how many digits a LONG INT allows in Access.
>3. Access Autonumber: In order to replicate the Access Autonumber
>"variable" type, it looks like I need to create a TRIGGER (however one
>does that . . .)
This is a common issue. Here's an example that should work. Type it in using SQL*Plus.
CREATE TABLE my_table (
X NUMBER NOT NULL
);
CREATE SEQUENCE my_seq
START WITH 1;
CREATE OR REPLACE TRIGGER my_table_insert
BEFORE INSERT ON my_table
FOR EACH ROW
DECLARE
next_key number;
BEGIN
SELECT my_seq.nextval into next_key
FROM dual;
:new.x := next_key;
END;
/
To prevent the key from being updated, you can write this trigger:
CREATE OR REPLACE TRIGGER my_table_update
BEFORE UPDATE ON my_table
FOR EACH ROW
WHEN (new.x <> old.x)
BEGIN
RAISE_APPLICATION_ERROR(-20000,'Do not change key');
END;
/
The above trigger depends on the column's NOT NULL constraint to prevent someone from changing the key to a null value.
>However, the Oracle book I've got obliquely advises not
>to create too many triggers if I can avoid it for DB performance
>reasons. Is there an alternate method?
The only alternative is to have your application generate the sequential keys. IMHO, you should create the triggers if you need them. Grabbing a value from a stored sequence represents a miniscule performance hit. You'll probably need to do it anyway, whether from a trigger or not. Many versions of Oracle ago there was an issue with triggers not being compiled, but that's no longer the case.
>4. At this point I have a generic SQL book and "Oracle 8i, a Beginner's
>Guide," as my central resources. Can you point me to any more dedicated
>SQL or migration resources (free and online preferred but I'm not too
>picky at the moment ;)
Some people pointed you to an Access migration kit. That might be worth a look.
Jonathan
 |
 |