| Author |
 Topic  |
|
|
paco
Junior Member
Spain
187 Posts |
 Posted - 30 March 2001 : 02:25:49
|
Hi,
I know that Oracle is not supported yet, if it did it would be perfect for me. Anybody knows when we'll have Oracle support?
Regards,
Paco
< |
|
|
carlyfarley
Starting Member
1 Posts |
Posted - 27 November 2001 : 10:26:54
|
I would like to know when an oracle version will be out too.
Im finding it hard to get a SQL licience at work at the moment because we already have Oracle
can some get back to us both on this
Thanks
Carl
< |
 |
|
|
gphares
Starting Member
1 Posts |
Posted - 15 August 2002 : 16:03:22
|
Greetings:
I just modified Snitz to work with Oracle 8i. It's not ready yet for full release to the public but I can tell show/tell you what you need to do to make it "Oracle Friendly".
1) In the config.asp program, add this line
strConnString = "DSN=ORACLEDB;UID=userid;PWD=password"
where ORACLEDB is the name of your database in the TSNNAMES.ORA file
2) When you set up the tables for the Oracle database, remember that Oracle does not have the "autonumber" feature like Access & SQL Server. You need to set up "Sequences" in Oracle for each autonumber field and grant your userid select permission to them. The sequence for the CONFIG_ID sequence must start with at least 125 because the table already has existing records. Essentially, you use the Oracle sequence feature to generate the next number for your insert statement. You will need a separate sequence for each autonumber field.
3) Since Oracle doesn't support "memo" fields, you need to create the memo fields as varchar2 database field in Oracle. I used varchar2(2000) and it worked fine for me. You could probably use a blob but it isn't as efficient as a varchar2 with indexes.
4) Copy the tables from Access to Oracle. After linking the Oracle tables, I used MS/Access to load the records and it worked just fine. You need to make sure that the Oracle userid in the config.asp file has appropriate access to the tables.
5) SQL Server & Oracle SQL are similiar EXCEPT for joins of multiple tables. Any SQL code that uses "joins" must be replaced by the corresponding Oracle version. I can send examples of the Oracle version via email if you want. They are quite different.
6) All "insert" SQL Statements must be modified to include the auto number field. Because we are letting Oracle generate the next number, we want to include this as part of the insert statement. The modified code below is from the program "post.info.asp".
strSql = "INSERT INTO " & strTablePrefix & "TOPICS (TOPIC_ID, FORUM_ID"
....snipit..
strSql = strSql & ") VALUES (" & "TableOwner.FORUM_TOPIC_ID.nextval, "
strSql = strSql & Forum_ID
In the original version, TOPIC_ID was an autonumber generated field. We told Oracle to go the sequence table FORUM_TOPIC_ID and get the next available number and update the next number field by 1 so it's ready for the next user.
7) In a few cases, some records was created with null values. This causes errors in the "cint" command. I had to prefix the code with a "if isnull (myfield) then myfield = "0" end if" command to make sure that the command doesn't error out. If you set up the Oracle database to preload a "0" instead of a null on the insert, this will probably get rid of the problem.
Hope it helps,
Gene Phares
gphares@mn.Nilfisk-Advance.com
< |
|
|
|
pweighill
Junior Member
United Kingdom
453 Posts |
Posted - 15 August 2002 : 16:32:10
|
quote:
Originally posted by gphares
6) All "insert" SQL Statements must be modified to include the auto number field. Because we are letting Oracle generate the next number, we want to include this as part of the insert statement. The modified code below is from the program "post.info.asp".
strSql = "INSERT INTO " & strTablePrefix & "TOPICS (TOPIC_ID, FORUM_ID"
....snipit..
strSql = strSql & ") VALUES (" & "TableOwner.FORUM_TOPIC_ID.nextval, "
strSql = strSql & Forum_ID
In the original version, TOPIC_ID was an autonumber generated field. We told Oracle to go the sequence table FORUM_TOPIC_ID and get the next available number and update the next number field by 1 so it's ready for the next user.
Just wondering, would it be possible to do this with triggers or default values in the create table statement?
e.g
CREATE TABLE TOPICS
( TOPIC_ID NUMBER(9) DEFAULT TableOwner.FORUM_TOPIC_ID.nextval,
...
);
This would mean that there would be no need to modify the update statements in the asp code, making it easier to support.< |
Edited by - pweighill on 15 August 2002 17:21:21 |
|
|
|
pweighill
Junior Member
United Kingdom
453 Posts |
Posted - 16 August 2002 : 11:02:02
|
I've had a look a the SQL Reference Manual for Oracle 9.2 and in section 2.84 it states that you cannot use a sequence value in a DEFAULT, so that leaves the trigger option.
CREATE TRIGGER TableOwner.TOPICS_INSERT
BEFORE INSERT ON TableOwner.TOPICS
FOR EACH ROW
BEGIN
:new.TOPIC_ID := TableOwner.FORUM_TOPIC_ID.nextval;
END; < |
|
|
| |
Topic |
|
Forum Locked
