Can i use the bind variable inside a procedure?

Author Name Status Posted Time Poutt Keitis 1/15/2008 11:31:17 PM Author is Offline Hello everybody, Can i use the bind variable inside a procedure? and the query like update emp set empname=:name where empno=:no; but i work like update emp set empname=&name where empno=&no inside procedures it works properly Code: ( text ) declare i number:=0; begin while i<3 loop update dept set empno=&empno where dname='&deptname'; i:=i+1; end loop; end; but here it prompts empno and deptname for first t ime and then uses it for all three times of the lo op.how do i give three diff values for each of the three execution. Thanks in advance Pouttu.

Subject Author Status Date Re: Can i use the bind variable inside a procedure ? Potthaccio Vaavaacione 1/17/2008 2:08:52 AM Hi,   If you want to pass 3 different values ,why using loop. directly execute three different SQL statme nts. Post Comments Re: Is there any provision...? Poutt Keitis 1/17/2008 2:13:51 AM No oththacci, Is there any provision to get different input from user for every time i enter the loop and dipslay the new value. example Code: ( text ) declare    eno number(4);    begin        for i in 1..5         loop             eno:=&eno;            dbms_output.put_line(eno);        end loop;    end; here i give an input 1 , but thats taken granted for all 5 times and it displays 1 1 1 1 1 but i want to give 5 differnet numbers each time i enter the loop. p lease help me to know whether this is possible or not Post Comments Re: No, that is not possible... Konjanja Takeuchi 1/17/2008 2:23:17 AM No, that is not possible unless you make use of 5 different variables. Alternative could be to use start and end value as an input parameter. Say you want employees whose empno is between 1 and 8 then you can use this code as shown below: Code: ( oracle8 ) SQL> ed Wrote FILE afiedt.buf 1 DECLARE 2 TYPE empdet IS TABLE OF emp%ROWTYPE; 3 empd empdet; 4 eno NUMBER(4); 5 eno1 NUMBER; 6 BEGIN 7 eno:=&1; 8 eno1:=&2; 9 SELECT * BULK COLLECT INTO empd FROM emp WHERE empno BETWEEN eno AND eno1; 10 FOR I IN 1..empd.COUNT LOOP 11 DBMS_OUTPUT.PUT_LINE(empd(i).empno||','||empd(i).e name); 12 END LOOP; 13* END; SQL> / Enter VALUE FOR 1: 1 old 7: eno:=&1; NEW 7: eno:=1; Enter VALUE FOR 2: 2 old 8: eno1:=&2; NEW 8: eno1:=2; 1,AAAA 2,AAAA PL/SQL PROCEDURE successfully completed. Post Comments Re: Thanks Poutt Keitis 1/17/2008 2:25:12 AM Thanks a lot for the reply sir Post Comments Re: You are welcome Konjanja Takeuchi 1/17/2008 2:27:03 AM You are welcome Pouttu. Post Comments

Related Forums Optimizing Images Get a Website Template for Free!!! Get a Website Template for Free!!! Do You Sell Your Templates on Ebay? Template Tutorials