CODEWITHSUNDEEP
sqloracle-databaseclob
Nirmalya
Nirmalya

Reputation: 205

Storing data from database in a CLOB object using EXECUTE IMMEDIATE BULK COLLECT INTO

I am trying to write a procedure that will populate a CLOB object with certain data from database.

I have a SQL select query stored in a table. I am fetching that query and executing it in the procedure to get a column of data (supplier id). The reason why I have the query stored in DB is because it is subject to change frequently but it will provide the same column as its result. Now I am trying to put this data in a CLOB object and this is where I am stuck. I am unable to get the supplier ids from the v_report_type in the CLOB object.

Can someone please guide me to write the proper code.

Below is the snippet of what I wrote in the procedure.

DECLARE

TYPE report_type IS TABLE OF supplier.supplier_id%TYPE
v_report_type report_type;

v_query1 varchar(4000);

v_report_clob CLOB;


BEGIN

v_report_clob:= null;

select query1 into v_query1 from report_query where report_id = 20;

EXECUTE IMMEDIATE v_query1 BULK COLLECT INTO v_report_type;


v_report_clob := v_report_clob||v_report_type;


//Unrelated code here

END;

Regards Nirmalya

Upvotes: 0

Views: 976

Answers (1)

kpater87
kpater87

Reputation: 1270

The main problem that I see in your code is that you are trying to concatenate CLOB with the collection data type. You can try to replace the line with concatenation by:

v_report_clob := v_report_clob || v_report_type(1); --always take only first value

or

FOR i IN 1..v_report_type.COUNT LOOP
  v_report_clob := v_report_clob || v_report_type(i); --concatenate all values into single CLOB, here you can add also e.g. extra spaces between values
END LOOP;

Upvotes: 1

Related Questions