CODEWITHSUNDEEP
batch-filesqlplus
Divakar Ragupathy
Divakar Ragupathy

Reputation: 53

How to pass path in double quotes to sql file

I want to pass the current batch file path to sql file which will be called by sqlplus and spool the result into that path. My code is below. Its failing at spool line as the path is not passed with double quotes. Can you please help me

start sqlplus %DBUName%/%DBPwd%@%GISName% "@%~dp0SQL\SetUp\SETUP.sql" "%~dp0Config\SETUP.ok"

In the set up sql, I have given the below line

SPOOL &1

Upvotes: 1

Views: 1253

Answers (2)

Endoro
Endoro

Reputation: 37579

I'm not sure, what you need. Do you need more quotes or no quotes. Whatsoever- you can have as many quotes as you want. I do not have sqlplus, but it's not so important. I can show you this with 'calc.exe`:

enter image description here

And I think, this is not a cmd/Batch issue. It belongs to your sqlplus.

@ECHO OFF &SETLOCAL
title Test of Quotes
set dbuname=dbuname
set DBPwd=DBPwd
set gisname=gisname

start calc %dbuname%/%DBPwd%@%gisname% @%~dp0SQL\SetUp\SETUP.sql %~dp0Config\SETUP.ok
start calc %dbuname%/%DBPwd%@%gisname% @%~dp0SQL\SetUp\SETUP.sql "%~dp0Config\SETUP.ok"
start calc %dbuname%/%DBPwd%@%gisname% "@%~dp0SQL\SetUp\SETUP.sql" %~dp0Config\SETUP.ok
start calc %dbuname%/%DBPwd%@%gisname% "@%~dp0SQL\SetUp\SETUP.sql" "%~dp0Config\SETUP.ok"
start calc %dbuname%/%DBPwd%@%gisname% "@%~dp0SQL\SetUp\SETUP.sql" ""%~dp0Config\SETUP.ok""
start calc %dbuname%/%DBPwd%@%gisname% ""@%~dp0SQL\SetUp\SETUP.sql"" ""%~dp0Config\SETUP.ok""
start calc %dbuname%/%DBPwd%@%gisname% """@%~dp0SQL\SetUp\SETUP.sql""" ""%~dp0Config\SETUP.ok""
start calc %dbuname%/%DBPwd%@%gisname% """@%~dp0SQL\SetUp\SETUP.sql""" """%~dp0Config\SETUP.ok"""

ping -n 3 localhost > nul

wmic process where name='calc.exe' get commandline

Upvotes: 0

Magoo
Magoo

Reputation: 80113

Quick guess:

start "" sqlplus....

Well now! There was an interesting exercise.

start "" sqlplus %DBUName%/%DBPwd%@%GISName% "@%~dp0SQL\SetUp\SETUP.sql" "%~dp0Config\SETUP.ok"

Is a standard solution to the "quoted arguments" problem with start. The first "quoted string" is assigned as the window's title - that is what the normal complaint is. Inserting the apparently-redundant empty string provides no title to the window - it's actually the simplest form of "this is the window title I want" - and a lot easier to type.

I didn't have time to test this in any way, which is why I suggested it as a guess. I tried with a batch file called sqlplus.bat which simply displayed its arguments:

@echo off
setlocal
echo @~nx0 received ==^>%*^<==
pause
exit

But this seemed to deliver all of the arguments as expected.

So since sqlplus is an executable, I thought I'd install it and try. Er - 33Mb min, and all the legalese sign-on and passwords and yet another account I'm only ever going to use once...Na.

So I built my own sqlplus.exe in Delphi - simply evaluating the command-line arguments. Thirty seconds' work.

Problem was that there was no way I could get the executable to show quotes in the parameters using the function that purports to return arguments by position. So THAT I had to investigate. I discovered a function to return the entire command-line as supplied - and there were the quotes, large as life.

Turns out there's a Delphi bug where paramstr(x) removes enclosing quotes from the value returned for argument x. Reported that to EMBT.

And re-tested, using the executable I'd constructed - displaying the command-line itself (cmdline if you're interested) and - well, bad news in the context of this problem - my tests show that

start sqlplus %DBUName%/%DBPwd%@%GISName% "@%~dp0SQL\SetUp\SETUP.sql" "%~dp0Config\SETUP.ok"

and

start "" sqlplus %DBUName%/%DBPwd%@%GISName% "@%~dp0SQL\SetUp\SETUP.sql" "%~dp0Config\SETUP.ok"

both deliver the same command-line - quotes included as written.

So - it would appear that the problem is with sqlplus or the instruction(s) sqlplus executes. The quoted strings appear to be delivered to the executable just fine.

Suppose OP executes

sqlplus %DBUName%/%DBPwd%@%GISName% "@%~dp0SQL\SetUp\SETUP.sql" "%~dp0Config\SETUP.ok"

(after substituting the appropriate parameters) - does this show the quotes as expected? (that question is really an SQLPLUS question and off-topic for this tag. AND I've no idea about sqlplus.

Additional (Well, really a comment, but it doesn't fit the comment system too well...) I believe we're running into language or terminalogical problems...

Does

sqlplus %DBUName%/%DBPwd%@%GISName% "@%~dp0SQL\SetUp\SETUP.sql" "%~dp0Config\SETUP.ok"

when executed with exactly that syntax (but with %dbuName%, etc. substituted with the actual name) operate correctly when executed directly from the command prompt?

Upvotes: 2

Related Questions