newbi: struggling with syntax

davisb · #1 (**permalink**) 02-03-10, 17:13

At the prompt line, I submit:
sqlplus frscvt/passw@fintest @x.sql brenda irv x.prt
-the report gets created in ther current directory.

When I submit:
sqlplus frscvt/passw@fintest @x.sql brenda carmen /afs/uis.njit.edu/erp/user/finsvc/banner/dev/reports/x.prt
-I get error:
SP2-0606: Cannot create SPOOL file "/afs/uis.njit.edu/erp/user/finsvc/banner/dev/reports/x.prt"
How do I correct the submit command

The sql code is below:
set verify off

SET SERVEROUTPUT ON FORMAT WRAPPED;
SET TERMOUT ON;
SET FEEDBACK OFF;
SET PAGESIZE 55;
SET LINESIZE 132;
SPOOL '&3';

declare
X VARCHAR2(31);
Y VARCHAR2(31);

begin
dbms_output.enable(10000);
X :='&1';
Y :='&2';
dbms_output.put_line('var1 = '||X);
dbms_output.put_line('var2 = '||Y);

end;

/
exit;

anacedent · #2 (**permalink**) 02-03-10, 17:21

>/afs/uis.njit.edu/erp/user/finsvc/banner/dev/reports/x.prt
Does not look valid to me.

The fully qualified pathname must exist on the system from which you execute sqlplus.

Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version

davisb · #3 (**permalink**) 02-03-10, 17:27

The path is valid. we are on windows... accessing a fileserver on the unix box. Can I send a screenshot?
Here is ANOTHER log that references a similar path.

Invoking JOBID 459734, module - AWM_FTP_EDEA_F, alias name - AWM_FTP_EDEA_F
2010-02-02 18:02:30 Parameters:
2010-02-02 18:02:30 Remote UserId = frs_report
2010-02-02 18:02:30 Remote Host = edea.njit.edu
2010-02-02 18:02:30 FTP Command = GET
2010-02-02 18:02:30 Local Path = /afs/uis/erp/user/finsvc/banner/dev/interfaces/hrldfl_payroll/
2010-02-02 18:02:30 Local File/Pattern = null
2010-02-02 18:02:30 Remote Path = /FRS_Report/FRStest_fy10/Other/Banner/
2010-02-02 18:02:30 Remote File/Pattern = hrldfl.dat
2010-02-02 18:02:30 File Type = ASCII
2010-02-02 18:02:30 Minimum Run Time = 0
2010-02-02 18:02:30 Maximum Run Time = 0
2010-02-02 18:02:30 220 edea Microsoft FTP Service (Version 5.0).

davisb · #4 (**permalink**) 02-03-10, 17:28

The path is valid. we are on windows... accessing a fileserver on the unix box. Can I send a screenshot?
Here is ANOTHER log that references a similar path.

Invoking JOBID 459734, module - AWM_FTP_EDEA_F, alias name - AWM_FTP_EDEA_F
2010-02-02 18:02:30 Parameters:
2010-02-02 18:02:30 Remote UserId = frs_report
2010-02-02 18:02:30 Remote Host = edea.njit.edu
2010-02-02 18:02:30 FTP Command = GET
2010-02-02 18:02:30 Local Path = /afs/uis/erp/user/finsvc/banner/dev/interfaces/hrldfl_payroll/
2010-02-02 18:02:30 Local File/Pattern = null
2010-02-02 18:02:30 Remote Path = /FRS_Report/FRStest_fy10/Other/Banner/
2010-02-02 18:02:30 Remote File/Pattern = hrldfl.dat
2010-02-02 18:02:30 File Type = ASCII
2010-02-02 18:02:30 Minimum Run Time = 0
2010-02-02 18:02:30 Maximum Run Time = 0
2010-02-02 18:02:30 220 edea Microsoft FTP Service (Version 5.0).

davisb · #5 (**permalink**) 02-03-10, 17:30

The path is valid. we are on windows... accessing a fileserver on the unix box. Can I send a screenshot?
Here is ANOTHER log that references a similar path.

Invoking JOBID 459734, module - AWM_FTP_EDEA_F, alias name - AWM_FTP_EDEA_F
2010-02-02 18:02:30 Parameters:
2010-02-02 18:02:30 Remote UserId = frs_report
2010-02-02 18:02:30 Remote Host = edea.njit.edu
2010-02-02 18:02:30 FTP Command = GET
2010-02-02 18:02:30 Local Path = /afs/uis/erp/user/finsvc/banner/dev/interfaces/hrldfl_payroll/
2010-02-02 18:02:30 Local File/Pattern = null
2010-02-02 18:02:30 Remote Path = /FRS_Report/FRStest_fy10/Other/Banner/
2010-02-02 18:02:30 Remote File/Pattern = hrldfl.dat
2010-02-02 18:02:30 File Type = ASCII
2010-02-02 18:02:30 Minimum Run Time = 0
2010-02-02 18:02:30 Maximum Run Time = 0
2010-02-02 18:02:30 220 edea Microsoft FTP Service (Version 5.0).

davisb · #6 (**permalink**) 02-03-10, 17:56

oh wow... so sorry ... it appeared on this end that the browser was locked up so i tried on other browers & finally quit & emailed it in.

davisb · #7 (**permalink**) 02-03-10, 17:57

also if i just do SPOOL x.prt .... it works & creates a file inthe directory the sql is run ... I's like to create that file eslewhere.

shammat · #8 (**permalink**) 02-03-10, 18:02

Quote:

Originally Posted by davisb

The path is valid. we are on windows... accessing a fileserver on the unix box. Can I send a screenshot?
Here is ANOTHER log that references a similar path.

What happens when you type

Code:

dir /afs/uis.njit.edu/erp/user/finsvc/banner/dev/reports/x.prt

in a Windows commandline window?

I don't think that is a valid path in Windows.
SQL*Plus in Windows needs to be able to deal with forward slashes as well, which I also doubt.

davisb · #9 (**permalink**) 02-03-10, 18:07

If I change the spool to x.prt .... and run:
sqlplus frscvt/susana65@fintest @x.sql brenda bill

i get an output file on "/afs/uis.njit.edu/erp/user/finsvc/banner/dev/sql/x.prt"
which contains:
var1 = brenda
var2 = bill
What I would like is t oget the same file to be created at "/afs/uis.njit.edu/erp/user/finsvc/banner/dev/sql/x.prt"
instead.

davisb · #10 (**permalink**) 02-03-10, 18:15

"/afs/uis.njit.edu/erp/user/finsvc/banner/dev/sql/x.prt" is where the sql file is located

My command line looks like this...
W:\banner\dev\sql>sqlplus frscvt/susana65@fintest @x.sql brenda bill
where W is mapped to "/afs/uis.njit.edu/erp/user/finsvc/"

shammat · #11 (**permalink**) 02-03-10, 18:18

I don't get it:

Quote:

Originally Posted by davisb

i get an output file on "/afs/uis.njit.edu/erp/user/finsvc/banner/dev/sql/x.prt"

vs.

Quote:

I would like is t oget the same file to be created at "/afs/uis.njit.edu/erp/user/finsvc/banner/dev/sql/x.prt"

Seems to me, you already have what you want

davisb · #12 (**permalink**) 02-03-10, 18:18

oops ... anyway .. i will chime back into this muuch later this evening... and again 1st thing inthe morning.
I am on my way to dialysis treatment.
chow... and thanks!

davisb · #13 (**permalink**) 02-03-10, 18:22

no..... the file is being created in /sql/x.prt
in the same place where the sql file is sitting

i need the file to be created in /reports/x.prt

in the former, i use ......... SPOOL x.prt
in the latter,
i tried ....... SPOOL /../../../x.prt
also...
i tried ....... SPOOL '/../../../x.prt'
i tried ....... SPOOL "'/../../../x.prt'"
i tried ....... SPOOL "/../../../x.prt"

shammat · #14 (**permalink**) 02-03-10, 18:44

Quote:

Originally Posted by davisb

i tried ....... SPOOL /../../../x.prt

Why don't use a correct Windows path? If you are running SQL*Plus on Windows the forward slash will not work. You need to use:

SPOOL ..\..\..\..\x.prt

Additionally: if you start a path with \ ( or in your case /) this references the root directory.

So "\.." means "go one directory up, starting with the root directory", which is clearly not possible...

But this is getting way off topic now. This is a pure Windows problem

davisb · #15 (**permalink**) 02-04-10, 11:25

W:\banner\dev\sql>sqlplus frscvt/susana65@fintest @x.sql brenda carmen \afs\uis.njit.edu\erp\user\finsvc\banner\dev\repor ts\x.prt

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Feb 4 10:23:32 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SP2-0103: Nothing in SQL buffer to run.
SP2-0556: Invalid file name.
var1 = brenda
var2 = carmen
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Internet Services

Web Development

Digital Marketing

Consumer Tech