Home » SQL & PL/SQL » SQL & PL/SQL » complation error in function (oracle 10g)
complation error in function [message #416050] Thu, 30 July 2009 05:03 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
CREATE OR REPLACE FUNCTION GetAPPids (bill_no int,str varchar2) 
RETURN 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;
/




getting error as

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/15 PLS-00103: Encountered the symbol "(" when expecting one of the
following:
. @ % ; is authid as cluster order using external character
deterministic parallel_enable pipelined aggregate
Re: complation error in function [message #416053 is a reply to message #416050] Thu, 30 July 2009 05:08 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's not oracle syntax
Re: complation error in function [message #416054 is a reply to message #416050] Thu, 30 July 2009 05:10 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Why did you think or believe that you can copy-paste existing T-SQL code into PLSQL and expect it to compile without any errors?
Re: complation error in function [message #416055 is a reply to message #416053] Thu, 30 July 2009 05:11 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
can you suggest me in giving the syntax for it.

i want the function to work for comma seperated values to be inserted into the table using other table
Re: complation error in function [message #416062 is a reply to message #416055] Thu, 30 July 2009 05:34 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

MAy be u can try this generic function that returns table type

CREATE OR REPLACE TYPE myscalartype AS OBJECT (
POSITION NUMBER (12),
parameter_value VARCHAR2 (50)
);

CREATE OR REPLACE TYPE mytabletype AS TABLE OF myscalartype;
/

FUNCTION parse (p_text IN VARCHAR2, p_sep IN VARCHAR2)
RETURN mytabletype PIPELINED
IS
l_text LONG := p_text || p_sep;
n NUMBER;
l_rec myscalartype := myscalartype (NULL, NULL);
l_position INTEGER DEFAULT 0;
BEGIN
LOOP
n := INSTR (l_text, p_sep);

IF (NVL (n, 0) > 0)
THEN
l_position := l_position + 1;
l_rec.POSITION := l_position;
l_rec.parameter_value := SUBSTR (l_text, 1, n - 1);
l_text := SUBSTR (l_text, n + 1);
ELSE
l_rec.POSITION := NULL;
END IF;

EXIT WHEN l_rec.POSITION IS NULL;
PIPE ROW (l_rec);
END LOOP;

RETURN;
END parse;

[Updated on: Thu, 30 July 2009 05:35]

Report message to a moderator

Re: complation error in function [message #416063 is a reply to message #416062] Thu, 30 July 2009 05:40 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
how can i pass values through this.
Re: complation error in function [message #416066 is a reply to message #416062] Thu, 30 July 2009 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 30 July 2009 09:20
@Prajakta001

From your previous post:
Michel Cadot wrote on Wed, 29 July 2009 14:53
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
...

PLEASE DO IT.

...
Regards
Michel



[Updated on: Thu, 30 July 2009 05:59]

Report message to a moderator

Re: complation error in function [message #416068 is a reply to message #416063] Thu, 30 July 2009 06:00 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

or u can try

SELECT *
FROM (SELECT Trim(Substr(txt,Instr(txt,',',1,LEVEL) + 1,Instr(txt,',',1,LEVEL + 1) - Instr(txt,',',1,LEVEL) - 1)) AS token
FROM (SELECT ','
||:in_string
||',' AS txt
FROM dual)
CONNECT BY LEVEL <= Length(txt) - Length(Replace(txt,',','')) - 1)
Re: complation error in function [message #416069 is a reply to message #416066] Thu, 30 July 2009 06:02 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

Michel ,
Noted...
icon14.gif  Re: complation error in function [message #416089 is a reply to message #416068] Thu, 30 July 2009 07:22 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
good sql
Previous Topic: How to find table based on data in a column
Next Topic: Deadlocks: "Rows waited on" section explanation
Goto Forum:
  


Current Time: Tue Mar 19 05:03:29 CDT 2024