Solved

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

Posted on 2014-10-02
9
271 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
Technology Partners: 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!

 
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

Industry Leaders: 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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

761 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