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?

asked Jun 27, 2014 at 19:52
4
  • 1
    Why are you unable to use DBI? Commented Jun 27, 2014 at 19:54
  • 1
    +1, use DBI if at all possible. Commented Jun 27, 2014 at 19:55
  • stackoverflow.com/questions/8966577/sqlplus-saving-to-file stackoverflow.com/questions/15253440/… 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. Commented Jun 28, 2014 at 2:59

2 Answers 2

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)
answered Jun 27, 2014 at 20:49
Sign up to request clarification or add additional context in comments.

Comments

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'
answered Jun 28, 2014 at 2:00

2 Comments

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

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.