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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
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_
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
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
ASKER
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.
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_
end;
Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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