Oracle, dynamic sql and named parameters

Question

(I've found this which partly answers the question, the declare way would look neater though given that my sql is will be used in oracle and mssql:) Binding variables in dynamic PL/SQL

I have some dynamic sql which I'm executing using syntax like the below:

EXECUTE IMMEDIATE plsql_block USING employeeid, sortname;

and I can then access those variables inside the dynamic sql using :1, :2, etc. Can I use named parameters instead? something like

EXECUTE IMMEDIATE plsql_block USING employeeid => employeeid

and then access them inside the dynamic sql using :employeeid rather than relying on position?

If not my thought is to do something like this at the beginning of the sql:

declare employeeid varchar(15 := :1;

and then I can change my dynamic sql to my hearts content without worrying about positioning.

Is there a better way?

thanks

Robert Giesecke · Accepted Answer · 2011-04-20 13:44:15Z

3

Your workaround is what I have used in the past.
There's also good old DBMS_Sql, which allows binding variables like you would be able to via OCI. (Which is basically what DBMS_Sql seems to be doing anyways ;-) )

answered Apr 20, 2011 at 13:44

Robert Giesecke

4,31424 silver badges22 bronze badges

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

Comments

miracle_the_V · Accepted Answer · 2015-05-25 13:23:47Z

3

You can use named arguments in DBMS_SQL:

DECLARE
        res INT;
        cr INT;
BEGIN
        cr := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(cr, :plsql_block, DBMS_SQL.NATIVE);
        DBMS_SQL.BIND_VARIABLE(cr, ':employee', :employee);
        res := DBMS_SQL.EXECUTE(cr);
        DBMS_SQL.CLOSE_CURSOR(cr);
END;

edited May 25, 2015 at 13:23

miracle_the_V

1,1761 gold badge15 silver badges33 bronze badges

answered Apr 20, 2011 at 13:47

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Collectives™ on Stack Overflow

Oracle, dynamic sql and named parameters

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest