To create a bat file to run a query [message #486161] |
Tue, 14 December 2010 01:30 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/a4f996fcc36011259adb9e92898a1f8d?s=64&d=mm&r=g) |
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 #486176 is a reply to message #486166] |
Tue, 14 December 2010 02:35 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ThomasG
Messages: 3212 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 #486180 is a reply to message #486178] |
Tue, 14 December 2010 02:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
flyboy
Messages: 1903 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 #486236 is a reply to message #486235] |
Tue, 14 December 2010 06:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/a4f996fcc36011259adb9e92898a1f8d?s=64&d=mm&r=g) |
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.
|
|
|
|
|
|