[sqlplus]spool et saut de ligne [Résolu]

[maxvador]

Bonjour,

Mon probleme n'est pas tres simple à expliquer. Avec un script sql, je génère un autre script sql via un spool. C'est ce nouveau fichier qui contient la requete récuperant les infos dont j'ai besoin.

Voici mon 1er script:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SET echo off ver off feed off pages 0
--set linesize 200
SET termout off
SET feedback off
SET trimout off 
SET heading off 
SET sqlprompt ' '
SET verify off
SET space 0 
SET NEWPAGE none
SET PAGESIZE 0 
SET trimspool ON
spool &2
 
SELECT 'set echo off ver off feed off pages 0 pagesize 0 termout off feedback off
spool &3 ' FROM dual union ALL
SELECT 'select ' FROM dual union ALL
SELECT * FROM (SELECT decode(column_id,maxid,column_name,column_name||'|| ''|'' ||') FROM user_tab_columns a,(SELECT max(column_id) maxid,table_name  FROM user_tab_columns WHERE tabl
e_name='PRRT' GROUP BY table_name) b WHERE a.table_name =b.table_name ORDER BY column_id) union ALL
SELECT ' from PRRT where prrt_dateacnt=&1 and prrt_stus=7
and prrt_methpaym IN (4, 20, 21, 22)  ' FROM dual union ALL SELECT 'union ' FROM dual union ALL
SELECT 'select ' FROM dual union ALL
SELECT * FROM (SELECT decode(column_id,maxid,column_name,column_name||'|| ''|'' ||') FROM user_tab_columns a,(SELECT max(column_id) maxid,table_name  FROM user_tab_columns WHERE tabl
e_name='PRRT' GROUP BY table_name) b WHERE a.table_name =b.table_name ORDER BY column_id) union ALL
SELECT ' from PRRT, APIS_PRRT_RPT where prrt_dateacnt=&1 and prrt_stus=7
and prrt_methpaym NOT IN (4, 20, 21, 22) and prrt_oprf=reference ;' FROM dual;
SELECT ' spool off
exit; ' FROM dual;
spool off
exit;

En sortie j'obtiens un truc de ce genre:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
SELECT 
a || '|' ||
b|| '|' || 
.....
c|| '|' || 
d
WHERE prrt_dateacnt=20070525 AND prrt_stus=7
AND prrt_methpaym IN (4, 20, 21, 22)
 
union
SELECT 
a || '|' ||
b|| '|' || 
.....
c|| '|' || 
d
FROM PRRT, APIS_PRRT_RPT WHERE prrt_dateacnt=20070525  AND prrt_stus=7
AND prrt_methpaym NOT IN (4, 20, 21, 22) AND prrt_oprf=reference ;
spool off
exit;

Voila le message d'erreur que j'obtiens:

Code :

SP2-0042: unknown command "union" - rest of line ignored.

Si je supprime le saut de ligne à la main entre la fin de la 1ere requete et le "union" ca fonctionne.
Donc ma question est Comment supprimer ce saut de ligne et pourquoi est ce qu'il est là?

Merci

[laurentschneider]

set linesize 30000
set trimspool on

[maxvador]

J'ai essayé mais ca fait toujours pareil.

[laurentschneider]

ok, j'ai vu, évite de mettre des retours chariots dans ton litteral.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
SQL>  SELECT ' from PRRT where prrt_dateacnt=1 and prrt_stus=7
  2   and prrt_methpaym IN (4, 20, 21, 22)  ' FROM dual
  3   union ALL SELECT 'union ' FROM dual;
'FROMPRRTWHEREPRRT_DATEACNT=1ANDPRRT_STUS=7ANDPRRT_METHPAYMIN(4,20,21,22)'
----------------------------------------------------------------------------------------
 FROM PRRT WHERE prrt_dateacnt=1 AND prrt_stus=7
 AND prrt_methpaym IN (4, 20, 21, 22)
 
union
 
SQL>
SQL>
SQL>  SELECT ' from PRRT where prrt_dateacnt=1 and prrt_stus=7 and prrt_methpaym IN (4, 20, 21, 22)     ' FROM dual
  2   union ALL SELECT 'union ' FROM dual;
'FROMPRRTWHEREPRRT_DATEACNT=1ANDPRRT_STUS=7ANDPRRT_METHPAYMIN(4,20,21,22)'
---------------------------------------------------------------------------------------
 FROM PRRT WHERE prrt_dateacnt=1 AND prrt_stus=7 AND prrt_methpaym IN (4, 20, 21, 22)
union

[maxvador]

Cool merci, en mettant tout sur une meme ligne ca fonctionne. J'aurai pu chercher longtemps avant de trouver. Il est capricieux le sqlplus