473,320 Members | 1,810 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to execute .sql command using Perl script.

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
4 19645
Kelicula
176 Expert 100+
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
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
176 Expert 100+
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
@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

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

Similar topics

1
by: Matt | last post by:
I used to execute a command line script in IIS 4 using Dynuexec. This is a third part server object I installed on the server (http://www.dynu.com/dynuexec.asp). I installed this object onto my...
2
by: Kai Thorsrud | last post by:
Hi I'm currently into converting a perl linux app into a .Net windows service. The application monitors our syslog log files to capture i.p adress changes on some of our routers having dynamic...
0
by: vijay | last post by:
hai every bady.. this is vijay from india. I want execute "shutdown immediate" command on oracle database from perl script. I was trying to execute through connect(), prepare() & execute()..But It's...
3
by: Acrobatic | last post by:
Hello I've got a simple Perl script that works fine from the command line but not from PHP via the browser. It's a conversion program that converts an image to a different format, and writes the...
23
by: ticfranca | last post by:
Hi, I'm getting this error in the code below: sub Pega_recorde { $database = 'bundinha'; $host = 'localhost'; $usuario = 'myhumoradm'; $senha = 'my8xr2d2'; ...
9
by: skyy | last post by:
Hi i am trying to use the system() command to run some linux command using perl script... Eg.. Deleting files. system("rm $filename"); However, when my $filename contain " character, the...
1
shrek123
by: shrek123 | last post by:
Hi, I am in a big mess. I have to execute command on remote machine using perl script. I have used INET Socket module to do this operation. Following is the code snipet. use IO::Socket;
1
parajaganesh
by: parajaganesh | last post by:
I am using Windows ClearCase Explorer. I do some scripting process to create a new view in ClearCase. But it cannot be displayed in the current screen. We need to press Alt + F5 after running my...
2
by: ksn2007 | last post by:
Hi All, I have a perl script which runs forever.I want to run it from another machine with ssh. The command i gave is ssh user@host perl myscript.perl The command is ok for the scripts...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.