Solved

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

Posted on 2014-10-02
9
264 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
  • 4
  • 4
9 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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
Comment Utility
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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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
 
LVL 31

Expert Comment

by:awking00
Comment Utility
How is v_count_table_rows declared and initialized?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Gadsden Consulting
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
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 how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

728 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now