Subject: plsql help on static pages
Here is a function that is causing an error:
create function static_page__delete_stale_items (
integer, -- session_id in
sp_extant_files.session_id%TYPE,
integer -- package_id in
apm_packages.package_id%TYPE
) returns integer as '
declare
p_session_id alias for $1;
p_package_id alias for $2;
v_root_folder_id sp_folders.folder_id%TYPE;
v_stale_file_row static_pages.static_page_id%
TYPE;
v_stale_folder_row sp_folders.folder_id%TYPE;
begin
v_root_folder_id := static_page__get_root_folder
(p_package_id);
-- First delete all files that are descendants of the
root folder
-- but aren''t in sp_extant_files
for v_stale_file_row in
select static_page_id from static_pages
where folder_id in (
select folder_id from sp_folders
where tree_sortkey like ( select tree_sortkey || ''%''
from sp_folders
where folder_id = v_root_folder_id)
) and static_page_id not in (
select static_page_id
from sp_extant_files
where session_id = p_session_id
)
loop
PERFORM static_page__delete(v_stale_file_row);
end loop;
-- Now delete all folders that aren''t in
sp_extant_folders. There are two
-- views created on the fly here: dead (all descendants of the
root
-- folder not in sp_extant_folders) and path (each
folder and its depth).
-- They are joined together to get the depth of all
the folders that
-- need to be deleted. The root folder is excluded
because it won''t
-- show up in the filesystem search, so it will be
missing from
-- sp_extant_folders.
--
for v_stale_folder_row in
select dead.folder_id from
(select folder_id from sp_folders
where (folder_id) not in (
select folder_id
from sp_extant_folders
where session_id =
p_session_id
)
) dead,
(select tree_level(''folder_id'') as depth
from sp_folders
where tree_sortkey like ( select tree_sortkey || ''%''
from sp_folders
where folder_id = v_root_folder_id)
) path
where dead.folder_id = path.folder_id
and dead.folder_id <> v_root_folder_id
order by path.depth desc
loop
delete from sp_folders
where folder_id = v_stale_folder_row;
perform content_folder__delete
(v_stale_folder_row);
end loop;
return 0;
end;' language 'plpgsql';
I tried to figure out where in that area there could be an error. I
suspect it is somewhere in the query, but I commented it out and
still got the error message.
Sorry about all these questions
