CODEWITHSUNDEEP
sqlperloracle-databasedbi
user1579740
user1579740

Reputation: 21

Perl DBI modifying Oracle database by creating a VIEW

I wrote a Perl script to check the data in an Oracle database. Because the query process is very complex I chose to create a VIEW in the middle. Using this view the code could be largely simplified.

The Perl code run well when I used it to query the database starting from a file, like Perl mycode.pl file_a. The Perl code reads lines from file_a and creates/updates the view until the end of the input. The results I achieved are completely right.

The problem came when I simultaneously run

perl mycode.pl file_a

and

perl mycode.pl file_b

to access the same database. According to my observation, the VIEW used by the first process will be modified by the second process. These two processes were intertwined on the same view.

Is there any suggestion to make these two processes not conflict with one another?

The Perl code for querying database is normally like this, but the details in each real query is more complex. 

my ($gcsta,$gcsto,$cms) = @t; #(details of @t is read from a line in file a or b)

my $VIEWSS = 'CREATE OR REPLACE VIEW VIEWSS AS SELECT ID,GSTA,GSTO,GWTA FROM TABLEA   WHERE GSTA='.$gcsta.' AND GSTO='.$gcsto.' AND CMS='.$cms;

my $querying = q{ SELECT COUNT(*) FROM VIEWSS WHERE VIEWSS.ID=1};

my $inner_sth = $dbh->prepare($VIEWSS);
my $inner_rv  = $inner_sth->execute();

$inner_sth = $dbh->prepare($querying);
$inner_rv  = $inner_sth->execute();

Upvotes: 0

Views: 540

Answers (2)

Borodin
Borodin

Reputation: 126742

You must

  • Create the view only once, and use it everywhere

  • Use placeholders in your SQL statements, and pass the actual parameters with the call to execute

Is this the full extent of your SQL? Probably not, but if so it really is fairly simple.

Take a look at this refactoring for some ideas. Note that is uses a here document to express the SQL. The END_SQL marker for the end of the text must have no whitespace before or after it.

If your requirement is more complex than this then please describe it to us so that we can better help you

my $stmt = $dbh->prepare(<<'END_SQL');
SELECT count(*)
FROM tablea
WHERE gsta = ? AND gsto = ? AND cms= ? AND id = 1
END_SQL

my $rv = $stmt->execute($gcsta, $gcsto, $cms);


If you must use a view then you should use placeholders in the CREATE VIEW as before, and make every set of changes into a transaction so that other processes can't interfere. This involves disabling AutoCommit when you create the database handle $dbh and adding a call to $dbh->commit when all the steps are complete

use strict;
use warnings;

use DBI;

my $dbh = DBI->connect('dbi:Oracle:mydbase', 'user', 'pass',
        { AutoCommit => 0, RaiseError => 1 } );

my $make_view = $dbh->prepare(<<'END_SQL');
CREATE OR REPLACE VIEW viewss AS
SELECT id, gsta, gsto, gwta
FROM tablea
WHERE gsta = ? AND gsto = ? AND cms= ? AND id = 1
END_SQL

my $get_count = $dbh->prepare(<<'END_SQL');
SELECT count(*)
FROM viewss
WHERE id = 1
END_SQL

while (<>) {

  my ($gcsta, $gcsto, $cms) = split;

  my $rv = $make_view->execute($gcsta, $gcsto, $cms);
  $rv = $get_count->execute;
  my ($count) = $get_count->fetchrow_array;
  $dbh->commit;
}

Upvotes: 1

h4ck3rm1k3
h4ck3rm1k3

Reputation: 2100

Is the view going to be the same or different?

If the views are all the same then create it only once, or check if it exists with the all_views table : http://docs.oracle.com/cd/B12037_01/server.101/b10755/statviews_1202.htm#i1593583

You can easily create a view including your pid with the $$ variable to be the pid, but it wont be unique across computers, oracle has also some unique ids, see http://docs.oracle.com/cd/B14117_01/server.101/b10759/functions150.htm, for example, the SESSIONID.

But do you really need to do this? why dont you prepare a statement and then execute it? http://search.cpan.org/dist/DBI/DBI.pm#prepare

thanks, mike

Upvotes: 0

Related Questions