Skip to Main Content

SQL & PL/SQL

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!

select multiple rows & columns from dynamic sql query result in Oracle 19c

User_CSOQUSep 6 2020 — edited Sep 8 2020

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

Comments
Post Details
Added on Sep 6 2020
12 comments
2,931 views