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!
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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(); ---->????
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.
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.
ASKER
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.
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.
ASKER
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!
ASKER
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