Thanks for the question, Saminathan.

Asked: December 26, 2003 - 8:12 pm UTC

Last updated: December 29, 2003 - 11:09 am UTC

Version: 8.1.7.3

Viewed 1000+ times

You Asked

Dear Tom,

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1058232381458#10353825155270 <code>

I am exactly looking for this 2 dimensional array(name-value pair) feature in 8.1.7.3.

Could you please provide me the correlated_array code? I did search this site for "correlated_array" but no luck.

<Tom_Answer>
For example, once upon a time ago I wrote a simple "correlated array" type. It was an object type with some getter/setter methods, a nested table of NAME/VALUE
pairs. I could create a variable:

declare
x correlated_array := correlated_array();
begin
x.set('somename','someval');
x.set('someothername','someotherval');

dbms_output.put_line( x.get('somename') );
dbms_output.put_line( x.get('someothername') );
</Tom_Answer>

Thanks in advance,
Sami

and Tom said...

PROMPT CorrelationType
create or replace type CorrelationType as object
( name varchar2(50),
value varchar2(8192)
)
/

PROMPT CorrelationArrayType
create or replace type CorrelationArrayType as varray(255) of CorrelationType
/

PROMPT CorrelatedArray Spec
create or replace type CorrelatedArray as object
(
vals CorrelationArrayType,
member function valueof( p_name in varchar2 ) return varchar2,
pragma restrict_references(valueof,wnds,rnds),
member procedure addpair( p_name in varchar2, p_value in varchar2 ),
member procedure addHeaderline( p_line in varchar2 ),
member procedure updateValue( p_name in varchar2, p_value in varchar2 )
)
/

PROMPT CorrelatedArray Body
create or replace type body CorrelatedArray as

member procedure updateValue( p_name in varchar2, p_value in varchar2 ) is
l_found boolean := FALSE;
begin
for i in 1 .. vals.count loop
if vals(i).name = p_name then
vals(i).value := p_value;
l_found := TRUE;
exit;
end if;
end loop;
if not l_found then
addpair( p_name, p_value );
end if;
end updateValue;

member function valueof( p_name in varchar2 ) return varchar2 is
l_name varchar2(50) default upper(p_name);
l_idx number default 1;
begin
for i in 1 .. vals.count loop
exit when ( vals(i).name = l_name );
l_idx := l_idx+1;
end loop;

if ( l_idx <= vals.count ) then
return vals(l_idx).value;
else
return NULL;
end if;
end valueof;

member procedure addpair( p_name in varchar2, p_value in varchar2 ) is
begin
vals.extend;
vals( vals.count ) := CorrelationType( upper(p_name), p_value );
end addpair;

member procedure addHeaderLine( p_line in varchar2 ) is
l_n number default instr(p_line,':');
begin
addpair( ltrim(rtrim(substr( p_line, 1, l_n-1 ))),
ltrim(rtrim(substr(p_line,l_n+1))) );
end addHeaderLine;

end;
/

show error

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Usage of "addHeaderLine"

Sami, December 27, 2003 - 7:51 pm UTC

Dear Tom, Thanks a million!

I understood all the methods(addpair,updateValue,Valueof) but NOT clear about addHeaderLine member.

Coudl you please give me simple example how it will be used?

Thanks again,
Sami

December 28, 2003 - 10:40 am UTC

it was a routine I used when parsing internet style headers which are typically in the form:

Name: Value

Like:

From: Tkyte@oracle.com
To: Someone@somewhere

it would just parse the header line around the ":"

modified addpair() --vals.extend;

Sami, December 28, 2003 - 6:24 pm UTC

Dear Tom,

Your code is really useful in many ways in our dev.

A)
I modified addpair() like below. Otherwise the very 1st entry is getting stored in array(vals) 2nd position.

member procedure addpair( p_name in varchar2, p_value in varchar2 ) is
l_count number default 0;
begin
vals( vals.count ) := CorrelationType( upper(p_name), p_value,l_count );
vals.extend;
end addpair;

B)Also added new member to get "name-string" based on the position.

member function valueAt( p_position in number ) return varchar2 is
begin
if ( p_position <= vals.count ) then
return vals(p_position).name;
else
return NULL;
end if;
end valueAt;

Thanks for all your help!
Sami

December 29, 2003 - 9:50 am UTC

a) no, thats not right.

vals starts with 0 elements.

vals.extend makes it have 1
vals(vals.count) would be vals(1)

the extend should be before the assignment.

Correct me If i am wrong?

Saminathan Seerangan, December 29, 2003 - 10:25 am UTC

Dear Tom,

Kindly explain why CA.VALUEAT(1) becomes NULL here. Is something wrong in my "ValueAt(p_position in nymber)" member body.

I think PL/SQL varray  is bit differ
(starting from position 1) from other languages 
like C or JAVA. Even PL/SQL varray diagram dipicts the same[x(1) x(2)... x(10)]

Correct me if I am wrong?

SQL> DECLARE
  2      CT CORRELATIONTYPE;
  3      CAT CORRELATIONARRAYTYPE;
  4      CA  CORRELATEDARRAY;
  5      K NUMBER :=0;
  6  BEGIN
  7      DBMS_OUTPUT.ENABLE(1000000);
  8      CAT:=CORRELATIONARRAYTYPE(CT);
  9      CA:=CORRELATEDARRAY(CAT);
 10
 11      CA.ADDPAIR('AUTHOR','TOM KYTE');
 12      CA.ADDPAIR('WEBSITE','ASKTOM.ORACLE.COM');
 13      CA.ADDPAIR('COMPANY','ORACLE');
 14      CA.ADDPAIR('BOOKNAME','EFFECTIVE ORACLE BY DESIGN');
 15
 16      DBMS_OUTPUT.PUT_LINE( CA.VALUEAT(1)||' is '||CA.VALUEOF(CA.VALUEAT(1)));
 17      DBMS_OUTPUT.PUT_LINE( CA.VALUEAT(2)||' is '||CA.VALUEOF(CA.VALUEAT(2)));
 18      DBMS_OUTPUT.PUT_LINE( CA.VALUEAT(3)||' is '||CA.VALUEOF(CA.VALUEAT(3)));
 19      DBMS_OUTPUT.PUT_LINE( CA.VALUEAT(4)||' is '||CA.VALUEOF(CA.VALUEAT(4)));
 20      DBMS_OUTPUT.PUT_LINE( CA.VALUEAT(5)||' is '||CA.VALUEOF(CA.VALUEAT(5)));
 21  END;
 22  /

is <======CA.VALUEAT(1)
AUTHOR is TOM KYTE <======CA.VALUEAT(2)
WEBSITE is ASKTOM.ORACLE.COM
COMPANY is ORACLE
BOOKNAME is EFFECTIVE ORACLE BY DESIGN

PL/SQL procedure successfully completed.

SQL>


==============VALUEAT BEGIN======================
 MEMBER FUNCTION VALUEAT( P_POSITION IN NUMBER )  RETURN VARCHAR2 IS
  BEGIN                                                                                     
    IF ( P_POSITION <= VALS.COUNT ) THEN
      RETURN VALS(P_POSITION).NAME;
    ELSE
      RETURN NULL;
    END IF;
  END VALUEAT;   


==============VALUEAT END======================

December 29, 2003 - 10:55 am UTC

ops$tkyte@ORA9IR2> DECLARE
  2      CA  CORRELATEDARRAY := CorrelatedArray(CorrelationArrayType());
  3  BEGIN
  4      CA.ADDPAIR('AUTHOR','TOM KYTE');
  5      CA.ADDPAIR('WEBSITE','ASKTOM.ORACLE.COM');
  6      CA.ADDPAIR('COMPANY','ORACLE');
  7      CA.ADDPAIR('BOOKNAME','EFFECTIVE ORACLE BY DESIGN');
  8          for i in 1 .. ca.vals.count
  9          loop
 10          DBMS_OUTPUT.PUT_LINE( i || ') ' || CA.vals(i).name||' is '||CA.vals(i).value );
 11          end loop;
 12  END;
 13  /

1) AUTHOR is TOM KYTE
2) WEBSITE is ASKTOM.ORACLE.COM
3) COMPANY is ORACLE
4) BOOKNAME is EFFECTIVE ORACLE BY DESIGN
 
PL/SQL procedure successfully completed.
 


you stuffed something in there in the "first" position in the way you initialized the variable.  The above shows how I would initialize it and the output (using MY code...)

Fact speaks louder than theory

Sami, December 29, 2003 - 11:09 am UTC

Thanks Tom,Really appreciated. I could not give you more than *****(the response rate)

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Usage of "addHeaderLine"

modified addpair() --vals.extend;

Correct me If i am wrong?

Fact speaks louder than theory

More to Explore

PL/SQL demos

PL/SQL docs

Two dimensional array(name-value pair) feature in 8.1.7.3.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Usage of "addHeaderLine"

modified addpair() --vals.extend;

Correct me If i am wrong?

Fact speaks louder than theory

More to Explore

PL/SQL demos

PL/SQL docs