8-6. Returning Data from a Dynamic Query into a Record
Problem
You are writing a block of code that will need to use dynamic SQL to execute a query because the exact SQL string is not known until runtime. The query needs to return the entire contents of the table row so that all columns of data can be used. You want to return the columns into a record variable.
Solution
Create a native dynamic SQL query to accommodate the SQL string that is unknown until runtime. FETCH the data using BULK COLLECT into a table of records. Our solution example shows rows from the jobs table being fetched into records, after which the individual record columns of data can be worked with. The following code block demonstrates this technique:
CREATE OR REPLACE PROCEDURE obtain_job_info(min_sal NUMBER DEFAULT 0,
max_sal NUMBER DEFAULT 0)
AS
sql_text VARCHAR2(1000);
TYPE job_tab IS TABLE OF jobs%ROWTYPE;
job_list job_tab;
job_elem jobs%ROWTYPE;
max_sal_temp NUMBER;
filter_flag BOOLEAN := FALSE;
cursor_var NUMBER;
TYPE cur_type IS REF CURSOR;
cur cur_type;
BEGIN
sql_text := 'SELECT * ' ||
'FROM JOBS WHERE ' ||
'min_salary >= :min_sal ' ||
'and max_salary <= :max_sal';
IF max_sal = 0 THEN
SELECT max(max_salary)
INTO max_sal_temp
FROM JOBS;
ELSE
max_sal_temp := max_sal;
END IF;
OPEN cur FOR sql_text USING min_sal, max_sal_temp;
FETCH cur BULK COLLECT INTO job_list;
CLOSE cur;
FOR i IN job_list.FIRST .. job_list.LAST LOOP
DBMS_OUTPUT.PUT_LINE(job_list(i).job_id || ' - ' || job_list(i).job_title);
END LOOP;
END;
As the salaries are obtained from the user input, they are used to determine how the bind variables will be populated within the query. The SQL is then executed, and the results are traversed. Each record is fetched and returned into a PL/SQL table of job records using BULK_COLLECT, and then in turn, each record is used to process the results. In this example, the data is simply printed out using DBMS_OUTPUT.PUT_LINE, but any number of tasks could be completed with the data.
How It Works
Dynamic SQL can be processed in a number of ways. In this solution, a record type is created by using the %ROWTYPE attribute of the table that is being queried. In this case, the %ROWTYPE attribute of the JOBS table is being used as a record. The data that is returned from performing a SELECT * on the JOBS table will be stored within that record, and then it will be processed accordingly. The record is created using the following syntax:
record_name table_name%ROWTYPE;
Using this format, the record_name is any name of your choice that complies with PL/SQL's naming conventions. The table_name is the name of the table from which you will be gathering the data for each column, and the %ROWTYPE attribute is a special table attribute that creates a record type.
To process each record, create a REF CURSOR using the dynamic SQL string and perform a BULK COLLECT to fetch each row of data into a record in the table of JOBS records. The BULK COLLECT will load all of the resulting records at once into a PL/SQL collection object. Once all the data has been retrieved into an object, it can be processed accordingly. The BULK COLLECT is much more efficient than fetching each row from the table one-by-one using a LOOP construct.