function required in oracle - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

function required in oracle

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-30 : 05:44:28

[code]
drop function GetAPPids
GO
CREATE FUNCTION dbo.GetAPPids (@bill_no int,@str varchar(4000))
RETURNS @appids Table(bill_number int, appointment_id int)
BEGIN
DECLARE @appid int, @delimeter int, @beginid int, @cnt int
SET @cnt = 1
SET @beginid=1
SET @delimeter = CHARINDEX(',', @str, @cnt)
WHILE @delimeter>0
BEGIN
SET @appid = substring(@str,@beginid,@delimeter-1)
INSERT INTO @appids values(@bill_no, cast(@appid as int))
SET @delimeter = CHARINDEX(',', @str, @beginid)
SET @beginid = @delimeter+1
END
RETURN
END
GO

[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-30 : 05:53:24

Post in Oracle forums by specifying what you want to do thru function

Madhivanan

Failing to plan is Planning to fail

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-30 : 05:54:26

CREATE OR REPLACE FUNCTION GetAPPids (bill_no int,str varchar2)
RETURNS appids TABLE(bill_number int, appointment_id int) AS
@appid int;
@delimeter int;
@beginid int;
@cnt int;
BEGIN
cnt:=1;
beginid=1;
delimeter:= INSTR(STR,CNT,',');
WHILE @delimeter>0 LOOP
BEGIN
appid := substr(str,beginid,delimeter-1);
INSERT INTO appids values(bill_no,appid);
delimeter := INSTR( str, beginid,',');
beginid := delimeter+1;
END;
END LOOP;
RETURN appids;
END;
/

i have written like this but getting exception as
2/1 PLS-00103: Encountered the symbol "RETURNS" when expecting one of
the following:
return

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-30 : 06:10:06

try to post in www.dbforums.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources