This week's giveaway is in the EJB and other Java EE Technologies forum.
We're giving away four copies of EJB 3 in Action and have Debu Panda, Reza Rahman, Ryan Cuprak, and Michael Remijan on-line!
See this thread for details.
The moose likes JDBC and the fly likes PL SQL cursor is throwing error...any idea? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "PL SQL cursor is throwing error...any idea?" Watch "PL SQL cursor is throwing error...any idea?" New topic
Author

PL SQL cursor is throwing error...any idea?

Vinod Vijay
Ranch Hand

Joined: Sep 13, 2011
Posts: 119

I have a quesiton for you all. I have taken a small chunk of code from already existing working PL SQL procedure which is suppose to work but it is not. I have no idea why cursor is throwing error.
Any idea???

Following below is the code and attached screen shot for your reference:-





[Thumbnail for PL SQL error.GIF]


Vinod Vijay Nair
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3434
    
  47

You cannot declare variables in the code in PL/SQL; move the cursor declaration immediately before the BEGIN line.

Moreover, you're doing a join in the code. It is very ineffective. Do all the joins directly in the SQL, it will perform better.
Vinod Vijay
Ranch Hand

Joined: Sep 13, 2011
Posts: 119

Martin Vajsar wrote:You cannot declare variables in the code in PL/SQL; move the cursor declaration immediately before the BEGIN line.

Moreover, you're doing a join in the code. It is very ineffective. Do all the joins directly in the SQL, it will perform better.


Thanks my PL SQL block started to work. But could please explain me what do you mean by "Do all the joins directly in the SQL"
I guess, I used joining in SQL only. Is there anything else to do for good performance? Please advice
Jussi Taimiaho
Ranch Hand

Joined: Mar 01, 2004
Posts: 40
You are making two consecutive loops for searching data from 3 tables, which can all be joined with an inner join -query.

...
FROM spt_quote_line, spt_bucket_global, spt_coverage
WHERE spt_quote_line.bucket_id = spt_bucket_global.bucket_id
AND spt_coverage.coverage_code = spt_bucket_global.coverage_code

and so forth
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: PL SQL cursor is throwing error...any idea?
 
Similar Threads
pl/sql error
Help required on Stored Procedure
Please help! "Wrong number or types of arguments in call to 'PUT-LINE'
PL/SQL: getting table counts for multiple tables
wrong number or types of arguments in call to 'PUT_LINE'