0

I have seen various examples but I am unable to use DBI. How would I use a perl variable in an external sql script? For example the code in the perl script I would want would be:

$text = 'Germany';
@sql_output = `sqlplus -s user/pass\@databasename <<!
 @/pathtofile/test.sql;
  QUIT;
  !`;
print @sql_output;

The sql script would be:

SELECT DISTINCT City FROM Customers
Where Country = '$text'

(Just as an example, I'm using the code comes from w3schools.com sql tutorial http://www.w3schools.com/SQl/trysql.asp?filename=trysql_select_distinct)

An example that I found through searching had:

@/pathtofile/test.sql $text;

But it did not work when I tried it with my code. Is this the correct way to incorporate a perl variable into an external sql script?

Brittany
  • 41
  • 9
  • 1
    Why are you unable to use DBI? – friedo Jun 27 '14 at 19:54
  • 1
    +1, use DBI if at all possible. – jcaron Jun 27 '14 at 19:55
  • http://stackoverflow.com/questions/8966577/sqlplus-saving-to-file http://stackoverflow.com/questions/15253440/how-to-output-oracle-sql-result-into-a-file-in-windows – mpapec Jun 27 '14 at 20:11
  • Are DBI and DBD::Oracle installed correctly? Can you run "perl -e 'use DBI; use DBD::Oracle; print'" successfully? Seriously, don't do what you're doing above. Use DBI. It will be well worth your time to work out the kinks. DBD-Oracle is great. It leverages OCI and can do things like bulk inserts/updates. – Hambone Jun 28 '14 at 02:59

2 Answers2

0
  1. Define your replacement variable(s)
  2. Use them in a here doc based on the content of your test.sql
  3. Save it to test.sql
  4. Execute your backtick command

OR

  • If you want to learn basic SQL: Install a DBMS with a GUI that allows interactive/ad hoc queries (eg SQLite/SQliteman/SQLite browser plugin, MySQL/MySQLWorkbench, Access, OO/LO Base, ...)
  • If you want to work with Oracle: Concentrate on a complete/working install (which includes DBD:Oracle if your scripting language is Perl)
Ekkehard.Horner
  • 38,498
  • 2
  • 45
  • 96
0

This one works, basically the flow is as below

  1. Open the SQL File
  2. Replace the $text with the variable value
  3. Write it to the temporary file
  4. Execute SQL plus on the temporary file (Every time when you re-run this it will replace the file)

Try out and let me know.

test.pl

#!/usr/bin/perl

$text = 'Germany1';
open(HEADER,"SQLQuery.sql");
while(<HEADER>) { $_ =~ s/\$text/$text/g; $output .= $_; }
close(HEADER);

print "$output";


open (MYFILE, '>SQLQueryTemp.sql'); 

print MYFILE $output;

close(MYFILE);

#SQLQueryTemp.sql contains the $text replaced with 'Germany'

#@sql_output = `sqlplus -s user/pass\@databasename <<!
 #@/pathtofile/SQLQueryTemp.sql;
  #QUIT;
  #!`;
#print @sql_output;

SQLQuery.sql

SELECT DISTINCT City FROM Customers
Where Country = '$text'
Zack Dawood
  • 2,230
  • 20
  • 18