We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Running a stored proc and returning a single value
Posted on 2013-02-01
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
6
2-
2
- +1
13 Comments
Active today
dual contains one column called "DUMMY"
and the contents are a string 'X'
you can't put 'X' into an INTEGER type variable
and the contents are a string 'X'
you can't put 'X' into an INTEGER type variable
Active today
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
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
Active today
also note, select from dual is not the proper way to assign a value to a pl/sql variable
use := instead
for example
CREATE OR REPLACE PROCEDURE Check_Run (
p_retVal OUT INTEGER
)
AS
BEGIN
p_retVal := 1234;
END Check_Run;
use := instead
for example
CREATE OR REPLACE PROCEDURE Check_Run (
p_retVal OUT INTEGER
)
AS
BEGIN
p_retVal := 1234;
END Check_Run;
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;
Active 1 day ago
You'd need to call it like this:
You've specified a parameter in the proc definition and then you aren't calling the proc with that parameter.
set serveroutput on
declare
myvar varchar2
begin
Check_Run(myvar);
dbms_output.put_line(myvar);
end;
/
You've specified a parameter in the proc definition and then you aren't calling the proc with that parameter.
Active today
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_
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;
Active 1 day ago
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.
Active today
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
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_
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.
Active today
are you using sql*plus?
if so
set serveroutput on
then run the block to see the dbms_output results
if you are using some other tool there will probably be a window or tab available to view dbms_output results and you'll have to enable it there. How/where you do that will be dependent on your specific tool
if so
set serveroutput on
then run the block to see the dbms_output results
if you are using some other tool there will probably be a window or tab available to view dbms_output results and you'll have to enable it there. How/where you do that will be dependent on your specific tool
Active 2 days ago
And if there will always be a single return value, you can change it to a function.
Unless DML (insert,delete,update) is needed to determine the return value you can call the function from sql.
The return value in this example wil change depending on the moment when the function is called.
SQL> CREATE OR REPLACE FUNCTION Check_Run
2 RETURN INTEGER
3 AS
4 v_retVal pls_integer;
5 BEGIN
6 select mod(to_number(to_char(sysd
7 return v_retVal;
8 END Check_Run;
9 /
SQL> select check_run from dual;
CHECK_RUN
----------
1
SQL> /
CHECK_RUN
----------
1
SQL> /
CHECK_RUN
----------
1
SQL> /
CHECK_RUN
----------
1
SQL> /
CHECK_RUN
----------
0
SQL> /
CHECK_RUN
----------
0
SQL> /
CHECK_RUN
----------
1
Unless DML (insert,delete,update) is needed to determine the return value you can call the function from sql.
The return value in this example wil change depending on the moment when the function is called.
SQL> CREATE OR REPLACE FUNCTION Check_Run
2 RETURN INTEGER
3 AS
4 v_retVal pls_integer;
5 BEGIN
6 select mod(to_number(to_char(sysd
7 return v_retVal;
8 END Check_Run;
9 /
SQL> select check_run from dual;
CHECK_RUN
----------
1
SQL> /
CHECK_RUN
----------
1
SQL> /
CHECK_RUN
----------
1
SQL> /
CHECK_RUN
----------
1
SQL> /
CHECK_RUN
----------
0
SQL> /
CHECK_RUN
----------
0
SQL> /
CHECK_RUN
----------
1
Featured Post
Are you interested in becoming an AWS Certified Solutions Architect?
Discover a new interactive way of training for the exam.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Confronted with some SQL you don't know can be a daunting task.
It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as:
(+)   as used in Oracle;   *=   =*   as used in Sybase …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface. I made a script to download the Alert Log, look for errors, and email me the trace files. In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA. It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month7 days, 6 hours left to enroll
Earn Certification
Certified Information Systems Auditor (CISA)
$99.00$89.10
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.