• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

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

 
Ranch Hand
Posts: 165
Tomcat Server Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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:-


PL-SQL-error.GIF
[Thumbnail for PL-SQL-error.GIF]
PL SQL error
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 165
Tomcat Server Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
 
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
reply
    Bookmark Topic Watch Topic
  • New Topic