Frank Danny
asked on
Why don't I see Below columns in my Stored Procedure under TMP_RS?
I am PL/SQL newbie.Can anyone please tell me,Why below columns under TMP_RS are not in my stored procedure i.e activity_search?I did run Stored procedure and observed under TMP_RS I see columns but I do not see anywhere in Stored Procedure/code, columns were set either by alias or anything.Am I missing something here?Below is TMP_RS results for below procedure
[b]procedure [/b]activity_search (
in_user_id in twp_user.id%type
,in_start_date in activity.activity_start_date%type
,in_end_date in activity.activity_end_date%type
,in_activity_type_ids in varchar2
,in_description in varchar2
,in_subject in varchar2
,in_tickers in varchar2
,in_user_name in varchar2
,in_department_ids in varchar2
,in_sort_by in varchar2
,in_folder_id in varchar2
,in_contact_folder_ids in varchar2
,in_contact_folder_flag in char
,in_company_folder_ids in varchar2
,in_company_folder_flag in char
,in_contact_email_flag in varchar2
,in_record_per_set in number
,in_attendee_id in number
,in_followup_start_date in activity.follow_up_date%type
,in_followup_end_date in activity.follow_up_date%type
,in_followup_user_ids in varchar2
,in_activity_subtype_ids in varchar2
,in_company_type_ids in varchar2
,in_coverage_ids in varchar2
,out_search_id out number
,out_retcode out number
,tmp_rs in out rs_cursor) is
l_sql_statement varchar2(32767) := null;
l_sql_statement_2 varchar2(32767) := null;
l_workaround_sql varchar2(32767);
tmp_cursor small_cursor;
l_activity_ids varchar2(32767);
tmp_twp_user_folder twp_user_folder%rowtype;
l_search_id number(12);
l_counter pls_integer := 0;
l_contact_ids varchar2(32767);
type result_rectype is table of number index by binary_integer;
activity_result_rectype result_rectype;
l_user_name varchar2(2000) := null;
l_activity_owner pls_integer := 0;
l_company_folder_flag char := null;
l_contact_folder_flag char := null;
begin
out_retcode := 0;
select seq_activity_search_result.nextval into out_search_id from dual;
-- 15NOV2003: For better performance, rewrite the following in_user_name condition
-- if in_user_name is not null then
-- l_user_name := format_user_name(in_user_name);
-- end if;
-- 15NOV2003: New for performance
if in_user_name is not null then
-- if instr(in_user_name, ',') = 0 then
-- l_user_name := null;
-- l_activity_owner := 1;
-- else
l_user_name := in_user_name;
l_activity_owner := 2;
-- end if;
end if;
/*
if in_user_name is not null then
if instr(in_username, ',')
select login into l_user_name from twp_user where id=in_user_id;
if upper(trim(l_user_name)) = upper(trim(in_user_name)) then
l_user_name := null;
l_activity_owner := 1;
else
l_user_name := format_user_name(in_user_name);
l_activity_owner := 2;
end if;
end if;
*/
ins_debug_stmt('AC','Procedure: Activity Search Start, Search ID: '||out_search_id||' l_use_rname: '||l_user_name||' in_user_name '||in_user_name);
/*
ins_debug_stmt('AC','start date: '||in_start_date);
ins_debug_stmt('AC','end date: '||in_end_date);
ins_debug_stmt('AC','activity_type_ids: '||in_activity_type_ids);
ins_debug_stmt('AC','description: '||in_description);
ins_debug_stmt('AC','sort by: '||in_sort_by);
ins_debug_stmt('AC','folder id: '||in_folder_id);
ins_debug_stmt('AC','contact folder ids: '||in_contact_folder_ids);
ins_debug_stmt('AC','contact folder flag: '||in_contact_folder_flag);
ins_debug_stmt('AC','company folder id: '||in_company_folder_ids);
ins_debug_stmt('AC','company folder flag: '||in_company_folder_flag);
ins_debug_stmt('AC','attendee id: '||in_attendee_id);
ins_debug_stmt('AC','subject: '||in_subject);
ins_debug_stmt('AC','tickers: '||in_tickers);
ins_debug_stmt('AC','username: '||in_user_name);
ins_debug_stmt('AC','department id: '||in_department_ids);
*/
-- Nothing was filter, only display 500 rows
if (in_start_date is null and in_end_date is null and in_activity_type_ids is null and in_description is null
and in_subject is null and in_tickers is null and in_user_name is null
and in_sort_by is null and in_folder_id = 0 and in_contact_folder_ids is null
and in_company_folder_ids is null
and in_attendee_id is null
and in_followup_start_date is null
and in_followup_end_date is null
and in_followup_user_ids is null
and in_activity_subtype_ids is null
and in_coverage_ids is null
and in_company_type_ids is null) then
select 'select * from '
||'(select a.id,0 company_id,0 contact_id,a.activity_start_date,a.last_update from activity a '
||' where a.department_id in ('|| in_department_ids ||')'
||' order by 4 desc nulls last, 5 desc) '
||' where rownum<501'
-- select 'select a.id, 0 company_id, 0 contact_id, a.activity_start_date, a.last_update from activity a '
-- ||' where rownum < 501 '
into l_sql_statement
from dual;
-- Only filter the activity type
elsif (in_start_date is null and in_end_date is null and in_activity_type_ids is not null and in_description is null
and in_subject is null and in_tickers is null and in_user_name is null
and in_sort_by is null and in_folder_id = 0 and in_contact_folder_ids is null
and in_company_folder_ids is null
and in_attendee_id is null
and in_followup_start_date is null
and in_followup_end_date is null
and in_followup_user_ids is null
and in_activity_subtype_ids is null
and in_coverage_ids is null
and in_company_type_ids is null) then
select 'select * from '
||'(select a.id,0 company_id,0 contact_id,a.activity_start_date,a.last_update from activity a '
||' where a.activity_type_plist_id in ('||in_activity_type_ids||')'
||' and a.department_id in ('|| in_department_ids ||')'
||' order by 4 desc nulls last, 5 desc) '
||' where rownum<501'
into l_sql_statement
from dual;
-- Filtering combination
else
select 'select distinct a.id,0 company_id,0 contact_id,a.activity_start_date,a.last_update from activity a'
||decode(in_tickers, null, null, ',activity_ticker at')
-- 15NOV2003: ||decode(in_user_name, null, null, ',twp_user tu')
-- ||decode(l_activity_owner, 0, null, 1, null, 2, ',twp_user tu')
||decode(in_folder_id, null, null, 0, null, ',twp_user_activity_folder fc')
||decode(in_contact_folder_ids
,null, decode(in_company_folder_ids, null, null, 0, null, ',contact_activity_if ca')
,0, null
,decode(in_contact_folder_flag
,null, ',contact_activity_if ca'
, 'N', ',contact_activity_if ca'
, 'Y', ',contact_activity_if ca,twp_user_contact_folder tccf'
)
)
||decode(in_company_folder_ids
,null, null
,0, null
,decode(in_company_folder_flag
,null, null
, 'N', null
, 'Y', ',twp_user_company_folder tcof'
)
)
||decode(in_coverage_ids
,null,null
,0, null
,', contact_activity_if ca2 ')
||decode(in_followup_user_ids
,null, null
,0, null
,', activity_followup af')
||decode(in_company_type_ids,
null, null,
0, null,
' ,contact_activity_if ctcaif, company_type_if ctif')
||' where 0=0'
||decode(in_folder_id, null, null, 0, null,' and a.id=fc.activity_id and fc.folder_id in ('||in_folder_id||')')
||decode(in_contact_folder_ids
,null, null
,0, null
,decode(in_contact_folder_flag
,null, ' and a.id=ca.activity_id and ca.contact_id in ('||in_contact_folder_ids||')'
,'N', ' and a.id=ca.activity_id and ca.contact_id in ('||in_contact_folder_ids||')'
,'Y', ' and a.id=ca.activity_id and ca.contact_id=tccf.contact_id and tccf.folder_id in ('||in_contact_folder_ids||')'
)
)
||decode(in_company_folder_ids
,null, null
,0, null
,decode(in_company_folder_flag
,null, ' and a.id=ca.activity_id and ca.company_id in ('||in_company_folder_ids||')'
,'N', ' and a.id=ca.activity_id and ca.company_id in ('||in_company_folder_ids||')'
,'Y', ' and a.id=ca.activity_id and ca.company_id=tcof.company_id and tcof.folder_id in ('||in_company_folder_ids||')'
)
)
--05152013 adding in ability to filter by coverage
||decode(in_coverage_ids
,null, null
,0, null
, ' and a.id = ca2.activity_id and ca2.company_id in (select distinct company_id from company_coverage where company_id is not null and twp_user_id in (' || in_coverage_ids ||'))'
)
||decode(in_tickers, null, null, ' and a.id = at.activity_id and at.ticker in ('||in_tickers||')')
-- 15NOV2003: ||decode(in_user_name, null, null, ' and tu.id = a.owner_twp_user_id and tu.login in ('||l_user_name||')')
-- replace the above line in the following decode statement
||decode(l_activity_owner
,0, null
,1, ' and a.owner_twp_user_id = '||in_user_id
,2, ' and a.owner_twp_user_id in ('||l_user_name||')'
)
||decode(in_description, null, null, ' and upper(a.comments) like '||'''%'||upper(in_description)||'%''')
||decode(in_subject, null, null, ' and upper(a.subject) like '||'''%'||upper(in_subject)||'%''')
||decode(in_activity_type_ids, null, null, ' and a.activity_type_plist_id in ('||in_activity_type_ids||')')
||decode(in_activity_subtype_ids, null, null, ' and a.activity_sub_type_plist_id in ('||in_activity_subtype_ids||')')
||decode(in_department_ids, null, null, ' and (a.department_id in ('||in_department_ids||') and a.department_id is not null)')
||decode(in_start_date, null, null,
decode(in_end_date, null, ' and trunc(activity_start_date) =''' || in_start_date || '''',
' and trunc(a.activity_start_date) >= ''' || in_start_date || ''' and trunc(a.activity_start_date) <= ''' || in_end_date || ''''))
||decode(in_followup_start_date
,null, null
,decode(in_followup_end_date
,null, ' and trunc(a.follow_up_date) = ''' || in_followup_start_date || ''''
,' and trunc(a.follow_up_date) >= ''' || in_followup_start_date || ''' and trunc(a.follow_up_date) <= ''' || in_followup_end_date || ''''))
||decode(in_followup_user_ids
,null, null
,' and a.id = af.activity_id and af.twp_user_id in (' || in_followup_user_ids || ')')
||decode(in_company_type_ids,
null, null,
0, null,
' and ctcaif.activity_id = a.id and ctcaif.company_id = ctif.company_id and ctif.company_type_plist_id in (' || in_company_type_ids || ')')
into l_sql_statement
from dual;
end if;
if in_attendee_id is not null then
select 'select a.id,0 company_id,0 contact_id,a.activity_start_date,a.last_update from activity a,twp_user_activity_if tuaf'
||decode(in_tickers, null, null, ',activity_ticker at')
||decode(in_contact_folder_ids,null,null,0,null,',contact_activity_if ca' )
||decode(in_followup_user_ids
,null, null
,0, null
,', activity_followup af')
-- ||decode(in_folder_id, null, null, 0, null, ',twp_user_activity_folder fc')
||' where 0=0 and tuaf.activity_id=a.id and tuaf.twp_user_id in ('||in_attendee_id||')'
-- ||decode(in_folder_id, null, null, 0, null,' and a.id=fc.activity_id and fc.folder_id in ('||in_folder_id||')')
||decode(in_contact_folder_ids,null,null,0,null,' and a.id=ca.activity_id and ca.contact_id in ('||in_contact_folder_ids||')')
||decode(in_tickers, null, null, ' and a.id = at.activity_id and at.ticker in ('||in_tickers||')')
||decode(in_description, null, null, ' and upper(a.comments) like '||'''%'||upper(in_description)||'%''')
||decode(in_subject, null, null, ' and upper(a.subject) like '||'''%'||upper(in_subject)||'%''')
||decode(in_activity_type_ids, null, null, ' and a.activity_type_plist_id in ('||in_activity_type_ids||')')
||decode(in_activity_subtype_ids, null, null, ' and a.activity_sub_type_plist_id in ('||in_activity_subtype_ids||')')
||decode(in_department_ids, null, null, ' and (a.department_id in ('||in_department_ids||') and a.department_id is not null)')
||decode(in_start_date, null, null,
decode(in_end_date, null, ' and trunc(activity_start_date) =''' || in_start_date || '''',
' and trunc(a.activity_start_date) >= ''' || in_start_date || ''' and trunc(a.activity_start_date) <= ''' || in_end_date || ''''))
||decode(in_followup_start_date
,null, null
,decode(in_followup_end_date
,null, ' and trunc(a.follow_up_date) = ''' || in_followup_start_date || ''''
,' and trunc(a.follow_up_date) >= ''' || in_followup_start_date || ''' and trunc(a.follow_up_date) <= ''' || in_followup_end_date || ''''))
||decode(in_followup_user_ids
,null, null
,' and a.id = af.activity_id and af.twp_user_id in (' || in_followup_user_ids || ')')
into l_sql_statement_2
from dual;
end if;
if l_sql_statement_2 is not null then
l_sql_statement := l_sql_statement ||' union '||l_sql_statement_2;
end if;
-- l_sql_statement := l_sql_statement ||' order by '||nvl(in_sort_by, g_default_sort_by);
l_sql_statement := l_sql_statement ||' order by 4 desc nulls last, 5 desc';
ins_debug_stmt('AC','Activity Search, SQL: '||l_sql_statement);
l_workaround_sql := l_sql_statement;
open tmp_cursor for l_workaround_sql;
ins_debug_stmt('AC','Begin Build Statement');
build_statement (in_user_id
,out_search_id
,out_retcode
,tmp_cursor);
ins_debug_stmt('AC','After Build Statement');
-- If the user wants to get the email address for all the contacts that were part of activities that matched the search criteria
-- just return a cursor of the email addresses
if in_contact_email_flag = 'Y' then
l_sql_statement := 'select distinct c.email_addr from contact c, contact_activity_if ca, activity_search_result asr'
||' where c.id = ca.contact_id and ca.activity_id = asr.activity_id and asr.search_id='||out_search_id||' and c.email_addr is not null';
ins_debug_stmt('AC','Final run SQL: '||l_sql_statement);
open tmp_rs for l_sql_statement;
else
run_query (
out_search_id
,out_retcode
,1
,in_record_per_set
,tmp_rs);
end if;
ins_debug_stmt('AC','Finished run query');
exception
when OTHERS then
out_retcode := -1;
end;
--
ASKER
@slightwv Thank you! I am using oracle sql developer and yes you are right.. I am asking where the headers are coming from?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>I am asking where the headers are coming from?
OK, are you assigning a value to tmp_rs before clicking on whatever you are clicking on?
OK, are you assigning a value to tmp_rs before clicking on whatever you are clicking on?
ASKER
@slightwv I am not assigning any value to tmp_rs
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you @slightwv...........I found headers in run_query
ASKER
Thank you
My guess is you are asking where the headers like PARENT_ACTIVITY_ID are coming from.
Just a guess because I don't have the tool you are looking at to test with but:
The OUT parameter is a cursor and defined as "IN OUT". My guess is it is getting the values from the INPUT cursor that is being passed in.