| Author |
 Topic  |
|
h2o
Starting Member
46 Posts |
 Posted - 03 August 2000 : 16:06:17
|
Every time I save an access table into an Oracle schema using ODBC... all of the fields are converted VCHAR2 in the newly constructed Oracle table. Must have something to do with the Oracle ODBC driver. Any Ideas on keeping the datatypes the same through the exporting process.
< |
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 03 August 2000 : 16:49:06
|
How can you keep the datatypes the same? There are some Access datatypes that have no corresponding SQL Server datatype, and I imagine Oracle is close to SQL Server in their datatype lists.
There are many SQL Server datatypes that have no corresponding Access datatype.
Access has 'text' and 'memo'. SQL Server has 'char', 'nchar', 'varchar', nvarchar', 'text' (not the same as Access text). How to choose?
======
Doug G
======< |
 |
|
|
Reinsnitz
Snitz Forums Admin
USA
3545 Posts |
Posted - 04 August 2000 : 10:33:47
|
another big issue I run into is the ID fields being auto sequenced and generated in Oracle... my understanding is that you have to write a trigger to do this, where SQL and Access do this out of the box.
If you have something that does this... and if you get everything working in Oracle... let me know and I'll include your data in the Core Applet so that everyone can use it if needed! 
Reinsnitz (Mike)
http://forum.snitz.com
><)))´>
The path of sorrow, and the path alone,
Leads to the land where sorrow is unknown.
No traveller e'er reached that bless'd abode,
Who found not thorns and briars in his road.
--Abraham Lincoln
< |
|
|
|
n/a
deleted
1 Posts |
Posted - 25 August 2000 : 01:02:42
|
quote:
How can you keep the datatypes the same? There are some Access datatypes that have no corresponding SQL Server datatype, and I imagine Oracle is close to SQL Server in their datatype lists.
There are many SQL Server datatypes that have no corresponding Access datatype.
Text is the equivlant of a memo field.
Varchar is a good 256 char max general purpose field.
INT is for integers only and excellent for sequencing issues.
You'll have to convert after import.
< |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 25 August 2000 : 01:23:42
|
Are you referring to Oracle datatypes? SQL Server allows 8K varchar (v7.0, anyway) non-unicode strings.
======
Doug G
======< |
|
|
|
h2o
Starting Member
46 Posts |
Posted - 25 August 2000 : 16:44:48
|
I am having troubles with integer fields turning into text when they are pushed into oracle. Since I started this post I have created a PL/SQL procedure that basically populates oracle tables with access data. This way I can force what ever data type I wish.
Mike, your correct in saying that the most effective way to emulate a autonumber in oracle is to create a trigger. This trigger would run when ever a new record was created and would basically take the recordcount of the table and add one, or what ever type of step you would want. The problem with this is, you are going to have to support oracle back-end stuff now too. Are you in the position where you can support PL/SQL questions? Maybe you could create another forum room for PL/SQL questions. You could always just emulate the autonumber in the ASP code.
Edited by - h2o on 25 August 2000 16:45:55< |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 28 August 2000 : 03:43:47
|
Hmm, looking at the article Doug G linked to it looks like you can do about anything with SQL in Oracle as you can do in SQL-Server.
That would mean you could make a inc_create_forum.asp for Oracle also....
BTW about the autonumber thing: this would best be a trigger in the database and not a Oracle specific thing in the ASP-code.
h20 -
What do you mean by PL/SQL questions ?
Are those questions about certain sql-statements in the forum-code not working with Oracle ?
If so, report them here in this forum please so we can have a look to see if we could make certain sql-statements more general.
Would you mind taking a look at inc_create_forum.asp to see if you can come up with something that works for Oracle.
After that it would just take some decisions about what fieldtype to use i.e when Access 97 uses TEXT, SQL 6.5 uses CHAR(255) and SQL 7 NCHAR(255) and Oracle ..... etc.
Pierre Gorissen 
I'm a fulltime workaholic back from vacation...
< |
|
|
|
Reinsnitz
Snitz Forums Admin
USA
3545 Posts |
Posted - 30 August 2000 : 13:24:50
|
PL/SQL is Oracle's Proprietary SQL statements
Reinsnitz (Mike)
http://forum.snitz.com
reinhold@bigfoot.com
><)))'>
"For God hath not given us the spirit of fear; but of power, and of love, and of a sound mind."
-- 2 Tim 1:7< |
|
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 30 August 2000 : 14:00:01
|
quote:
PL/SQL is Oracle's Proprietary SQL statements
As in "not the same as in other SQL versions" ?
Pierre Gorissen
A fool learns from experience... a wise man learns from others...< |
|
|
|
Reinsnitz
Snitz Forums Admin
USA
3545 Posts |
Posted - 30 August 2000 : 14:21:42
|
right... just like MS SQL Server's SQL is different from MS Access's SQL Oracle has it's own and calls it PL/SQL
Reinsnitz (Mike)
http://forum.snitz.com
reinhold@bigfoot.com
><)))'>
"For God hath not given us the spirit of fear; but of power, and of love, and of a sound mind."
-- 2 Tim 1:7< |
|
|
|
Nathan L
New Member
USA
83 Posts |
Posted - 30 August 2000 : 18:17:01
|
It's stupid how SQL was adopted as a standard for database access and all these companies have screwed it up.  But hey, what do you expect?
Response.Write ("The Reign of Nice")< |
|
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 30 August 2000 : 18:29:14
|
I asked, because I thought we had removed all proprietary SQL statements in the forum and only used SQL statements that would work on both Access and SQL-server....
Pierre Gorissen
A fool learns from experience... a wise man learns from others...< |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 30 August 2000 : 18:47:02
|
Nathan-
It's the other way around, all these companies screwed up the SQL THEN SQL was accepted as a standard. Oracle had PL/SQL, Sybase had Transact/SQL, and Access had Access SQL prior to the current SQL standard. Like any other standardized language, there are vendor-specific features.
However, AFAIK, SQL Server, Oracle, and Access all support the ANSI-92 standard very well. Many features, like autonumbers, etc, are outside the standard though.
======
Doug G
======< |
|
|
|
Reinsnitz
Snitz Forums Admin
USA
3545 Posts |
Posted - 08 September 2000 : 20:41:52
|
yea... we can probably make an autonumber in the forum code... but then that would mean conversion for all the pre-existing forums out there on upgrade... it would however ensure compatibility accross platforms... debate debate debate
As for making a PL/SQL forum... that is what this forum is for... all Oracle specific discussion... especialy since it is not supported as part of Snitz Forums 2000 yet... we don't have anyone who has been able to make a working version in Oracle.
Reinsnitz (Mike)
http://forum.snitz.com
reinhold@bigfoot.com
><)))'>
"For God hath not given us the spirit of fear; but of power, and of love, and of a sound mind."
-- 2 Tim 1:7< |
|
|
|
swathi
Starting Member
USA
1 Posts |
Posted - 09 November 2000 : 17:42:53
|
Hi,
I am trying to move the forum to Unix server with Oracle database. I have installed Chilisoft to start testing. If anyone else is trying to do the same, please do let me know, so we don't have to go through the same changes twice. Thanks.
BTW, to do the autoincrement in oracle, you can create a sequence
CREATE SEQUENCE reply_seq START WITH 1 ;
and everytime you insert a row in the table use the following command
insert into reply (reply_id, topic_id) Values (reply_seq.NEXTVAL, "test");
Swathi< |
|
|
|
Topic |
|
Forum Locked
