Link to home
Start Free TrialLog in
Avatar of wilflife
wilflife

asked on

Running a stored proc and returning a single value

CREATE OR REPLACE PROCEDURE Check_Run (
  p_retVal OUT INTEGER
)
AS
BEGIN
   select * into p_retVal from dual;
END Check_Run;
exec Check_Run;

I get an error when running this.

I know simple.
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sean Stuber
Sean Stuber

either change p_retVal to some string data type (like VARCHAR2)

or,  use some select  other than * from dual.

incidentally, using * is bad practice if you aren't putting the results into a record type since * means "all columns"  and therefore implies it could be more than one
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wilflife

ASKER

exec Check_Run;

Gives me this error even if i change it to
Error starting at line 18 in command:
exec Check_Run
Error report:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CHECK_RUN'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

   select 1 into p_retVal from dual;
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
exec Check_Run;


this doesn't make sense to do this,  
the error message explains why

  "wrong number or types of arguments in call to 'CHECK_RUN' "

your procedure returns a value but you don't have a parameter in your call to hold that  value

declare
   v_my_int integer;
begin
    check_run(v_my_int);
    dbms_output.put_line(v_my_int);
end;
Further to my response, you'd need to have changed your proc to have the parameter be a varchar2, or if you're doing something like "select 1 from dual" then you'd need to change the data type of the variable when you call the proc to be an integer.
as noted above


select 1 into p_retVal from dual;


is the wrong way to do value assignment.
Using sql forces extra work called a "context switch"


p_retVal := 1;

is the right way
The reason i am doing it with the select 1 from dual is later i will put a more complex sql statement i have designed in.

How do i get this to return?

Now runs but just says anonymous block completed and nothing in dbms output?

I ran it like this:

declare
   v_my_int integer;
begin
    check_run(v_my_int);
    dbms_output.put_line(v_my_int);
end;

Thanks.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial