# Constructing Dynamic SQL

**URL:** <https://forums.toadworld.com/t/constructing-dynamic-sql/33923>\
**Category:** Toad for Oracle & DevOps Toolkit\
**Created:** [August 13, 2015, 2:18pm UTC](https://forums.toadworld.com/t/constructing-dynamic-sql/33923 "2015-08-13T14:18:10Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![ttusing](https://avatars.discourse-cdn.com/v4/letter/t/bbe5ce/32.png) [@ttusing](https://forums.toadworld.com/u/ttusing)\
**Post date:** [August 13, 2015, 2:18pm UTC](https://forums.toadworld.com/t/constructing-dynamic-sql/33923/1 "2015-08-13T14:18:10Z")

</div>

I’m new to Toad and have a simple question; I previously wrote SAS macros and procedures and am entering this new Toad/Oracle environment. I’m trying to figure out how to do something similar to what I did in SAS where I could create functions where I used SAS macro language in my SQL statements.

For example, I’d like to make some sort of script where the user inputs some parameters to a function that are used in a SQL statement to return a table. For example, a function where the parameter is the parameter is later called as the parameter of a WHERE clause.

I don’t think procedures can return the results of a SQL query. Not sure where to start with this.

Thanks!

---

<div class="post-metadata">

**Author:** ![Gary.Jerep](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/gary.jerep/32/205_2.png) [@Gary.Jerep](https://forums.toadworld.com/u/Gary.Jerep)\
**Post date:** [September 21, 2015, 11:27am UTC](https://forums.toadworld.com/t/constructing-dynamic-sql/33923/2 "2015-09-21T11:27:47Z")

</div>

Procedures in Oracle’s PL/SQL can certainly be used to return results of a query. I’m not a PL/SQL expert, but here’s a simple example below, and one of many ways to execute SQL within PL/SQL. Example is set up as an anonymous block, but you can make a callable procedure out of it, with input parameters, etc.

Hope this helps.

DECLARE

CURSOR example\_cursor is

select \* from employee

where substr(emp\_dept,1,1) in (‘W’,‘X’, ‘Y’, ‘Z’)

and job\_id not like ‘%PRES’

and job\_id not like ‘%VP’

and job\_id not like ‘%MAN’

and job\_id not like ‘%MGR’;

record\_ex example\_cursor%ROWTYPE;

BEGIN

FOR i IN 1 … 10000 LOOP

OPEN example\_cursor;

LOOP

FETCH example\_cursor INTO record\_ex;

EXIT WHEN example\_cursor%NOTFOUND;

END LOOP;

CLOSE example\_cursor;

END LOOP;

END;