OraFAQ Forum: Windows » To create a bat file to run a query

Home » Infrastructure » Windows » To create a bat file to run a query

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

To create a bat file to run a query [message #486161]

Tue, 14 December 2010 01:30

RemyaRM
Messages: 6
Registered: December 2010

Junior Member

Hi

I need to create a bat file which include a query to run a package.
I use plsql developer to develop the package. its username,password and database is user,pswd,db1 respectively.
the query to run the package is "SELECT

COLUMN1 AS "LAST NAME",
COLUMN2 AS "FIRST NAME",
COLUMN3 AS "LOCATION"

FROM TABLE(PKG.GET_SUM('09-NOV-2010','12-NOV-2010')) "

can anyone help me what code shud I write to create a bat file

Thanks in advance

Report message to a moderator

Re: To create a bat file to run a query [message #486164 is a reply to message #486161]

Tue, 14 December 2010 01:42

delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City

Senior Member

You can not directly write query in bat file and execute it.

Instead of that you can create SQL script which contains that query and can execute that script on SQL prompt manually.

Or if you want to be done this automatically then you have to create job and have to execute that job on demand or at particular time interval.

regards,
Delna

Report message to a moderator

Re: To create a bat file to run a query [message #486166 is a reply to message #486164]

Tue, 14 December 2010 01:47

Littlefoot
Messages: 21783
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Not exactly YOUR example, but close enough: first part is to create a SQL file that'll contain SELECT statement:

-- SQL_FILE.SQL

select deptno, dname
from dept;

exit;

Then, batch file:

REM BAT_FILE.BAT

sqlplus -s scott/tiger@ora10 @sql_file.sql

Finally, execution:

M:\>bat_file

M:\>REM BAT_FILE.BAT

M:\>sqlplus -s scott/tiger@ora10 @sql_file.sql

Session altered.


Session altered.


    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS


M:\>

Report message to a moderator

Re: To create a bat file to run a query [message #486176 is a reply to message #486166]

Tue, 14 December 2010 02:35

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Actually, it IS possible to write the SQL directly into the batch file in Windows with a bracket-multi-line echo command and a pipe to SQLPlus.

(
echo select 1 from dual;
echo select 2 from dual;
echo select sum(1^^^) from dual;
) | sqlplus user/pass@db

The drawback being that you have to have an "echo" at each start of a line, and you have to escape each closing bracket with three carets (^^^)

Report message to a moderator

Re: To create a bat file to run a query [message #486178 is a reply to message #486166]

Tue, 14 December 2010 02:41

RemyaRM
Messages: 6
Registered: December 2010

Junior Member

Hi
I created a script file like this

connect usr/pswd@db1
spool c:\temp`get_sums.lst

SELECT

COLUMN1 AS "LAST NAME",
COLUMN2 AS "FIRST NAME",
COLUMN3 AS "LOCATION"

FROM TABLE(PKG.GET_SUM('09-NOV-2010','12-NOV-2010'))
/

spool off
exit;

and bat file like

sqlplus /nolog @C:\get_sums.sql

But I got the error like this

SP2-0158: unknown COLUMN option "AS"
SP2-0158: unknown COLUMN option "AS"
SP2-0158: unknown COLUMN option "AS"
SP2-0158: unknown COLUMN option "AS"
SP2-0158: unknown COLUMN option "AS"
SP2-0158: unknown COLUMN option "AS"
SP2-0158: unknown COLUMN option "AS"
SP2-0158: unknown COLUMN option "AS"
SP2-0158: unknown COLUMN option "AS"
SP2-0158: unknown COLUMN option "AS"
SP2-0158: unknown COLUMN option "AS"
SP2-0158: unknown COLUMN option "AS"
SP2-0734: unknown command beginning "FROM TABLE..." - rest of line ignored.
SELECT * ERROR at line 1: ORA-00936: missing expression

Please help

Report message to a moderator

Re: To create a bat file to run a query [message #486180 is a reply to message #486178]

Tue, 14 December 2010 02:53

flyboy
Messages: 1901
Registered: November 2006

Senior Member

Treating of blank lines inside SQL statement depends on SQLBLANKLINES setting of SQL*Plus - default is OFF. Either set it to ON or remove the blank lines from the SELECT command (adding comments on blank lines could be an option).

Report message to a moderator

Re: To create a bat file to run a query [message #486184 is a reply to message #486180]

Tue, 14 December 2010 03:28

RemyaRM
Messages: 6
Registered: December 2010

Junior Member

Hi Thank you all for the wonderful answers..I got the result but I am not getting it as a table format. How can I get this in a table format. My out put is like this
LAST NAME
--------------------------------------------------------------------------------
FIRST NAME
--------------------------------------------------------------------------------
LOCATION
--------------------------------------------------------------------------------
Activity1

LAST NAME
--------------------------------------------------------------------------------
FIRST NAME
--------------------------------------------------------------------------------
LOCATION
--------------------------------------------------------------------------------
Employee1

I want in this format
LAST NAME FIRST NAME LOCATION

Report message to a moderator

Re: To create a bat file to run a query [message #486186 is a reply to message #486184]

Tue, 14 December 2010 03:41

flyboy
Messages: 1901
Registered: November 2006

Senior Member

Open SQL*Plus® User's Guide and Reference for your Oracle version. It may be found with other Oracle documentation books e.g. online on http://tahiti.oracle.com/

Read there about COLUMN (FORMAT option) and SET (at least LINESIZE and PAGESIZE variables) commands and use them appropriately.

Report message to a moderator

Re: To create a bat file to run a query [message #486231 is a reply to message #486186]

Tue, 14 December 2010 06:18

RemyaRM
Messages: 6
Registered: December 2010

Junior Member

I tried that But still I am not getting the correct table format. can anyone help?

Report message to a moderator

Re: To create a bat file to run a query [message #486235 is a reply to message #486231]

Tue, 14 December 2010 06:29

Michel Cadot
Messages: 68510
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Post what you tried, you should be able to do it appropriately combining SET LINESIZE and COLUMN statements.
Before any post, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: To create a bat file to run a query [message #486236 is a reply to message #486235]

Tue, 14 December 2010 06:56

RemyaRM
Messages: 6
Registered: December 2010

Junior Member

Hi This is my code
connect usr/pswd@db1
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 100

COLUMN COLUMN1 HEADING 'LAST NAME'
COLUMN COLUMN2 HEADING 'FIRST NAME'
COLUMN COLUMN3 HEADING 'LOCATION'
COLUMN COLUMN4 HEADING 'ADDRESS'
COLUMN COLUMN5 HEADING 'A'
COLUMN COLUMN6 HEADING 'B'
COLUMN COLUMN7 HEADING 'C'
COLUMN COLUMN8 HEADING 'D'
COLUMN COLUMN9 HEADING 'E'
spool c:\temp\get_sums.csv

SELECT COLUMN1 ,COLUMN2 ,COLUMN3 ,COLUMN4 ,COLUMN5 ,
COLUMN6 ,COLUMN7, COLUMN8 ,COLUMN9 FROM

TABLE(ACTY_SUMM('09-NOV-2010','12-NOV-2010'))
/

spool off
exit;

i changed the line size and pagesize. But not able to get the desirable format. I have 12 columns.I need it in a report format.

Report message to a moderator

Re: To create a bat file to run a query [message #486238 is a reply to message #486236]

Tue, 14 December 2010 07:02

Michel Cadot
Messages: 68510
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

As you didn't specify the FORMAT parameter in your COLUMN statements SQL*Plus takes its default ones.
Specify this parameter accordingly to your needs.

Regards
Michel

Report message to a moderator

Re: To create a bat file to run a query [message #486339 is a reply to message #486238]

Wed, 15 December 2010 00:37

RemyaRM
Messages: 6
Registered: December 2010

Junior Member

Can you please explain your answer. how to use the format parameter with the COLUMN COLUMN1 HEADING 'LAST NAME'
I want the report in the following format

FirstNAME LASTNAME LOCATION A B C D E F G H I
NAME1 LNAME1 LOC1 A1 B1 C1 D1 E1 F1 G1 H1 I1
NAME2 LNAME2 LOC1 A2 B2 C2 D2 E2 F2 G2 H2 I2
LOCTOT
NAME3 LNAME3 LOC2 A3 B3 C3 D3 E3 F3 G3 H3 I3
LOCTOT

I need all the column in one row and their corresponding values of each person under each column and after each location there will be location total and at the end there will be grant total. I tried with the linesize,page size and all. still no result. Can anyone help me to get the report in this format

Report message to a moderator

Re: To create a bat file to run a query [message #486353 is a reply to message #486339]

Wed, 15 December 2010 01:41

Michel Cadot
Messages: 68510
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

COLUMN COLUMN1 HEADING 'LAST NAME' FORMAT ...

Please read the documentation.
And read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Wed, 15 December 2010 01:52]

Report message to a moderator

Previous Topic:	Set font properties of arabic text (OLE2)
Next Topic:	Cannot Connect

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Oct 01 10:35:24 CDT 2023