Re: Query problem - stored proc v. SQL

Is the query based on a view?
Are the procedure and the query executed under the same Oracle user?

Russ Conway <service_at_hallofsports.com> schreef in berichtnieuws 3AF6FC9C.F314543E_at_hallofsports.com...
> Any idea what I'm doing wrong here? I can't seem to make this stored
> procedure work. The problem is part of a larger procedure, but I've
> boiled it down to this...
>
> CREATE OR REPLACE PROCEDURE TEST
> (vTeamSport VARCHAR2)
> AS
>
> vTeamID NUMBER(10);
>
> BEGIN
>
> /* Get TeamID for sport */
> SELECT TEAMID INTO vTeamID
> FROM TEAM
> WHERE TEAMSPORT = vTeamSport
> AND LEAGUEABBREVIATION IS NULL
> AND TEAMNAME IS NULL;
>
> END;
> /
>
> I tried the following test:
> SQL> exec test('Auto Racing');
> BEGIN test('Auto Racing'); END;
>
> *
> ERROR at line 1:
> ORA-01403: no data found
> ORA-06512: at "HOSWEB.TEST", line 10
> ORA-06512: at line 1
>
> But when I run the query on its own...
>
> SQL> SELECT TEAMID
> 2 FROM TEAM
> 3 WHERE TEAMSPORT = 'Auto Racing'
> 4 AND LEAGUEABBREVIATION IS NULL
> 5 AND TEAMNAME IS NULL;
>
> TEAMID
> ---------
> 619
>
> I've been trouble shooting this for a few hours. Have I lost it or
> something?!?!
>
> TIA
>
> Russ Conway
>
Received on Tue May 08 2001 - 10:18:56 CDT