DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Display 'No data to display' in table

User_JV7C1Feb 12 2018 — edited Feb 13 2018

I am on JET version 3.2 and i am using a html table (not ojTable) to display the data in an array. I preferred using html table, as my requirement is to render the column headers as link, with a menu to be displayed on the click of the same, update the header with the item selected from the menu. This was easier to be implemented with a html table, so i went with it. Now my question is how do i display the text 'No data to display' when there are no rows in the array? The array will get updated everytime the user clciks on the header and chooses a different value.

I tried the following approach:

<table>

<thead>

<tr>

//code to display the column header as link

</th>

//code to display the column header as link

</th>

</tr>

</thead>

//displayData is a knockout observable maintained in the view model

//displayData is set to 'true' if the length of 'arrayData' is > 0, else its set to false

<tr>

</tr>

</tbody>

</div>

<tr>

<td style="text-align: center;" >No data to display</td>

</tr>

</tbody>

</div>

</table>

View Model Code:

self.arrayData = ko.observableArray();

self.arrayDataLength = ko.computed(function() {

return self.arrayData.length ;

});

self.displayData = ko.computed(function() {

if (self.arrayData.length > 0) return true;

else

return false;

});

This approach is currently not working completely, in the sense, initially when the page is loaded and there is no data in the array, only the 'No data to display' is displayed. But once i update the headers (by clicking on the header link and selecting a different item), the array gets populated, now the newly added rows into the array are dispalyed in the table, but the 'No data to display' text is not getting hidden, though i am controlling the display with a flag which is a knockout observable. Can someone please point out why?

I didnt want to have two tables, one for showing data and other for no data , as the header section is going to be the same and its not a hardcoded header, hence it would make the code redundant. Can someone please advise if this is a right approach or not?

This post has been answered by Andrew Bennett on Feb 12 2018

Jump to Answer

Solomon Yakobson

Answer

You can use VARRAY:

create or replace procedure sp_main_target(iv_req_id IN sys.OdciNumberList)
is
lv_count number(10);
begin
for v_i in 1..iv_req_id.count loop
select count(1) into lv_count from staging where req_id = iv_req_id(v_i);
if lv_count > 0 then
dbms_output.put_line('Insertion into target table');
MERGE INTO target_tab t
   USING (SELECT key_id, inv_id, i_name, req_id FROM staging
   WHERE req_id = iv_req_id(v_i)) S
   ON (t.inv_id = S.inv_id)
   WHEN MATCHED THEN UPDATE SET
   t.key_id = s.key_id,
   t.i_name = s.i_name,
   t.req_id = s.req_id
   WHEN NOT MATCHED THEN INSERT (t.key_id,t.inv_id,t.i_name,t.req_id)
     VALUES (s.key_id,s.inv_id,s.i_name,s.req_id);
else
dbms_output.put_line('Request id is not available');
end if;
end loop;
commit;
end sp_main_target;
/

Procedure created.

SQL> exec sp_main_target(sys.OdciNumberList(1,2,3));
Insertion into target table
Insertion into target table
Request id is not available


PL/SQL procedure successfully completed.


SQL> select * from target_tab;


    KEY_ID     INV_ID I_NAME                             REQ_ID
---------- ---------- ------------------------------ ----------
       110       1001 test1                                   1
       111       1002 test2                                   2


SQL>

SY.

Marked as Answer by Albert Chao · Oct 25 2021

Albert Chao

@solomon-yakobson Can we do this using for loop cursor?

Solomon Yakobson

We could but why call SQL from PL/SQL if all can be done in PL/SQL?

create or replace procedure sp_main_target(iv_req_id IN sys.OdciNumberList)
is
lv_count number(10);
begin
for v_rec in (select * from table(iv_req_id)) loop
select count(1) into lv_count from staging where req_id = v_rec.column_value;
if lv_count > 0 then
dbms_output.put_line('Insertion into target table');
MERGE INTO target_tab t
   USING (SELECT key_id, inv_id, i_name, req_id FROM staging
   WHERE req_id = v_rec.column_value) S
   ON (t.inv_id = S.inv_id)
   WHEN MATCHED THEN UPDATE SET
   t.key_id = s.key_id,
   t.i_name = s.i_name,
   t.req_id = s.req_id
   WHEN NOT MATCHED THEN INSERT (t.key_id,t.inv_id,t.i_name,t.req_id)
     VALUES (s.key_id,s.inv_id,s.i_name,s.req_id);
else
dbms_output.put_line('Request id is not available');
end if;
end loop;
commit;
end sp_main_target;
/

Procedure created.

SQL> exec sp_main_target(sys.OdciNumberList(1,2,3));
Insertion into target table
Insertion into target table
Request id is not available

PL/SQL procedure successfully completed.

SQL> select * from target_tab;

    KEY_ID     INV_ID I_NAME                             REQ_ID
---------- ---------- ------------------------------ ----------
       110       1001 test1                                   1
       111       1002 test2                                   2

SQL>

SY.

Albert Chao

@solomon-yakobson Actually this stored procedure I am going to invoke from the front end so will this work? Front end wherein I will provide the request-id and process will happen. Or do we have to use connect by caluse for comma-separated inputs?

Solomon Yakobson

Front end is very generic statement? Is it Java, .Net, perl, python....? Anyway, you can use OracleArray in JDBC.
SY.

Albert Chao

@solomon-yakobson Thanks. Got it

1 - 6

Added on Feb 12 2018

#oracle-jet, #table

2 comments

2,391 views

DevOps, CI/CD and Automation

Display 'No data to display' in table

Comments

Post Details