How do you return values with Procedures? 
Author Message
 How do you return values with Procedures?

        I have a simple procedure that takes in a VARCHAR2 and outputs a number while doing an insert:
(id out NUMBER, email in VARCHAR2)
IS
BEGIN
        select foo_seq.nextval into id from dual;
        insert into foo(id, email, visit_date)
         values(id, email, sysdate);
end;


ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TESTDEMO'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

What am I doing wrong?

Thanks,
Sean Dolan



Sat, 11 Aug 2001 03:00:00 GMT
 How do you return values with Procedures?


(if that email address didn't require changing)

Quote:
>    I have a simple procedure that takes in a VARCHAR2 and outputs a number while doing an insert:
>(id out NUMBER, email in VARCHAR2)
>IS
>BEGIN
>    select foo_seq.nextval into id from dual;
>    insert into foo(id, email, visit_date)
>         values(id, email, sysdate);
>end;


>ORA-06550: line 1, column 7:
>PLS-00306: wrong number or types of arguments in call to 'TESTDEMO'
>ORA-06550: line 1, column 7:
>PL/SQL: Statement ignored

you need to send it 2 parameters.  for example:

declare
   l_id    number;
begin

   dbms_output.put_line( 'the id = ' || l_id );
end;
/

might be a good way to test it.  Or

SQL> variable id number

PL/SQL procedure successfully completed.

SQL> print id

        ID
----------
         5

would be another way.

Quote:
>What am I doing wrong?

>Thanks,
>Sean Dolan


Thomas Kyte

Oracle Service Industries
Reston, VA   USA

--
http://govt.us.oracle.com/    -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation



Sat, 11 Aug 2001 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. RETURN VALUE in stored procedure not always returning to ADO

2. Stored procedure returning empty output parameters or return value

3. How to capture the return value and output parameter value of a stored procedure in VB

4. Stored procedure returning variable value, not column value?

5. Return value and Output values from Stored Procedure

6. return select values in stored procedure to a calling stored procedure

7. Executing a stored procedure from another procedure that returns values

8. fetching the return value of a sql procedure from another procedure

9. Trigger doing UNLOAD to file OR calling Store Procedure doing the UNLOAD to file

10. Using @@Identity as a return value of a stored procedure

11. Procedure returning value in MS SQL 7.0 ?

12. Returning a value from sp_exequtesql to a stored procedure


 
Powered by phpBB® Forum Software