Re: Migrating from MS Access to oracle

condolences (in regards to table structure your developer built) -:)

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, April 11, 2003 2:48 PM

> Reasons to port from Access:
> -- Management want all custom apps accessible from the intranet.
> -- Too many versions/configurations to support. We still have several
> Access 2.0 database using different security databases.
> -- Prevent users from using Access to build their own custom app and
then
> hit us up to support it when it doesn't work correctly.
>
> Web developer does say "whatever you want to use". I think the DBA
troubles
> are already here. He built an intranet page using ASP and created this
> table:
>
> SQL> desc purch_req
> Name Null? Type
> ----------------------------------------------------- --------
> ------------------------
> REQNBR NOT NULL CHAR(10)
> REQNAME

VARCHAR2(50)
> SUG_SUPPLIER

VARCHAR2(50)
> SALES_ORDER_NBR CHAR(10)
> PO_NBR CHAR(10)
> REQ_DATE DATE
> EXT

VARCHAR2(4)
> DEPT

VARCHAR2(6)
> REQUEST_QTY1 NUMBER(7)
> REQUEST_QTY2 NUMBER(7)
> REQUEST_QTY3 NUMBER(7)
> REQUEST_QTY4 NUMBER(7)
> REQUEST_QTY5 NUMBER(7)
> REQUEST_QTY6 NUMBER(7)
> REQUEST_QTY7 NUMBER(7)
> REQUEST_QTY8 NUMBER(7)
> REQUEST_QTY9 NUMBER(7)
> REQUEST_QTY10 NUMBER(7)
> REQUEST_DOCK_DATE1 DATE
> REQUEST_DOCK_DATE2 DATE
> REQUEST_DOCK_DATE3 DATE
> REQUEST_DOCK_DATE4 DATE
> REQUEST_DOCK_DATE5 DATE
> REQUEST_DOCK_DATE6 DATE
> REQUEST_DOCK_DATE7 DATE
> REQUEST_DOCK_DATE8 DATE
> REQUEST_DOCK_DATE9 DATE
> REQUEST_DOCK_DATE10 DATE
> ACT_DOCK_DATE1 DATE
> ACT_DOCK_DATE2 DATE
> ACT_DOCK_DATE3 DATE
> ACT_DOCK_DATE4 DATE
> ACT_DOCK_DATE5 DATE
> ACT_DOCK_DATE6 DATE
> ACT_DOCK_DATE7 DATE
> ACT_DOCK_DATE8 DATE
> ACT_DOCK_DATE9 DATE
> ACT_DOCK_DATE10 DATE
> UM1 CHAR(4)
> UM2 CHAR(4)
> UM3 CHAR(4)
> UM4 CHAR(4)
> UM5 CHAR(4)
> UM6 CHAR(4)
> UM7 CHAR(4)
> UM8 CHAR(4)
> UM9 CHAR(4)
> UM10 CHAR(4)
> REQ_PART_NBR1 CHAR(25)
> REQ_PART_NBR2 CHAR(25)
> REQ_PART_NBR3 CHAR(25)
> REQ_PART_NBR4 CHAR(25)
> REQ_PART_NBR5 CHAR(25)
> REQ_PART_NBR6 CHAR(25)
> REQ_PART_NBR7 CHAR(25)
> REQ_PART_NBR8 CHAR(25)
> REQ_PART_NBR9 CHAR(25)
> REQ_PART_NBR10 CHAR(25)
> DESC1
> VARCHAR2(250)
> DESC2
> VARCHAR2(250)
> DESC3
> VARCHAR2(250)
> DESC4
> VARCHAR2(250)
> DESC5
> VARCHAR2(250)
> DESC6
> VARCHAR2(250)
> DESC7
> VARCHAR2(250)
> DESC8
> VARCHAR2(250)
> DESC9
> VARCHAR2(250)
> DESC10
> VARCHAR2(250)
> UNIT_COST1

NUMBER(15,4)
> UNIT_COST2

NUMBER(15,4)
> UNIT_COST3

NUMBER(15,4)
> UNIT_COST4

NUMBER(15,4)
> UNIT_COST5

NUMBER(15,4)
> UNIT_COST6

NUMBER(15,4)
> UNIT_COST7

NUMBER(15,4)
> UNIT_COST8

NUMBER(15,4)
> UNIT_COST9

NUMBER(15,4)
> UNIT_COST10

NUMBER(15,4)
> TOTAL

NUMBER(15,4)
> MANAGER

VARCHAR2(40)
> DIRECTOR

VARCHAR2(40)
> DAN

VARCHAR2(40)
> STEVE

VARCHAR2(40)
> BUYER CHAR(30)
> DENIED CHAR(1)
> SUPPLIER CHAR(30)
> BUYER_SIG

VARCHAR2(50)
> BUYER_DATE DATE
> TAX_STATUS

VARCHAR2(10)
> CONFIRM_WITH

VARCHAR2(50)
> SHIP_VIA

VARCHAR2(50)
> TRANSPORT_TRMS_CD CHAR(3)
> FOB

VARCHAR2(50)
> FOB_TERMS_CODE CHAR(3)
> ACTUAL_COST1

NUMBER(15,4)
> ACTUAL_COST2

NUMBER(15,4)
> ACTUAL_COST3

NUMBER(15,4)
> ACTUAL_COST4

NUMBER(15,4)
> ACTUAL_COST5

NUMBER(15,4)
> ACTUAL_COST6

NUMBER(15,4)
> ACTUAL_COST7

NUMBER(15,4)
> ACTUAL_COST8

NUMBER(15,4)
> ACTUAL_COST9

NUMBER(15,4)
> ACTUAL_COST10

NUMBER(15,4)
> ACTUAL_QTY1 NUMBER(7)
> ACTUAL_QTY2 NUMBER(7)
> ACTUAL_QTY3 NUMBER(7)
> ACTUAL_QTY4 NUMBER(7)
> ACTUAL_QTY5 NUMBER(7)
> ACTUAL_QTY6 NUMBER(7)
> ACTUAL_QTY7 NUMBER(7)
> ACTUAL_QTY8 NUMBER(7)
> ACTUAL_QTY9 NUMBER(7)
> ACTUAL_QTY10 NUMBER(7)
> EXT_COST1

NUMBER(15,4)
> EXT_COST2

NUMBER(15,4)
> EXT_COST3

NUMBER(15,4)
> EXT_COST4

NUMBER(15,4)
> EXT_COST5

NUMBER(15,4)
> EXT_COST6

NUMBER(15,4)
> EXT_COST7

NUMBER(15,4)
> EXT_COST8

NUMBER(15,4)
> EXT_COST9

NUMBER(15,4)
> EXT_COST10

NUMBER(15,4)
> MISC_SIG

VARCHAR2(50)
> RECVD_LINE1

VARCHAR2(1)
> RECVD_LINE2

VARCHAR2(1)
> RECVD_LINE3

VARCHAR2(1)
> RECVD_LINE4

VARCHAR2(1)
> RECVD_LINE5

VARCHAR2(1)
> RECVD_LINE6

VARCHAR2(1)
> RECVD_LINE7

VARCHAR2(1)
> RECVD_LINE8

VARCHAR2(1)
> RECVD_LINE9

VARCHAR2(1)
> RECVD_LINE10

VARCHAR2(1)
> FULL_RECVD

VARCHAR2(1)
> COMMENTS
> VARCHAR2(500)
> RECVD_DATE DATE
> RECVD_DATE2 DATE
> RECVD_DATE3 DATE
> RECVD_DATE4 DATE
> RECVD_DATE5 DATE
> RECVD_DATE6 DATE
> RECVD_DATE7 DATE
> RECVD_DATE8 DATE
> RECVD_DATE9 DATE
> RECVD_DATE10 DATE
> SENT_TO_CINCOM DATE
>
>
> YIKES!!!
>
> Thanks for the feedback,
>
> Jeff
>
> -----Original Message-----
> Sent: Thursday, April 10, 2003 2:44 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Jeff
> If it is just a few users, why not continue to use Access as a
front-end
> to Oracle? Past that, as Roy suggests visit with your Web developer and
ask
> which are the supported tools at your site. If he shrugs and says
"whatever
> you want to use", then take this as a harbinger of DBA troubles to come.
>
> Dennis Williams
> DBA, 40%OCP, 100% DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Thursday, April 10, 2003 1:19 PM
> To: Multiple recipients of list ORACLE-L
>
>
> My concern with migrating from MS Access to oracle isn't with moving the
> data but what tool am I going to use to build the application which
accesses
> the data. We have several Access applications that I would love to move
to
> our intranet. I just don't know where to begin, all we have for a web
> developer is a guy right out of college. I would appreciate it if someone
> could share with me what has been done elsewhere. Has anyone gone from
> using client-side applications to using web-based applications? What
> development tools/languages did you decide to use and why?
>
> Thanks,
> Jeff
>
> -----Original Message-----
> Sent: Thursday, April 10, 2003 8:14 AM
> To: Multiple recipients of list ORACLE-L
>
>
> I don't know if this is the cleanest way to do what you want but here
goes:
>
> 1. Save/export Access table into MS Excel.
> 2. Use the transpose function to convert columns to rows.
> 3. Clean up new column names
> 4. Save as .csv (comma delimited file)
> 5. Create table(s) in Oracle
> 5. Create sql loader script based to load into table.
>
> I've used this approach before with sucess. It spared me from having to
> write code to do the transposing. If you need to do some extra
> validation/conversion you could set up a staging table to load into, then
> apply validation logic with PL/SQL procedures functions to load into final
> table. Hope it helps
>
>
> D.Phillips
>
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, April 09, 2003 1:33 AM
>
>
> > Hi List,
> > I would like your views on migrating from Microsoft
> > Access to Oracle8i.
> > Folloing is one of the scenarios that we might come
> > across.
> > There are columns in table need to insert as a row.
> > e.g. Access Table1 has column col1,col2,col3
> > we need to insert those col1 as row1 ,col2 as row2 of
> > Oracle table.
> > 1.Can this be done using sql loader ?
> > 2.What are the typical limitations u experienced of
> > sql loader ?
> > 3.What are other ways to migrate (i.e. other than sql
> > loader)and also validate migrated data.
> >
> > I will appreciate any comments and views.
> >
> >
> > Platform:Oracle8i,MS Access 2000
> > O.S.:Win2000.
> >
> >
> > Thanks
> > Sam
> >
> > __________________________________________________
> > Do you Yahoo!?
> > Yahoo! Tax Center - File online, calculators, forms, and more
> > http://tax.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: sam d
> > INET: sam_orafan_at_yahoo.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: David L Phillips
> INET: dphillip_at_dci-usa.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Eberhard, Jeff
> INET: Jeff.Eberhard_at_Rolls-RoyceGS.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.COM
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Eberhard, Jeff
> INET: Jeff.Eberhard_at_Rolls-RoyceGS.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Igor Neyman
  INET: ineyman_at_perceptron.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).