Automatng import

[John]

Hi all

I am planning to make a cronjob so i would like to know if tht can be achived through 
a perl script.

To be more specific, i have an Oracle RDBMS and i want to execute some commands in the 
shell

.
system(sqlplus system/password);
system(drop jim cascade);



can be the above done? To execute internal commands as the sqlplus programs provides?

Re: Automatng import

[John]

I feel that  and system() is the same thing.

Well, what i want is to import some tables from a full export dump.

The problem is that if the user's tables will be overwritten during the
automating import.

For example
system(imp system/password fromuser=(myuser) touser=(myuser)
file=export.dmp tables=(table1, table2, table3, ...));

Will the above command work and keep the table(1-Z) every time up to date?

Or should i drop the user first? (that's the difficult part concerning Perl
and internal commands of sqlplus)
- Original Message -
From: Kevin Moore [EMAIL PROTECTED]
To: John [EMAIL PROTECTED]
Sent: Friday, December 26, 2003 9:40 PM
Subject: Re: Automatng import


 Try  drop user jim cascade

 John wrote:

 Hi all
 
 I am planning to make a cronjob so i would like to know if tht can be
achived through a perl script.
 
 To be more specific, i have an Oracle RDBMS and i want to execute some
commands in the shell
 
 .
 system(sqlplus system/password);
 system(drop jim cascade);
 
 
 
 can be the above done? To execute internal commands as the sqlplus
programs provides?
 
 

RE: Automatng import

[Paul Boutros]

Two ideas:

1. I'm not sure why can't you just access everything via DBI?

my $dbh = DBI-connect($connect_string, $user, $password);
$dbh-do(drop user jim cascade);
$dbh-commit();
$dbh-do(create user jim...);

2. If you really need to use system calls to SQL/PLUS why don't
you write the calls into a file and then execute the entire file
with one system command.

open(OUT, '', 'commands.txt');
print OUT   drop user jim cascade;\n,
commit;\n,
create user jim...;
close(OUT);

system(sqlplus scott/tiger commands.txt);

# or if you want to retain output:
my $val = `sqlplus scott/tiger commands.txt`;
print $val;

For all the sql/plus command-line options you'll need to check
the Oracle documentation.

Paul

 -Original Message-
 From: John [mailto:[EMAIL PROTECTED]
 Sent: Friday, December 26, 2003 3:38 PM
 To: dbi-users
 Subject: Re: Automatng import


 Quoting Kevin Moore [EMAIL PROTECTED]:

  Try  drop user jim cascade
 
  John wrote:
 
  Hi all
  
  I am planning to make a cronjob so i would like to know if tht can be
  achived through a perl script.
  
  To be more specific, i have an Oracle RDBMS and i want to execute some
  commands in the shell
  
  .
  system(sqlplus system/password);
  system(drop jim cascade);
  
  
  
  can be the above done? To execute internal commands as the
 sqlplus programs
  provides?
  
  
 
 

 I am afraid that this trick isn't going to work as far as system() will be
 waiting until the sqlplus command ends.

 What do you say?

SQL::Interpolate - request for comments

[David Manura]

Details are given below on a simple Perl module I wrote called 
SQL::Interpolate.  I'm interested if others find this useful, find it 
redundant to existing modules, or have better ideas for designing it, as 
I'm considering submitting it to CPAN.  (And if this is not the best 
place to propose this question, please let me know.)  This module arose 
as I was writing a lot of SQL construction/variable binding code 
(similar to Recipe 14.13--Building Queries Programmatically, Perl 
Cookbook, 2nd ed., Christiansen  Torkington) that was able to be more 
simply expressed using a dbi_interpolate function as shown.  I had 
previously tried using SQL::Abstract for this purpose and with some 
success, but it seemed less expressive than SQL (lacking certain 
constructs), failed under certain cases (e.g. using 'IN' with a 
zero-length list), and its syntax for nested AND/OR expressions wasn't 
as intuitive as in plain SQL.

=
web page: http://www.math2.org/david/sql-interpolate/
current version: 0.1
=
ABSTRACT
The purpose of SQL::Interpolate is to make writing SQL queries in Perl 
more natural, less redundant, and less error-prone. SQL::Interpolate 
takes your query specification and generates a correctly formatted SQL 
statement along with a list of bind values. These result values can then 
be passed to DBI or used for another purpose. SQL::Interpolate serves a 
purpose similar to that of SQL::Abstract 
http://search.cpan.org/%7Enwiger/SQL-Abstract/Abstract.pm except that 
SQL::Interpolate still exposes and utilizes the full native SQL syntax 
of your database.

SYNOPSIS

 use DBI;
 use SQL::Interpolate;
 
 my $rows = $dbh-selectall_arrayref(dbi_interpolate qq[
 SELECT * FROM table
 WHERE color IN], [EMAIL PROTECTED], qq[
   AND y = ], \$x, qq[
 LIMIT], [1, 10]
 );
 
 $dbh-do(dbi_interpolate qq[
 INSERT INTO table ], {
 color = $new_color,
 shape = $new_shape}
 );
 
 $dbh-do(dbi_interpolate qq[
 UPDATE table SET ], {
 color = $new_color,
 shape = $new_shape}, qq[
 WHERE color  ], \$color
 );
 
 my($sql, @bind) = sql_interpolate qq[
 SELECT * FROM table WHERE color IN], [EMAIL PROTECTED], qq[
 AND y = ], \$x
 );
 $d-prepare($sql);
 $d-execute(@bind);

=

- david