Home » SQL & PL/SQL » SQL & PL/SQL » SQL Table type variable access (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
SQL Table type variable access [message #564727] Tue, 28 August 2012 03:59 Go to next message
saipradyumn
Messages: 187
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All ,

I am unable to insert the result set of query into corresponding SQL Table type variable where as
same functionality can be accomplished by PL/SQL table type variable. Can't we access the same by using SQL type variable?

Ex:
Step 1: SQL Object type , Table type Objects creation :
drop type sql_emp_tab_type ;

drop type  sql_emp_type ;

create or replace type sql_emp_type as object
(
  empno number,
  ename varchar2(20),
  sal   number , 
  deptno number
)
;
create or replace type sql_emp_tab_type as table of sql_emp_type ;



Step 2: Accessing the table type object from PL/SQL block

SQL> 
SQL> declare
  2  
  3    tab_type_var sql_emp_tab_type := sql_emp_tab_type();
  4  
  5    begin
  6  
  7    tab_type_var.extend(10);
  8  
  9    select se.empno, se.ename, se.sal, se.deptno bulk collect
 10      into tab_type_var
 11      from scott.emp se
 12     where se.deptno = 10;
 13  
 14    dbms_output.put_line('tab_type_var ' || tab_type_var.count);
 15  
 16  end;
 17  /
 
declare

  tab_type_var sql_emp_tab_type := sql_emp_tab_type();

  begin

  tab_type_var.extend(10);

  select se.empno, se.ename, se.sal, se.deptno bulk collect
    into tab_type_var
    from scott.emp se
   where se.deptno = 10;

  dbms_output.put_line('tab_type_var ' || tab_type_var.count);

end;
 
ORA-06550: line 12, column 5:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 10, column 3:
PL/SQL: SQL Statement ignored



Step 3: Instead of SQL Table type , if we define the corresponding PL/SQL table type variable


SQL> declare
  2  
  3  --tab_type_var   sql_emp_tab_type  := sql_emp_tab_type();
  4  
  5  type pl_sql_emp_type is record
  6  (
  7    empno number,
  8    ename varchar2(20),
  9    sal   number ,
 10    deptno number) ;
 11  
 12  type pl_sql_tab_type is table of  pl_sql_emp_type ;
 13  
 14  tab_type_var  pl_sql_tab_type ;
 15  
 16  begin
 17  
 18  --tab_type_var.extend(10);
 19  
 20  select se.empno, se.ename, se.sal, se.deptno bulk collect
 21    into tab_type_var
 22    from scott.emp se
 23   where se.deptno = 10;
 24  
 25   dbms_output.put_line( 'tab_type_var '||tab_type_var.count);
 26  
 27  end  ;
 28  /
 
tab_type_var 3
 
PL/SQL procedure successfully completed
 
SQL> 






Thanks
SaiPradyumn
Re: SQL Table type variable access [message #564729 is a reply to message #564727] Tue, 28 August 2012 04:12 Go to previous messageGo to next message
Michel Cadot
Messages: 59184
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Do NOT use object type to store data.
2/ you have to create a type row
SQL> declare
  2  
  3    tab_type_var sql_emp_tab_type := sql_emp_tab_type();
  4  
  5    begin
  6  
  7    select sql_emp_type (se.empno, se.ename, se.sal, se.deptno)
  8      bulk collect into tab_type_var
  9      from scott.emp se
 10     where se.deptno = 10;
 11  
 12    dbms_output.put_line('tab_type_var ' || tab_type_var.count);
 13  
 14  end;
 15  /
tab_type_var 3

PL/SQL procedure successfully completed.

In addition, you don't need to allocate the elements, this is done by BULK COLLECT.

Regards
Michel

[Updated on: Tue, 28 August 2012 04:14]

Report message to a moderator

Re: SQL Table type variable access [message #564733 is a reply to message #564729] Tue, 28 August 2012 04:44 Go to previous messageGo to next message
saipradyumn
Messages: 187
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Michel

1/ Do NOT use object type to store data.

Above statement means Can't we use the SQL Table type variable to store the data
or
Oracle doesn't recommend to do so

In the below example we are storing some static hard values into SQL type variable


SQL> declare
  2  
  3  tab_type_var   sql_emp_tab_type  := sql_emp_tab_type();
  4  rec_1  sql_emp_type :=  sql_emp_type(123, 'XYZ',10.23 , 20);
  5  rec_2  sql_emp_type :=  sql_emp_type(852, 'abc',18569 , 10);
  6  rec_3  sql_emp_type :=  sql_emp_type(987, 'mno',9521 , 20);
  7  
  8  
  9  begin
 10  tab_type_var.delete();
 11  tab_type_var.extend(10);
 12  tab_type_var(1)  :=  rec_1 ;
 13  tab_type_var(2)  :=  rec_2 ;
 14  tab_type_var(3)  :=  rec_3 ;
 15  
 16  for i in tab_type_var.first .. tab_type_var.last loop
 17  dbms_output.put_line( 'tab_type_var '||tab_type_var(i).ename);
 18  end loop;
 19  
 20  end  ;
 21  /
 
tab_type_var XYZ
tab_type_var abc
tab_type_var mno
tab_type_var 
tab_type_var 
tab_type_var 
tab_type_var 
tab_type_var 
tab_type_var 
tab_type_var 
 
PL/SQL procedure successfully completed
 
SQL> 


Following is my actual requirement:

I have a procedure with input parameter as SQL table type collection object. But we are not sure about the ordering the collection object.
I need to perform some operations on ordered collection only ( ordered key should be one of attribute of Object type .In above example Key should be Deptno ).
First I need to apply the ordering logic on in/put parameter then need to apply actual procedure logic.



Instead of defining one more pl/sql object , just i want to use same data type as input parameter
Re: SQL Table type variable access [message #564736 is a reply to message #564733] Tue, 28 August 2012 05:24 Go to previous message
Michel Cadot
Messages: 59184
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ This is what I recommend. Bad performances, hard to read, hard to maintain, hard to write...
2/
Quote:
In the below example we are storing some static hard values into SQL type variable

In your example you do not store in the database, this is what I meant by "store".

Quote:
I have a procedure with input parameter as SQL table type collection object. But we are not sure about the ordering the collection object.
I need to perform some operations on ordered collection only ( ordered key should be one of attribute of Object type .In above example Key should be Deptno ).
First I need to apply the ordering logic on in/put parameter then need to apply actual procedure logic.


It is not clear but it seems you understand what I mean with "hard to write"...

Quote:
Instead of defining one more pl/sql object , just i want to use same data type as input parameter


There I really don't understand what you have and what you want.
An example may be useful.

Regards
Michel


Previous Topic: decode function
Next Topic: Using escape character in dynamic SQL
Goto Forum:
  


Current Time: Tue Sep 23 05:56:15 CDT 2014

Total time taken to generate the page: 0.16737 seconds