Quotes get real tricky when doing system calls. This method works for me
.... put your SQL into a varible using 'qx' (or qq, then run it with system),
such as
qx{sqlplus / << SQLEOF
set heading off
set linesize 120
spool viewdata.kj
select table_name,column_name,data_length,data_type
from dba_tab_columns
where table_name = $VIEWNAME
and owner = 'XXXX';
SQLEOF
};
-Jeff
--- Tony Esposito <[EMAIL PROTECTED]> wrote:
> Strike that.......I missed your earlier email.....sorry!
>
> > Anthony (Tony) Esposito
> > Senior Technical Consultant
> > Inovis(tm), formerly Harbinger and Extricity
> > 2425 N. Central Expressway, Suite 900
> > Richardson, TX 75080
> > (972) 643-3115
> > [EMAIL PROTECTED]
> >
>
>
> -----Original Message-----
> From: Tony Esposito
> Sent: Wednesday, April 02, 2003 2:11 PM
> To: 'Jensen Kenneth B SrA AFPC/DPDMPQ'; 'Jeff Westman'; beginners
> Subject: RE: Sql statements in perl scripts
>
>
> Any reason why you can not use DBI::Oracle instead of using a system call
> and 'here' document to access SQL*Plus?
> HTH
>
> > Anthony (Tony) Esposito
> > Senior Technical Consultant
> > Inovis(tm), formerly Harbinger and Extricity
> > 2425 N. Central Expressway, Suite 900
> > Richardson, TX 75080
> > (972) 643-3115
> > [EMAIL PROTECTED]
> >
>
>
> -----Original Message-----
> From: Jensen Kenneth B SrA AFPC/DPDMPQ
> [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, April 02, 2003 2:03 PM
> To: 'Jeff Westman'; beginners
> Subject: RE: Sql statements in perl scripts
>
>
> When I just quote the variable, this is what the spool looks like.
> ~~~~~
> select table_name,column_name,data_length,data_type from dba_tab_columns
> where table_name = '""'
> and owner = 'XXXXX';
>
> no rows selected
> ~~~~~
>
> perl also throws a warning that MAIN::$VIEWNAME & MAIN::$TABLENAME are only
> used once.
>
> I've tried countless ways, really starting to go bald at an early age from
> this.
>
> -----Original Message-----
> From: Jeff Westman [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, April 02, 2003 1:56 PM
> To: beginners
> Subject: Re: Sql statements in perl scripts
>
>
> Hi,
>
> --- Jensen Kenneth B SrA AFPC/DPDMPQ <[EMAIL PROTECTED]>
> wrote:
> > I can't install the DBI modules to talk to our database, so I am
> > trying to run some commands from a system call. I am trying to pass
> > some variables retrieved from command line arguments to the sql
> > statements. Here's my problem, I can't get the variables to expand for
> > use in the sql code
> >
> > #!perl -w
> > use strict;
> > use warnings;
> >
> > my ($TABLENAME, $VIEWNAME) = (shift,shift);
> >
> > system('sqlplus / << SQLEOF
> > set heading off
> > set linesize 120
> >
> > spool viewdata.kj
> > select table_name,column_name,data_length,data_type from
> > dba_tab_columns where table_name = \'$VIEWNAME\' and owner = 'XXXX';
> >
> > spool tabdata.kj
> > select table_name,column_name,data_length,data_type from
> > dba_tab_columns where table_name = \'$TABLENAME\' and owner =
> > 'XXXX'; SQLEOF
> > ');
> > __END__
> >
> > The above will execute, but no rows will be selected because the
> > variables are treated as null. I tried declaring the variables
> > globally but still the same problem. I also tried to pass the
> > variables to the system call (system (), ($TABLENAME, $VIEWNAME);) But
> > couldn't get that to work either. $_[0] was read as "scriptname[0]"..
> >
> > I can insert a print statement inside the system call and print the
> > variables to STDOUT, but can't get sql to use them. Am I missing
> > something blatantly obvious? Any help very much appreciated.
>
>
> From what I can see, yes...... use double-quotes, not single-quotes.
> Single
> quotes will not expand varables. See 'perldoc -q quote'
>
> -JW
>
>
> > Ken
> >
> > P.S. I only have modules distributed with perl 5.6.1, and I am not
> > allowed to install any new ones at the present time.
>
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Tax Center - File online, calculators, forms, and more
> http://tax.yahoo.com
>
> --
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
> --
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
