Home » SQL & PL/SQL » SQL & PL/SQL » Is %ROWCOUNT compatible with 10g?
Is %ROWCOUNT compatible with 10g? [message #221727] Wed, 28 February 2007 03:37 Go to next message
shahul_555
Messages: 4
Registered: February 2007
Location: India
Junior Member
I get the following error:
PL/SQL: ORA-00911: invalid character

The error is pointed to the % in c_queues%ROWCOUNT. The same procedure gets compiled in 8i but not in 10g. Is there any compatibility issue?


PROCEDURE get_stats IS
CURSOR c_queues IS
SELECT Q.QUEUE_TABLE queue_table,
Q.NAME queue_name,
S.DESTINATION destination,
FROM USER_QUEUE_SCHEDULES S,
USER_QUEUES Q
WHERE Q.QUEUE_TYPE = 'NORMAL_QUEUE'
AND Q.NAME = S.QNAME(+);

BEGIN
FOR c_queues_rec IN c_queues LOOP

SELECT LOWER(USERNAME)||'@'||REPLACE LOWERHOST),'.world','')
INTO l_queue_rec_list(c_queues%ROWCOUNT).prop_target
FROM USER_DB_LINKS
WHERE DB_LINK = c_queues_rec.destination;

END LOOP;
END;

Re: Is %ROWCOUNT compatible with 10g? [message #221791 is a reply to message #221727] Wed, 28 February 2007 07:56 Go to previous messageGo to next message
webcomsystems
Messages: 4
Registered: February 2007
Location: UK
Junior Member
Hi,

Not sure about compatibility issues.

But I'd recommend putting the %ROWCOUNT value into a variable and doing a to_number() on it just to make sure.

Hope this helps.


David Drewette
http://www.oracleunittesting.com
Re: Is %ROWCOUNT compatible with 10g? [message #221907 is a reply to message #221791] Thu, 01 March 2007 00:02 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Or you could just BULK COLLECT, since all you want is to load the fetched values into a collection.

Ross Leishman
Re: Is %ROWCOUNT compatible with 10g? [message #221911 is a reply to message #221727] Thu, 01 March 2007 00:37 Go to previous messageGo to next message
shahul_555
Messages: 4
Registered: February 2007
Location: India
Junior Member
Thanks for your replies. But the problem here is I have migrated the database from oracle 8i to 10g. So I wanted to know when it works in 8i why not in 10g? Also I dont know the functionality of the procedure so I cannot/should not change it as my job is only to migrate the database and compile all invalid objects.
Re: Is %ROWCOUNT compatible with 10g? [message #222008 is a reply to message #221911] Thu, 01 March 2007 07:55 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
shahul_555 wrote on Thu, 01 March 2007 00:37
Thanks for your replies. But the problem here is I have migrated the database from oracle 8i to 10g. So I wanted to know when it works in 8i why not in 10g? Also I dont know the functionality of the procedure so I cannot/should not change it as my job is only to migrate the database and compile all invalid objects.


Well that is easy then. If your job is simply to migrate and compile invalid objects, and they won't compile, and you aren't allowed to change anything, then you are finished with your assignment.

The % operator is alive and well in 10g.

And that definitely looks like a bulk collect situation.
Previous Topic: How to remove carriage return?
Next Topic: Date Validation
Goto Forum:
  


Current Time: Sun Dec 04 21:01:29 CST 2016

Total time taken to generate the page: 0.04483 seconds