Hi All,
Can someone help me in sorting the subjected one.
DB Version - 12C
Scripts:-
create table cms_ftp_details
(slno number,
filename varchar2(256),
file_content blob,
is_read char(1),
create_id varchar2(30),
create_dt date,
lst_upd_id varchar2(30),
lst_upd_dt date)
/
create or replace function BLOB_TO_CLOB(B BLOB)
return clob is
c clob;
n number;
begin
if (b is null) then
return null;
end if;
if (length(b)=0) then
return empty_clob();
end if;
dbms_lob.createtemporary(c,true);
n:=1;
while (n+32767<=length(b)) loop
dbms_lob.writeappend(c,32767,utl_raw.cast_to_varchar2(dbms_lob.substr(b,32767,n)));
n:=n+32767;
end loop;
dbms_lob.writeappend(c,length(b)-n+1,utl_raw.cast_to_varchar2(dbms_lob.substr(b,length(b)-n+1,n)));
return c;
end;
/
create or replace function clob2blob(AClob CLOB) return BLOB is
Result BLOB;
o1 integer;
o2 integer;
c integer;
w integer;
begin
o1 := 1;
o2 := 1;
c := 0;
w := 0;
DBMS_LOB.CreateTemporary(Result, true);
DBMS_LOB.ConvertToBlob(Result, AClob, length(AClob), o1, o2, 0, c, w);
return(Result);
end clob2blob;
/
Insert into cms_ftp_details values(1,'AA_039',clob2blob('select * from employees'),'N','LAZAR',sysdate,null,null)
/
commit
/
create or replace
procedure Pro_sql_exec is
Cursor cur_details is
select filename,
BLOB_TO_CLOB(file_content) file_content
from di_cms_ftp_details
where nvl(is_read,'N') = 'N';
lv_query varchar2(32767);
lv_data clob;
begin
for rec_details in cur_details
Loop
lv_query := rec_details.file_content;
execute immediate lv_query into lv_data;
update cms_ftp_details set is_read ='Y'
where filename = rec_details.filename;
End loop;
commit;
end;
/
exec Pro_sql_exec
/
Error:-
Error starting at line : 35 in command -
exec Pro_sql_exec
Error report -
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at ".PRO_SQL_EXEC", line 18
ORA-06512: at line 1
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Also the thing im trying here is to executing the select query and extracting the data and need to send it a mail..
So please help me in coding part of sending a mail as well
Thanks..