Converting SQL Server stored proc to Oracle 8i

[jballum]

I am in the process of converting some SQL Server stored procs to Oracle and ran into a whole bunch that return a result set.

How can this be done in Oracle 8i?

Thanx

JB

 

[sybaseguru]

JB,

Just to recap operations in a relational database act on a complete set of rows. The set of rows returned by a SELECT statement consists of all the rows that satisfy the conditions in the WHERE clause of the statement. This complete set of rows returned by the statement is known as the result set. Applications, especially interactive online applications, cannot always work effectively with the entire result set as a unit. These applications need a mechanism to work with one row or a small block of rows at a time. Cursors are an extension to result sets that provide that mechanism and Oracle uses cursors to handle result sets. Oracle reference cursors (Result Sets) allow an application to retrieve data using stored procedures and stored functions. In other words in Oracle within a stored procedure you cannot do SELECT * from emp WHERE condition. You need to create a cursor and store the result set into cursor. An example will help, although I am not sure the type of transact SQL that you have in your sp.

create or replace procedure abc_sp
(
empID IN integer
)
as
BEGIN
declare
employee_id             number(6);
first_name              varchar2(20);
LAST_NAME               varchar2(25);
JOB_ID                  varchar2(10);
hire_date               date;
salary                  number(8,2);
newSalary               number(8,2);
counter                 NUMBER(10):= 0;
CURSOR EMPLOYEE_CURSOR IS
SELECT
               employee_id,
               first_name,
               last_name,
               job_id,
               hire_date,
               salary
FROM    employees FOR UPDATE;
except_old_friend       Exception;
except_never_met_them   Exception;
begin
        DBMS_OUTPUT.ENABLE(600000);
        SELECT
                employee_id,
                first_name,
                last_name,
                job_id,
                hire_date,
                salary
        into
                employee_id,
                first_name,
                last_name,
                job_id,
                hire_date,
                salary
        from    employees
                where employees.employee_id = empID;    -- will raise an error if data not found
DBMS_OUTPUT.PUT_LINE('Next');
OPEN EMPLOYEE_CURSOR;
FETCH EMPLOYEE_CURSOR
into
               employee_id,
               first_name,
               last_name,
               job_id,
               hire_date,
               salary;
if(first_name = '$$$$$')
then
        RAISE except_old_friend;
elsif(last_name = 'fox')
then
        RAISE except_never_met_them;
else
        dbms_output.put_line( first_name||' ' ||last_name|| ' is OK');
end if;
WHILE(EMPLOYEE_CURSOR%found)LOOP
        counter := counter + 1; -- Increment debug counter
        newSalary := salary * 1.05;
        update employees
        set salary = newSalary WHERE CURRENT OF EMPLOYEE_CURSOR;
        DBMS_OUTPUT.PUT_LINE('counter = '||counter||' Employee name is '||first_name||
                             ' '||last_name||' and salary is '||newSalary);
FETCH EMPLOYEE_CURSOR
into
               employee_id,
               first_name,
               last_name,
               job_id,
               hire_date,
               salary;
END LOOP;
CLOSE EMPLOYEE_CURSOR;
EXCEPTION
WHEN no_data_found
then
        DBMS_OUTPUT.PUT_LINE('Employee record with employee_id = ' || empID || ' not found');
WHEN except_old_friend
then
        dbms_output.put_line('I know this person ' || first_name ||' ' ||last_name);
WHEN except_never_met_them
then
        dbms_output.put_line('I do not know this person ' || first_name ||' ' ||last_name);
WHEN others
then
        dbms_output.put_line('Oracle error number : '|| sqlcode|| ' and message '||sqlerrm);
end;
END abc_sp;

Hope this clarifies a bit. Otherwise send an example of yout MSSQL sp.

 

[jee]

If you are wanting the result set to be returned from a procedure, you need to use a REF CURSOR. You can look this up in the PL/SQL users guide at:

http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a77069/toc.htm