Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Convert select statement to a stored procedure
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';