Using Dynamic SQL for Multi-row Queries

View All Scripts Login to Run Script

Script Name Using Dynamic SQL for Multi-row Queries
Description This script contains the code for my Oracle Magazine article of the same name.
Area PL/SQL General
Contributor Steven Feuerstein
Created Tuesday September 05, 2017

Statement 1

DECLARE
   TYPE name_salary_rt IS RECORD
   (
      name     VARCHAR2 (1000),
      salary   NUMBER
   );

   TYPE name_salary_aat IS TABLE OF name_salary_rt
      INDEX BY PLS_INTEGER;

   l_employees   name_salary_aat;
BEGIN
   EXECUTE IMMEDIATE 
      q'[select first_name || ' ' || last_name, salary 
           from hr.employees
          order by salary desc]'
      BULK COLLECT INTO l_employees;

   FOR indx IN 1 .. l_employees.COUNT
   LOOP
      DBMS_OUTPUT.put_line (l_employees (indx).name);
   END LOOP;
END;

Steven King
Neena Kochhar
Lex De Haan
John Russell
Karen Partners
Michael Hartstein
Nancy Greenberg
Shelley Higgins
Alberto Errazuriz
Lisa Ozer
Den Raphaely
Gerald Cambrault
Ellen Abel
Eleni Zlotkey
Clara Vishney
Janette King
Peter Tucker
Hermann Baer
Harrison Bloom
Tayler Fox
Danielle Greene
David Bernstein
Patrick Sully
Peter Hall
Alexander Hunold
Allan McEwen
Daniel Faviet
Alyssa Hutton
Jonathon Taylor
Jack Livingston
William Gietz
Adam Fripp
John Chen
Matthew Weiss
Lindsey Smith
Christopher Olsen
Payam Kaufling
Jose Manuel Urman
Ismael Sciarra
Louise Doran
Nanette Cambrault
William Smith
Elizabeth Bates
Mattea Marvins
Oliver Tuvault
Kimberely Grant
Sarath Sewall
Luis Popp
David Lee
Susan Mavris
Shanta Vollman
Sundar Ande
Charles Johnson
Amit Banda
Sundita Kumar
Pat Fay
Bruce Ernst
Kevin Mourgos
Valli Pataballa
David Austin
Jennifer Whalen
Nandita Sarchand
Diana Lorentz
Alexis Bull
Sarah Bell
Britney Everett
Kelly Chung
Jennifer Dilly
Renske Ladwig
Trenna Rajs
Julia Dellinger
Jason Mallin
Laura Bissot
Julia Nayer
Samuel McCain
Winston Taylor
Stephen Stiles
Alexander Khoo
Alana Walsh
Jean Fleaur
Curtis Davies
Kevin Feeney
Anthony Cabrio
Shelli Baida
Timothy Gates
Michael Rogers
Sigal Tobias
Vance Jones
Girard Geoni
Mozhe Atkinson
John Seo
Irene Mikkilineni
Guy Himuro
Douglas Grant
Donald OConnell
Randall Matos
Karen Colmenares
James Marlow
Randall Perkins
Peter Vargas
Martha Sullivan
Joshua Patel
Ki Gee
James Landry
Hazel Philtanker
Steven Markle
TJ Olson

Statement 2

CREATE OR REPLACE FUNCTION names_for (department_id_in   IN INTEGER,
                                      min_salary_in      IN NUMBER)
   RETURN SYS_REFCURSOR
IS
   l_cursor   SYS_REFCURSOR;
BEGIN
   IF department_id_in IS NOT NULL
   THEN
      OPEN l_cursor FOR
         SELECT last_name
           FROM hr.employees
          WHERE department_id = department_id_in;
   ELSE
      OPEN l_cursor FOR
         SELECT last_name
           FROM hr.employees
          WHERE salary >= min_salary_in;
   END IF;

   RETURN l_cursor;
END;

Function created.

Statement 3

DECLARE 
   TYPE name_salary_rt IS RECORD ( 
      name     VARCHAR2 (1000), 
      salary   NUMBER 
   ); 
 
   l_record   name_salary_rt; 
 
   l_cursor   SYS_REFCURSOR; 
BEGIN 
   OPEN l_cursor FOR  
      q'[select first_name || ' ' || last_name, salary  
           from hr.employees 
          order by salary desc]'; 
 
   LOOP 
      FETCH l_cursor INTO l_record; 
 
      EXIT WHEN l_cursor%NOTFOUND; 
 
      DBMS_OUTPUT.put_line (l_record.name); 
   END LOOP; 
 
   CLOSE l_cursor; 
END;

Steven King
Neena Kochhar
Lex De Haan
John Russell
Karen Partners
Michael Hartstein
Nancy Greenberg
Shelley Higgins
Alberto Errazuriz
Lisa Ozer
Den Raphaely
Gerald Cambrault
Ellen Abel
Eleni Zlotkey
Clara Vishney
Janette King
Peter Tucker
Hermann Baer
Harrison Bloom
Tayler Fox
Danielle Greene
David Bernstein
Patrick Sully
Peter Hall
Alexander Hunold
Allan McEwen
Daniel Faviet
Alyssa Hutton
Jonathon Taylor
Jack Livingston
William Gietz
Adam Fripp
John Chen
Matthew Weiss
Lindsey Smith
Christopher Olsen
Payam Kaufling
Jose Manuel Urman
Ismael Sciarra
Louise Doran
Nanette Cambrault
William Smith
Elizabeth Bates
Mattea Marvins
Oliver Tuvault
Kimberely Grant
Sarath Sewall
Luis Popp
David Lee
Susan Mavris
Shanta Vollman
Sundar Ande
Charles Johnson
Amit Banda
Sundita Kumar
Pat Fay
Bruce Ernst
Kevin Mourgos
Valli Pataballa
David Austin
Jennifer Whalen
Nandita Sarchand
Diana Lorentz
Alexis Bull
Sarah Bell
Britney Everett
Kelly Chung
Jennifer Dilly
Renske Ladwig
Trenna Rajs
Julia Dellinger
Jason Mallin
Laura Bissot
Julia Nayer
Samuel McCain
Winston Taylor
Stephen Stiles
Alexander Khoo
Alana Walsh
Jean Fleaur
Curtis Davies
Kevin Feeney
Anthony Cabrio
Shelli Baida
Timothy Gates
Michael Rogers
Sigal Tobias
Vance Jones
Girard Geoni
Mozhe Atkinson
John Seo
Irene Mikkilineni
Guy Himuro
Douglas Grant
Donald OConnell
Randall Matos
Karen Colmenares
James Marlow
Randall Perkins
Peter Vargas
Martha Sullivan
Joshua Patel
Ki Gee
James Landry
Hazel Philtanker
Steven Markle
TJ Olson

Statement 4

DECLARE 
   TYPE name_salary_rt IS RECORD ( 
      name     VARCHAR2 (1000), 
      salary   NUMBER 
   ); 
 
   TYPE name_salary_aat IS TABLE OF name_salary_rt 
      INDEX BY PLS_INTEGER; 
 
   l_employees   name_salary_aat; 
 
   l_cursor   SYS_REFCURSOR; 
BEGIN 
   OPEN l_cursor FOR q'[select first_name || ' ' || last_name, salary  
           from hr.employees 
          order by salary desc]'; 
 
   FETCH l_cursor BULK COLLECT INTO l_employees; 
 
   CLOSE l_cursor; 
 
   FOR indx IN 1 .. l_employees.COUNT 
   LOOP 
      DBMS_OUTPUT.put_line (l_employees (indx).name); 
   END LOOP; 
END;

Steven King
Neena Kochhar
Lex De Haan
John Russell
Karen Partners
Michael Hartstein
Nancy Greenberg
Shelley Higgins
Alberto Errazuriz
Lisa Ozer
Den Raphaely
Gerald Cambrault
Ellen Abel
Eleni Zlotkey
Clara Vishney
Janette King
Peter Tucker
Hermann Baer
Harrison Bloom
Tayler Fox
Danielle Greene
David Bernstein
Patrick Sully
Peter Hall
Alexander Hunold
Allan McEwen
Daniel Faviet
Alyssa Hutton
Jonathon Taylor
Jack Livingston
William Gietz
Adam Fripp
John Chen
Matthew Weiss
Lindsey Smith
Christopher Olsen
Payam Kaufling
Jose Manuel Urman
Ismael Sciarra
Louise Doran
Nanette Cambrault
William Smith
Elizabeth Bates
Mattea Marvins
Oliver Tuvault
Kimberely Grant
Sarath Sewall
Luis Popp
David Lee
Susan Mavris
Shanta Vollman
Sundar Ande
Charles Johnson
Amit Banda
Sundita Kumar
Pat Fay
Bruce Ernst
Kevin Mourgos
Valli Pataballa
David Austin
Jennifer Whalen
Nandita Sarchand
Diana Lorentz
Alexis Bull
Sarah Bell
Britney Everett
Kelly Chung
Jennifer Dilly
Renske Ladwig
Trenna Rajs
Julia Dellinger
Jason Mallin
Laura Bissot
Julia Nayer
Samuel McCain
Winston Taylor
Stephen Stiles
Alexander Khoo
Alana Walsh
Jean Fleaur
Curtis Davies
Kevin Feeney
Anthony Cabrio
Shelli Baida
Timothy Gates
Michael Rogers
Sigal Tobias
Vance Jones
Girard Geoni
Mozhe Atkinson
John Seo
Irene Mikkilineni
Guy Himuro
Douglas Grant
Donald OConnell
Randall Matos
Karen Colmenares
James Marlow
Randall Perkins
Peter Vargas
Martha Sullivan
Joshua Patel
Ki Gee
James Landry
Hazel Philtanker
Steven Markle
TJ Olson

Statement 5

DECLARE 
   TYPE name_salary_rt IS RECORD ( 
      name     VARCHAR2 (1000), 
      salary   NUMBER 
   ); 
 
   l_record   name_salary_rt; 
 
   l_cursor   SYS_REFCURSOR; 
BEGIN 
   OPEN l_cursor FOR q'[select first_name || ' ' || last_name, salary  
           from hr.employees 
          order by salary desc]'; 
 
   LOOP 
      FETCH l_cursor INTO l_record; 
 
      EXIT WHEN l_cursor%NOTFOUND; 
 
      DBMS_OUTPUT.put_line (l_record.name); 
   END LOOP; 
 
   CLOSE l_cursor; 
END;

Steven King
Neena Kochhar
Lex De Haan
John Russell
Karen Partners
Michael Hartstein
Nancy Greenberg
Shelley Higgins
Alberto Errazuriz
Lisa Ozer
Den Raphaely
Gerald Cambrault
Ellen Abel
Eleni Zlotkey
Clara Vishney
Janette King
Peter Tucker
Hermann Baer
Harrison Bloom
Tayler Fox
Danielle Greene
David Bernstein
Patrick Sully
Peter Hall
Alexander Hunold
Allan McEwen
Daniel Faviet
Alyssa Hutton
Jonathon Taylor
Jack Livingston
William Gietz
Adam Fripp
John Chen
Matthew Weiss
Lindsey Smith
Christopher Olsen
Payam Kaufling
Jose Manuel Urman
Ismael Sciarra
Louise Doran
Nanette Cambrault
William Smith
Elizabeth Bates
Mattea Marvins
Oliver Tuvault
Kimberely Grant
Sarath Sewall
Luis Popp
David Lee
Susan Mavris
Shanta Vollman
Sundar Ande
Charles Johnson
Amit Banda
Sundita Kumar
Pat Fay
Bruce Ernst
Kevin Mourgos
Valli Pataballa
David Austin
Jennifer Whalen
Nandita Sarchand
Diana Lorentz
Alexis Bull
Sarah Bell
Britney Everett
Kelly Chung
Jennifer Dilly
Renske Ladwig
Trenna Rajs
Julia Dellinger
Jason Mallin
Laura Bissot
Julia Nayer
Samuel McCain
Winston Taylor
Stephen Stiles
Alexander Khoo
Alana Walsh
Jean Fleaur
Curtis Davies
Kevin Feeney
Anthony Cabrio
Shelli Baida
Timothy Gates
Michael Rogers
Sigal Tobias
Vance Jones
Girard Geoni
Mozhe Atkinson
John Seo
Irene Mikkilineni
Guy Himuro
Douglas Grant
Donald OConnell
Randall Matos
Karen Colmenares
James Marlow
Randall Perkins
Peter Vargas
Martha Sullivan
Joshua Patel
Ki Gee
James Landry
Hazel Philtanker
Steven Markle
TJ Olson

Statement 6

DECLARE 
   l_cursor        PLS_INTEGER := DBMS_SQL.open_cursor; 
   l_names         DBMS_SQL.varchar2_table; 
   l_salaries      DBMS_SQL.number_table; 
   l_fetch_count   PLS_INTEGER; 
BEGIN 
   /* Parse the query  with a dynamic WHERE clause */ 
   DBMS_SQL.parse (l_cursor, 
                   q'[select first_name || ' ' || last_name, salary   
           from hr.employees 
          order by salary desc]', 
                   DBMS_SQL.native); 
 
   /* Define the columns in the cursor for this query */ 
   DBMS_SQL.define_array (l_cursor, 
                          1, 
                          l_names, 
                          10, 
                          1); 
   DBMS_SQL.define_array (l_cursor, 
                          2, 
                          l_salaries, 
                          10, 
                          1); 
 
   /* Execute the query and fetch the rows. */ 
   l_fetch_count := DBMS_SQL.execute (l_cursor); 
 
   LOOP 
      l_fetch_count := DBMS_SQL.fetch_rows (l_cursor); 
      DBMS_SQL.COLUMN_VALUE (l_cursor, 1, l_names); 
      DBMS_SQL.COLUMN_VALUE (l_cursor, 2, l_salaries); 
 
      EXIT WHEN l_fetch_count != 10; 
   END LOOP; 
 
   FOR indx IN 1 .. l_names.COUNT 
   LOOP 
      DBMS_OUTPUT.put_line (l_names (indx)); 
   END LOOP; 
 
   DBMS_SQL.close_cursor (l_cursor); 
END;

Steven King
Neena Kochhar
Lex De Haan
John Russell
Karen Partners
Michael Hartstein
Nancy Greenberg
Shelley Higgins
Alberto Errazuriz
Lisa Ozer
Den Raphaely
Gerald Cambrault
Ellen Abel
Eleni Zlotkey
Clara Vishney
Janette King
Peter Tucker
Hermann Baer
Harrison Bloom
Tayler Fox
Danielle Greene
David Bernstein
Patrick Sully
Peter Hall
Alexander Hunold
Allan McEwen
Daniel Faviet
Alyssa Hutton
Jonathon Taylor
Jack Livingston
William Gietz
Adam Fripp
John Chen
Matthew Weiss
Lindsey Smith
Christopher Olsen
Payam Kaufling
Jose Manuel Urman
Ismael Sciarra
Louise Doran
Nanette Cambrault
William Smith
Elizabeth Bates
Mattea Marvins
Oliver Tuvault
Kimberely Grant
Sarath Sewall
Luis Popp
David Lee
Susan Mavris
Shanta Vollman
Sundar Ande
Charles Johnson
Amit Banda
Sundita Kumar
Pat Fay
Bruce Ernst
Kevin Mourgos
Valli Pataballa
David Austin
Jennifer Whalen
Nandita Sarchand
Diana Lorentz
Alexis Bull
Sarah Bell
Britney Everett
Kelly Chung
Jennifer Dilly
Renske Ladwig
Trenna Rajs
Julia Dellinger
Jason Mallin
Laura Bissot
Julia Nayer
Samuel McCain
Winston Taylor
Stephen Stiles
Alexander Khoo
Alana Walsh
Jean Fleaur
Curtis Davies
Kevin Feeney
Anthony Cabrio
Shelli Baida
Timothy Gates
Michael Rogers
Sigal Tobias
Vance Jones
Girard Geoni
Mozhe Atkinson
John Seo
Irene Mikkilineni
Guy Himuro
Douglas Grant
Donald OConnell
Randall Matos
Karen Colmenares
James Marlow
Randall Perkins
Peter Vargas
Martha Sullivan
Joshua Patel
Ki Gee
James Landry
Hazel Philtanker
Steven Markle
TJ Olson

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database