Solved

PL/SQL cursor in Oracle Apex

Posted on 2010-09-20
6
1,954 Views
Last Modified: 2012-05-10
Dear experts!
I want to practice with cursors and am working with Oracle Application Express.

I wrote a cursor, which when run in sql developer tells me p180_cnt_qb is not declared.

P180_cnt_qb is an Apex page item i.e. variable of type number in my case. Can you please tell me how to use that item value within this cursor?

A million thanks in advance!



set serveroutput on 
DECLARE
 QB_ID number();
 :p180_cnt_qb number();
 QB_ID := :p180_cnt_qb;
  CURSOR bsch_grad IS SELECT int_beschirmung_prozent FROM tbl_qb_beschirmung where lng_qb_sub = QB_ID; 
  temp_beschirmungsgrad tbl_qb_beschirmung.int_beschirmung_prozent%TYPE; 
BEGIN      

  OPEN bsch_grad;     
  LOOP         
    FETCH bsch_grad INTO temp_beschirmungsgrad;         
    EXIT WHEN bsch_grad%NOTFOUND;         
    dbms_output.put_line(temp_beschirmungsgrad);     
  END LOOP;     
  CLOSE bsch_grad; 
END;

Open in new window

0
Comment
Question by:skahlert2010
  • 3
  • 2
6 Comments
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 33722745
try :

set serveroutput on
DECLARE
 QB_ID number();
 :p180_cnt_qb number();
  CURSOR bsch_grad IS SELECT int_beschirmung_prozent FROM tbl_qb_beschirmung where lng_qb_sub = QB_ID;
  temp_beschirmungsgrad tbl_qb_beschirmung.int_beschirmung_prozent%TYPE;
BEGIN      
 QB_ID := :p180_cnt_qb;
--> assignment to a variable should be in the begin block.. not in the declaration section.

  OPEN bsch_grad;    
  LOOP        
    FETCH bsch_grad INTO temp_beschirmungsgrad;        
    EXIT WHEN bsch_grad%NOTFOUND;        
    dbms_output.put_line(temp_beschirmungsgrad);    
  END LOOP;    
  CLOSE bsch_grad;
END;
0
 

Author Comment

by:skahlert2010
ID: 33723813
hello nav_kum_v!

Thanks for having a look and answering. I still receive the same error!

Bind-Variable "p180_cnt_qb" is not declared (NOT DECLARED)
anonymous block completed

I am not quite sure whatelse I should try to get this to work.

If I hardcode the part QB_ID := 123324 I'll get the same error.

Maybe you have another idea?

Many thanks,

skahlert2010
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33723861
actually i do not know APEX pl/sql code.. i was finding a generic issue there and hence highlighted..

by the way, normally we declare like the below in pl/sql ...
QB_ID number;

is it that in APEX, we need to declare like

QB_ID number();

do you execute your code in SQL*PLUS or someother UI ?

Also what is the difference between those 2 variables as one has : in front of it.

 QB_ID number();   --> ?????
 :p180_cnt_qb number();   ---->????
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 20

Expert Comment

by:gatorvip
ID: 33825832
>>P180_cnt_qb is an Apex page item i.e. variable of type number in my case.

What are you trying to do here? If that's a page item, it can only be used on that page (or, you can check its value in session value as well) within the APEX session.

>> QB_ID number();

I, too, am not familiar with this notation. Do you perhaps mean

QB_ID number;

instead? Adding the () is not something related to APEX notation.
0
 

Author Comment

by:skahlert2010
ID: 33941104
I have still not found out how to use the page item in the cursor.
However, it works if the cursor function is saved as a procedure and the QB_ID is passed to the procedure when it is called!

Thanks for your input!

I appreciate it, although I lost track of this question recently.
0
 

Author Closing Comment

by:skahlert2010
ID: 33941124
The solution didn't directly work for me but I learned something I was unaware of! Hence it deserves a B I guess! Thank you for the input!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now