topic Re: Execute SQL+ request and generate a csv file in QlikView

Execute SQL+ request and generate a csv file

mohamed_ahid — Wed, 01 Jul 2015 11:53:24 GMT

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)";

Re: Execute SQL+ request and generate a csv file

Clever_Anjos — Wed, 01 Jul 2015 11:57:39 GMT

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?

Re: Execute SQL+ request and generate a csv file

mohamed_ahid — Wed, 01 Jul 2015 12:08:32 GMT

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

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 .

Re: Execute SQL+ request and generate a csv file

mohamed_ahid — Wed, 01 Jul 2015 12:24:44 GMT

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

still not founding what is the issue .

Re: Execute SQL+ request and generate a csv file

sinanozdemir — Wed, 01 Jul 2015 12:44:49 GMT

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

Re: Execute SQL+ request and generate a csv file

mohamed_ahid — Wed, 01 Jul 2015 13:00:26 GMT

how can i check that ?

Re: Execute SQL+ request and generate a csv file

sinanozdemir — Wed, 01 Jul 2015 13:05:46 GMT

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

Re: Execute SQL+ request and generate a csv file

mohamed_ahid — Wed, 01 Jul 2015 13:18:47 GMT

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) */

||'|'|| password

||'|'|| data1

||'|'|| data2

FROM

stagt.ord_ftd_pas

WHERE

TYPE_OFFRE = 'data'

AND

;

SPOOL off;

EXIT SQL.SQLCODE

Re: Execute SQL+ request and generate a csv file

sinanozdemir — Wed, 01 Jul 2015 16:35:48 GMT

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.

Re: Execute SQL+ request and generate a csv file

mohamed_ahid — Thu, 02 Jul 2015 10:11:17 GMT

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