Tom,

I am calculating some percentile values using cume_dist analytic function as well as lag and lead functions. I need to store these values in an array to allow a table to be updated later.

The example uses "user_tables" view for simplicity.

I tried this code but got:

SQL> l
1 declare
2 type percentile_cur_typ is ref cursor;
3
4 c_percentile percentile_cur_typ;
5
6 type student_rec_typ is record(
7 time_series dbms_sql.date_table,
8 year_level dbms_sql.varchar2_table,
9 benchmark_student_group dbms_sql.varchar2_table,
10 strand dbms_sql.varchar2_table,
11 score dbms_sql.number_table,
12 percentile25 dbms_sql.number_table,
13 percentile75 dbms_sql.number_table
14 );
15
16 v_rec student_rec_typ;
17 v_bigstr varchar2(10000);
18 begin
19 v_bigstr :=
20 'select
21 last_analyzed,
22 lag(table_name) over (order by table_name) xx,
23 tablespace_name,
24 cluster_name,
25 num_rows,
26 blocks,
27 empty_blocks
28 from
29 user_tables';
30
31 open c_percentile for select
32 last_analyzed,
33 lag(table_name) over (order by table_name) xx,
34 tablespace_name,
35 cluster_name,
36 num_rows,
37 blocks,
38 empty_blocks
39 from
40 user_tables;
41
42 fetch c_percentile
43 bulk collect into v_rec.time_series,
44 v_rec.year_level,
45 v_rec.benchmark_student_group,
46 v_rec.strand,
47 v_rec.score,
48 v_rec.percentile25,
49 v_rec.percentile75;
50
51 close c_percentile;
52
53 dbms_output.enable(1000000);
54 dbms_output.put_line(v_rec.year_level.count);
55 end;
56* ;
SQL> /
lag(table_name) over (order by table_name) xx,
*
ERROR at line 33:
ORA-06550: line 33, column 34:
PLS-00103: Encountered the symbol "(" when expecting one of the following: , from into bulk

The select statement is perfectly fine on its own. To solve the problem I put the select statement into a variable. This method has worked before, but now its complaining about the fetch statement because I'm using a "bulk collect".


SQL> l
1 declare
2 type percentile_cur_typ is ref cursor;
3
4 c_percentile percentile_cur_typ;
5
6 type student_rec_typ is record(
7 time_series dbms_sql.date_table,
8 year_level dbms_sql.varchar2_table,
9 benchmark_student_group dbms_sql.varchar2_table,
10 strand dbms_sql.varchar2_table,
11 score dbms_sql.number_table,
12 percentile25 dbms_sql.number_table,
13 percentile75 dbms_sql.number_table
14 );
15
16 v_rec student_rec_typ;
17 v_bigstr varchar2(10000);
18 begin
19 v_bigstr :=
20 'select
21 last_analyzed,
22 lag(table_name) over (order by table_name) xx,
23 tablespace_name,
24 cluster_name,
25 num_rows,
26 blocks,
27 empty_blocks
28 from
29 user_tables';
30
31 open c_percentile for v_bigstr;
32
33 fetch c_percentile
34 bulk collect into v_rec.time_series,
35 v_rec.year_level,
36 v_rec.benchmark_student_group,
37 v_rec.strand,
38 v_rec.score,
39 v_rec.percentile25,
40 v_rec.percentile75;
41
42 close c_percentile;
43
44 dbms_output.enable(1000000);
45 dbms_output.put_line(v_rec.year_level.count);
46* end;
SQL>
SQL>
SQL> /
declare
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 33

If I remove the "bulk collect" it works, but I need to put these values into a table of arrays. I have been trying to use some native dynamic SQL to resolve this but I've had no luck. I have read the documentation about native dynamic SQL but it hasn't helped.

Is there a way to achieve this using Oracle 8.1.7.4 and if so how would you do it?

Regards
Tony