Home » SQL & PL/SQL » SQL & PL/SQL » Using Bind Variable (10G)  () 1 Vote
Using Bind Variable [message #584484] Tue, 14 May 2013 13:57 Go to next message
Yuvraaj
Messages: 118
Registered: January 2011
Location: California, USA
Senior Member
Hello All,

I have to use bind variable for dynamic sql in a procedure. Is there a way to have control on these values.

Say for example:

Procedur MyProc
 (
   In_EmpID       Number default null,
   In_EmpName  Varchar2 default null,
   in_JoinDate    Date default null
)

l_sql   varchar2(3000);

L_Bind1 NUMBER:= In_EmpID;
L_Bind2 VARCHAR2(240):=In_EmpName;
L_Bind3 VARCHAR2(240):=in_JoinDate;


TYPE Refcur IS REF CURSOR;
    
EmpListCur  RefCur

Begin
 
l_sql:= 'SELECT * FROM EMP WHERE EmpID = :B1 AND EmpName =:B2 AND JoinDate:= :B3;

Open EmpListCur FOR l_sql USING L_Bind3,L_Bind2,L_Bind1 ;
Loop
  ........

End Loop;

End;



The above procedure is kind of search in that, all IN param are not compulsory.

If In_EmpID is not sent from client and For EmpName and JoinDate it will be like below...

l_sql:= 'SELECT * FROM EMP WHERE EmpName =:B2 AND JoinDate:= :B3;

Open EmpListCur FOR l_sql USING L_Bind2,L_Bind1 ;

I have more than 5 In parameters, all 5 is not compulsory by default they are null and sql formation is also dynamic with in the procedure.

Now, I need to map bind variable to a proper one.. Is there a way to handle bind variable.....

Thanks in advance.
-YJ




Re: Using Bind Variable [message #584485 is a reply to message #584484] Tue, 14 May 2013 14:05 Go to previous messageGo to next message
Michel Cadot
Messages: 59166
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
in_JoinDate    Date default null
L_Bind3 VARCHAR2(240):=in_JoinDate;
JoinDate:= :B3;

Why l_Bind3 is not of type DATE?

Quote:
If In_EmpID is not sent from client and For EmpName and JoinDate it will be like below...


If you REALLY wants this then use DBMS_SQL instead.

Regards
Michel

Re: Using Bind Variable [message #584540 is a reply to message #584485] Wed, 15 May 2013 03:22 Go to previous message
sss111ind
Messages: 480
Registered: April 2012
Location: India
Senior Member

For null handling purpose it may help

CREATE OR replace PROCEDURE Myproc (in_empid    NUMBER DEFAULT NULL, 
                                    in_empname  VARCHAR2 DEFAULT NULL, 
                                    in_joindate DATE DEFAULT NULL) 
AS 
  l_sql      VARCHAR2(3000) := 'SELECT * FROM EMP WHERE 1=1 '; 
  TYPE refcur IS ref CURSOR; 
  emplistcur REFCUR; 
BEGIN 
    IF in_empid IS NOT NULL THEN 
      l_sql := l_sql 
               ||' AND  EMPNO =:In_EmpID '; 
    ELSE 
      l_sql := l_sql 
               || ' and  (1=1 or :In_EmpID IS NULL) '; 
    END IF; 

    IF in_empname IS NOT NULL THEN 
      l_sql := l_sql 
               ||' AND  ENAME =:In_EmpName '; 
    ELSE 
      l_sql := l_sql 
               || ' and  (1=1 or :In_EmpName IS NULL) '; 
    END IF; 

    IF in_joindate IS NOT NULL THEN 
      l_sql := l_sql 
               || 
      ' AND trunc(HIREDATE) = trunc(to_date(:in_JoinDate,''dd-MON-RR''))' 
      ; 
    ELSE 
      l_sql := l_sql 
               || ' and  (1=1 or :in_JoinDate IS NULL ) '; 
    END IF; 

    dbms_output.Put_line(l_sql); 

    OPEN emplistcur FOR l_sql USING in_empid, in_empname, in_joindate; 
--    LOOP 
--      NULL; 
--    END LOOP; 
END;  



For more information

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1669972300346534908

[Updated on: Wed, 15 May 2013 03:28]

Report message to a moderator

Previous Topic: ERROR : ORA-06512: at "NECDEMO.GETUESLIDREGTIME", line 25
Next Topic: BST timezone query error
Goto Forum:
  


Current Time: Mon Sep 22 18:08:49 CDT 2014

Total time taken to generate the page: 0.05641 seconds