Need help to Create Dynamic Stored Procedure

[borisch]

I have a stored procured in SQL and I need to re-write it for ORACLE 8.
I am new in Oracle and still learning.
Please Help. Thanks a lot.

CREATE PROCEDURE SearchProductCategory
@Product as varchar(50),
@SearchTypeProd as int,
@Category as varchar(50),
@SearchTypeCat as int

AS
declare @SQL as varchar(8000)
begin
set @SQL='SELECT Name, Category
FROM Product
WHERE Deleted =0 '
if @Product<>''
begin
if @SearchTypeProd=1 -- starts with
begin
set @SQL= @SQL + ' and Name like ''' + @Product + '%'''
end
if @SearchTypeProd=2 -- ends with
begin
set @SQL= @SQL + ' and Name like ''%' + @Product + ''''
end
if @SearchTypeProd=3 -- contain
begin
set @SQL= @SQL + ' and Name like ''%' + @Product + '%'''
end
if @SearchTypeProd=4 -- Equal
begin
set @SQL= @SQL + ' and Name = ''' + @Product + ''''
end
end
if @Category<>''
begin
if @SearchTypeCat=1 -- starts with
begin
set @SQL= @SQL + ' and Category like ''' + @Category + '%'''
end
if @SearchTypeCat=2 -- ends with
begin
set @SQL= @SQL + ' and Category like ''%' + @Category + ''''
end
if @SearchTypeCat=3 -- contain
begin
set @SQL= @SQL + ' and Category like ''%' + @Category + '%'''
end
if @SearchTypeCat=4 -- Equal
begin
set @SQL= @SQL + ' and Category = ''' + @Category + ''''
end
end
--print(@SQL)
exec(@SQL)
end

Thanks again.

 

[jee]

You need to look at the PL/SQL manual to get the Oracle syntax. You can view this online at the Oracle technology network site. You have to sign up, but access is free. here is the URL for that manual:

http://otn.oracle.com/doc/oracle8i_816/appdev.816/a77069/toc.htm

Chapter 7 covers stored procedures and Chapter 10 covers dynamic SQL.