Link to home
Start Free TrialLog in
Avatar of skahlert2010
skahlert2010

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of skahlert2010
skahlert2010

ASKER

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
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();   ---->????
>>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.
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.
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!