Re: Ansi SQL and stored procedures

tiz wrote:

> Hi, i'm trying to exec a simple stored proc with In and Out params:
>
> PROCEDURE GETNOMEFROMCOGNOME1
> Argument Name Type In/Out Default?
> ------------------------------ ----------------------- ------ --------
> COGNOME VARCHAR2 IN
> NOME VARCHAR2 OUT
>
> that's my code (basically):
>
> EXEC SQL ALLOCATE DESCRIPTOR 'input_descriptor';
> EXEC SQL ALLOCATE DESCRIPTOR 'output_descriptor';
>
> EXEC SQL PREPARE prep_statement FROM : dyn_statement;
>
> EXEC SQL SET DESCRIPTOR 'input_descriptor' VALUE 1 TYPE =
> :ANSI_varchar_type, LENGTH = :input_len, DATA = :cognome_data;
> EXEC SQL SET DESCRIPTOR 'input_descriptor' VALUE 2 TYPE =
> :ANSI_varchar_type, LENGTH = :input_len, DATA = :cognome_data;
>
> EXEC SQL SET DESCRIPTOR 'output_descriptor' VALUE 1 DATA=:a, TYPE =
> :ANSI_varchar_type;
> EXEC SQL SET DESCRIPTOR 'output_descriptor' VALUE 2 DATA=:b, TYPE =
> :ANSI_varchar_type;
>
> EXEC SQL EXECUTE prep_statement USING DESCRIPTOR 'input_descriptor' ;
>
> The error I get is:
> ORA-01422: exact fetch returns more than requested number of rows
>
>
> That's wrong with this code?
> Could so help me, please???
>
> thx
> tiz

Exactly what the error message says is the problem. You are SELECTing into variable(s) multiple values when the variable(s) can only hold a single value.

The cause either badly written code or a loss of data integrity coupled with a the fact that the coder apparently doesn't understand the concept of error trapping with an exception handler.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)