How to use a Perl variable in a SQL script (using backticks)

Question

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?

stackoverflow.com/questions/8966577/sqlplus-saving-to-file stackoverflow.com/questions/15253440/… — mpapec
– mpapec, Commented Jun 27, 2014 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
– Hambone, Commented Jun 28, 2014 at 2:59

Ekkehard.Horner · Accepted Answer · 2014-06-27 20:49:47Z

0

Define your replacement variable(s)
Use them in a here doc based on the content of your test.sql
Save it to test.sql
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)

answered Jun 27, 2014 at 20:49

Ekkehard.Horner

38.8k2 gold badges50 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zack Dawood · Accepted Answer · 2014-06-28 02:00:50Z

0

This one works, basically the flow is as below

Open the SQL File
Replace the $text with the variable value
Write it to the temporary file
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'

answered Jun 28, 2014 at 2:00

Zack Dawood

2,23020 silver badges18 bronze badges

2 Comments

Brittany Over a year ago

Hello, thanks for the suggestion! I was able to figure it out by using the & sign in the sql file instead of $.

Zack Dawood Over a year ago

Great if you have figured out.

Collectives™ on Stack Overflow

How to use a Perl variable in a SQL script (using backticks)

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related