Toad World® Forums

Constructing Dynamic SQL


#1

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!


#2

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;