oracle sql怎样备注,oracle批量备注的sql及存取LONG RAW字段

1.批量备注：

select 'comment on column '||table_name||'.'||column_name||'

is ''直达配比'';' From user_col_comments

where column_name = 'NONSTOP_SI' and comments is null;

2.查询某字段已备注说明：

select * From user_col_comments

where column_name = 'ARRIVAL_SI' and comments is not

null;

3.读图片

selectblob file_content into :picBlob from

pub.tb_carg_ticket_pic a where a.pic_id =:id;

此PB语句只能读取LONG RAW类型字段，不能读取BLOB类型字段，否则会报如下错误

4.更新LONG RAW类型字段

updateblob pub.tb_carg_ticket_pic set

file_content = :tot_b where pic_id = :pic_id;

if sqlca.SQLNRows <= 0

then

post MessageBox("提示","保存图像内容出错!"+sqlca.SQLErrText ,

stopsign!)

goto sql_error

end if

此PB语句同样不能读取BLOB类型字段

语句中:picBlob和:tot_b均为blob类型变量

在设计数据库的时候，要存取图片等如果要用到上述SQL语句,不能设计为blob字段，只能设计为LONG

RAW字段，否则会报上述错误，我用的是PB7.0，oralce 驱动是O73 ORACLE 7.3

附一个保存图片的函数：

string ls_pathname, ls_filename, ls_image_flag

long row , pic_id

dec li_id

integer li_FileNum, loops, i

long flen, bytes_read, new_pos, ll_tot

blob b, tot_b

ll_tot = dw_1.rowcount()

//判断是否一定需要票证影像

if ll_tot = 0 then

select nvl(image_flag,'N') into :ls_image_flag from

pub.ma_error_code where error_code =:as_error_code;

if ls_image_flag = 'Y' then

MessageBox("提示", "差错" + as_error_code + "需要票证影像，请添加影像!" ,

stopsign!)

goto sql_error

end if

end if

//循环读影像文件

for row = 1 to ll_tot

tot_b = blob("")

b = blob("")

ls_pathname = dw_1.object.pathname[row]

ls_filename = dw_1.object.filename[row]

flen = dw_1.object.filelen[row]

li_FileNum = FileOpen(ls_pathname, StreamMode!, Read!,

LockRead!)

//计算循环次数

IF flen > 32765 THEN

IF Mod(flen, 32765) = 0 THEN

loops = flen/32765

ELSE

loops = (flen/32765) + 1

END IF

ELSE

loops = 1

END IF

//循环读文件内容

new_pos = 1

FOR i = 1 to loops

bytes_read = FileRead(li_FileNum, b)

tot_b = tot_b + b

NEXT

FileClose(li_FileNum)

//先插入普通字段的数据

select pub.seq_carg_ticket_pic.nextval into :pic_id from

dual;

insert into

pub.tb_carg_ticket_pic(pic_id,main_record_id,file_name) values

(:pic_id,:al_record_id,:ls_filename);

if sqlca.sqlcode<>0 then

post MessageBox("提示","图像" + ls_pathname +

"插入出错!"+sqlca.SQLErrText , stopsign!)

goto sql_error

end if

//然后更新blob字段

updateblob pub.tb_carg_ticket_pic set file_content = :tot_b

where pic_id = :pic_id;

if sqlca.SQLNRows <= 0 then

post MessageBox("提示","保存图像内容出错!"+sqlca.SQLErrText ,

stopsign!)

goto sql_error

end if

next

dw_1.reset()

return 1

sql_error:

rollback;

return -1