[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* we do not actually delete old elements in the table, but simply nullify
* them. Thus, on new file openings, we first traverse the table looking
* for empty slots.
*/
TYPE file_handle IS RECORD ( fid NUMBER, offset INTEGER );
select count(*) into rowcount
from fnd_lob_access
where nvl(file_id,-1) = nvl(authenticate.file_id, -1)
and access_id = authenticate.access_id
and timestamp > sysdate;
insert into fnd_lob_access (access_id, file_id, timestamp)
values (fnd_crypto.RANDOMNUMBER,
file_id, sysdate+1)
returning access_id into result;
update fnd_lobs set fnd_lobs.expiration_date = sysdate + 0.5
where fnd_lobs.file_id = construct_download_url2.file_id;
select instr(file_name,'.',-1) into ext_length
from fnd_lobs
where file_id = construct_download_url2.file_id;
select substr(file_name,instr(file_name,'/',-1)+1) into file_name
from fnd_lobs
where file_id = construct_download_url2.file_id;
select function_id
into func_id
from fnd_form_functions
where function_name = 'FND_FNDFLUPL';
select function_id
into func_id
from fnd_form_functions
where function_name = 'FND_FNDFLUPL'
and upper(type) = 'JSP';
* explicitly deleted
* language The document language; defaults to userenv('LANG')
select file_id
into fid
from fnd_lob_access
where access_id = confirm_upload.access_id;
select fnd_lobs_s.nextval into fid from dual;
select dbms_lob.getlength(blob_content), mime_type
into bloblength, mt
from fnd_lobs_document
where name = confirm_upload.file_name
and rownum=1;
insert into fnd_lobs (file_id,
file_name,
file_content_type,
file_data,
upload_date,
expiration_date,
program_name,
program_tag,
language,
file_format)
(select confirm_upload.fid,
fn,
ld.mime_type,
ld.blob_content,
sysdate,
confirm_upload.expiration_date,
confirm_upload.program_name,
confirm_upload.program_tag,
confirm_upload.language,
fnd_gfm.set_file_format(mt)
from fnd_lobs_document ld
where ld.name = confirm_upload.file_name
and rownum=1);
update fnd_lob_access set file_id = fid
where access_id = confirm_upload.access_id;
delete from fnd_lobs_document;
delete from fnd_lobs_documentpart;
delete from fnd_lobs_document;
delete from fnd_lobs_documentpart;
select file_id into fid
from fnd_lob_access
where access_id = get_file_id.access_id;
SELECT file_id
FROM FND_LOBS FL
WHERE NOT EXISTS (SELECT '1'
FROM FND_DOCUMENTS_VL FDVL
WHERE FDVL.MEDIA_ID = FL.FILE_ID
AND FDVL.DATATYPE_ID = 6)
AND PROGRAM_NAME = 'FNDATTCH'
AND EXPIRATION_DATE IS NULL;
select count(*) into l_doc_cnt
from fnd_documents_vl
where datatype_id = 6
and media_id = l_file_id;
update fnd_lobs fl
set expiration_date = sysdate-1
where program_name = 'FNDATTCH'
and file_id = l_file_id;
delete from fnd_lobs where sysdate > expiration_date;
delete from fnd_lob_access where sysdate > timestamp;
delete from fnd_lobs
where fnd_lobs.program_name = purge_expired.program_name
and sysdate > expiration_date;
* purge_set - purge selected rows from the LOB table
*/
PROCEDURE purge_set(program_name varchar2,
program_tag varchar2 default null) is
pragma autonomous_transaction;
delete from fnd_lobs where fnd_lobs.program_name = purge_set.program_name;
delete from fnd_lobs
where fnd_lobs.program_name = purge_set.program_name
and fnd_lobs.program_tag = purge_set.program_tag;
insert into fnd_lobs (file_id, file_name, file_content_type,
file_data, upload_date, expiration_date, program_name, program_tag,
language,oracle_charset,file_format)
values (fnd_lobs_s.nextval, file_name, ct,
EMPTY_BLOB(), sysdate, sysdate + 1, program_name, program_tag,
l_lang, fnd_gfm.iana_to_oracle(iana_cs), l_file_format)
returning file_id into fh.fid;
update fnd_lobs set expiration_date = null where file_id = fh.fid;
select file_data, oracle_charset into flob, ocs
from fnd_lobs
where file_id = fh.fid
for update of file_data;
select tag into charset
from fnd_lookup_values_vl
where lookup_type = charmap
and lookup_code = substr(userenv('LANGUAGE'),
instr(userenv('LANGUAGE'),'.')+1);
select tag into cs
from fnd_lookup_values_vl
where lookup_type = charmap
and upper(lookup_code) = upper(ics);
select tag into ics
from fnd_lookup_values_vl
where lookup_type = charmap
and upper(lookup_code) = upper(cs)
and rownum = 1;
* If purge is specified, then the row is deleted immediately
*/
PROCEDURE download(file_id number,
access number,
purge varchar2 default NULL) is
doc blob;
* delete from fnd_lobs where file_id = download.file_id; */
update fnd_lobs
set fnd_lobs.expiration_date = sysdate + 0.5
where fnd_lobs.file_id = download.file_id;
select file_content_type, oracle_charset, program_name, file_data, file_name
into ct, ocs, pn, doc, fn
from fnd_lobs
where file_id = download_blob.fid;
INSERT INTO fnd_lob_access (access_id, file_id, timestamp)
VALUES (ticket, value, sysdate+1);
select file_id into value from fnd_lob_access
where access_id = ticket for update;
delete from fnd_lob_access where access_id = ticket;
* If purge is specified, then the row is deleted immediately
*/
PROCEDURE get(p_path varchar2) is
doc blob;
select lower(program_name),expiration_date
into prog_name, exp_date
from fnd_lobs
where file_id = l_file_id;
fnd_gfm.delete_lob(l_file_id);
SELECT fnd_lobs_s.nextval
INTO fid_to
FROM dual;
SELECT file_id,
file_name,
file_content_type,
upload_date,
expiration_date,
program_name,
program_tag,
file_data,
language,
oracle_charset,
file_format
INTO fnd_lobs_rec.file_id,
fnd_lobs_rec.file_name,
fnd_lobs_rec.file_content_type,
fnd_lobs_rec.upload_date,
fnd_lobs_rec.expiration_date,
fnd_lobs_rec.program_name,
fnd_lobs_rec.program_tag,
fnd_lobs_rec.file_data,
fnd_lobs_rec.language,
fnd_lobs_rec.oracle_charset,
fnd_lobs_rec.file_format
FROM fnd_lobs
WHERE file_id = fid_from;
INSERT INTO fnd_lobs (file_id,
file_name,
file_content_type,
upload_date,
expiration_date,
program_name,
program_tag,
file_data,
language,
oracle_charset,
file_format)
VALUES (fid_to,
fnd_lobs_rec.file_name,
fnd_lobs_rec.file_content_type,
fnd_lobs_rec.upload_date,
fnd_lobs_rec.expiration_date,
fnd_lobs_rec.program_name,
fnd_lobs_rec.program_tag,
fnd_lobs_rec.file_data,
fnd_lobs_rec.language,
fnd_lobs_rec.oracle_charset,
fnd_lobs_rec.file_format);
SELECT distinct ctx_format_code
INTO l_file_format
FROM fnd_mime_types
WHERE mime_type = l_mime_type;
select fnd_lobs_s.nextval into fid from dual;
INSERT INTO fnd_lobs (
file_id,
file_name,
file_content_type,
upload_date,
expiration_date,
program_name,
program_tag,
file_data,
language,
oracle_charset,
file_format )
VALUES (
fid,
file_name,
v_content_type,
sysdate,
null,
'FNDAPI',
null,
empty_blob(),
v_language,
fnd_gfm.iana_to_oracle(fnd_gfm.get_iso_charset),
fnd_gfm.set_file_format(v_content_type));
select file_data into l_blob_loc from fnd_lobs
where file_id = fid;
* delete_lob - this api deletes a lob in fnd_lobs using file_id.
*
*/
PROCEDURE DELETE_LOB (fid number) IS
BEGIN
delete from fnd_lobs where file_id = fid;
fnd_gfm.err_msg('delete_lob');
END delete_lob;
select count(distinct(upper(allow_file_upload))) into l_cnt
from fnd_mime_types where lower(file_ext) = lower(l_file_ext);
select distinct upper(allow_file_upload) into l_allow
from fnd_mime_types where lower(file_ext) = lower(l_file_ext)
and upper(allow_file_upload) = 'N';
select distinct upper(allow_file_upload) into l_allow
from fnd_mime_types where lower(file_ext) = lower(l_file_ext);