Solved

how to determine a value in a PL/Sql table for testing

Posted on 2014-10-02
9
272 Views
Last Modified: 2014-10-03
I'm testing some logic that populates this PL/Sql table, but I'm having trouble seeing the values when debugging.
TYPE varchar2_tabtype IS
   TABLE OF VARCHAR2(20)
   INDEX BY BINARY_INTEGER;

The procedure that uses this populates the table and passes it back
PROCEDURE GET_MTG_TIME_PERIODS
 (,P_MEETING_TIME_IN IN VARCHAR2
 ,P_DAY_OF_WEEK IN CHAR
 ,P_TABLE_IN_OUT IN OUT VARCHAR2_TABTYPE
 )
 IS

In one section of code, it's populated like this:
p_table_in_out(i) := v_end_position;

and I can see the value in debugging, made sense to me.

But in another section of code, it's populated like this:
p_table_in_out(v_count_table_rows) := 10;

and when I hover over this while debugging, it says "Not a variable" . . .

but it looks like it's doing the same thing, just a different counter for the PL/Sql table, "i" vs "v_count_table_rows"

perplexing . . .
0
Comment
Question by:Gadsden Consulting
[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
  • 4
  • 4
9 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40356923
Might be a 'debugger' issue?

What tool are you using?

Make sure v_count_table_rows and I are both numbers.  Make sure they both have a value.  Maybe v_count_table_rows is null?
0
 

Author Comment

by:Gadsden Consulting
ID: 40356924
slightwv, I don't think it's a debugger issue, although perhaps. I use PL/Sql Developer.

v_count_table_rows is a number, "i" is just defined in the loop (FOR i IN 1..v_length )

I know the logic works and populates the PL/Sql table, but just in debugging I'm running into this issue.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 40356940
Cannot help with the GUI.

Triple check that v_count_table_rows has a value between 1 and v_length.

If it does, I'm afraid you'll have to wait for an Expert that knows PL/SQL Developer or can see something I'm missing.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 32

Expert Comment

by:awking00
ID: 40357259
How is v_count_table_rows declared and initialized?
0
 

Author Comment

by:Gadsden Consulting
ID: 40357711
slightwv, thanks for following up.
>>Triple check that v_count_table_rows has a value between 1 and v_length.
- definitely it's between 1 and v_length.

awking00 -
>>How is v_count_table_rows declared and initialized?
- declared: v_count_table_rows NUMBER :=0;
- initialized:       v_count_table_rows := 1;  

I fixed my issued this morning after posting this. But I still need to figure out the debugging issue.

I changed the logic so that the part the worked before (p_table_in_out(i) := v_end_position;) now looks like this: p_table_in_out(v_count_table_rows) := v_end_position;

Will re-test and let you know.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40357813
I was able to see the post before you deleted it...

In that example is 'i' the same value as v_count_table_rows?

In other words:  Are they both set to 1,3,?

Maybe I=5 and v_count_tabel_rows is 1.
0
 

Author Comment

by:Gadsden Consulting
ID: 40357827
oops, really sorry, I guess a little panicky here . . .

When it says "it's not a variable", it's because the counter had not been set.

But when the counter is properly set and I hover my mouse at the proper time, I do get a value popping up. So it has nothing to do with debugging or the counter or anything like that, it's a user error.

As described, here's the proper value.
value in pl/sql tableI should have figured this out, I apologize for that, but thanks for the assist anyway.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40357854
>>it's because the counter had not been set.

Isn't that the same thing as what I posted above?

"Make sure they both have a value.  Maybe v_count_table_rows is null? "

I don't see the difference between 'set' and 'having a value'?
0
 

Author Comment

by:Gadsden Consulting
ID: 40359252
slightwv,

>> >>it's because the counter had not been set.
-- I should have said, "it's because the row in the PL/Sql table doesn't exist"
-- The *counter* was being set properly, but by the time I evaluated the item, the counter had been updated and no corresponding record existed in the table.

>>I don't see the difference between 'set' and 'having a value'?
- yes, you are correct, my terminology was wrong.

-------
The entire thing was confusing because of the terminology of PL/Sql developer. When I tried to look at a record in the table with an index that didn't exist, they say "Not a variable", and that confused me . . . it could have said "null" or "does not exist" . . . and then I DID see it when the counter matched a row in the table so that double confused me . . . and then I posted this and wasted everyone's time . . . sorry about that.

this is my first real case of using PL/Sql tables and got a little trigger happy . . .
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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, 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.

737 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