Home » SQL & PL/SQL » SQL & PL/SQL » Getting Error Encountered the symbol "@" when expecting one when excuting procedure in ora
Getting Error Encountered the symbol "@" when expecting one when excuting procedure in ora [message #229287] Fri, 06 April 2007 08:51 Go to next message
kamalat
Messages: 3
Registered: April 2007
Location: #
Junior Member
Hi,

I am getting following error when excuting follwoing function
in oracle. Can anybody help me on this. I am using this
function for converting string delimitted by "," and
stores these values into global
temporary table and i am going to use this returned global temporary table from function
in select query.

Error:

LS-00103: Encountered the symbol "@" when expecting one of the following:
<an identifier> <a double-quoted delimited-identifier>



CREATE function function_string_to_number
(
@string in VARCHAR,
@delimiter in CHAR
)
RETURN @output TABLE(
data VARCHAR(256)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)

WHILE @start < LEN(@string) BEGIN
IF @end = 0
SET @end = LEN(@string) + 1

INSERT INTO @output (data)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END



And also want the procedure for INGRES database which serve
the same purpose as the above function does.

[Updated on: Fri, 06 April 2007 08:55]

Report message to a moderator

Re: Getting Error Encountered the symbol "@" when expecting one when excuting procedure in [message #229288 is a reply to message #229287] Fri, 06 April 2007 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Here is an Oracle forum.
What you posted is not a (Oracle) PL/SQL program.
It is completly irrelevant here.

Regards
Michel

Re: Getting Error Encountered the symbol "@" when expecting one when excuting procedure in [message #229291 is a reply to message #229288] Fri, 06 April 2007 09:08 Go to previous messageGo to next message
kamalat
Messages: 3
Registered: April 2007
Location: #
Junior Member
Can u provide us the some idea to achieve
the above functionality?

We are facing following issue with the oracle query which contains more than
1000 items in the IN clause

For example:

select * from table where occurrence_id in ('1', '2', ..., '999', '1000', '1001',
'1002', ...);

If the list exceeds 1000, We get the following exception from the database.

java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000

One of the alternative is by splitting the IN clause into multiple IN clause as shown below:

select val from table where (
occurrence_id in (1...1000) or
occurrence_id in (1001...2000) )

This results in a performance issue if the list contains more than 4000 items.
As the query takes around 2 minutes 02 seconds if the list contains 3700 items. And
also we are setting query timeout to be 2.5 minutes

How to achieve this with temporary global table.

Thanks,
kamala
Re: Getting Error Encountered the symbol "@" when expecting one when excuting procedure in [message #229306 is a reply to message #229291] Fri, 06 April 2007 10:14 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
One way could be to store all those thousands of parameters into a table and use them in the WHERE clause? Such as
SELECT * FROM some_table
WHERE id IN (SELECT id FROM table_with_thousands_of_parameters);
Re: Getting Error Encountered the symbol "@" when expecting one when excuting procedure in [message #229329 is a reply to message #229287] Fri, 06 April 2007 11:53 Go to previous messageGo to next message
dba_bng
Messages: 13
Registered: April 2007
Junior Member
@string is not a valid identifier.

Identifiers can not start with @ in Oracle.
Re: Getting Error Encountered the symbol "@" when expecting one when excuting procedure in [message #229334 is a reply to message #229329] Fri, 06 April 2007 12:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Of course, this is not PL/SQL!

Regards
Michel
Re: [message #229484 is a reply to message #229288] Sun, 08 April 2007 22:31 Go to previous messageGo to next message
kamalat
Messages: 3
Registered: April 2007
Location: #
Junior Member
SELECT * FROM some_table
WHERE id IN (SELECT id FROM table_with_thousands_of_parameters);

Please help me on how to handle this in PL script
using Temporary global table -
which inserts the data contained in the string sepearated
by ","(values are broken based on ",") into global temporary table
and returns this temporary table.

I need this since the query which is being constructed is dynamic.



Thanks,
kamala

Re: Getting Error Encountered the symbol "@" when expecting one when excuting procedure in [message #229486 is a reply to message #229287] Sun, 08 April 2007 23:20 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>which inserts the data contained in the string sepearated by ","(values are broken based on ",") into global temporary table and returns this temporary table.
This "solution" smells like one cloned from SQL*Server with all the negative implications from such a borderline brain dead "design".
Comma separated lists within a single field is the antithesis of a 3rd normal form RDBMS design.
You're On Your Own (YOYO)!

[Updated on: Sun, 08 April 2007 23:21] by Moderator

Report message to a moderator

Re: Getting Error Encountered the symbol "@" when expecting one when excuting procedure in [message #229681 is a reply to message #229486] Mon, 09 April 2007 13:06 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
if occurrence_id is a string...


where (occurrence_id in ('1', '2', ..., '999')
or occurrence_id in ('1000', '1001', ..., '1999')
or ...)


or if it's a number...

where (occurrence_id in (1, 2, ..., 999)
or occurrence_id in (1000, 1001, ..., 1999)
or ...)

Previous Topic: Decode Function
Next Topic: Cardinality Examples
Goto Forum:
  


Current Time: Wed Dec 07 22:23:45 CST 2016

Total time taken to generate the page: 0.08540 seconds