Convert select statement to a stored procedure
- From: "Andyza" <andyza@xxxxxxxxxxxxx>
- Date: 10 Mar 2006 05:56:36 -0800
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?
.
- Follow-Ups:
- Re: Convert select statement to a stored procedure
- From: DA Morgan
- Re: Convert select statement to a stored procedure
- From: frank.van.bortel@xxxxxxxxx
- Re: Convert select statement to a stored procedure
- Prev by Date: Re: Create a user defined function
- Next by Date: Re: Convert select statement to a stored procedure
- Previous by thread: Create a user defined function
- Next by thread: Re: Convert select statement to a stored procedure
- Index(es):
Relevant Pages
|
