Home » RDBMS Server » Server Utilities » convertion of foxpro database(2.6 version) into oracle.....?
|
|
| Re: convertion of foxpro database(2.6 version) into oracle.....? [message #72384 is a reply to message #72346] |
Mon, 02 June 2003 18:00   |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If you are using Oracle 9i, then you may be able to use external tables.
If you are using Oracle 8i or earlier, the fastest-running method will probably be copying or listing the FoxPro tables to text files and using Oracle's SQL*Loader utility to load the data in the text files into Oracle tables. Prior to doing this, you will need to create Oracle tables of comparable structure and SQL*Loader control files. This is the method that I routinely use with FoxPro 2.6 for DOS and Oracle 8.1.7.
There are various other possibilities, such as ODBC or some third-party utility. Please click on the link below for a nice free utility from Tom Kyte. The utility is intended for DBASE files, so you will need to apply the modifications for FoxPro by Mattia Rossi. I have used this successfully before. It is convenient in that it can be used to automatically create the Oracle tables of comparable structure and you don't need to create any SQL*Loader control files. However, it is much slower than SQL*Loader. If your tables are small, this is the most convenient.
link above updated by Barbara Boehmer on October 22, 2007
[Updated on: Mon, 22 October 2007 09:08] Report message to a moderator |
|
|
|
| Re: convertion of foxpro database(2.6 version) into oracle.....? [message #72385 is a reply to message #72346] |
Mon, 02 June 2003 18:14 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I have taken Tom Kyte's original code, applied Mattia Rossi's modifications, and made some additional modification to produce the code below. If you copy the entire script below to a .sql file and run it from SQL*Plus, it will prompt you for the directory path where the FoxPro table is, the name of the FoxPro .dbf table, and the name of the Oracle table you wish to create, then it will create an Oracle table of comparable structure and load the data into it. I have just made the original a little more user-friendly. It is an all-in-one script with no need for separate runs or anything else. Although, I usually format my posts, I will skip the formatting in the hopes that it will make it easier to copy the code. If this is a one-time project and you are not familiar with SQL*Loader, it will probably be quicker and easier for you to copy this script and run it than to learn SQL*Loader and create the Oracle control files and Oracle tables manually. It can actually be separated into two separate scripts and I have labeled the beginning and end of those scripts. You only need to run the 1st script once to create the package, although it won't hurt to re-run it. Then, just run the second script once for each table. If you want to get fancier, Tom Kyte also has some java to read files from a directory into an Oracle table. You could use that, then use a cursor to loop through those, instead of re-runnning the 2nd script for each table.
-- 1st script:
-- create package and body:
create or replace package foxpro_pkg
as
-- procedure to a load a table with records
-- from a FoxPro file.
--
-- Uses a BFILE to read binary data and dbms_sql
-- to dynamically insert into any table you
-- have insert on.
--
-- p_dir is the name of an ORACLE Directory Object
-- that was created via the CREATE DIRECTORY
-- command
--
-- p_file is the name of a file in that directory
-- will be the name of the FoxPro file
--
-- p_tname is the name of the table to load from
--
-- p_cnames is an optional list of comma separated
-- column names. If not supplied, this pkg
-- assumes the column names in the FoxPro file
-- are the same as the column names in the
-- table
--
-- p_show boolean that if TRUE will cause us to just
-- PRINT (and not insert) what we find in the
-- FoxPro files (not the data, just the info
-- from the FoxPro headers....)
procedure load_Table( p_dir in varchar2,
p_file in varchar2,
p_tname in varchar2,
p_cnames in varchar2 default NULL,
p_show in BOOLEAN default FALSE);
end foxpro_pkg;
/
create or replace package body foxpro_pkg
as
-- Might have to change on your platform!!!
-- Controls the byte order of binary integers read in
-- from the FoxPro file
BIG_ENDIAN constant boolean default TRUE;
type dbf_header is RECORD
(
version varchar2(25), -- FoxPro version number
year int, -- 1 byte int year, add to 1900
month int, -- 1 byte month
day int, -- 1 byte day
no_records int, -- number of records in file,
-- 4 byte int
hdr_len int, -- length of header, 2 byte int
rec_len int, -- number of bytes in record,
-- 2 byte int
no_fields int -- number of fields
);
type field_descriptor is RECORD
(
name varchar2(11),
type char(1),
length int, -- 1 byte length
decimals int -- 1 byte scale
);
type field_descriptor_array
is table of
field_descriptor index by binary_integer;
type rowArray
is table of
varchar2(4000) index by binary_integer;
g_cursor binary_integer default dbms_sql.open_cursor;
-- Function to convert a binary unsigned integer
-- into a PLSQL number
function to_int( p_data in varchar2 ) return number
is
l_number number default 0;
l_bytes number default length(p_data);
begin
if (big_endian)
then
for i in 1 .. l_bytes loop
l_number := l_number +
ascii(substr(p_data,i,1)) *
power(2,8*(i-1));
end loop;
else
for i in 1 .. l_bytes loop
l_number := l_number +
ascii(substr(p_data,l_bytes-i+1,1)) *
power(2,8*(i-1));
end loop;
end if;
return l_number;
end to_int;
function mytrim(p_str in varchar2) return varchar2 is
i number;
j number;
v_res varchar2(100);
begin
for i in 1 .. 11 loop
if ascii(substr(p_str,i,1)) = 0 then
j:= i;
exit;
end if;
end loop;
v_res := substr(p_str,1,j-1);
return v_res;
end mytrim;
procedure get_header
(p_bfile in bfile,
p_bfile_offset in out NUMBER,
p_hdr in out dbf_header,
p_flds in out field_descriptor_array )
is
l_data varchar2(100);
l_hdr_size number default 32;
l_field_desc_size number default 32;
l_flds field_descriptor_array;
begin
p_flds := l_flds;
l_data := utl_raw.cast_to_varchar2(
dbms_lob.substr( p_bfile,
l_hdr_size,
p_bfile_offset ) );
p_bfile_offset := p_bfile_offset + l_hdr_size;
p_hdr.version := ascii( substr( l_data, 1, 1 ) );
p_hdr.year := 1900 + ascii( substr( l_data, 2, 1 ) );
p_hdr.month := ascii( substr( l_data, 3, 1 ) );
p_hdr.day := ascii( substr( l_data, 4, 1 ) );
p_hdr.no_records := to_int( substr( l_data, 5, 4 ) );
p_hdr.hdr_len := to_int( substr( l_data, 9, 2 ) );
p_hdr.rec_len := to_int( substr( l_data, 11, 2 ) );
p_hdr.no_fields := trunc( (p_hdr.hdr_len - l_hdr_size)/
l_field_desc_size );
for i in 1 .. p_hdr.no_fields
loop
l_data := utl_raw.cast_to_varchar2(
dbms_lob.substr( p_bfile,
l_field_desc_size,
p_bfile_offset ));
p_bfile_offset := p_bfile_offset + l_field_desc_size;
/*
p_flds(i).name := rtrim(substr(l_data,1,11),chr(0));
p_flds(i).type := substr( l_data, 12, 1 );
p_flds(i).length := ascii( substr( l_data, 17, 1 ) );
p_flds(i).decimals := ascii(substr(l_data,18,1) );
*/
p_flds(i).name := mytrim(substr(l_data,1,11));
p_flds(i).type := substr( l_data, 12, 1 );
p_flds(i).length := ascii( substr( l_data, 17, 1 ) );
p_flds(i).decimals := ascii(substr(l_data,18,1) );
end loop;
p_bfile_offset := p_bfile_offset +
mod( p_hdr.hdr_len - l_hdr_size,
l_field_desc_size );
end get_header;
function build_insert
( p_tname in varchar2,
p_cnames in varchar2,
p_flds in field_descriptor_array ) return varchar2
is
l_insert_statement long;
begin
l_insert_statement := 'insert into ' || p_tname || '(';
if ( p_cnames is NOT NULL )
then
l_insert_statement := l_insert_statement ||
p_cnames || ') values (';
else
for i in 1 .. p_flds.count
loop
if ( i <> 1 )
then
l_insert_statement := l_insert_statement||',';
end if;
l_insert_statement := l_insert_statement ||
'"'|| p_flds(i).name || '"';
end loop;
l_insert_statement := l_insert_statement ||
') values (';
end if;
for i in 1 .. p_flds.count
loop
if ( i <> 1 )
then
l_insert_statement := l_insert_statement || ',';
end if;
if ( p_flds(i).type = 'D' )
then
l_insert_statement := l_insert_statement ||
'to_date(:bv' || i || ',''yyyymmdd'' )';
else
l_insert_statement := l_insert_statement ||
':bv' || i;
end if;
end loop;
l_insert_statement := l_insert_statement || ')';
return l_insert_statement;
end build_insert;
function get_row
( p_bfile in bfile,
p_bfile_offset in out number,
p_hdr in dbf_header,
p_flds in field_descriptor_array ) return rowArray
is
l_data varchar2(4000);
l_row rowArray;
l_n number default 2;
begin
l_data := utl_raw.cast_to_varchar2(
dbms_lob.substr( p_bfile,
p_hdr.rec_len,
p_bfile_offset ) );
p_bfile_offset := p_bfile_offset + p_hdr.rec_len;
l_row(0) := substr( l_data, 1, 1 );
for i in 1 .. p_hdr.no_fields loop
l_row(i) := rtrim(ltrim(substr( l_data,
l_n,
p_flds(i).length ) ));
if ( p_flds(i).type = 'F' and l_row(i) = '.' )
then
l_row(i) := NULL;
end if;
l_n := l_n + p_flds(i).length;
end loop;
return l_row;
end get_row;
procedure show( p_hdr in dbf_header,
p_flds in field_descriptor_array,
p_tname in varchar2,
p_cnames in varchar2,
p_bfile in bfile )
is
l_sep varchar2(1) default ',';
procedure p(p_str in varchar2)
is
l_str long default p_str;
begin
while( l_str is not null )
loop
dbms_output.put_line( substr(l_str,1,250) );
l_str := substr( l_str, 251 );
end loop;
end;
begin
p('/*');
p( 'Size of FoxPro File: ' || dbms_lob.getlength(p_bfile) );
p( 'FoxPro Header Information: ' );
p( chr(9)||'Version = ' || p_hdr.version );
p( chr(9)||'Year = ' || p_hdr.year );
p( chr(9)||'Month = ' || p_hdr.month );
p( chr(9)||'Day = ' || p_hdr.day );
p( chr(9)||'#Recs = ' || p_hdr.no_records);
p( chr(9)||'Hdr Len = ' || p_hdr.hdr_len );
p( chr(9)||'Rec Len = ' || p_hdr.rec_len );
p( chr(9)||'#Fields = ' || p_hdr.no_fields );
p( chr(10)||'--Data Fields:' );
for i in 1 .. p_hdr.no_fields
loop
p( 'Field(' || i || ') '
|| 'Name = "' || p_flds(i).name || '", '
|| 'Type = ' || p_flds(i).Type || ', '
|| 'Len = ' || p_flds(i).length || ', '
|| 'Scale= ' || p_flds(i).decimals );
end loop;
p( chr(10) || 'Insert We would use:' );
p( build_insert( p_tname, p_cnames, p_flds ) );
p( chr(10) || 'Table that could be created to hold data:');
p('*/');
p( 'create table ' || p_tname );
p( '(' );
for i in 1 .. p_hdr.no_fields
loop
if ( i = p_hdr.no_fields ) then l_sep := ')'; end if;
dbms_output.put
( chr(9) || '"' || p_flds(i).name || '" ');
if ( p_flds(i).type = 'D' ) then
p( 'date' || l_sep );
elsif ( p_flds(i).type = 'F' ) then
p( 'float' || l_sep );
elsif ( p_flds(i).type = 'N' ) then
if ( p_flds(i).decimals > 0 )
then
p( 'number('||p_flds(i).length||','||
p_flds(i).decimals || ')' ||
l_sep );
else
p( 'number('||p_flds(i).length||')'||l_sep );
end if;
else
p( 'varchar2(' || p_flds(i).length || ')'||l_sep);
end if;
end loop;
p( '/' );
end show;
procedure load_Table( p_dir in varchar2,
p_file in varchar2,
p_tname in varchar2,
p_cnames in varchar2 default NULL,
p_show in boolean default FALSE )
is
l_bfile bfile;
l_offset number default 1;
l_hdr dbf_header;
l_flds field_descriptor_array;
l_row rowArray;
begin
l_bfile := bfilename( p_dir, p_file );
dbms_lob.fileopen( l_bfile );
get_header( l_bfile, l_offset, l_hdr, l_flds );
if ( p_show )
then
show( l_hdr, l_flds, p_tname, p_cnames, l_bfile );
else
dbms_sql.parse( g_cursor,
build_insert(p_tname,p_cnames,l_flds),
dbms_sql.native );
for i in 1 .. l_hdr.no_records loop
l_row := get_row( l_bfile,
l_offset,
l_hdr,
l_flds );
if ( l_row(0) <> '*' ) -- deleted record
then
for i in 1..l_hdr.no_fields loop
dbms_sql.bind_variable( g_cursor,
':bv'||i,
l_row(i),
4000 );
end loop;
if ( dbms_sql.execute( g_cursor ) <> 1 )
then
raise_application_error( -20001,
'Insert failed ' || sqlerrm );
end if;
end if;
end loop;
end if;
dbms_lob.fileclose( l_bfile );
exception
when others then
if ( dbms_lob.isopen( l_bfile ) > 0 ) then
dbms_lob.fileclose( l_bfile );
end if;
RAISE;
end load_table;
end foxpro_pkg;
/
-- end of 1st script
-- 2nd script:
-- create script to create tables:
STORE SET saved_settings REPLACE
SET ECHO OFF FEEDBACK OFF HEADING OFF SERVEROUTPUT ON SIZE 1000000 VERIFY OFF
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
ACCEPT path_for_foxpro_table PROMPT 'Enter path for FoxPro Table (/directory): '
ACCEPT name_of_foxpro_table PROMPT 'Enter name of FoxPro Table (.dbf): '
ACCEPT name_of_oracle_table PROMPT 'Enter name of Oracle Table: '
CREATE OR REPLACE DIRECTORY TEMP_DIR as '&path_for_foxpro_table';
SPOOL foxpro.sql
BEGIN
foxpro_pkg.load_table
('TEMP_DIR',
'&name_of_foxpro_table',
'&name_of_oracle_table',
p_show => TRUE );
END;
/
SPOOL OFF
START saved_settings
-- run script to create table:
START foxpro.sql
-- load data into table:
BEGIN
foxpro_pkg.load_table
('TEMP_DIR',
'&name_of_foxpro_table',
'&name_of_oracle_table');
END;
/
-- end of 2nd script
|
|
|
|
| quitar un codigo de barras que al parecer esta repetido en el codigo pero no lo [message #73988 is a reply to message #72385] |
Mon, 16 August 2004 06:47 |
ma elena rosas
Messages: 1
Registered: August 2004
|
Junior Member |
|
|
al parecer hay que quitar la parte del codigo de barras del codigo en fox pro version 2.6 cosa que no encuentro mas que esta parte:
=Prn_CodBar(.T.)
ok_imp = .T.
IF Ini_CodBar()
FOR IndEti=VAL(m.Consec) TO VAL(m.Consec)+m.cuantaseti-1
WAIT WINDOW 'Imprimiendo Etiqueta : '+ALLTRIM(STR(IndEti-VAL(m.Consec)+1)) NOWAIT
IF INKEY() = 27 AND MSGBOX("¿ Desea Abortar la Impresión ?","Confirmación",mb_YesNo+MB_IconQuestion)==6
EXIT
ELSE
IF !Imp_CodBar(PADR(m.CURP,18)+Secreta(m.FecExp,m.Edo,m.Jur,IndEti),"2")
EXIT
ENDIF
ENDIF
NEXT
ENDIF
=Prn_CodBar(.F.)
WAIT WINDOW 'Impresión Terminada' NOWAIT
estoy buscando en los archovs prg, spr, qpr, mnx, pjx, scx, frx y no encuentro nada podrian ayudarme es urgente
[Updated on: Fri, 18 February 2005 23:32] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Wed Dec 25 07:42:21 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
