Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Feb 2010
    Posts
    12

    Unanswered: newbi: struggling with syntax

    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;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >/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
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2010
    Posts
    12
    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).

  4. #4
    Join Date
    Feb 2010
    Posts
    12
    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).

  5. #5
    Join Date
    Feb 2010
    Posts
    12
    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).

  6. #6
    Join Date
    Feb 2010
    Posts
    12
    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.

  7. #7
    Join Date
    Feb 2010
    Posts
    12
    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.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by davisb View Post
    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.

  9. #9
    Join Date
    Feb 2010
    Posts
    12
    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.

  10. #10
    Join Date
    Feb 2010
    Posts
    12
    "/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/"

  11. #11
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I don't get it:
    Quote Originally Posted by davisb View Post
    i get an output file on "/afs/uis.njit.edu/erp/user/finsvc/banner/dev/sql/x.prt"
    vs.

    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

  12. #12
    Join Date
    Feb 2010
    Posts
    12
    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!

  13. #13
    Join Date
    Feb 2010
    Posts
    12
    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"

  14. #14
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by davisb View Post
    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

  15. #15
    Join Date
    Feb 2010
    Posts
    12
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •