Re: Convert select statement to a stored procedure

Andyza wrote:
> I have the following sql select code in a .asp page which I'd like to
> convert to a stored procedure on an Oracle 9i schema:
>
> Set conn = Server.CreateObject("ADODB.Connection")
>
> conn.Open Application("connstring")
>
> strSQL = "SELECT P.ID, P.FIRSTNAME, P.DSSURNAME,
> TO_CHAR(P.STARTDATE,'YYYY/MM/DD HH24:MI:SS') AS STARTTDATE " & _
> "FROM PEOPLE P " & _
> "WHERE (P.FIRSTNAME = '" & strName & "') " & _
> "AND P.DSDELETED = 'No'"
>
> Set rs = conn.Execute(strSQL)
>
> My Oracle procedure is:
>
> CREATE OR REPLACE PROCEDURE TestGetData (i_FIRSTNAME IN NVARCHAR2) AS
> o_ID OUT NVARCHAR2,
> o_FIRSTNAME OUT NVARCHAR2,
> o_SURNAME OUT NVARCHAR2,
> o_STARTDATE OUT NVARCHAR2
> BEGIN
> SELECT P.ID, P.FIRSTNAME, P.SURNAME,
> TO_CHAR(P.STARTDATE,'YYYY/MM/DD HH24:MI:SS') AS STARTTDATE
> INTO o_ID, o_FIRSTNAME, o_SURNAME, o_STARTDATE
> FROM PEOPLE P
> WHERE P.FIRSTNAME = i_FIRSTNAME
> AND P.DELETED = 'No';
> END;
>
> What am I doing wrong in the procedure?

What you are doing wrong is assuming that Oracle, which runs on multiple platforms and operating systems is a Microsoft product that runs on only one.

What you want to use is a REF CURSOR and you will find an example of doing this in Morgan's Library at www.psoug.org. Click on REF CURSOR.

HTH

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)