Is it possible to parameterize a query inside pl sql?

Question

The stored procedures being written here currently concats the parameters to the queries:

   SELECT * 
     FROM Names 
    WHERE Name = ' || prmName || ' 
 ORDER BY ' || prmSortField

Is it possible to parameterize this query inside the stored procedure? Possibly like:

query = 'select * From Names Where Name = @name Order By ' || prmSortField
call(query, prmName)

Note:
In case you wonder why we do so, there are two common parameters for our sp's: sortFieldIndex and sortDirection. Since we cannot directly parameterize these, the query is dynamically generated. But other parameters make the queries open for injection. So I am looking a way to parameterize some of the parameters.

cletus · Accepted Answer · 2009-01-16 09:45:59Z

4

Absolutely. Use cursors.

DECLARE
  CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS
    SELECT * FROM employees WHERE job_id = job AND salary > max_wage;
BEGIN
  FOR person IN c1('CLERK', 3000)
  LOOP
     -- process data record
    DBMS_OUTPUT.PUT_LINE('Name = ' || person.last_name || ', salary = ' ||
                         person.salary || ', Job Id = ' || person.job_id );
  END LOOP;
END;

answered Jan 16, 2009 at 9:45

cletus

627k169 gold badges922 silver badges945 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thiago Arrais Over a year ago

What about the OP's prmSortField variable? How can you parameterize the ordering field without dynamic queries?

Tony Andrews · Accepted Answer · 2009-01-18 12:33:30Z

4

For a dynamic query with bind values, do this:

procedure p (prmName varchar2, prmSortField varchar2)
is
    query varchar2(100);
    rc sys_refcursor;
    names_rec names%rowtype;
begin
    query = 'select * From Names Where Name = :name Order By ' || prmSortField
    open rc for query using prmName;
    loop
        fetch rc into names_rec;
        exit when rc%notfound;
        -- process this row
    end loop;
    close rc;
end;

edited Jan 18, 2009 at 12:33

answered Jan 16, 2009 at 10:30

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Comments

Stew S · Accepted Answer · 2009-01-16 12:30:35Z

-1

For a more elaborate procedure that supports optional parameter values (but uses sys context), check out the following post on Asktom.com

PRATTY -- Thanks for the question regarding 'CURSOR'...

answered Jan 16, 2009 at 12:30

Stew S

6091 gold badge6 silver badges10 bronze badges

Collectives™ on Stack Overflow

Is it possible to parameterize a query inside pl sql?

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related