Re: Convert select statement to a stored procedure
- From: DA Morgan <damorgan@xxxxxxxxx>
- Date: Sun, 12 Mar 2006 10:35:21 -0800
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@xxxxxxxxxxxxxxxx
(replace x with u to respond)
.
- References:
- Convert select statement to a stored procedure
- From: Andyza
- Convert select statement to a stored procedure
- Prev by Date: Oracle course Introduction to Oracle 9i: SQL
- Next by Date: Re: Create a user defined function
- Previous by thread: Re: Convert select statement to a stored procedure
- Next by thread: Fetch SYS_REFCURSOR with unknown structure?
- Index(es):
Relevant Pages
|
