java - Oracle cursor not returning the complete resultset -


this post in continuation problem solved generating dynamic sql. for loop inside cursor oracle

below pl/sql executing generates dynamic sql looping through type created mentioned in post above.

procedure p_xxx_xx(     p_id_i in varchar2,     p_error_code_o out varchar2,     p_error_message_o out varchar2,     pcur_xxx out sys_refcursor )     l_array siebel.intl_crm.t_array;   strselect_statement  varchar2(20000);   begin   l_array := f_split_string(p_company_id_i);      strselect_statement :=      'select contact.row_id contact_id,          contact.fst_name contact_first_name,          contact.last_name contact_last_name,         contact.cell_ph_num cell_phone,         contact.email_addr email_address,         party.party_id company_id,         addr.addr address_line_1,         addr.addr_line_2 address_line_2,         addr.addr_line_3 address_line_3,         addr.city,         addr.state state_province,         addr.zipcode,         addr.country,         null raw_most_recent_activity_dt,       contact.work_ph_num work_phone,       contact.alt_email_addr alternate_email,       contact.x_preferred_phone preferred_phone,       contact.x_preferred_email preferred_email,       contact.suppress_email_flg email_flag,       contact.fax_ph_num fax_phone,        null most_recent_activity_dt,        null mra_company_id,        null mra_company_name,       null accnt_val_cd       s_party_per party        join s_contact contact        on party.person_id = contact.row_id        left join siebel.s_addr_per addr       on contact.pr_per_addr_id = addr.row_id       party.reference_type_cd ='''||'current employee'        ||'''and upper(trim(contact.cust_stat_cd)) ='''||'current'        ||'''and party.party_id in (';       in l_array.first .. l_array.last loop       strselect_statement := strselect_statement ||                              '''' || l_array(i) || ''',';       end loop;       -- rid of unwanted trailing comma       strselect_statement := substr(strselect_statement, 1,                                   length(strselect_statement)-1);            -- add right parentheses close in list       strselect_statement := strselect_statement || ')';       dbms_output.put_line('query: '||strselect_statement);       -- open cursor       open pcur_company_contacts_new strselect_statement;     p_error_code_o     := sqlcode;     p_error_message_o  := sqlerrm;   end p_company_contact_new;

below sql generated after looping through array

    select contact.row_id contact_id,              contact.fst_name contact_first_name,              contact.last_name contact_last_name,             contact.cell_ph_num cell_phone,             contact.email_addr email_address,             party.party_id company_id,             addr.addr address_line_1,             addr.addr_line_2 address_line_2,             addr.addr_line_3 address_line_3,             addr.city,             addr.state state_province,             addr.zipcode,             addr.country,             null raw_most_recent_activity_dt,           contact.work_ph_num work_phone,           contact.alt_email_addr alternate_email,           contact.x_preferred_phone preferred_phone,           contact.x_preferred_email preferred_email,           contact.suppress_email_flg email_flag,           contact.fax_ph_num fax_phone,            null most_recent_activity_dt,            null mra_company_id,            null mra_company_name,           null accnt_val_cd           s_party_per party            join s_contact contact            on party.person_id = contact.row_id            left join siebel.s_addr_per addr           on contact.pr_per_addr_id = addr.row_id           party.reference_type_cd ='current employee'and upper(trim(contact.cust_stat_cd)) ='current'and party.party_id in ('3-9ttcj2','1-19g1tz','1-b3-4924','1-b1-2288','1-160jf','1-1qff2l', '1-az-1282','1-b5-2052','1-ujhq9','3-c75csw','3-c7gdyr',);

when run generated sql in editor, fetches resultset id's passed. when run pl/sql block, returns portion of resultset not complete set. calling procedure java , when test ids separately, see data in database. guessing issue returning cursor. did face similar kind of issue?

thanks in advance!


Comments

Post a Comment

Popular posts from this blog

Receive udp packets in android gstreamer -

i using eclipse juno in windows 7. doing programming on android 4.2 platform. want receive udp packets in gstreamer application. giving uri udp://127.0.0.1:1234 , sending file vlc player same computer. emulator giving message could not get/set setting from/on resource while in case of rtp, giving message no uri implementer found rtp ideas how receive streaming media in android?
Read more

php - Extract Text from HTTP referer -

i using following code show referer link page on website. how can modify code shows part of link. i.e if website url www.example.com/?s=printing want extract printing. , should happen if format www.example.com/?s=aaa , not if format else www.example.com/printing. code: <?php session_start(); if ( !isset( $_session["origurl"] ) ) $_session["origurl"] = $_server["http_referer"]; echo $_session["origurl"] ?> i figured out , following code works: <?php session_start(); if ( !isset( $_session["origurl"] ) ) $_session["origurl"] = $_server["http_referer"]; $mysearchterm = $_server["http_referer"]; $whatiwant = substr($mysearchterm, strpos($mysearchterm, "=") +1); echo $whatiwant; ?>
Read more

c# - ASP.NET MVC Enumerable to delimited HTML in Razor -

this question has answer here: looping through list in razor , adding separator between items 4 answers i discovered can use string.join(", ", string[] enumerable) turn list of strings single comma delimited string. similar produce example list of hyperlinks in razor view? instead of like: @foreach(var item in enumerable) { <a href="@item.url">@item.title</a> if(item != enumerable.last()) { <span>, </span> } } and if so, advisable or should stop being lazy? @html.raw(string.join("<span>, </span>", enumerable.select(item => string.format("<a href=\"{0}\">{1}</a>", item.url, item.title))))
Read more