Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Convert select statement to a stored procedure

Convert select statement to a stored procedure

From: Andyza <andyza_at_webmail.co.za>
Date: 10 Mar 2006 05:56:36 -0800
Message-ID: <1141998996.485290.147900@v46g2000cwv.googlegroups.com>


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? Received on Fri Mar 10 2006 - 07:56:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US