Home » Infrastructure » Windows » PL/SQL with Batch file
PL/SQL with Batch file [message #563797] Fri, 17 August 2012 03:20 Go to next message
vibo
Messages: 21
Registered: August 2012
Junior Member
Hi,

I am executing multiple PL/SQL files(.sql) with the help of a single batch file.
The batch file sql.bat has got 3 sub sql sub-tasks to complete once its run. The sql.bat is show below

@Echo off

CD C:\Report
echo Loadin tables from text file Report.txt
sqlplus security/password <c:\Report\loader_security.sql

echo Creating Security table
sqlplus security/password <c:\Report\creating_security_final.sql

echo Inserting text file Security table
sqlplus security/password <c:\Report\insert_security_final.sql

PAUSE



The sql.bat runs perfectly if I double click on the sql.bat file separately.
But if I call the sql.bat from a different batch file 'Final.bat' it throws the below error.

Error
-----------
Executing SQL commands and loading file into SQL tables
Loadin tables from text file Report.txt
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
Creating Security table
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
Inserting text file Security table
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
Press any key to continue . . .

Could you please help me what is incorrect.

The Final.bat file calls other bat files too. It is as show below.

CD C:\Report\Security
echo Merging all Files
CALL merge.bat

CD C:\Report\Security
echo Deleting old files
CALL del.bat

CD C:\Report\Security
echo Executing SQL commands and loading file into SQL tables
CALL sql.bat


Thanks for your help in advance.
VIBO

Re: PL/SQL with Batch file [message #563799 is a reply to message #563797] Fri, 17 August 2012 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59399
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Set your Oracle environment (ORACLE_SID, ORACLE_HOME, PATH) in each batch file.

Regards
Michel
Re: PL/SQL with Batch file [message #564229 is a reply to message #563799] Wed, 22 August 2012 04:39 Go to previous messageGo to next message
vibo
Messages: 21
Registered: August 2012
Junior Member
Thanks Michel. The above solution worked for couple of SQL queries.

Now I'm trying to delete a table in a particular schema through a batch file. The batch file code is given below.

set ORACLE_SID=orcl
set ORACLE_HOME=C:\oracle\product\11.2.0\dbhome_1
set PATH=C:\oracle\product\11.2.0\dbhome_1\BIN


echo Delete Tables if any exist 
sqlplus security/password <c:\Report\clean_tables.sql

pause



clean_tables.sql codes

  begin
    execute immediate 'drop table external_tables';
    execute immediate 'drop table security';
    exception
    when others then
    null;
  end;


The database gets disconnected and does not execute the sql table and throws the below error. Could you please help.

C:\Report>set ORACLE_HOME=C:\oracle\product\11.2.0\dbhome_1

C:\Report>set PATH=C:\oracle\product\11.2.0\dbhome_1\BIN

C:\Report>echo Delete Tables if any exist
Delete Tables if any exist

C:\Report>sqlplus security/password 0<c:\Report\clean_tables.sql

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 22 15:06:58 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 2 3 4 5 6 7 8 Disconnected from Oracle Database 11g En
terprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Re: PL/SQL with Batch file [message #564236 is a reply to message #564229] Wed, 22 August 2012 05:36 Go to previous messageGo to next message
Michel Cadot
Messages: 59399
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The database gets disconnected and does not execute the sql table and throws the below error


1/ You are diconnected because the end of the script
2/ There is no error (as you ignore them)
3/ Expected result, Oracle did what you asked.

Regards
Michel
Re: PL/SQL with Batch file [message #564240 is a reply to message #564236] Wed, 22 August 2012 05:52 Go to previous messageGo to next message
vibo
Messages: 21
Registered: August 2012
Junior Member
The objective to run the clean_tables.sql script is to check if the tables 'security' and 'external_tables' exists or not in the schema and delete it if it does. Even if the table does not exist it should not throw an error and pass over the control to the next batch file.

Here the control get passed on to the next batch files but clean_tables.sql does not get executed.
It gets disconnected as I have shown before.

Not sure what I am doing wrong. Please help!
Re: PL/SQL with Batch file [message #564248 is a reply to message #564240] Wed, 22 August 2012 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 59399
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but clean_tables.sql does not get executed.


A PL/SQL block ends with a "/" to be executed.
In addition, your PL/SQL block is... silly.
Just:
drop table external_tables;
drop table security;

This is the script you want to execute.

Regards
Michel
Re: PL/SQL with Batch file [message #564257 is a reply to message #564248] Wed, 22 August 2012 06:50 Go to previous messageGo to next message
vibo
Messages: 21
Registered: August 2012
Junior Member
Yes I would first want to run this pl/sql even if the tables does not exist in the schema.

Yes it does look silly as per requirements.

Thanks
VIBO
Re: PL/SQL with Batch file [message #564280 is a reply to message #564257] Wed, 22 August 2012 08:35 Go to previous messageGo to next message
Michel Cadot
Messages: 59399
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Yes I would first want to run this pl/sql even if the tables does not exist in the schema


You can do it with the 2 SQL and just ignore the return code.
And as you don't want to see anything from this part you can just have
sqlplus security/password -s @c:\Report\clean_tables.sql

with c:\Report\clean_tables.sql containing
set echo off
set termout off
drop table external_tables;
drop table security;
exit;

Regards
Michel

Re: PL/SQL with Batch file [message #564330 is a reply to message #564280] Wed, 22 August 2012 12:34 Go to previous messageGo to next message
vibo
Messages: 21
Registered: August 2012
Junior Member
This works fine.
But what if the table does not exist, it throws an error table or view does not exist.

For this scenario if the table does not exist it should just leave it as it is or pass the control to the next batch file.
(That is the reason in my initial code I had used an exception).
Re: PL/SQL with Batch file [message #564332 is a reply to message #564330] Wed, 22 August 2012 12:44 Go to previous messageGo to next message
Michel Cadot
Messages: 59399
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if the table does not exist it should just leave it as it is or pass the control to the next batch file.

What does this mean?
A script cannot make one thing or another thing on the same condition, how does it choose the thing?

Regards
Michel

Re: PL/SQL with Batch file [message #564350 is a reply to message #564332] Thu, 23 August 2012 00:34 Go to previous messageGo to next message
vibo
Messages: 21
Registered: August 2012
Junior Member
Well here is the entire scenario.

I need to load data from notepad to tables.
Batch file 1 - Before loading I need to check if the table exist in the schema I need to delete the tables.
Batch file 2 - It uses the external tables to load data from notepad into tables.
Batch file 3 - Inserts data from external tables into different table.

So before I run Batch file 2 and 3 I need to make sure Batchfile 1 is run without errors i.e. to ensure the external table and security table has been deleted.

So above I was trying to run Batchfile 1 i.e delete tables from a schema 'security' if the table exists or not.

Hope it makes sense.

Regards,
VIBO
Re: PL/SQL with Batch file [message #564361 is a reply to message #564350] Thu, 23 August 2012 02:32 Go to previous message
vibo
Messages: 21
Registered: August 2012
Junior Member
Thanks Michel.
Previous Topic: Batch file v/s SQL developer
Next Topic: Passing values/arguments to a batch file
Goto Forum:
  


Current Time: Mon Oct 20 03:49:57 CDT 2014

Total time taken to generate the page: 0.04632 seconds