Home » SQL & PL/SQL » SQL & PL/SQL » type array PLS-00103: Encountered
| type array PLS-00103: Encountered [message #19384] |
Wed, 13 March 2002 14:37  |
Rm69
Messages: 39
Registered: January 2002
|
Member |
|
|
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
begin declare end exception exit for goto if loop mod null
pragma raise return select update while <an ident
Create Or Replace Procedure Peedee
(V_Start_Date Date,v_End_Date Date)
Is
Begin
Declare
Type Array Is Table Of Varchar2(30);
l_columns array := array('da1386','da2386','da3386','da4386',
'da5386','da6386','da7386','da8386','da9386','da0386');
begin
for i in 1 .. l_columns.count
loop
execute immediate
'INSERT INTO step2
(DEA386,BRN386,BRO386,SBR386,LOF386,CAPITAL,INSTITUITION,
AMOUNT,LOAN_COUNT,PDINCL,summary_date,change_date)
SELECT DEA386,BRN386,BRO386,SBR386,LOF386,CAPITAL,Institution,
SUM (' || l_columns(i) || '),
COUNT (' || l_columns(i) || '),pdincl, SYSDATE, :v_start_date
FROM step1
WHERE bro386 NOT IN (''SBSA'', ''KIOSKS'', ''HMC'', ''CREDIT'',
''ESP'', ''PSA'', ''AIN'', ''HOSPERSA'',
''REGIONAL'', ''COVERHOLD'', ''WACC'',
''NATU'', ''ACC CARE'', ''TAF BROK'', ''AB CC'')
AND lof386 NOT IN (''153'', ''1114'', ''97'', ''4843'', ''4849'',
''1157'', ''E0080'', ''J0196'')
AND (ah1386 LIKE ''%AFRICAN BANK%'' OR ah2386 LIKE ''%AFRICAN BANK%''
ah3386 LIKE ''%AFRICAN BANK%'' OR ah4386 LIKE ''%AFRICAN BANK%'' OR
ah5386 LIKE ''%AFRICAN BANK%'' OR ah6386 LIKE ''%AFRICAN BANK%'' OR
ah7386 LIKE ''%AFRICAN BANK%'' OR ah8386 LIKE ''%AFRICAN BANK%'' OR
ah9386 LIKE ''%AFRICAN BANK%'' OR ah0386 LIKE ''%AFRICAN BANK%'')
AND did386 BETWEEN :v_start_date AND :v_end_date
GROUP BY brn386'
using v_start_date, v_start_date, v_end_date;
end loop;
|
|
|
|
|
|
| Re: type array PLS-00103: Encountered -Still not working [message #19403 is a reply to message #19384] |
Thu, 14 March 2002 12:51  |
Ray
Messages: 58
Registered: January 2000
|
Member |
|
|
Sorry l had sent the wrong version of the procedure. l do have all the ends for both loops. This is the error l cant get past
The following error has occurred:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8
ORA-00907: missing right parenthesis
ORA-06512: at "SLEEZY.ASKTOM", line 18
ORA-06512: at line 9
CREATE OR REPLACE Procedure Asktom
(V_Start_Date Date,v_End_Date Date)
Is
v_cnt NUMBER;
Begin
---------------
-- BSPs 1-10
---------------
declare
type array is table of varchar2(30);
l_columns array := array('da1386','da2386','da3386','da4386',
'da5386','da6386','da7386','da8386','da9386','da0386');
begin
for i in 1 .. l_columns.count
loop
execute immediate
'INSERT INTO MIS_PERSAL_Tom
(branch,total,loan_count,pdincl,summary_date,change_date)
SELECT brn386, SUM (' || l_columns(i) || '),
COUNT (' || l_columns(i) || '),(capital*1.4),SYSDATE, :v_start_date
FROM PW38600P
WHERE bro386 NOT IN (''SBSA'', ''KIOSKS'', ''HMC'', ''CREDIT'',
''ESP'', ''PSA'', ''AIN'', ''HOSPERSA'',
''REGIONAL'', ''COVERHOLD'', ''WACC'',
''NATU'', ''ACC CARE'', ''TAF BROK'', ''AB CC'')
AND lof386 NOT IN (''153'', ''1114'', ''97'', ''4843'', ''4849'',
''1157'', ''E0080'', ''J0196'')
AND (ah1386 LIKE ''%AFRICAN BANK%'' OR ah2386 LIKE ''%AFRICAN BANK%''
ah3386 LIKE ''%AFRICAN BANK%'' OR ah4386 LIKE ''%AFRICAN BANK%'' OR
ah5386 LIKE ''%AFRICAN BANK%'' OR ah6386 LIKE ''%AFRICAN BANK%'' OR
ah7386 LIKE ''%AFRICAN BANK%'' OR ah8386 LIKE ''%AFRICAN BANK%'' OR
ah9386 LIKE ''%AFRICAN BANK%'' OR ah0386 LIKE ''%AFRICAN BANK%'')
AND did386 BETWEEN :v_start_date AND :v_end_date
GROUP BY brn386'
using v_start_date, v_start_date, v_end_date;
end loop;
----------------
-- HMCs 1-10
----------------
declare
type array is table of varchar2(30);
l_columns array := array('da1386','da2386','da3386','da4386',
'da5386','da6386','da7386','da8386','da9386','da0386');
begin
for i in 1 .. l_columns.count
loop
execute immediate
'INSERT INTO MIS_PERSAL_Tom
(branch,total,loan_count,pdincl,summary_date,change_date)
SELECT brn386, SUM (' || l_columns(i) || '),
COUNT (' || l_columns(i) || '),(capital*1.4),SYSDATE, :v_start_date
FROM PW38600P
WHERE bro386 = ''HMC''
AND(ah1386 LIKE ''%AFRICAN BANK%'' OR ah2386 LIKE ''%AFRICAN BANK%''
ah3386 LIKE ''%AFRICAN BANK%'' OR ah4386 LIKE ''%AFRICAN BANK%'' OR
ah5386 LIKE ''%AFRICAN BANK%'' OR ah6386 LIKE ''%AFRICAN BANK%'' OR
ah7386 LIKE ''%AFRICAN BANK%'' OR ah8386 LIKE ''%AFRICAN BANK%'' OR
ah9386 LIKE ''%AFRICAN BANK%'' OR ah0386 LIKE ''%AFRICAN BANK%'')
AND did386 BETWEEN v_start_date AND v_end_date
GROUP BY brn386, bro386'
using v_start_date, v_start_date, v_end_date;
end loop;
end;
end;
----------------------------------------------------------------
--- Check for Duplicates Before Inserting into History Table --
----------------------------------------------------------------
v_cnt := 0;
SELECT COUNT (*)
INTO v_cnt
FROM MIS_PERSAL_HIST
WHERE TO_DATE(v_start_date,'yyyy-mm-dd') =
TO_DATE(change_date,'yyyy-mm-dd')
AND TO_DATE(SYSDATE,'yyyy-mm-dd') =
TO_DATE(summary_date,'yyyy-mm-dd');
IF v_cnt = 0
THEN
INSERT INTO MIS_PERSAL_HIST
(branch, brocker, sbrockercd, total, loan_count, change_date,
summary_date)
SELECT branch, brocker, sbrockercd, total, loan_count, change_date,
summary_date
FROM MIS_PERSAL_Tom;
END IF;
COMMIT;
END;
|
|
|
|
| Re: type array PLS-00103: Encountered [message #20435 is a reply to message #19384] |
Thu, 23 May 2002 09:45 |
Negar
Messages: 2
Registered: May 2002
|
Junior Member |
|
|
create or replace package codas_citypair as
--extract selected data from raw file
procedure selected_data(v_airport in varchar2);
--map to selected city pairs
procedure selected_city(v_airport in varchar2);
--determine performance times
procedure performance_times(v_airport in varchar2);
--figure out 3rd std. Deviation
procedure std_deviation(v_airport in varchar2);
end codas_citypair;
/
create or replace package body codas_citypair as
--extract selected data from raw file
procedure selected_data(v_airport in varchar2) is
B varchar2(20);
cursor c1 is
select * from codas_0010 where Oag_s_g2g <> -1 and
Oag_s_g2g <> 0 and E_oag_g2g<> "B" and dep_locid=v_airport;
begin
i codas_0010%rowtype;
open c1;
loop
fetch c1 into i;
exit when c1%notfound;
end loop;
close c1;
dbms_output.put_line(i);
end selected_data;
--map to selected city pairs
procedure selected_city(v_airport in varchar2) is
cursor c2 is
Select * from codas_0010 where aptlist.airport = codas_0010.dep_locid and
aptlist.airport = codas_0010.arr_locid and dep_locid=v_airport;
begin
i codas_0010%rowtype;
open c2;
loop
fetch c2 into i;
exit when c2%notfound;
end loop;
close c2;
dbms_output.put_line(i);
end selected_city;
--determine performance times
procedure performance_times(v_airport in varchar2) is
cursor c3 is
Select dep_locid, arr_locid, count(oag_s_g2g) as cnt_oag_g2g,
min(oag_s_g2g) as min_oag_g2g, max(oag_s_g2g) as max_oag_g2g, avg(oag_s_g2g) as avg_oag_g2g,
stddev(oag_s_g2g) as std_oag_g2g, min(asqp_g2g) as min_asqp_g2g, max(asqp_g2g) as max_asqp_g2g,
avg(asqp_g2g) as avg_asqp_g2g, min(filed_ete) as min_filed_ete, max(filed_ete) as max_filed_ete,
avg(filed_ete) as avg_filed_ete, stddev(filed_ete) as std_filed_ete, min(plan_ete) as min_plan_ete,
max(plan_ete) as max_plan_ete, avg(plan_ete) as avg_plan_ete, min(airborne) as min_airborne, max(airborne) as max_airborne,
avg(airborne) as avg_airborne, stddev(airborne) as std_airborne, min(delay_air) as min_delay_air , max(delay_air) as max_delay_air,
avg(delay_air) as avg_delay_air, avg(gap_a) as avg_gap_a, avg(gap_d) as avg_gap_d from codas_0010 where dep_locid=v_airport
group by arr_locid, dep_locid;
begin
i codas_0010%rowtype;
open c3;
loop
fetch c3 into i;
exit when c3%notfound;
end loop;
close c3;
dbms_output.put_line(i);
end performance_times;
--figure out 3rd std. Deviation
procedure std_deviation(v_airport in varchar2) is
cursor c4 is
Select dep_locid,arr_locid, count(oag_s_g2g) as cnt_oag_g2g,
min(oag_s_g2g) as min_oag_g2g, max(oag_s_g2g) as max_oag_g2g, avg(oag_s_g2g) as avg_oag_g2g,
stddev(oag_s_g2g) as std_oag_g2g, min(asqp_g2g) as min_asqp_g2g, max(asqp_g2g) as max_asqp_g2g,
avg(asqp_g2g) as avg_asqp_g2g, min(filed_ete) as min_filed_ete, max(filed_ete) as max_filed_ete,
avg(filed_ete) as avg_filed_ete, stddev(filed_ete) as std_filed_ete, min(plan_ete) as min_plan_ete,
max(plan_ete) as max_plan_ete, avg(plan_ete) as avg_plan_ete, min(airborne) as min_airborne, max(airborne) as max_airborne,
avg(airborne) as avg_airborne, stddev(airborne) as std_airborne, min(delay_air) as min_delay_air , max(delay_air) as max_delay_air,
avg(delay_air) as avg_delay_air, avg(gap_a) as avg_gap_a, avg(gap_d) as avg_gap_d
from codas_0010
where ((std_filed_ete) <> 0) or ((nvl(std_filed_ete ,0)))
and ((avg_filed_ete)-(3*(std_filed_ete)) <= (oag_s_g2g))
and arr_locid=v_airport and dep_locid=v_airport
group by arr_locid, dep_locid;
begin
i codas_0010%rowtype;
open c4;
loop
fetch c4 into i;
exit when c4%notfound;
end loop;
close c4;
dbms_output.put_line(i);
end std_deviation;
end codas_citypair;
/
|
|
|
|
| Encountered PLS-00103: Encountered the symbol "CODAS_0010" when expecting one [message #20437 is a reply to message #19384] |
Thu, 23 May 2002 09:47 |
Negar
Messages: 2
Registered: May 2002
|
Junior Member |
|
|
create or replace package codas_citypair as
--extract selected data from raw file
procedure selected_data(v_airport in varchar2);
--map to selected city pairs
procedure selected_city(v_airport in varchar2);
--determine performance times
procedure performance_times(v_airport in varchar2);
--figure out 3rd std. Deviation
procedure std_deviation(v_airport in varchar2);
end codas_citypair;
/
create or replace package body codas_citypair as
--extract selected data from raw file
procedure selected_data(v_airport in varchar2) is
B varchar2(20);
cursor c1 is
select * from codas_0010 where Oag_s_g2g <> -1 and
Oag_s_g2g <> 0 and E_oag_g2g<> "B" and dep_locid=v_airport;
begin
i codas_0010%rowtype;
open c1;
loop
fetch c1 into i;
exit when c1%notfound;
end loop;
close c1;
dbms_output.put_line(i);
end selected_data;
--map to selected city pairs
procedure selected_city(v_airport in varchar2) is
cursor c2 is
Select * from codas_0010 where aptlist.airport = codas_0010.dep_locid and
aptlist.airport = codas_0010.arr_locid and dep_locid=v_airport;
begin
i codas_0010%rowtype;
open c2;
loop
fetch c2 into i;
exit when c2%notfound;
end loop;
close c2;
dbms_output.put_line(i);
end selected_city;
--determine performance times
procedure performance_times(v_airport in varchar2) is
cursor c3 is
Select dep_locid, arr_locid, count(oag_s_g2g) as cnt_oag_g2g,
min(oag_s_g2g) as min_oag_g2g, max(oag_s_g2g) as max_oag_g2g, avg(oag_s_g2g) as avg_oag_g2g,
stddev(oag_s_g2g) as std_oag_g2g, min(asqp_g2g) as min_asqp_g2g, max(asqp_g2g) as max_asqp_g2g,
avg(asqp_g2g) as avg_asqp_g2g, min(filed_ete) as min_filed_ete, max(filed_ete) as max_filed_ete,
avg(filed_ete) as avg_filed_ete, stddev(filed_ete) as std_filed_ete, min(plan_ete) as min_plan_ete,
max(plan_ete) as max_plan_ete, avg(plan_ete) as avg_plan_ete, min(airborne) as min_airborne, max(airborne) as max_airborne,
avg(airborne) as avg_airborne, stddev(airborne) as std_airborne, min(delay_air) as min_delay_air , max(delay_air) as max_delay_air,
avg(delay_air) as avg_delay_air, avg(gap_a) as avg_gap_a, avg(gap_d) as avg_gap_d from codas_0010 where dep_locid=v_airport
group by arr_locid, dep_locid;
begin
i codas_0010%rowtype;
open c3;
loop
fetch c3 into i;
exit when c3%notfound;
end loop;
close c3;
dbms_output.put_line(i);
end performance_times;
--figure out 3rd std. Deviation
procedure std_deviation(v_airport in varchar2) is
cursor c4 is
Select dep_locid,arr_locid, count(oag_s_g2g) as cnt_oag_g2g,
min(oag_s_g2g) as min_oag_g2g, max(oag_s_g2g) as max_oag_g2g, avg(oag_s_g2g) as avg_oag_g2g,
stddev(oag_s_g2g) as std_oag_g2g, min(asqp_g2g) as min_asqp_g2g, max(asqp_g2g) as max_asqp_g2g,
avg(asqp_g2g) as avg_asqp_g2g, min(filed_ete) as min_filed_ete, max(filed_ete) as max_filed_ete,
avg(filed_ete) as avg_filed_ete, stddev(filed_ete) as std_filed_ete, min(plan_ete) as min_plan_ete,
max(plan_ete) as max_plan_ete, avg(plan_ete) as avg_plan_ete, min(airborne) as min_airborne, max(airborne) as max_airborne,
avg(airborne) as avg_airborne, stddev(airborne) as std_airborne, min(delay_air) as min_delay_air , max(delay_air) as max_delay_air,
avg(delay_air) as avg_delay_air, avg(gap_a) as avg_gap_a, avg(gap_d) as avg_gap_d
from codas_0010
where ((std_filed_ete) <> 0) or ((nvl(std_filed_ete ,0)))
and ((avg_filed_ete)-(3*(std_filed_ete)) <= (oag_s_g2g))
and arr_locid=v_airport and dep_locid=v_airport
group by arr_locid, dep_locid;
begin
i codas_0010%rowtype;
open c4;
loop
fetch c4 into i;
exit when c4%notfound;
end loop;
close c4;
dbms_output.put_line(i);
end std_deviation;
end codas_citypair;
/
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 17:31:02 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
