Solved

Running a stored proc and returning a single value

Posted on 2013-02-01
13
271 Views
Last Modified: 2014-05-27
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.
0
Comment
Question by:wilflife
  • 6
  • 2
  • 2
  • +1
13 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 300 total points
ID: 38843781
dual contains one column  called "DUMMY"

and the contents are a string 'X'

you can't put 'X' into an INTEGER type variable
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38843790
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
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 300 total points
ID: 38843794
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;
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:wilflife
ID: 38843802
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;
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 100 total points
ID: 38843812
You'd need to call it like this:

set serveroutput on
declare
myvar varchar2
begin
Check_Run(myvar);
dbms_output.put_line(myvar);
end;
/

Open in new window


You've specified a parameter in the proc definition and then you aren't calling the proc with that parameter.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38843815
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;
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38843821
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.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38843829
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
0
 

Author Comment

by:wilflife
ID: 38843845
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.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 300 total points
ID: 38843855
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
0
 
LVL 20

Assisted Solution

by:flow01
flow01 earned 100 total points
ID: 38844344
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(sysdate,'ss')),2)  into v_retVal from dual;
  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
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Sum causes too much time 5 42
convert in derived column 7 30
SQL: launch actions one before the other 10 23
pl/sql - query very slow 26 60
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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 how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question