help to solve ,plsql not execute

Toad for Oracle
1

hi,all
I am beginner in plsql and this prog not execute pls solve it.I use free toad version.

CREATE OR REPLACE PROCEDURE s
(p_id IN employee.EMPLOYEE_ID%TYPE,
P_name OUT employee.LAST_NAME%TYPE,
P_salary OUT employee.SALARY%TYPE,
P_comm OUT employee.COMMISSION_PCT%TYPE)

IS

BEGIN

  select last_name,salary,commission_pct
  into P_name,P_salary,P_comm
  from employee
  where EMPLOYEE_ID=P_id;

END ;

2

REMOVE THE “OUT “argument from the parameter sections and let me
know how it works

Regards,

Venkat

3

Is it possible that the problem is not in the procedure itself but rather how you execute it?
If OUT is used, then these parameters should be defined in the calling block.

The following worked fine :

declare
P_name employee.LAST_NAME%TYPE;
P_salary employee.salary%TYPE;
P_comm employee.commission_pct%TYPE;

begin

 s('xxxx?',p_name,p_salary,p_comm);.commission_pct%TYPE;

  dbms_output.put_line ('p_name '||p_name);   
  dbms_output.put_line ('p_salary '||p_salary);   
  dbms_output.put_line ('p_comm '||p_comm);

end;
/

4

Your OUT specifications is OK.

I Ran the following using one of my test tables and it compiled.

CREATE OR REPLACE PROCEDURE s
(p_id IN er_emp.ID%TYPE,
P_name OUT er_emp.NAME%TYPE)
IS
BEGIN
select name
into P_name
from er_emp
where id = p_id;
END ;
/

Then I tested it with

declare
name varchar2(100);
begin
s(4,name);
DBMS_OUTPUT.PUT_LINE ( name );
end;
/

And it worked.

5

I am beginner in plsql and this prog not execute pls solve it.

Hi Anil? Tembhare?;

A little more information would be helpful. There’s any number of reasons
why something would “not work”. For example, the code might not
compile. The data set might not find any records (as one poster identified). The
data set might find too many records.

It would be very helpful if you could post the actual Oracle error message that
you are receiving when you try to call your procedure.

Roger S.

6

And furthermore. You say you are a beginner. How beginner? After executing the
CREATE statement did you actually run the procedure? How? You said nothing
happened. Did mean nothing nothing or a little bit nothing? If actually nothing
happened, no error message, etc. then are you sure you actually executed
something that would run the procedure. And of course did it work, whatever you
tried in SQL*Plus. Because if nothing happens there then it is not Toad that is
the problem.
image001.jpeg