By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,863 Members | 1,766 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,863 IT Pros & Developers. It's quick & easy.

How to execute .sql command using Perl script.

P: 1
Hi Folks,

I am facing problem to to execute .sql command in sql prompt using perl script. I am able to connect sql promt using "

Expand|Select|Wrap|Line Numbers
  1.         my $odbcinput = "odbcinput.txt";
  2.         my $odbcoutput = "odbcoutput.txt";
  3.  
  4.  
  5.         open (ODBCSQLFH, ">$odbcinput") || die "Unable to create input file ($odbcinput) for odbcsql\n";
  6.      print ODBCSQLFH "/ade/jareddy_lcm/lcm/sysman/admin/emdrep/sql/lcm/latest/tgt_types/fsn_tgt_types.sql";
  7.          close ODBCSQLFH;
  8.         {
  9.                 print ODBCSQLFH "sqlplus sysman/sysman@repos /o $odbcoutput $odbcinput\n";
  10.                 system ("sqlplus sysman/sysman\@repos");"
  11.  
This script is connecting to sql promt there i wants to execute

SQL> "@ADE_LCM_ROOT/lcm/sysman/admin/emdrep/sql/lcm/latest/tgt_types/fsn_tgt_types.sql"

using Perl script.

Thanks,

Ram.
Sep 14 '07 #1
Share this Question
Share on Google+
4 Replies


Kelicula
Expert 100+
P: 176
Hi Folks,

I am facing problem to to execute .sql command in sql prompt using perl script. I am able to connect sql promt using "
Expand|Select|Wrap|Line Numbers
  1.         my $odbcinput = "odbcinput.txt";
  2.         my $odbcoutput = "odbcoutput.txt";
  3.  
  4.  
  5.         open (ODBCSQLFH, ">$odbcinput") || die "Unable to create input file ($odbcinput) for odbcsql\n";
  6.      print ODBCSQLFH "/ade/jareddy_lcm/lcm/sysman/admin/emdrep/sql/lcm/latest/tgt_types/fsn_tgt_types.sql";
  7.          close ODBCSQLFH;
  8.         {
  9.                 print ODBCSQLFH "sqlplus sysman/sysman@repos /o $odbcoutput $odbcinput\n";
  10.                 system ("sqlplus sysman/sysman\@repos");"
  11.  
This script is connecting to sql promt there i wants to execute

SQL> "@ADE_LCM_ROOT/lcm/sysman/admin/emdrep/sql/lcm/latest/tgt_types/fsn_tgt_types.sql"
using Perl script.

Thanks,

Ram.
You can execute a system command (assuming you know how to execute in the shell) with a perl script by using backticks.

They are the symbol above the Tab key on the left side of your keyboard (also has tilda on it)

You can write:

Expand|Select|Wrap|Line Numbers
  1. $sqlCommand = `SQL>something`;
  2.  
  3. print $sqlCommand;
  4.  
  5.  
  6.  
More info Here http://www.devdaily.com/blog/Content/2/16/220/
Sep 14 '07 #2

P: 89
why dont you use the DBI module. Its easy...
I am giving an example.
Expand|Select|Wrap|Line Numbers
  1. # ! /usr/local/bin/perl
  2.  
  3. use CGI;
  4. use DBI;
  5.  
  6. $cgiobj = CGI-> new;
  7. Print $cgiobj-> header('text/html');
  8.  
  9. $parm = DBI:<oracle>:<DbName>:<machine>;
  10. $user = ......;
  11. $passwod = ....;
  12.  
  13. $conn = DBI-> connect($parm,$user,$passwod);
  14. $sql = 'select * from emp';
  15.  
  16. $state = $conn-> prepare($sql);
  17. $state-> execute;
  18. while(@row = $state-> fetchrow_array);
  19.  {
  20.   print '$_ \n';
  21.  }
  22. $state->finish;
  23. $conn->disconnected;
  24.  
Feb 15 '08 #3

Kelicula
Expert 100+
P: 176
why dont you use the DBI module. Its easy...
I am giving an example.
Expand|Select|Wrap|Line Numbers
  1. # ! /usr/local/bin/perl
  2.  
  3. use CGI;
  4. use DBI;
  5.  
  6. $cgiobj = CGI-> new;
  7. Print $cgiobj-> header('text/html');
  8.  
  9. $parm = DBI:<oracle>:<DbName>:<machine>;
  10. $user = ......;
  11. $passwod = ....;
  12.  
  13. $conn = DBI-> connect($parm,$user,$passwod);
  14. $sql = 'select * from emp';
  15.  
  16. $state = $conn-> prepare($sql);
  17. $state-> execute;
  18. while(@row = $state-> fetchrow_array);
  19.  {
  20.   print '$_ \n';
  21.  }
  22. $state->finish;
  23. $conn->disconnected;
  24.  
Yes, That IS the best approach.
Many engineers have put lots of time into developing a simply powerful, abstraction layer to dealing with (most all) databases.

It will save you TONS of energy!!

See Here: DBI
Feb 15 '08 #4

P: 3
@Kelicula
I've got a similar problem, but I've got hundreds of legacy scripts I need to call. I would like to be able to call them the same way I do in sqlplus or any other database tool.

I tried calling sqlplus, but my script dies after 64 system calls (windows fork error).

How do you call an external script from dbi? I tried start, @name.sql, exec, run, source, and a few other ways. I can't believe the programmers of Oraperl, DBD and DBI combined didn't have the foresight to create a method of calling a script.
Nov 25 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.