Solved

PL/SQL cursor in Oracle Apex

Posted on 2010-09-20
6
2,050 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

733 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