Re: Stored procedures from SQL Server

Doug Needham wrote:
>
> I have PO7 for Windows NT up and working, and the ODBC drivers
> installed. I think
> everything is working somewhat. I am trying to port a SQL Server 6.5
> database over to Oracle for testing, and I have this problem :
> This is a stored procedure in SQL Server, and when I try to move this to
> Oracle it fails. Questions.
> 1) Can I do this in oracle? (return a set of data from a stored
> procedure)
> 2) if so, how?
> 3) if not why?
> CREATE PROCEDURE Allocated_Time_All_For_Date(CC IN varchar2, ID IN
> varchar2, TD IN varchar2)
> AS
> SELECT * from Allocated_Time WHERE
> company_code = CC AND
> employee_or_equipment_id = ID AND
> timesheet_date = TD
> ORDER BY rate_user_hours;
>
> Any assistance would be appreciated.

The first observation is that all your procedure parameters are all INs - there is no means of returning the data you require.

Your options are:
1) include OUT parameter(s).

If you need to return multiple rows of data at a time, you will need to set up an ARRAY type variable (TABLE OF <ORACLE type>) e.g TYPE num_array IS TABLE OF NUMBER...

2) change the procedure to a function and return data that way

Martyn Received on Wed Mar 11 1998 - 00:00:00 CST