哪位高手帮忙看一下,执行后出现 ORA-00936:missing expression 错误。代码如下:declare
cursor c_lst is
select distinct tb_owner,tb_nm,db_col,ff_atr_len
from t_ffgt_clob where tb_owner in ('FFBASEGROUP3USER','FFBASEGROUP3CMS');
len_dvd number(9) := 1000;
byte_len number(16) := 0;
pos number(6) :=0;
------
TYPE cursor_type IS REF CURSOR;
v_cursor cursor_type;
clob_col clob;
ff_cls varchar2(64);
sufix varchar2(16);
--
rows_b4k number(12) := 0;
rows_lg_ff number(12) :=0;
max_lenss number(12) :=0;
row_cols number(12) := 0;
sum_bytes number(16) := 0;
c_reduction varchar2(512);
o__nm varchar2(16) ;
begin
for v_p1 in c_lst loop
begin
sum_bytes := 0;
row_cols :=0;
rows_b4k :=0;
max_lenss :=0;
rows_lg_ff := 0;
c_reduction :=
'select ' || v_p1.db_col
||' from ' || v_p1.tb_owner ||'.' ||v_p1.tb_nm ;
open v_cursor for c_reduction;
loop
fetch v_cursor into clob_col ;
exit when v_cursor%notfound;
byte_len :=0;
if clob_col is null then
byte_len :=0;
else
for k in 1.. ceil(dbms_lob.getlength( clob_col ) /len_dvd) loop
byte_len := byte_len + lengthb(dbms_lob.substr( clob_col , len_dvd , 1 + (k-1) * len_dvd ) );
end loop;
end if;
-- sum_bytes := sum_bytes + byte_len;
if byte_len > max_lenss then
max_lenss := byte_len;
end if;
row_cols := row_cols + 1;
if byte_len > 4000 then
rows_b4k := rows_b4k +1;
end if;
if byte_len > v_p1.ff_atr_len then
rows_lg_ff := rows_lg_ff + 1;
end if;
end loop;
close v_cursor;
update t_ffgt_clob
set TAB_ROWS = row_cols,
MAX_LEN = max_lenss,
ROWS_LARGER_4K = rows_b4k,
ROWS_LARGER_FFLEN = rows_lg_ff
where tb_owner = v_p1.tb_owner
and tb_nm = v_p1.tb_nm
and db_col = v_p1.db_col ;
commit;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
end loop;end;
cursor c_lst is
select distinct tb_owner,tb_nm,db_col,ff_atr_len
from t_ffgt_clob where tb_owner in ('FFBASEGROUP3USER','FFBASEGROUP3CMS');
len_dvd number(9) := 1000;
byte_len number(16) := 0;
pos number(6) :=0;
------
TYPE cursor_type IS REF CURSOR;
v_cursor cursor_type;
clob_col clob;
ff_cls varchar2(64);
sufix varchar2(16);
--
rows_b4k number(12) := 0;
rows_lg_ff number(12) :=0;
max_lenss number(12) :=0;
row_cols number(12) := 0;
sum_bytes number(16) := 0;
c_reduction varchar2(512);
o__nm varchar2(16) ;
begin
for v_p1 in c_lst loop
begin
sum_bytes := 0;
row_cols :=0;
rows_b4k :=0;
max_lenss :=0;
rows_lg_ff := 0;
c_reduction :=
'select ' || v_p1.db_col
||' from ' || v_p1.tb_owner ||'.' ||v_p1.tb_nm ;
open v_cursor for c_reduction;
loop
fetch v_cursor into clob_col ;
exit when v_cursor%notfound;
byte_len :=0;
if clob_col is null then
byte_len :=0;
else
for k in 1.. ceil(dbms_lob.getlength( clob_col ) /len_dvd) loop
byte_len := byte_len + lengthb(dbms_lob.substr( clob_col , len_dvd , 1 + (k-1) * len_dvd ) );
end loop;
end if;
-- sum_bytes := sum_bytes + byte_len;
if byte_len > max_lenss then
max_lenss := byte_len;
end if;
row_cols := row_cols + 1;
if byte_len > 4000 then
rows_b4k := rows_b4k +1;
end if;
if byte_len > v_p1.ff_atr_len then
rows_lg_ff := rows_lg_ff + 1;
end if;
end loop;
close v_cursor;
update t_ffgt_clob
set TAB_ROWS = row_cols,
MAX_LEN = max_lenss,
ROWS_LARGER_4K = rows_b4k,
ROWS_LARGER_FFLEN = rows_lg_ff
where tb_owner = v_p1.tb_owner
and tb_nm = v_p1.tb_nm
and db_col = v_p1.db_col ;
commit;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
end loop;end;
解决方案 »
- ORACLE 11G SQL优化求教~~
- 紧急求助,如何同时给多个表修改数据。
- P/LSQL developer oracle 有时会出现全选后,只粘贴出部分数据
- oracle日期的一个疑惑???
- sql求助!
- imp的时候提示:插入的值对于列过大 ,一共有几百个表,一半出现这个提示!
- 问一个简单的问题
- oracle manager server问题,急急急急急急急急急急急急急急急急急急在线请教各位老大!谢谢
- wm_concat函数使用问题
- oracle9i与oracle11查询日期
- Oracle9i用户下的表导入到Oracle10g里面-----急,在线等
- 如何获取varchar2类型的最大值?
没有用过
你DEBUG呢,哪里出错,看看,错误的行数....
调试为上策,可以很方便找到语法错误和逻辑错误。
请问4楼 lengthb 有问题么???
Cause: A required part of a clause or expression has been omitted. For example, a SELECT statement may have been entered without a list of columns or expressions or with an incomplete expression. This message is also issued in cases where a reserved word is misused, as in SELECT TABLE.
Action: Check the statement syntax and specify the missing component.
Oracle BI EE Training
Expert provider of Oracle BI EE Technical and End User Training
应该是参数错误