{"id":211,"date":"2008-02-08T10:39:00","date_gmt":"2008-02-08T12:39:00","guid":{"rendered":"http:\/\/www.soudba.com.br\/?p=211"},"modified":"2008-02-08T10:39:00","modified_gmt":"2008-02-08T12:39:00","slug":"busca-customizada-no-oracle-portal","status":"publish","type":"post","link":"https:\/\/www.soudba.com.br\/?p=211","title":{"rendered":"Busca customizada no Oracle PORTAL"},"content":{"rendered":"

Link da documentacao:
http:\/\/www.oracle.com\/technology\/products\/ias\/portal\/html\/plsqldoc\/pldoc1014\/wwsrc_api.html<\/p>\n

Temos uma funcao para efetuar uns testes:<\/p>\n

create or replace procedure search_results(searchterm varchar2, score number) as<\/span>
x varchar2(100);<\/span>
y number;<\/span>
l_results wwsrc_api.items_result_array_type;<\/span>
l_count number;<\/span>
l_scores wwsrc_api.number_list_type;<\/span>
begin<\/span>
x := searchterm;<\/span>
y := score;<\/span>
l_results := wwsrc_api.item_search(<\/span>
p_mainsearch => x,<\/span>
p_out_count => l_count,<\/span>
p_out_scores => l_scores<\/span>
);<\/span>
htp.p(‘Number of total hits: ‘ || l_count);<\/span>
<\/span>
htp.p(‘resultado : ‘ || l_results.count);<\/span>
htp.p(‘resultado : ‘ || l_results(1).url);<\/span>
htp.p(‘resultado : ‘ || l_results(2).url) ;<\/span>
<\/span>
htp.p(‘
‘);<\/span>
htp.p(‘
‘);<\/span>
for i in 1..l_results.count loop<\/span>
— if (l_scores(i) > y) then<\/span>
htp.p(‘‘ || i || ‘<\/b> –
‘”>’ || l_results(i).display_name || ‘<\/span><\/a>‘);<\/span>
htp.p(‘
‘);<\/span>
htp.p(‘score = ‘ || l_scores(i));<\/span>
htp.p(‘
‘);<\/span>
— end if;<\/span>
end loop;<\/span>
exception when others then<\/span>
null;<\/span>
end;<\/span><\/p>\n

Mas ai tive que dar uma melhorada e criei a funcao:<\/p>\n

CREATE OR REPLACE PROCEDURE PESQUISA(p_ATTRIBUTE_TEXT varchar2,<\/span>
P_TEMA varchar2,<\/span>
P_ASSUNTO varchar2,<\/span>
P_AUTOR varchar2,<\/span>
P_DAT_INI DATE,<\/span>
P_DAT_FIM DATE,<\/span>
<\/span>
P_TIPOTERMO VARCHAR2 default ‘ANY’)<\/span><\/p>\n

is<\/span>
\/*<\/span>
<\/span>
FALTA VALIDAR SE O ITEM FOI APROVADOR<\/span>
e PUBLICADO….<\/span>
<\/span>
Limitar a pesquisa a 3 caracteres e no maximo 128 caracteres.<\/span>
<\/span>
*\/<\/span><\/p>\n

l_results wwsrc_api.items_result_array_type;<\/span>
l_count number;<\/span>
l_scores wwsrc_api.number_list_type;<\/span>
l_attributes wwsrc_runtime_attr_varray;<\/span>
l_pggroups wwsrc_api.number_list_type;<\/span><\/p>\n

v_contador number default 1;<\/span>
v_databusca date;<\/span><\/p>\n

v_sqldinamico varchar2(4000);<\/span><\/p>\n

type tpCursor is REF CURSOR;<\/span><\/p>\n

cCursor tpCursor;<\/span>
v_sql varchar2(4000) default v_sqldinamico;<\/span><\/p>\n

— Mostrar na tela retorno do cursor.<\/span><\/p>\n

v_tema portal.WWSBR_ALL_CATEGORIES.display_name%TYPE;<\/span>
v_temaanterior portal.WWSBR_ALL_CATEGORIES.display_name%TYPE;<\/span><\/p>\n

v_assunto portal.WWSBR_ALL_CATEGORIES.display_name%TYPE;<\/span>
v_titulo portal.WWSBR_ALL_CATEGORIES.display_name%type;<\/span>
v_DATADEPUBLICACAO date;<\/span>
v_urlpage varchar2(4000);<\/span>
v_descricao varchar2(4000);<\/span>
v_tipotermo varchar2(4000);<\/span>
v_nome varchar2(4000);<\/span><\/p>\n

— solucao temporaria <\/span>
v_pgname portal.WWSBR_ALL_CONTENT_AREAS.name%TYPE;<\/span><\/p>\n

begin<\/span>
—<\/span>
— Verificar se foram designados parametros.<\/span>
—<\/span>
if length(NVL(p_ATTRIBUTE_TEXT, ”) )
length(p_ATTRIBUTE_TEXT ) >= 128 then<\/span>
htp.print(‘Refa\u00e7a a pesquisa. Informe acima de 3 caracteres e no maximo 128 caracteres para a busca.’);<\/span>
else<\/span>
— <\/span>
— Se foram passados parametros<\/span>
— entao estaremos<\/span>
— com os parametros para informar na busca.<\/span>
— <\/span>
if p_tipotermo = ‘ANY’ then<\/span>
v_tipotermo := portal.wwsrc_api.CONTAINS_ANY;<\/span>
elsif p_tipotermo = ‘ALL’ then<\/span>
v_tipotermo := portal.wwsrc_api.CONTAINS_ALL;<\/span>
<\/span>
elsif p_tipotermo = ‘TO’ then<\/span>
v_tipotermo := portal.wwsrc_api.EQUALS_TO;<\/span>
end if;<\/span>
<\/span>
— Build up attribute object with author criteria. <\/span>
— <\/span>
— Quais os page groups para a busca? OU quais os portais…<\/span>
—<\/span>
l_pggroups(1) := 813; –813– Portal Publico<\/span>
— l_pggroups(2) := 833; — Portal Publico<\/span>
<\/span>
—<\/span>
— Para pegar o nome do portal DEFAULT <\/span>
— vou pesquisar o nome para passar como parametro fixo<\/span>
— Visto que a consulta somente retorna os itens da pagina LIB<\/span>
— chamado aberto com a oracle para resolver.<\/span>
— <\/span>
select name<\/span>
into v_pgname<\/span>
from portal.WWSBR_ALL_CONTENT_AREAS<\/span>
where portal.WWSBR_ALL_CONTENT_AREAS.id = 813;<\/span>
<\/span>
<\/span>
if p_ATTRIBUTE_TEXT is not null then<\/span>
portal.wwsrc_api.specify_attributes(p_id => portal.wwsbr_api.ATTRIBUTE_KEYWORDS,<\/span>
p_value => p_ATTRIBUTE_TEXT,<\/span>
p_operator => v_tipotermo,<\/span>
p_in_out_attr_varray => l_attributes);<\/span>
<\/span>
portal.wwsrc_api.specify_attributes(p_id => portal.wwsbr_api.ATTRIBUTE_TITLE, <\/span>
p_value => p_ATTRIBUTE_TEXT,<\/span>
p_operator => v_tipotermo,<\/span>
p_in_out_attr_varray => l_attributes);<\/span>
<\/span>
portal.wwsrc_api.specify_attributes(p_id => portal.wwsbr_api.ATTRIBUTE_URL, <\/span>
p_value => p_ATTRIBUTE_TEXT,<\/span>
p_operator => v_tipotermo,<\/span>
p_in_out_attr_varray => l_attributes);<\/span>
end if;<\/span>
<\/span>
— <\/span>
— Primeiro tentar converter o texto de procura para a data inicial<\/span>
—<\/span>
if P_DAT_INI is not null then<\/span><\/p>\n

begin<\/span>
v_databusca := to_char(p_ATTRIBUTE_TEXT, ‘DD-MON-RRRR’);<\/span>
exception<\/span>
when others then<\/span>
v_databusca := null;<\/span>
end;<\/span>
<\/span>
begin<\/span>
v_databusca := to_char(p_ATTRIBUTE_TEXT, ‘DDMMYYYY’);<\/span>
exception<\/span>
when others then<\/span>
v_databusca := null;<\/span>
end;<\/span>
<\/span>
begin<\/span>
v_databusca := to_char(p_ATTRIBUTE_TEXT, ‘DDMMYYYY’);<\/span>
exception<\/span>
when others then<\/span>
v_databusca := null;<\/span>
end;<\/span>
<\/span>
begin<\/span>
v_databusca := to_char(p_ATTRIBUTE_TEXT, ‘DDMMYY’);<\/span>
exception<\/span>
when others then<\/span>
v_databusca := null;<\/span>
end;<\/span>
<\/span>
begin<\/span>
v_databusca := to_char(p_ATTRIBUTE_TEXT, ‘DDMMYY’);<\/span>
exception<\/span>
when others then<\/span>
v_databusca := null;<\/span>
end;<\/span>
<\/span>
end if;<\/span><\/p>\n

—<\/span>
— Tentar converter o formato da data INICIAL<\/span>
—<\/span><\/p>\n

if P_DAT_INI is not null then<\/span><\/p>\n

begin<\/span>
v_databusca := to_char(P_DAT_INI, ‘DD-MON-RRRR’);<\/span>
exception<\/span>
when others then<\/span>
v_databusca := null;<\/span>
end;<\/span>
<\/span>
begin<\/span>
v_databusca := to_char(P_DAT_INI, ‘DDMMYYYY’);<\/span>
exception<\/span>
when others then<\/span>
v_databusca := null;<\/span>
end;<\/span>
<\/span>
begin<\/span>
v_databusca := to_char(P_DAT_INI, ‘DDMMYYYY’);<\/span>
exception<\/span>
when others then<\/span>
v_databusca := null;<\/span>
end;<\/span>
<\/span>
begin<\/span>
v_databusca := to_char(P_DAT_INI, ‘DDMMYY’);<\/span>
exception<\/span>
when others then<\/span>
v_databusca := null;<\/span>
end;<\/span>
<\/span>
begin<\/span>
v_databusca := to_char(P_DAT_INI, ‘DDMMYY’);<\/span>
exception<\/span>
when others then<\/span>
v_databusca := null;<\/span>
end;<\/span>
<\/span>
end if;<\/span>
<\/span>
if v_databusca is not null then<\/span>
dbms_session.set_nls(‘NLS_DATE_FORMAT’, ”’DD-MON-RRRR”’);<\/span>
portal.wwsrc_api.specify_attributes(p_id => portal.wwsbr_api.ATTRIBUTE_PUBLISHDATE, –portal.wwsbr_api.<\/span>
p_value => v_databusca,<\/span>
p_operator => portal.wwsrc_api.GREATER_THAN, <\/span>
p_in_out_attr_varray => l_attributes);<\/span>
<\/span>
end if;<\/span>
<\/span>
<\/span>
—<\/span>
— Colocar a validacao da data final<\/span>
—<\/span>
<\/span>
if P_DAT_FIM is not null then<\/span><\/p>\n

begin<\/span>
v_databusca := to_char(P_DAT_FIM, ‘DD-MON-RRRR’);<\/span>
exception<\/span>
when others then<\/span>
v_databusca := null;<\/span>
end;<\/span>
<\/span>
begin<\/span>
v_databusca := to_char(P_DAT_FIM, ‘DDMMYYYY’);<\/span>
exception<\/span>
when others then<\/span>
v_databusca := null;<\/span>
end;<\/span>
<\/span>
begin<\/span>
v_databusca := to_char(P_DAT_FIM, ‘DDMMYYYY’);<\/span>
exception<\/span>
when others then<\/span>
v_databusca := null;<\/span>
end;<\/span>
<\/span>
begin<\/span>
v_databusca := to_char(P_DAT_FIM, ‘DDMMYY’);<\/span>
exception<\/span>
when others then<\/span>
v_databusca := null;<\/span>
end;<\/span>
<\/span>
begin<\/span>
v_databusca := to_char(P_DAT_FIM, ‘DDMMYY’);<\/span>
exception<\/span>
when others then<\/span>
v_databusca := null;<\/span>
end;<\/span>
<\/span>
end if;<\/span>
<\/span>
if P_DAT_FIM is not null then<\/span>
v_databusca := to_char(P_DAT_INI, ‘DD-MON-RRRR’);<\/span>
portal.wwsrc_api.specify_attributes(p_id => portal.wwsbr_api.ATTRIBUTE_PUBLISHDATE, –portal.wwsbr_api.<\/span>
p_value => v_databusca,<\/span>
p_operator => portal.wwsrc_api.LESS_THAN,<\/span>
p_in_out_attr_varray => l_attributes);<\/span>
end if;<\/span>
<\/span>
—<\/span>
— Filtrar pelo AUTOR<\/span>
—<\/span>
if P_AUTOR is not null then<\/span>
portal.wwsrc_api.specify_attributes(p_id => portal.wwsbr_api.ATTRIBUTE_AUTHOR, –portal.wwsbr_api.<\/span>
p_value => P_AUTOR,<\/span>
p_operator => v_tipotermo,<\/span>
p_in_out_attr_varray => l_attributes);<\/span>
end if;<\/span>
<\/span>
—<\/span>
— Filtrar pelo TEMA<\/span>
—<\/span>
if P_TEMA is not null then<\/span>
<\/span>
portal.wwsrc_api.specify_attributes(p_id => portal.wwsbr_api.ATTRIBUTE_CATEGORY, <\/span>
p_value => P_TEMA,<\/span>
p_operator => v_tipotermo,<\/span>
p_in_out_attr_varray => l_attributes);<\/span>
end if;<\/span><\/p>\n

—<\/span>
— Filtrar pelo ASSUNTO<\/span>
—<\/span><\/p>\n

if P_ASSUNTO is not null then<\/span>
<\/span>
portal.wwsrc_api.specify_attributes(p_id => portal.wwsbr_api.ATTRIBUTE_CATEGORY, <\/span>
p_value => P_ASSUNTO,<\/span>
p_operator => v_tipotermo,<\/span>
p_in_out_attr_varray => l_attributes);<\/span>
end if;<\/span>
<\/span>
— The search needs to be performed by a User in the Portal<\/span>
— The is the Portal UI password for the portal user.<\/span>
— portal.wwctx_api.set_context(‘portal’, ‘senha’);<\/span>
— Perform the search. <\/span>
portal.wwctx_api.set_context(‘andre.rocha’, ‘senha’);<\/span>
<\/span>
begin<\/span>
<\/span>
l_results := portal.wwsrc_api.item_search(p_page_groups => l_pggroups, –p_mainsearch => ‘portalpublicodl’,<\/span>
p_attributes => l_attributes,<\/span>
p_attributesmatch => v_tipotermo,<\/span>
p_out_count => l_count,<\/span>
p_out_scores => l_scores);<\/span>
<\/span>
exception<\/span>
when others then<\/span>
htp.print(‘Erro na pesquisa. Favor contactar o analista respons\u00e1vel. Erro: ‘ || sqlerrm);<\/span>
end;<\/span>
— dbms_output.put_line(‘Number of results: ‘ || l_count);<\/span>
<\/span>
v_sqldinamico := null;<\/span>
for i in 1 .. l_results.count loop<\/span>
<\/span>
— Preencher dados do ID do item para que eu possa retornar a pagina.<\/span>
<\/span>
— Mostrar os IDs <\/span>
— <\/span>
— htp.print(‘ FOLDER_ID ‘ || l_results(i).FOLDER_ID);<\/span>
— htp.print(‘ CAID ‘ || l_results(i).CAID);<\/span>
<\/span>
if v_sqldinamico is null then<\/span>
v_sqldinamico := ‘(‘ || l_results(i).id;<\/span>
if i = l_results.count then<\/span>
v_sqldinamico := v_sqldinamico || ‘) ‘;<\/span>
end if;<\/span>
else<\/span>
<\/span>
if i = l_results.count then<\/span>
v_sqldinamico := v_sqldinamico || ‘,’ || l_results(i).id || ‘) ‘;<\/span>
else<\/span>
v_sqldinamico := v_sqldinamico || ‘,’ || l_results(i).id;<\/span>
end if;<\/span>
<\/span>
end if;<\/span>
<\/span>
end loop;<\/span>
<\/span>
—<\/span>
— Mostrar os ids de pesquisa<\/span>
—<\/span>
<\/span>
–htp.print(v_sqldinamico);<\/span>
<\/span>
if nvl(l_results.count, 0) > 0 then<\/span>
<\/span>
— agora filtrar tb a categoria com os itens e paginas. <\/span>
— Montar esta consulta dinamica para conseguir<\/span>
— listar ASSUNTO x TEMA x resultados.<\/span>
<\/span>
v_sql := ‘ ‘;<\/span>
<\/span>
v_sql := v_sql ||<\/span>
‘ select rownum linha, substr(categoria.descricao, ‘;<\/span>
v_sql := v_sql || ‘1, ‘;<\/span>
v_sql := v_sql || ‘ decode(instr(categoria.descricao, ‘ || ”” || ‘-‘ || ”” ||<\/span>
‘ ), ‘;<\/span>
v_sql := v_sql || ‘ 0,’;<\/span>
v_sql := v_sql || ‘ 100,’;<\/span>
v_sql := v_sql || ‘ instr(categoria.descricao, ‘ || ”” || ‘-‘ || ”” ||<\/span>
‘) – 2)) tema,’;<\/span>
v_sql := v_sql || ‘ categoria.descricao assunto,’;<\/span>
v_sql := v_sql || ‘ a.display_name titulo,’;<\/span>
v_sql := v_sql || ‘ a.name nome,’;<\/span>
<\/span>
v_sql := v_sql || ‘ case’;<\/span>
v_sql := v_sql || ‘ when a.ITEMTYPE = ‘ || ”” || ‘basefile’ || ”” ||<\/span>
‘ then’;<\/span>
v_sql := v_sql || ‘ a.url’;<\/span>
v_sql := v_sql || ‘ when a.ITEMTYPE = ‘ || ”” || ‘baseimage’ || ”” ||<\/span>
‘ then’;<\/span>
v_sql := v_sql || ‘ a.url’;<\/span>
v_sql := v_sql || ‘ when a.ITEMTYPE = ‘ || ”” || ‘baseimagemap’ || ”” ||<\/span>
‘ then’;<\/span>
v_sql := v_sql || ‘ a.url’;<\/span>
v_sql := v_sql || ‘ when a.ITEMTYPE = ‘ || ”” ||<\/span>
‘baseloginlogout’ || ”” || ‘ then’;<\/span>
v_sql := v_sql || ‘ a.url’;<\/span>
v_sql := v_sql || ‘ when a.ITEMTYPE = ‘ || ”” || ‘basemaplink’ || ”” ||<\/span>
‘ then ‘;<\/span>
v_sql := v_sql || ‘ a.url’;<\/span>
v_sql := v_sql || ‘ when a.ITEMTYPE = ‘ || ”” || ‘basepagelink’ || ”” ||<\/span>
‘ then’;<\/span>
v_sql := v_sql || ‘ a.url’;<\/span>
v_sql := v_sql || ‘ when a.ITEMTYPE = ‘ || ”” || ‘basepagepath’ || ”” ||<\/span>
‘ then’;<\/span>
v_sql := v_sql || ‘ a.url’;<\/span>
v_sql := v_sql || ‘ when a.ITEMTYPE = ‘ || ”” || ‘baseplsql’ || ”” ||<\/span>
‘ then’;<\/span>
v_sql := v_sql || ‘ a.url’;<\/span>
v_sql := v_sql || ‘ when a.ITEMTYPE = ‘ || ”” || ‘baseportallink’ || ”” ||<\/span>
‘ then’;<\/span>
v_sql := v_sql || ‘ a.url’;<\/span>
v_sql := v_sql || ‘ when a.ITEMTYPE = ‘ || ”” ||<\/span>
‘baseportletinstance’ || ”” || ‘ then’;<\/span>
v_sql := v_sql || ‘ a.url’;<\/span>
v_sql := v_sql || ‘ when a.ITEMTYPE = ‘ || ”” || ‘basesearch’ || ”” ||<\/span>
‘ then’;<\/span>
v_sql := v_sql || ‘ a.url’;<\/span>
v
\n_sql := v_sql || ‘ when a.ITEMTYPE = ‘ || ”” || ‘basesmarttext’ || ”” ||<\/span>
‘ then’;<\/span>
— v_sql := v_sql ||<\/span>
— ‘ bhtrans_publico.FRETORNAURLPAGE(substr(a.text, 1, 350), pgs.id, pg.name)’;<\/span>
v_sql := v_sql ||<\/span>
‘ bhtrans_publico.FRETORNAURL(substr(a.text, 1, 350), FRETORNAPGIDPUBLICA(a.id,’ ||<\/span>
l_pggroups(1) || ‘), ‘ || ”” || v_pgname || ”” || ‘) ‘;<\/span>
<\/span>
v_sql := v_sql || ‘ when a.ITEMTYPE = ‘ || ”” || ‘basetab’ || ”” ||<\/span>
‘ then’;<\/span>
v_sql := v_sql || ‘ a.url’;<\/span>
v_sql := v_sql || ‘ when a.ITEMTYPE = ‘ || ”” || ‘basetext’ || ”” ||<\/span>
‘ then’;<\/span>
v_sql := v_sql ||<\/span>
‘ bhtrans_publico.FRETORNAURL(substr(a.text, 1, 350), FRETORNAPGIDPUBLICA(a.id,’ ||<\/span>
l_pggroups(1) || ‘), ‘ || ”” || v_pgname || ”” || ‘) ‘;<\/span>
— ‘ bhtrans_publico.FRETORNAURLPAGE(substr(a.text, 1, 350), pgs.id, pg.name)’;<\/span>
<\/span>
v_sql := v_sql || ‘ when a.ITEMTYPE = ‘ || ”” || ‘baseurl’ || ”” ||<\/span>
‘ then’;<\/span>
v_sql := v_sql || ‘ a.url ‘;<\/span>
v_sql := v_sql || ‘ end DESCRICAO,’;<\/span>
<\/span>
—<\/span>
— TEMPORARIA<\/span>
— Solucao para retornar a pagina do portal PUBLICO<\/span>
— e nao a do portal LIB<\/span>
<\/span>
— v_sql := v_sql ||<\/span>
— ‘ a.publish_date DATADEPUBLICACAO, bhtrans_publico.FRETORNAURLPAGE(substr(a.text, 1, 100), pgs.id, pg.name) URLPAGE ‘;<\/span>
<\/span>
v_sql := v_sql ||<\/span>
‘ a.publish_date DATADEPUBLICACAO, bhtrans_publico.FRETORNAURL(substr(a.text, 1, 100), FRETORNAPGIDPUBLICA(a.id,’ ||<\/span>
l_pggroups(1) || ‘), ‘ || ”” || v_pgname || ”” ||<\/span>
‘) URLPAGE ‘;<\/span>
<\/span>
v_sql := v_sql || ‘ from portal.wwsbr_all_items a,’;<\/span>
v_sql := v_sql || ‘ portal.WWSBR_ALL_CONTENT_AREAS pg,’;<\/span>
v_sql := v_sql || ‘ portal.wwsbr_user_folders pgs,’;<\/span>
v_sql := v_sql || ‘ (select c.id,’;<\/span>
v_sql := v_sql || ‘ substr(replace(SYS_CONNECT_BY_PATH(‘ || ”” || ‘ ‘ || ”” ||<\/span>
‘ || c.display_name,’;<\/span>
v_sql := v_sql || ‘ ‘ || ”” ||<\/span>
‘; -‘ || ”” || ‘),’;<\/span>
v_sql := v_sql || ‘ ‘ || ”” || ‘;’ || ”” || ‘,’;<\/span>
v_sql := v_sql || ‘ null),’;<\/span>
v_sql := v_sql || ‘ 4) as descricao ‘;<\/span>
v_sql := v_sql || ‘ from portal.WWSBR_ALL_CATEGORIES c ‘;<\/span>
v_sql := v_sql ||<\/span>
‘ where c.language = Portal.wwctx_api.get_nls_language()’ ||<\/span>
‘ and c.id in ‘ ||<\/span>
‘ (select distinct x.category_id<\/span>
from portal.wwsbr_all_items x<\/span>
where x.id in ‘ || v_sqldinamico || ‘)’;<\/span>
<\/span>
v_sql := v_sql || ‘ start with c.PARENTID = 0’;<\/span>
v_sql := v_sql || ‘ connect by prior c.ID = c.PARENTID’;<\/span>
v_sql := v_sql || ‘ and prior c.CAID = c.CAID’;<\/span>
v_sql := v_sql || ‘ and prior c.language = c.language’ ||<\/span>
‘ ) categoria,’;<\/span>
v_sql := v_sql || ‘ portal.WWSBR_ALL_CATEGORIES d ‘;<\/span>
v_sql := v_sql || ‘ where a.category_id = categoria.id’;<\/span>
v_sql := v_sql ||<\/span>
‘ and a.language = Portal.wwctx_api.get_nls_language()’;<\/span>
v_sql := v_sql || ‘ and a.caid = pg.id’;<\/span>
v_sql := v_sql || ‘ and a.caid = pgs.caid’;<\/span>
v_sql := v_sql || ‘ and pgs.caid = pg.id’;<\/span>
v_sql := v_sql || ‘ and a.FOLDER_ID = pgs.id’;<\/span>
v_sql := v_sql || ‘ and a.active = 1’;<\/span>
v_sql := v_sql || ‘ and a.is_current_version = 1’;<\/span>
<\/span>
v_sql := v_sql || ‘ and a.category_id = d.id’;<\/span>
v_sql := v_sql || ‘ and a.id in ‘ || v_sqldinamico;<\/span>
<\/span>
v_sql := v_sql || ‘ order by 2, 3 ‘;<\/span>
<\/span>
–htp.print(‘CONSULTA: ‘ || v_SQL);<\/span>
<\/span>
open cCursor for v_SQL;<\/span>
<\/span>
— Mostrar o SQL pronto <\/span>
htp.print(‘CONSULTA: ‘ || v_SQL);<\/span>
<\/span>
v_temaanterior := ‘ ‘;<\/span>
loop<\/span>
<\/span>
fetch cCursor<\/span>
into v_contador, v_tema, v_assunto, v_titulo,v_nome, v_descricao, v_DATADEPUBLICACAO, v_urlpage;<\/span>
<\/span>
exit when cCursor%notfound;<\/span>
if v_urlpage is null then<\/span>
<\/span>
if v_temaanterior v_tema then<\/span>
v_temaanterior := v_tema;<\/span>
htp.print(‘<\/p>\n‘);
htp.print(‘ <\/span><\/p>\n\n‘);
htp.print(‘ <\/span><\/p>\n
CATEGORIA: ‘ || v_tema || ‘<\/td>\n

‘);
htp.print(‘ <\/span><\/tr>\n

‘);
htp.print(‘<\/span><\/tbody>\n<\/table>\n

‘);<\/span>
end if;<\/span>
<\/span>
htp.print(‘<\/p>\n‘);
htp.print(‘ <\/span><\/p>\n\n‘);
htp.print(‘ <\/span><\/p>\n‘);
htp.print(‘ <\/span><\/p>\n‘);
<\/span>
htp.print(‘ <\/span><\/p>\n
‘ || v_contador || ‘ –<\/td>\n

‘);
htp.print(‘ <\/span><\/p>\n

‘ || nvl(v_titulo,v_nome) || ‘<\/td>\n

‘);
<\/span>
— v_returnurl:= ‘‘ || v_returnurl || ‘<\/a>‘;<\/span>
<\/span>
<\/span>
htp.print(‘ <\/span><\/p>\n

<\/td>\n

‘);
htp.print(‘ <\/span><\/tr>\n

‘);
<\/span>
htp.print(‘ <\/span><\/p>\n

<\/td>\n

‘);
<\/span>
<\/span>
<\/span>
htp.print(‘ <\/span><\/p>\n

\n

‘ || FRETORNANEGRITO(v_descricao, trim(p_ATTRIBUTE_TEXT))|| ‘<\/p>\n

‘);
htp.print(‘ <\/span><\/td>\n

‘);
htp.print(‘ <\/span><\/p>\n

<\/td>\n

‘);
htp.print(‘ <\/span><\/tr>\n

‘);
htp.print(‘ <\/span><\/p>\n

<\/td>\n

‘);
htp.print(‘ <\/span><\/p>\n

Publicado em ‘ ||
to_char(v_DATADEPUBLICACAO, ‘DD\/MM\/YYYY’) || ‘ <\/span><\/td>\n

‘);
htp.print(‘ <\/span><\/p>\n

<\/td>\n

‘);
htp.print(‘ <\/span><\/tr>\n

‘);
htp.print(‘<\/span><\/tbody>\n<\/table>\n

‘);<\/span>
<\/span>
end if;<\/span>
— DBMS_OUTPUT.PUT_LINE(trim(v_tema));<\/span>
<\/span>
end loop;<\/span>
<\/span>
close cCursor;<\/span>
<\/span>
else<\/span>
htp.print(‘N\u00e3o foram encontrados registros em sua busca, favor especifique novos valores.’);<\/span>
<\/span>
end if;<\/span>
end if;<\/span><\/p>\n

exception<\/span>
when others then<\/span>
htp.print(‘Error in Search Execution!’);<\/span>
dbms_output.put_line(‘Error in Search Execution!’);<\/span>
dbms_output.put_line(sqlerrm);<\/span>
<\/span>
end;<\/span><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

Link da documentacao:http:\/\/www.oracle.com\/technology\/products\/ias\/portal\/html\/plsqldoc\/pldoc1014\/wwsrc_api.html Temos uma funcao para efetuar uns testes: create or replace procedure search_results(searchterm varchar2, score number) as x varchar2(100); y number; l_results wwsrc_api.items_result_array_type; l_count number; l_scores wwsrc_api.number_list_type;begin x := searchterm; y := score; l_results := wwsrc_api.item_search( p_mainsearch => … Continue lendo →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[339],"tags":[],"class_list":["post-211","post","type-post","status-publish","format-standard","hentry","category-wwsrc_api-item_search"],"_links":{"self":[{"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/posts\/211","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=211"}],"version-history":[{"count":0,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/posts\/211\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=211"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=211"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=211"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}