请问这个函数哪有错?为何动态就不行?
j_d 2005-09-06 02:55:28 CREATE OR REPLACE FUNCTION "CGS"."GETCLOB2" (
v_cttId in varchar2,
v_datSb in varchar2,
v_datId in varchar2,
v_hjnId in varchar2,
v_pos in number) return varchar2
as
lobloc clob;
buffer varchar2(32767);
amount number := 28000;
offset number := 1;
query_str varchar2(1000);
v_cursor number;
ccc number;
begin
buffer:=v_cttId || '-' || v_datSb ||'-' || v_datId || '-' || v_hjnId;
v_cursor:=dbms_sql.open_cursor;
-- query_str:='select all_wrd from km_contents_tbl where ctt_id=' || v_cttId || ' and dat_sb=' || v_datSb || ' and dat_id=' || v_datId || ' and hjn_id=' || v_hjnId;
query_str:='select all_wrd from km_contents_tbl where ctt_id=:P1 and dat_sb=:P2 and dat_id=:P3 and hjn_id=:P4 ';
--query_str:='select all_wrd from km_contents_tbl where ctt_id=''CGC'' and dat_sb=''022T'' and dat_id=''29963'' and hjn_id=''012''';
--:1 and dat_sb=:2 and dat_id=:3 and hjn_id=:4';
dbms_sql.parse(v_cursor,query_str,dbms_sql.NATIVE);
dbms_sql.bind_variable(v_cursor,'P1',v_cttId);
dbms_sql.bind_variable(v_cursor,'P2',v_datSb);
dbms_sql.bind_variable(v_cursor,'P3',v_datId);
dbms_sql.bind_variable(v_cursor,'P4',v_hjnId);
dbms_sql.define_column(v_cursor,1,lobloc);
ccc:=dbms_sql.execute(v_cursor);
if(dbms_sql.fetch_rows(v_cursor)>0)
then
dbms_sql.COLUMN_VALUE(v_cursor,1,lobloc);
else
buffer :=buffer || '-ERR 001';
return buffer;
end if;
dbms_sql.close_cursor(v_cursor);
--query_str :='select all_wrd from km_contents_tbl where ctt_id=' || v_cttId || ' and dat_sb=' || v_datSb || ' and dat_id=' || v_datId || ' hjn_id=' || v_hjnId;
-- query_str :='select all_wrd from km_contents_tbl where ctt_id=:1 and dat_sb=:2 and dat_id= :3 and hjn_id=:4';
--initialize buffer with data to be found
--EXECUTE immediate query_str INTO lobloc; --USING v_cttId,v_datSb,v_datId,v_hjnId;
-- WHERE ctt_id='CGC' and dat_sb='022T' and dat_id= '29963' and hjn_id='012';
offset:=offset+(v_pos-1)*28000;
if lobloc is not null then
dbms_lob.read(lobloc,amount,offset,buffer);
end if;
buffer :=buffer || '-ERR 002';
return buffer;
exception
when no_data_found then
buffer :=buffer || '-ERR 003';
return buffer;
end;