Home » Infrastructure » Windows » To create a bat file to run a query
To create a bat file to run a query [message #486161] Tue, 14 December 2010 01:30 Go to next message
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
Re: To create a bat file to run a query [message #486164 is a reply to message #486161] Tue, 14 December 2010 01:42 Go to previous messageGo to next message
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
Re: To create a bat file to run a query [message #486166 is a reply to message #486164] Tue, 14 December 2010 01:47 Go to previous messageGo to next message
Littlefoot
Messages: 21623
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:\>
Re: To create a bat file to run a query [message #486176 is a reply to message #486166] Tue, 14 December 2010 02:35 Go to previous messageGo to next message
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 (^^^)
Re: To create a bat file to run a query [message #486178 is a reply to message #486166] Tue, 14 December 2010 02:41 Go to previous messageGo to next message
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
Re: To create a bat file to run a query [message #486180 is a reply to message #486178] Tue, 14 December 2010 02:53 Go to previous messageGo to next message
flyboy
Messages: 1895
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).
Re: To create a bat file to run a query [message #486184 is a reply to message #486180] Tue, 14 December 2010 03:28 Go to previous messageGo to next message
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

Re: To create a bat file to run a query [message #486186 is a reply to message #486184] Tue, 14 December 2010 03:41 Go to previous messageGo to next message
flyboy
Messages: 1895
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.
Re: To create a bat file to run a query [message #486231 is a reply to message #486186] Tue, 14 December 2010 06:18 Go to previous messageGo to next message
RemyaRM
Messages: 6
Registered: December 2010
Junior Member
I tried that But still I am not getting the correct table format. can anyone help?
Re: To create a bat file to run a query [message #486235 is a reply to message #486231] Tue, 14 December 2010 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67546
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
Re: To create a bat file to run a query [message #486236 is a reply to message #486235] Tue, 14 December 2010 06:56 Go to previous messageGo to next message
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.
Re: To create a bat file to run a query [message #486238 is a reply to message #486236] Tue, 14 December 2010 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 67546
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
Re: To create a bat file to run a query [message #486339 is a reply to message #486238] Wed, 15 December 2010 00:37 Go to previous messageGo to next message
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
Re: To create a bat file to run a query [message #486353 is a reply to message #486339] Wed, 15 December 2010 01:41 Go to previous message
Michel Cadot
Messages: 67546
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:
  


Current Time: Sun Nov 29 17:03:18 CST 2020