Execute SQL+ request and generate a csv file

[mohamed_ahid]

HI all ,

i have the bellow code which works before on my application .Today i was surprised to know that the task has failed because this peace of script is not generating any csv file any more .

Any idea ?

SET cnx_Oracle_Tns = 'databse';

SET cnx_Oracle_User = 'user';

SET cnx_Oracle_Pass = 'user';

SET cnx_Oracle_Id = 'test';

SET cnx_Oracle_Requete = '$(v_Path_Exporter)request.sql+';

SET cnx_Oracle_Log = '$(v_Path_Exporter)request.log';

LET cnx_Oracle_FileExport = '$(today_date)' & '_test' & '.csv';

EXECUTE cmd.exe /C sqlplus $(cnx_Oracle_User)/$(cnx_Oracle_Pass)@$(cnx_Oracle_Tns) @$(cnx_Oracle_Requete) "$(v_Path_Source)$(cnx_Oracle_FileExport)" > "$(cnx_Oracle_Log)";

[Clever_Anjos]

I would check

• Can user/password connect to database?
• Does this users have GRANT of Select into your query (request.sql)
• What´s the content of  cnx_Oracle_Log?

[mohamed_ahid]

thx for responding .

• for the log file cnx_Oracle_Log i have inside :

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 1 14:05:55 2015

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning and Real Application Testing options

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning and Real Application Testing options

• the user can connect to the databse .

• for the third point i'll check that .

[mohamed_ahid]

everything works great when i copy the sql script and executing it directly on QV .

still not founding what is the issue .

[sinanozdemir]

Are you using ODBC or OLE DB connection? I see this happen with OLE DB connections.

[mohamed_ahid]

how can i check that ?

[sinanozdemir]

First look at the script to see if it is using the below syntax:

[mohamed_ahid]

there is nothing like this in my script .

my script posted before call a file called request.sql+ and inside it i have :

SET echo off;

SET feedback off;

SET heading off;

SET lines 10000;

SET pagesize 50000;

SET termout off;

SET trims on;

SET trimspool on;

SET underline off;

SET verify off;

SPOOL &1

SELECT /*+ PARALLEL(4) */

  LOGIN

  ||'|'|| password

  ||'|'|| data1

  ||'|'|| data2

FROM

  stagt.ord_ftd_pas

WHERE

  TYPE_OFFRE = 'data'

  AND

  LOGIN is not null

;

SPOOL off;

/

EXIT SQL.SQLCODE

[sinanozdemir]

Sorry, I am not a SQLPlus person, but I Looked at some examples online regarding your issue and here are some possible reasons:

• Empty lines in the sql code. SQLPlus interprets empty lines as "stop previous statement and start a new one"
• Another solution is to clear the cache and re-run the cmd.exe command

Sorry again. SQLPlus is not my forte, in fact none of the Oracle RDBMS's.

[mohamed_ahid]

thx .

Finally the issue is caused by the space in my relatif path in variable '$(v_Path_Exporter) .

when i changed it it works .

is there any way to keep spaces in my path with sql+.

thx all