perl split file for database insert

[Ratzie]

I am probably making this really difficult but...
First off I can not use the DBI:DBD oracle module.

What I need to do is pull data from a table, sort, then put back in table.
Yes, the vendor should have put a sort in the app. but, I am now learning perl to do this.

I have pulled the data into an array, (dont know if this is correct for then inserting back.)

sub doSqlSelect {

my @buffer = qx { sqlplus -S $oraLoginId <<'EOF';
set heading off
set pagesize 0
set feedback off
SELECT feature||','||description
FROM vt_feature
ORDER by 1;
EOF
};

So now I have my array, separated by comma.
I need to truncate the table. (deal with that later)
But, now I need to insert back into the table.
I was thinking this should have been a hash, or do I split to array?

[James R. Ferguson · Accepted solution]

Hi:

Hashes require unique keys, but should be ideal:

my %table;
...
$table{$feature} = $description;
...
for my $key (sort keys %table) {
print "$key is $table{$key}\n";
}

Regards!

...JRF...

[TwoProc]

Why not just do all of this in Oracle itself?

alter table vt_feature rename to vt_feature_old;

create table vt_feature as
(SELECT feature||','||description
FROM vt_feature
ORDER by 1);

drop table vt_feature_old;

... Don't forget to recreate indexes, grant permissions, etc.

I must say though, I'm wondering why you want to sort the data in an Oracle database and not discuss indexes, primary keys, constraints...

[Hein van den Heuvel]

>> Yes, the vendor should have put a sort in the app. but, I am now learning perl to do this.

Sorry Laura, but that is a no go.
Oracle may, and will, return data in any order it pleases to, unless instructed to apply 'ORDER'.

Even when one inserts rows in order, then it is only likely, but not garantueed, that a select will return them in order of insertion.

Now if you like gambling then you can do the select -> truncate -> insert business, but then why not do that all within Oracle as John suggests. I suppose you could pipe those commands into Oracle from a perl script:

:
open (SQL, "| sqlplus....");
print SQL...

On perl usage. I would NOT suck data into an regular array first, only to re-store as associative array. Why not process as you go:

my $sql_command = << "EOF"
:
EOF

foreach (`$sql_command`) {
chomp;
my ($key,$value) = split;
$table{$key} = $value;
}

for my $key (sort keys %table) {
print "$key is $table{$key}\n";
}


Good luck! (You'll need some :-).

Hein.

[TwoProc]

correction from my previous posting...

create table vt_feature as
(SELECT feature||','||description
FROM vt_feature_old <<<<< fixed here
ORDER by 1);

Sorry about that, you need to select from the backed-up tablename, not the destination.

[Hein van den Heuvel]

Why not fix the code behind the vendors back? Just rename the old table and create a view with the old name on the old table properly ordered. No data will be moved. Sorry, no perl or unix stuff involved.
Basic Oracle stuff. Example below.

Waddaya think?
Regards,

Hein van den Heuvel
HvdH Performance Consulting.



SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 30 19:46:24 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect hein
Connected.
SQL>
SQL> create table vt_feature (feature varchar(20), description varchar(50));

Table created.

SQL> insert into vt_feature values ('aap','this is a monkey');
1 row created.
SQL> insert into vt_feature values ('noot','Dutch for a nut');
1 row created.
SQL> insert into vt_feature values ('mies','Proper name for a girl');
1 row created.
SQL> select * from vt_feature;

FEATURE DESCRIPTION
-------------------- -----------------------
aap this is a monkey
noot Dutch for a nut
mies Proper name for a girl

SQL> rename vt_feature to vt_feature_base;
Table renamed.
SQL> create view vt_feature as select * from vt_feature_base order by 1;

View created.
SQL> select * from vt_feature;

FEATURE DESCRIPTION
-------------------- -----------------------
aap this is a monkey
mies Proper name for a girl
noot Dutch for a nut

SQL> select * from vt_feature where description like 'P%';

FEATURE DESCRIPTION
-------------------- -----------------------
mies Proper name for a girl

SQL>

[Ratzie]

thanks