OraFAQ Forum: Windows » PL/SQL with Batch file

Home » Infrastructure » Windows » PL/SQL with Batch file

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PL/SQL with Batch file [message #563797]

Fri, 17 August 2012 03:20

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

Code: [Select all] [Show/ hide]

@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.

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: PL/SQL with Batch file [message #563799 is a reply to message #563797]

Fri, 17 August 2012 03:30

Michel Cadot
Messages: 68756
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Set your Oracle environment (ORACLE_SID, ORACLE_HOME, PATH) in each batch file.

Regards
Michel

Report message to a moderator

Re: PL/SQL with Batch file [message #564229 is a reply to message #563799]

Wed, 22 August 2012 04:39

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.

Code: [Select all] [Show/ hide]

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

Code: [Select all] [Show/ hide]

  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

Report message to a moderator

Re: PL/SQL with Batch file [message #564236 is a reply to message #564229]

Wed, 22 August 2012 05:36

Michel Cadot
Messages: 68756
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: PL/SQL with Batch file [message #564240 is a reply to message #564236]

Wed, 22 August 2012 05:52

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!

Report message to a moderator

Re: PL/SQL with Batch file [message #564248 is a reply to message #564240]

Wed, 22 August 2012 06:23

Michel Cadot
Messages: 68756
Registered: March 2007
Location: Saint-Maur, France, https...

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:

Code: [Select all] [Show/ hide]

drop table external_tables;
drop table security;

This is the script you want to execute.

Regards
Michel

Report message to a moderator

Re: PL/SQL with Batch file [message #564257 is a reply to message #564248]

Wed, 22 August 2012 06:50

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

Report message to a moderator

Re: PL/SQL with Batch file [message #564280 is a reply to message #564257]

Wed, 22 August 2012 08:35

Michel Cadot
Messages: 68756
Registered: March 2007
Location: Saint-Maur, France, https...

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

Code: [Select all] [Show/ hide]

sqlplus security/password -s @c:\Report\clean_tables.sql

with c:\Report\clean_tables.sql containing

Code: [Select all] [Show/ hide]

set echo off
set termout off
drop table external_tables;
drop table security;
exit;

Regards
Michel

Report message to a moderator

Re: PL/SQL with Batch file [message #564330 is a reply to message #564280]

Wed, 22 August 2012 12:34

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).

Report message to a moderator

Re: PL/SQL with Batch file [message #564332 is a reply to message #564330]

Wed, 22 August 2012 12:44

Michel Cadot
Messages: 68756
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: PL/SQL with Batch file [message #564350 is a reply to message #564332]

Thu, 23 August 2012 00:34

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

Report message to a moderator

Re: PL/SQL with Batch file [message #564361 is a reply to message #564350]

Thu, 23 August 2012 02:32

vibo
Messages: 21
Registered: August 2012

Junior Member

Thanks Michel.

Report message to a moderator

Previous Topic:	Batch file v/s SQL developer
Next Topic:	Passing values/arguments to a batch file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 17 04:08:05 CDT 2025