CODEWITHSUNDEEP
variablesplsqloracle10gbind
Merlin051
Merlin051

Reputation: 301

Oracle Bind Variables with Package

I'm new to the world of Oracle PLSQL coming from a T-SQL background.

I'm having an issue when trying to use bind variables to produce some dynamic sql to call a method within a package.

The Method I'm trying to call is .New__ and belongs to the package PACKAGE_NAME

My .New__ Method looks like this

PROCEDURE New__ (
info_       OUT    VARCHAR2,
objid_      OUT    VARCHAR2,
objversion_ OUT    VARCHAR2,
attr_       IN OUT NOCOPY VARCHAR2,
action_     IN     VARCHAR2 )

Therefore Im calling it like this:

DECLARE
p0 VARCHAR2(10) := null; -- param 0 of the method
p1 VARCHAR2(10) := null; -- param 1 of the method
p2 VARCHAR2(10) := null; -- param 2 of the method
p3 VARCHAR2(20) := null; -- param 3 of the method
p4 VARCHAR2(20) :='DO';  -- param 4 of the method
p5 VARCHAR2(100) :='PACKAGE_NAME.NEW__'; -- package and method name

BEGIN

EXECUTE IMMEDIATE 'BEGIN '||p5||'(:p0,:p1,:p2,:p3,:p4); END;'
USING OUT  p0,p1,p2,  IN OUT  p3, IN p4 ;

END;

The error I'm getting is "ORA-06536: IN bind variable bound to an OUT position"

I've obviously missed soemthing, can you help me out?

Upvotes: 2

Views: 3562

Answers (1)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60272

default parameter binding is IN, so your p1, p2 are IN, not OUT. I think you want:

USING OUT p0, OUT p1, OUT p2, IN OUT p3, IN p4 ;

Upvotes: 5

Related Questions