Convert a MS-SQL function to Oracle

[henryz]

Hi,

I have a function in MS-SQL, but have difficulity to convert it into Oracle, can someone help please?

Here is the Function:

--===== Create a function to concatenate orders by customer
CREATE FUNCTION dbo.ConcatOrderNum (@pCustomerID AS INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @oResult VARCHAR(8000)
SELECT @oResult= COALESCE(@oResult+',','')+OrderNumber
FROM Customer_Orders WITH (NOLOCK)
WHERE CustomerID = @pCustomerID
ORDER BY OrderNumber
RETURN @oResult
END
go

Here is what I tried:
CREATE FUNCTION dbo.ConcatOrderNum (@pCustomerID AS INT)
RETURNS VARCHAR(8000)
AS
BEGIN
@oResult VARCHAR(8000);
SELECT @oResult := COALESCE(@oResult||',','')||OrderNumber
FROM Customer_Orders WITH (NOLOCK)
WHERE CustomerID = @pCustomerID
ORDER BY OrderNumber;
RETURN @oResult;
END;
/

But I get compilation error, can someone spot where I was stuck?

Thanks,
Henry

 

[p5wizard]

try 'show errors' after you get the compilation error

SQL> show errors
Errors for FUNCTION CONCATORDERNUM:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/26 PLS-00103: Encountered the symbol "@" when expecting one of the
following:
<an identifier> <a double-quoted delimited-identifier>
current

In PL/SQL a variable identifier is not preceded by an @

You take it from here...



HTH,

p5wizard

 

[Beilstwh]

CREATE FUNCTION ConcatOrderNum (pCustomerID NUMBER)
RETURNS VARCHAR
AS
Result VARCHAR2(8000);
BEGIN
SELECT DECODE(Result,NULL,NULL,',')||OrderNumber
FROM Customer_Orders
WHERE CustomerID = pCustomerID
ORDER BY OrderNumber;

RETURN (oResult);
END;

Bill
Oracle DBA/Developer
New York State, USA

 

[Beilstwh]

Wrong!!!, sorry miss typed

CREATE FUNCTION ConcatOrderNum (pCustomerID NUMBER)
RETURNS VARCHAR
AS
cursor get_data as
SELECT OrderNumber
FROM Customer_Orders
WHERE CustomerID = pCustomerID
ORDER BY OrderNumber;

Result VARCHAR2(8000);
BEGIN

result := null;
for pnt in get_data loop
if result is not null then
result := result||',';
end if;
if length(result) + length(pnt.ordernumber) > 8000 then
exit;
end if;
result := result||pnt.OrderNumber;
end loop;
RETURN (oResult);
END;

Bill
Oracle DBA/Developer
New York State, USA