Hi to all.
I have client oracle 8i and i'm using pl sql developer.
I have the following plsql procedure:
set serveroutput on size 1000000;
DECLARE
type t_cur is ref cursor;
type type_rec is record (codicepool char(12),
codicefisico char(12),
givenname varchar2(12),
denominazione char(40),
tipoedificio varchar2(50),
tipoimpianto varchar2(40),
stationtypecaminus char(40),
codiceateco varchar2(8),
capacitatrasporto number(9),
capacitaconferita1 number(9), capacitaconferita2 number(9),capacitaconferita3 number(9),capacitaconferita4 number(9),
capacitaconferita5 number (9),capacitaconferita6 number(9),capacitaconferita7 number(9),capacitaconferita8 number(9),
capacitaconferita9 number (9),capacitaconferita10 number(9),capacitaconferita11 number(9),capacitaconferita12 number(9),
capacitaconferita13 number(9),capacitaconferita14 number(9),capacitaconferita15 number(9),capacitaconferita16 number(9),
capacitaconferita17 number(9),capacitaconferita18 number(9),capacitaconferita19 number(9),capacitaconferita20 number(9),
capacitaconferita21 number(9),capacitaconferita22 number(9),capacitaconferita23 number(9),capacitaconferita24 number(9),
capacitaconferita25 number(9),capacitaconferita26 number(9),capacitaconferita27 number(9),capacitaconferita28 number(9),
capacitaconferita29 number(9),capacitaconferita30 number(9),capacitaconferita31 number(9),
energia1 number(9),energia2 number(9),energia3 number(9),energia4 number(9),energia5 number(9),energia6 number(9),energia7 number(9),
energia8 number(9),energia9 number(9),energia10 number(9),energia11 number(9),energia12 number(9),energia13 number(9),energia14 number(9),
energia15 number(9),energia16 number(9),energia17 number(9),energia18 number(9),energia19 number(9),energia20 number(9),energia21 number(9),
energia22 number(9),energia23 number(9),energia24 number(9),energia25 number(9),energia26 number(9),energia27 number(9),energia28 number(9),
energia29 number(9),energia30 number(9),energia31 number(9),
volumeprofilato1 number(9),volumeprofilato2 number(9),volumeprofilato3 number(9),volumeprofilato4 number(9),volumeprofilato5 number(9),
volumeprofilato6 number(9),volumeprofilato7 number(9),volumeprofilato8 number(9),volumeprofilato9 number(9),
volumeprofilato10 number(9),
volumeprofilato11 number(9),volumeprofilato12 number(9),volumeprofilato13 number(9),volumeprofilato14 number(9),volumeprofilato15 number(9),
volumeprofilato16 number(9),volumeprofilato17 number(9),volumeprofilato18 number(9),volumeprofilato19 number(9),volumeprofilato20 number(9),
volumeprofilato21 number(9),volumeprofilato22 number(9),volumeprofilato23 number(9),volumeprofilato24 number(9),volumeprofilato25 number(9),
volumeprofilato26 number(9),volumeprofilato27 number(9),volumeprofilato28 number(9),volumeprofilato29 number(9),volumeprofilato30 number(9),
volumeprofilato31 number(9),tipo_misura char(20));
cur t_cur;
bilancio_rec type_rec;
f utl_file.file_type;
begin
f := utl_file.fopen('SAMPLEDATA','prova_bilancio.txt','W');
OPEN cur for
SELECT q.codicepool, q.codicefisico, q.denominazione, q.tipoedificio,
q.tipoimpianto, q.stationtypecaminus, q.codiceateco,
q.capacitatrasporto, c.cc1 AS capacitaconferita1,
c.cc2 AS capacitaconferita2, c.cc3 AS capacitaconferita3,
c.cc4 AS capacitaconferita4, c.cc5 AS capacitaconferita5,
c.cc6 AS capacitaconferita6, c.cc7 AS capacitaconferita7,
c.cc8 AS capacitaconferita8, c.cc9 AS capacitaconferita9,
c.cc10 AS capacitaconferita10, c.cc11 AS capacitaconferita11,
c.cc12 AS capacitaconferita12, c.cc13 AS capacitaconferita13,
c.cc14 AS capacitaconferita14, c.cc15 AS capacitaconferita15,
c.cc16 AS capacitaconferita16, c.cc17 AS capacitaconferita17,
c.cc18 AS capacitaconferita18, c.cc19 AS capacitaconferita19,
c.cc20 AS capacitaconferita20, c.cc21 AS capacitaconferita21,
c.cc22 AS capacitaconferita22, c.cc23 AS capacitaconferita23,
c.cc24 AS capacitaconferita24, c.cc25 AS capacitaconferita25,
c.cc26 AS capacitaconferita26, c.cc27 AS capacitaconferita27,
c.cc28 AS capacitaconferita28, c.cc29 AS capacitaconferita29,
c.cc30 AS capacitaconferita30, c.cc31 AS capacitaconferita31,
v.dk1 / 10 AS energia1, v.dk2 / 10 AS energia2, v.dk3 / 10 AS energia3,
v.dk4 / 10 AS energia4, v.dk5 / 10 AS energia5, v.dk6 / 10 AS energia6,
v.dk7 / 10 AS energia7, v.dk8 / 10 AS energia8, v.dk9 / 10 AS energia9,
v.dk10 / 10 AS energia10, v.dk11 / 10 AS energia11,
v.dk12 / 10 AS energia12, v.dk13 / 10 AS energia13,
v.dk14 / 10 AS energia14, v.dk15 / 10 AS energia15,
v.dk16 / 10 AS energia16, v.dk17 / 10 AS energia17,
v.dk18 / 10 AS energia18, v.dk19 / 10 AS energia19,
v.dk20 / 10 AS energia20, v.dk21 / 10 AS energia21,
v.dk22 / 10 AS energia22, v.dk23 / 10 AS energia23,
v.dk24 / 10 AS energia24, v.dk25 / 10 AS energia25,
v.dk26 / 10 AS energia26, v.dk27 / 10 AS energia27,
v.dk28 / 10 AS energia28, v.dk29 / 10 AS energia29,
v.dk30 / 10 AS energia30, v.dk31 / 10 AS energia31,
v.vol1 AS volumeprofilato1, v.vol2 AS volumeprofilato2,
v.vol3 AS volumeprofilato3, v.vol4 AS volumeprofilato4,
v.vol5 AS volumeprofilato5, v.vol6 AS volumeprofilato6,
v.vol7 AS volumeprofilato7, v.vol8 AS volumeprofilato8,
v.vol9 AS volumeprofilato9, v.vol10 AS volumeprofilato10,
v.vol11 AS volumeprofilato11, v.vol12 AS volumeprofilato12,
v.vol13 AS volumeprofilato13, v.vol14 AS volumeprofilato14,
v.vol15 AS volumeprofilato15, v.vol16 AS volumeprofilato16,
v.vol17 AS volumeprofilato17, v.vol18 AS volumeprofilato18,
v.vol19 AS volumeprofilato19, v.vol20 AS volumeprofilato20,
v.vol21 AS volumeprofilato21, v.vol22 AS volumeprofilato22,
v.vol23 AS volumeprofilato23, v.vol24 AS volumeprofilato24,
v.vol25 AS volumeprofilato25, v.vol26 AS volumeprofilato26,
v.vol27 AS volumeprofilato27, v.vol28 AS volumeprofilato28,
v.vol29 AS volumeprofilato29, v.vol30 AS volumeprofilato30,
v.vol31 AS volumeprofilato31, q.tipo_misura
FROM volume v,
+(SELECT ss.pipeline AS pipeline, ss.prodyear AS prodyear,+
ss.prodmonth AS prodmonth, ss.groupid AS codicepool,
+/****LINE 98 IS THIS***/ ss.meter AS codicefisico, ss.description AS denominazione,+
DECODE (sra.tipoedificio,
NULL, NULL,
+/****LINE 101 IS THIS***/ (SELECT descrizione+
FROM sap_tipi_edificio
WHERE tipoedificio = sra.tipoedificio)
+/****LINE 104 IS THIS***/ ) AS tipoedificio,+
DECODE (sra.tipoimpianto,
NULL, NULL,
+/****LINE 107 IS THIS***/ (SELECT descrizione+
FROM sap_tipi_impianto
WHERE tipoimpianto = sra.tipoimpianto)
+) AS tipoimpianto,+
st.description AS stationtypecaminus,
sra.tranpzona AS codiceateco,
ss.maxvolume AS capacitatrasporto,
DECODE (ss.stationtype,
+'ED', 'MG',+
+'EN', 'NMG',+
+'Non pervenuto'+
+) AS tipo_misura+
--SS.stationtype as tipo_misura+
FROM station_storico ss, sap_remiattributi sra, stationtype st
WHERE ss.prodyear = 2008
AND ss.prodmonth = 2
AND ( ss.prodyear = sra.prodyear()+
AND ss.prodmonth = sra.prodmonth()+
AND ss.pipeline = RPAD (sra.pipeline(), 12)+
AND ss.meter = RPAD (sra.remi(), 12)+
+) --Other Join tra Station Storico e Sap_remiattributi+
AND (st.stationtype = ss.stationtype
+) -- join tra Station_storico e stationtype+
+) q,+
+(+
--AAAAA+
+(SELECT pipeline, prodyear, prodmonth, delmeter AS meter,+
SUM (dk1) AS cc1, SUM (dk2) AS cc2, SUM (dk3) AS cc3,
SUM (dk4) AS cc4, SUM (dk5) AS cc5, SUM (dk6) AS cc6,
SUM (dk7) AS cc7, SUM (dk8) AS cc8, SUM (dk9) AS cc9,
SUM (dk10) AS cc10, SUM (dk11) AS cc11, SUM (dk12) AS cc12,
SUM (dk13) AS cc13, SUM (dk14) AS cc14, SUM (dk15) AS cc15,
SUM (dk16) AS cc16, SUM (dk17) AS cc17, SUM (dk18) AS cc18,
SUM (dk19) AS cc19, SUM (dk20) AS cc20, SUM (dk21) AS cc21,
SUM (dk22) AS cc22, SUM (dk23) AS cc23, SUM (dk24) AS cc24,
SUM (dk25) AS cc25, SUM (dk26) AS cc26, SUM (dk27) AS cc27,
SUM (dk28) AS cc28, SUM (dk29) AS cc29, SUM (dk30) AS cc30,
SUM (dk31) AS cc31
FROM statcont
WHERE prodyear = 2008
AND prodmonth = 2
AND pipeline IN
+((RPAD ('RN', 12)),+
+(RPAD ('RR=A', 12)),+
+(RPAD ('RR=B', 12)),+
+(RPAD ('RR=C', 12))+
+)+
GROUP BY pipeline, prodyear, prodmonth, delmeter
UNION
SELECT pipeline, prodyear, prodmonth, recmeter AS meter,
SUM (dk1) AS cc1, SUM (dk2) AS cc2, SUM (dk3) AS cc3,
SUM (dk4) AS cc4, SUM (dk5) AS cc5, SUM (dk6) AS cc6,
SUM (dk7) AS cc7, SUM (dk8) AS cc8, SUM (dk9) AS cc9,
SUM (dk10) AS cc10, SUM (dk11) AS cc11, SUM (dk12) AS cc12,
SUM (dk13) AS cc13, SUM (dk14) AS cc14, SUM (dk15) AS cc15,
SUM (dk16) AS cc16, SUM (dk17) AS cc17, SUM (dk18) AS cc18,
SUM (dk19) AS cc19, SUM (dk20) AS cc20, SUM (dk21) AS cc21,
SUM (dk22) AS cc22, SUM (dk23) AS cc23, SUM (dk24) AS cc24,
SUM (dk25) AS cc25, SUM (dk26) AS cc26, SUM (dk27) AS cc27,
SUM (dk28) AS cc28, SUM (dk29) AS cc29, SUM (dk30) AS cc30,
SUM (dk31) AS cc31
FROM statcont
WHERE prodyear = 2008
AND prodmonth = 2
AND pipeline IN
+((RPAD ('RN', 12)),+
+(RPAD ('RR=A', 12)),+
+(RPAD ('RR=B', 12)),+
+(RPAD ('RR=C', 12))+
+)+
GROUP BY pipeline, prodyear, prodmonth, recmeter)
UNION
+(SELECT pipeline, prodyear, prodmonth, delmeter AS meter,+
SUM (dk1) AS cc1, SUM (dk2) AS cc2, SUM (dk3) AS cc3,
SUM (dk4) AS cc4, SUM (dk5) AS cc5, SUM (dk6) AS cc6,
SUM (dk7) AS cc7, SUM (dk8) AS cc8, SUM (dk9) AS cc9,
SUM (dk10) AS cc10, SUM (dk11) AS cc11, SUM (dk12) AS cc12,
SUM (dk13) AS cc13, SUM (dk14) AS cc14, SUM (dk15) AS cc15,
SUM (dk16) AS cc16, SUM (dk17) AS cc17, SUM (dk18) AS cc18,
SUM (dk19) AS cc19, SUM (dk20) AS cc20, SUM (dk21) AS cc21,
SUM (dk22) AS cc22, SUM (dk23) AS cc23, SUM (dk24) AS cc24,
SUM (dk25) AS cc25, SUM (dk26) AS cc26, SUM (dk27) AS cc27,
SUM (dk28) AS cc28, SUM (dk29) AS cc29, SUM (dk30) AS cc30,
SUM (dk31) AS cc31
FROM statcont
WHERE prodyear = 2008
AND prodmonth = 2
AND pipeline IN
+((RPAD ('RR=D', 12)),+
+(RPAD ('RR=E1', 12)),+
+(RPAD ('RR=E2', 12)),+
+(RPAD ('RR=F', 12)),+
+(RPAD ('RR=G', 12)),+
+(RPAD ('RR=H', 12))+
+)+
GROUP BY pipeline, prodyear, prodmonth, delmeter
UNION
SELECT pipeline, prodyear, prodmonth, recmeter AS meter,
SUM (dk1) AS cc1, SUM (dk2) AS cc2, SUM (dk3) AS cc3,
SUM (dk4) AS cc4, SUM (dk5) AS cc5, SUM (dk6) AS cc6,
SUM (dk7) AS cc7, SUM (dk8) AS cc8, SUM (dk9) AS cc9,
SUM (dk10) AS cc10, SUM (dk11) AS cc11, SUM (dk12) AS cc12,
SUM (dk13) AS cc13, SUM (dk14) AS cc14, SUM (dk15) AS cc15,
SUM (dk16) AS cc16, SUM (dk17) AS cc17, SUM (dk18) AS cc18,
SUM (dk19) AS cc19, SUM (dk20) AS cc20, SUM (dk21) AS cc21,
SUM (dk22) AS cc22, SUM (dk23) AS cc23, SUM (dk24) AS cc24,
SUM (dk25) AS cc25, SUM (dk26) AS cc26, SUM (dk27) AS cc27,
SUM (dk28) AS cc28, SUM (dk29) AS cc29, SUM (dk30) AS cc30,
SUM (dk31) AS cc31
FROM statcont
WHERE prodyear = 2008
AND prodmonth = 2
AND pipeline IN
+((RPAD ('RR=D', 12)),+
+(RPAD ('RR=E1', 12)),+
+(RPAD ('RR=E2', 12)),+
+(RPAD ('RR=F', 12)),+
+(RPAD ('RR=G', 12)),+
+(RPAD ('RR=H', 12))+
+)+
GROUP BY pipeline, prodyear, prodmonth, recmeter)
UNION
+(SELECT pipeline, prodyear, prodmonth, delmeter AS meter,+
SUM (dk1) AS cc1, SUM (dk2) AS cc2, SUM (dk3) AS cc3,
SUM (dk4) AS cc4, SUM (dk5) AS cc5, SUM (dk6) AS cc6,
SUM (dk7) AS cc7, SUM (dk8) AS cc8, SUM (dk9) AS cc9,
SUM (dk10) AS cc10, SUM (dk11) AS cc11, SUM (dk12) AS cc12,
SUM (dk13) AS cc13, SUM (dk14) AS cc14, SUM (dk15) AS cc15,
SUM (dk16) AS cc16, SUM (dk17) AS cc17, SUM (dk18) AS cc18,
SUM (dk19) AS cc19, SUM (dk20) AS cc20, SUM (dk21) AS cc21,
SUM (dk22) AS cc22, SUM (dk23) AS cc23, SUM (dk24) AS cc24,
SUM (dk25) AS cc25, SUM (dk26) AS cc26, SUM (dk27) AS cc27,
SUM (dk28) AS cc28, SUM (dk29) AS cc29, SUM (dk30) AS cc30,
SUM (dk31) AS cc31
FROM statcont
WHERE prodyear = 2008
AND prodmonth = 2
AND pipeline IN
+((RPAD ('RR=I', 12)),+
+(RPAD ('RR=L', 12)),+
+(RPAD ('RR=M', 12)),+
+(RPAD ('RR=N', 12)),+
+(RPAD ('RR=O', 12)),+
+(RPAD ('RR=P', 12)),+
+(RPAD ('RR=Q', 12)),+
+(RPAD ('RR=R', 12))+
+)+
GROUP BY pipeline, prodyear, prodmonth, delmeter
UNION
SELECT pipeline, prodyear, prodmonth, recmeter AS meter,
SUM (dk1) AS cc1, SUM (dk2) AS cc2, SUM (dk3) AS cc3,
SUM (dk4) AS cc4, SUM (dk5) AS cc5, SUM (dk6) AS cc6,
SUM (dk7) AS cc7, SUM (dk8) AS cc8, SUM (dk9) AS cc9,
SUM (dk10) AS cc10, SUM (dk11) AS cc11, SUM (dk12) AS cc12,
SUM (dk13) AS cc13, SUM (dk14) AS cc14, SUM (dk15) AS cc15,
SUM (dk16) AS cc16, SUM (dk17) AS cc17, SUM (dk18) AS cc18,
SUM (dk19) AS cc19, SUM (dk20) AS cc20, SUM (dk21) AS cc21,
SUM (dk22) AS cc22, SUM (dk23) AS cc23, SUM (dk24) AS cc24,
SUM (dk25) AS cc25, SUM (dk26) AS cc26, SUM (dk27) AS cc27,
SUM (dk28) AS cc28, SUM (dk29) AS cc29, SUM (dk30) AS cc30,
SUM (dk31) AS cc31
FROM statcont
WHERE prodyear = 2008
AND prodmonth = 2
AND pipeline IN
+((RPAD ('RR=I', 12)),+
+(RPAD ('RR=L', 12)),+
+(RPAD ('RR=M', 12)),+
+(RPAD ('RR=N', 12)),+
+(RPAD ('RR=O', 12)),+
+(RPAD ('RR=P', 12)),+
+(RPAD ('RR=Q', 12)),+
+(RPAD ('RR=R', 12))+
+)+
GROUP BY pipeline, prodyear, prodmonth, recmeter)
--AAAA+
+) c+
WHERE v.pipeline = q.pipeline
AND v.meter = q.codicefisico
AND v.prodyear = q.prodyear
AND v.prodmonth = q.prodmonth
AND ( c.pipeline() = v.pipeline+
AND c.meter() = v.meter+
AND c.prodyear() = v.prodyear+
AND c.prodmonth() = v.prodmonth+
+);+
loop
fetch cur into bilancio_def_entry;
EXIT WHEN cur%notfound;
utl_file.put_line(f, bilancio.codicepool);
end loop;
utl_file.fclose(f);
CLOSE cur;
END;
+/+
Executing it I have:
ORA-06550: line 98, column 25:
Please-00103: Encountered symbol "SELECT" anziché uno dei seguenti:
+( - ++
mod not null others <an identifier>
+<a double-quoted delimited-identifier> <a bind variable> avg+
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
+<a string literal with character set specification>+
+<a number> <a single-quoted SQL string>+
ORA-06550: riga 101, column 23:
Please-00103: Encountered symbol ")" anziché uno dei seguenti:
+; return+
returning and or
ORA-06550: line 104, colonna 25:
Please-00103: Trovato il simbolo "SELECT" anziché uno dei seguenti:
+( - ++
mod not null others <an identifier>
+<a double-quoted delimited-identifier> <a bind variable> avg+
count current exists max min prior sql stddev sum variance
ORA-06550: line 107, column 23:
Please-00103: Encountered symbol ")" anziché uno dei seguenti:
+; return+
returning and or
When i execute the query select from an sql window (not in pl sql procedure) i have no problem.
Any idea?
Please help me to come out from th problem.
Thanks