Home » SQL & PL/SQL » SQL & PL/SQL » Procedure (Oracle 9i)
Procedure [message #446986] Thu, 11 March 2010 04:40 Go to next message
prakashaa
Messages: 31
Registered: November 2009
Location: Bangalore
Member
Hi All,
I have created a procedure


CREATE OR REPLACE PROCEDURE p1
IS
v_sal NUMBER;
v_name VARCHAR2(20);
BEGIN
SELECT ename,sal INTO v_ename,v_sal FROM emp WHERE empno=7369;
DBMS_OUTPUT.PUT_LINE(v_ename||' '||v_sal);
END;
/

After that i droped the sal column from the emp table, then i executed that procedure it is showing error.

without getting error how can we run that procedure .
Please help me.



Re: Procedure [message #446988 is a reply to message #446986] Thu, 11 March 2010 04:44 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
If SAL column is not there in your table, how can you refer that coulms in SELECT query?
This behaviour of Oracle is quite obvious.

regards,
Delna
Re: Procedure [message #446989 is a reply to message #446988] Thu, 11 March 2010 04:45 Go to previous messageGo to next message
prakashaa
Messages: 31
Registered: November 2009
Location: Bangalore
Member
While i was creating the procedure it is there after that it is dropped.
Re: Procedure [message #446992 is a reply to message #446986] Thu, 11 March 2010 05:04 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
And after it is dropped you get an error.
What else would you expect to happen?
You want to run that procedure again you need to change it to not reference the column you droppped.
Re: Procedure [message #446995 is a reply to message #446989] Thu, 11 March 2010 05:39 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Instead of dropping SAL column from EMP, drop any other column from any other table!
This will solve your intention of dropping column and your procedure will continue its work.

regards,
Delna
Re: Procedure [message #447136 is a reply to message #446995] Thu, 11 March 2010 23:44 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
CREATE OR REPLACE PROCEDURE p1
IS
v_sal NUMBER;
v_name VARCHAR2(20);
BEGIN
SELECT ename,sal INTO v_ename,v_sal FROM emp WHERE empno=7369;
DBMS_OUTPUT.PUT_LINE(v_ename||' '||v_sal);
END;
/

in addition,
It is not a compiled one.
v_name is different from v_ename.
This is why Orafaq guide line saying that OP should post what he did and what he got through sqlplus...

sriram Smile
Previous Topic: Outer join with cut-off condition
Next Topic: What is the Difference between a Stand Alone Function & a Function declared in a Package.
Goto Forum:
  


Current Time: Sun Dec 04 04:39:38 CST 2016

Total time taken to generate the page: 0.11890 seconds