OraFAQ Forum: SQL & PL/SQL » PL SQL table generate auto index

Home » SQL & PL/SQL » SQL & PL/SQL » PL SQL table generate auto index (Oracle 19C)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PL SQL table generate auto index [message #689812]

Wed, 01 May 2024 06:44

sss111ind
Messages: 636
Registered: April 2012
Location: India

Senior Member

HI All,

When the values are stored in the table type the index should be automatic generated. Because the fields are mapped from Java and there is no indexx column available there. We need a unique value for each row for dml operation. Please help.

create or replace TYPE emp_rec IS object(
  ename  varchar2(100),
  sal number,
  comm number ,
  indexx number
  );
/

create or replace TYPE emp_tab IS TABLE OF emp_rec;
/



declare
l_emp_tab emp_tab ;
begin
select emp_rec(ename,sal,comm,rownum) bulk collect into  l_emp_tab from emp;
for i in l_emp_tab.first .. l_emp_tab.last loop
    dbms_output.put_line(l_emp_tab(i).indexx);--need to create auto index not manual
 
end loop;
end;

Report message to a moderator

Re: PL SQL table generate auto index [message #689813 is a reply to message #689812]

Wed, 01 May 2024 07:27

Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What do you mean by an auto-index? isn't I, the table index one?

SQL> declare
  2  l_emp_tab emp_tab ;
  3  begin
  4  select emp_rec(ename,sal,comm,rownum) bulk collect into  l_emp_tab from emp;
  5  for i in l_emp_tab.first .. l_emp_tab.last loop
  6      dbms_output.put_line(i);
  7
  8  end loop;
  9  end;
 10  /
1
2
3
4
5
6
7
8
9
10
11
12
13
14

PL/SQL procedure successfully completed.

Report message to a moderator

Re: PL SQL table generate auto index [message #689814 is a reply to message #689813]

Wed, 01 May 2024 08:40

sss111ind
Messages: 636
Registered: April 2012
Location: India

Senior Member

Thank You Michel Sir,

But can I get index access from table type. Is it possible ?

Report message to a moderator

Re: PL SQL table generate auto index [message #689815 is a reply to message #689812]

Wed, 01 May 2024 09:36

Barbara Boehmer
Messages: 9094
Registered: November 2002
Location: California, USA

Senior Member

I am not quite sure what you are looking for or why. It would probably help to have more information about the process.

If you are looking for a permanently assigned unique id you are not going to have that with a temporary variable in a pl/sql block.

If you insert the data into a permanent table of a type, then Oracle automatically generates a unique object_id that you can access just like you would a rowid, using the pseudocolumn, except that the object_id is not subject to change. Please see the example below.

C##SCOTT@XE_21.3.0.0.0> create or replace TYPE emp_rec IS object(
  2    ename  varchar2(100),
  3    sal number,
  4    comm number);
  5  /

Type created.

C##SCOTT@XE_21.3.0.0.0> show errors
No errors.
C##SCOTT@XE_21.3.0.0.0> create TABLE emp_tab OF emp_rec
  2  /

Table created.

C##SCOTT@XE_21.3.0.0.0> show errors
No errors.
C##SCOTT@XE_21.3.0.0.0> insert into emp_tab(ename, sal, comm) select ename, sal, comm from emp
  2  /

14 rows created.

C##SCOTT@XE_21.3.0.0.0> column ename	 format a15
C##SCOTT@XE_21.3.0.0.0> column object_id format a32
C##SCOTT@XE_21.3.0.0.0> select ename, sal, comm, object_id from emp_tab
  2  /

ENAME                  SAL       COMM OBJECT_ID                                 
--------------- ---------- ---------- --------------------------------          
SMITH                  800            974905BDCD334183ACEB3D600BE4A4EF          
ALLEN                 1600        300 CC18F98E54414442B11C58FCF5547FF3          
WARD                  1250        500 CAD84222E34048368BFD1EAEC68359A7          
JONES                 2975            5D31BB1A95594BBA8D2C183C343000BE          
MARTIN                1250       1400 66CB1F19CCE94903A246D581A05EA0B5          
BLAKE                 2850            921CEC075461465EA45E323A039BDED4          
CLARK                 2450            4DCC61A224E54AD9A67346EAE5E84946          
SCOTT                 3000            B8C8AB3BC4804DEAA239806E481D60BD          
KING                  5000            4166E98391B4448E8DCF6E6CAB9EA5AF          
TURNER                1500          0 AF71DBF39FA7436E941330AB3FEFD492          
ADAMS                 1100            5F1F85332B544980A8833C8001F439B1          
JAMES                  950            E7C8A44B779F421787A7B4FF92482D3C          
FORD                  3000            9AD50191F9B24DF48D427BB0BC661BF0          
MILLER                1300            0A743EFA0E05470C9D7FA30EEE535FC7          

14 rows selected.

Report message to a moderator

Previous Topic:	insert data from csv file
Next Topic:	ORA-30372: fine grain access policy conflicts with materialized view

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 15 14:01:39 CDT 2024