Please help me to select multiple rows & columns from dynamic sql query result?
I am showing this code as sample from my very large store procedure.
Insite my store procedure dynamic query is also large & where condition has also my condition.
Explain:
set serveroutput on;
declare
V_query VARCHAR2(4000);
V_Client_Name varchar2(100);
V_Start_modify_date date;
V_End_modify_date date;
begin
V_Client_Name :='Adams Smith';
V_Start_modify_date :='01-JAN-2020';
V_End_modify_date :='06-SEP-2020';
V_query := 'SELECT Client_ID "Client ID",Client_Code "Client Code",Phone_No "Phone No", Bill_No "Bill No",Client_Name "Client Name",Modify_Date "Modify Date"
FROM Client_Table';
V_query := V_query || ' Where modify_date between ''' || TO_CHAR(V_Start_modify_date,'DD-MON-YYYY') || ''' and ''' || TO_CHAR(V_End_modify_date,'DD-MON-YYYY') || ''' ' ;
V_query := V_query || ' and Client_Name = ''' || V_Client_Name || ''' ' ;
dbms_output.put_line(V_query);
---- Note
--------how to select result from V_query ?
end;
/
Sample Data:
create table client_table
(Client_ID number(10),Client_Code varchar2(10),Phone_No varchar2(20),Bill_No varchar2(20),Client_Name varchar(100),Table_Name varchar2(128),modify_date date);
insert into client_table (CLIENT_ID, CLIENT_CODE, PHONE_NO, BILL_NO, CLIENT_NAME, TABLE_NAME,modify_date)
values (10,'10-002','002-12345678','009-002-12345','Adams Smith','tbl_client_tg','02-FEB-2020');
insert into client_table (CLIENT_ID, CLIENT_CODE, PHONE_NO, BILL_NO, CLIENT_NAME, TABLE_NAME,modify_date)
values (20,'20-005','005-22345678','001-005-70712','Adams Smith','tbl_client_tg','05-APR-2020');
insert into client_table (CLIENT_ID, CLIENT_CODE, PHONE_NO, BILL_NO, CLIENT_NAME, TABLE_NAME,modify_date)
values (30,'30-007','007-22345678','001-007-82390','Adams Arnold','tbl_master','17-MAY-2020');
commit;
------ Output from dbms_output.put_line(V_query);
SELECT Client_ID "Client ID",Client_Code "Client Code",Phone_No "Phone No", Bill_No "Bill No",Client_Name "Client Name",Modify_Date "Modify Date"
FROM Client_Table Where modify_date between '01-JAN-2020' and '06-SEP-2020' and Client_Name = 'Adams Smith';
My expected Output: (from V_query result is below)
Client ID Client Code Phone No Bill No Client Name Modify Date
10 10-002 002-12345678 009-002-12345 Adams Smith 02-FEB-2020
20 20-005 005-22345678 001-005-70712 Adams Smith 05-APR-2020
Please help me.
Thanks
Solaiman