PL/SQL cursor in Oracle Apex

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

skahlert2010Asked:
Who is Participating?
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
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
 
skahlert2010Author Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
gatorvipCommented:
>>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
 
skahlert2010Author Commented:
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
 
skahlert2010Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.