Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    5

    Unanswered: perl and SQLPLUS

    I wonder if anyone can help me solve a scripting problem I have passing parameters from a perl script to a sql file executed by SQLPLUS.

    My perl statement executes the SQLPLUS passing a variable with the value of a filename that has spaces in....

    $SQL = "$SQL_Dir$SQLFile_Name";
    $SQLResultsFile = "\"$SQLResultsFile\"";
    $SQLParm = $SQLResultsFile." ".$Another_Value; # is a file name in a location with spaces in the path i.e. "C:\directory a\filename.txt"
    $SQLCmd = "sqlplus -S $Oracle_ID/$Oracle_PW \@$SQL $SQLParm"

    system $SQLCmd;

    the SQL script executed looks like...

    set linesize 120
    set head off
    set pagesize 0
    set feedback off
    set trimspool on
    set ver off
    set escape \

    spool \'&&1\'

    ...<SQL statement including &&2>

    spool off

    exit;


    The problem is that SQLPLUS objects to the spaces in the filename and cannot create it. It subsequently also incorrectly assigns the second parameter value. Does anyone know how I script this so that it is passed correctly from perl to SQLPLUS and the value resolved in the SQLPLUS session?

    Any help now greatly appreciated.

    Kind Regards

    Ian

  2. #2
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    It's a matter of quotes. As you can see, file names with spaces are not a problem for SQL*Plus. In the first test case I do no put quotes around my connection string as you are doing. In the second case I do, but I put still put the call file in a separate set of quotes.

    TEST 1:

    C:\>sqlplus / as sysdba "@c:\temp\help me.txt"

    SQL*Plus: Release 10.1.0.3.0 - Production on Thu Feb 17 09:01:33 2005

    Copyright (c) 1982, 2004, Oracle. All rights reserved.


    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
    With the Partitioning, Oracle Label Security, OLAP and Data Mining options


    SYSDATE
    ---------
    17-FEB-05

    TEST 2:

    C:\>sqlplus "/ as sysdba" "@c:\temp\help me.txt"

    SQL*Plus: Release 10.1.0.3.0 - Production on Thu Feb 17 09:02:53 2005

    Copyright (c) 1982, 2004, Oracle. All rights reserved.


    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
    With the Partitioning, Oracle Label Security, OLAP and Data Mining options

    SYSDATE
    ---------
    17-FEB-05
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  3. #3
    Join Date
    Jan 2005
    Posts
    5

    Smile

    Thanks Mark. I have now manged to get my quoting right ! Thanks.

Posting Permissions

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