Solved

PL/SQL cursor in Oracle Apex

Posted on 2010-09-20
6
1,996 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

786 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