You Asked

emp table
create table emp(empno number,ename,varchar2(30),sal number deptno number);
dept table
create table dept(deptno number,deptname,varchar2(30);

declare
type ty_test(emp emp%rowtype,dept dept%rowtype);
type ty_table is table of ty_test;
tbl_record ty_table:=ty_table();
v_table1:='select * from emp';
v_table2:='select * from dept';
I number:=1;
val varchar2(300);
c1 sys_refcursor;
begin
open c1 for v_table1;
FETCH C1 INTO tbl_record(I).p_emp;
EXIT WHEN C1%NOTFOUND;
I:=i+1;
END LOOP;
CLOSE C1;
i:=1;
open c1 for v_table2;
FETCH C1 INTO tbl_record(I).p_dept;
EXIT WHEN C1%NOTFOUND;
I:=i+1;
END LOOP;
CLOSE C1;
/*
my requirement is i will get a parameter of a number ,table name and column_name i should get that value from the collection tbl_record
say parameter is no : 1, table_name = 'EMP' COLUMN_NAME='ENAME'
how do i get the value of tbl_record(no).table_name.COLUMN_NAME dynamically
if i hardcode tbl_record(1).emp.ENAME and assign to variable i can get it for example ( val:=hardcode tbl_record(1).emp.ENAME) ..how do i do it dynamically based on parameter
*/
end;

and Connor said...

I'm not sure I follow your code - certainly the constructs are not valid

SQL> declare
  2  type ty_test(emp scott.emp%rowtype,dept scott.dept%rowtype);
  3  begin
  4    null;
  5  end;
  6  /
type ty_test(emp scott.emp%rowtype,dept scott.dept%rowtype);
            *
ERROR at line 2:
ORA-06550: line 2, column 13:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
; is authid as force under accessible
ORA-06550: line 3, column 1:
PLS-00103: Encountered the symbol "BEGIN"
ORA-06550: line 5, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
end not pragma final instantiable order overriding static
member constructor map

If you want to return both EMP and DEPT data, then you would return 2 collections to the calling environments. However, if your really saying that these are just examples, and that we could be passing an arbitrary list of tables, then we're into dynamic SQL territory. If the number and/or name of columns that are coming back from a cursor are not known until run time, then this will require dynamic SQL. Here's an example of a script that demonstrates the various facilities in DBMS_SQL to see

- what columns are in the query
- what data types they are
- how to fetch them iteratively

SQL> set serverout on
SQL> declare
  2      l_query varchar2(32767) := 'select * from scott.dept';
  3
  4      l_theCursor     integer default dbms_sql.open_cursor;
  5      l_columnValue   varchar2(4000);
  6      l_status        integer;
  7      l_descTbl       dbms_sql.desc_tab;
  8      l_colCnt        number;
  9      n number := 0;
 10    procedure p(msg varchar2) is
 11      l varchar2(4000) := msg;
 12    begin
 13      while length(l) > 0 loop
 14        dbms_output.put_line(substr(l,1,80));
 15        l := substr(l,81);
 16      end loop;
 17    end;
 18  begin
 19      dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
 20      --
 21      -- how to get the columns from the query
 22      --
 23      dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
 24
 25      --
 26      -- how to define return values from the cursor once we fetch from it
 27      --
 28      for i in 1 .. l_colCnt loop
 29          dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
 30      end loop;
 31
 32      l_status := dbms_sql.execute(l_theCursor);
 33
 34      --
 35      -- how to fetch each column and each row
 36      --
 37      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
 38          for i in 1 .. l_colCnt loop
 39              dbms_sql.column_value( l_theCursor, i, l_columnValue );
 40              p( rpad( l_descTbl(i).col_name, 30 )
 41                || ': ' ||
 42                l_columnValue );
 43          end loop;
 44          dbms_output.put_line( '-----------------' );
 45          n := n + 1;
 46      end loop;
 47      if n = 0 then
 48        dbms_output.put_line( chr(10)||'No data found '||chr(10) );
 49      end if;
 50  end;
 51  /
DEPTNO                        : 10
DNAME                         : ACCOUNTING
LOC                           : NEW YORK
-----------------
DEPTNO                        : 20
DNAME                         : RESEARCH
LOC                           : DALLAS
-----------------
DEPTNO                        : 30
DNAME                         : SALES
LOC                           : CHICAGO
-----------------
DEPTNO                        : 40
DNAME                         : OPERATIONS
LOC                           : BOSTON
-----------------

Rating

(1 rating)

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

Comments

Rajesh, March 03, 2017 - 2:49 pm UTC

working example:
 emp table
create table emp(empno number,ename varchar2(30),sal number ,deptno number);
dept table
 create table dept(deptno number,deptname varchar2(30));
insert into emp values (1,'A',100,10);
insert into emp values (2,'B',100,20);
INSERT INTO DEPT VALUES(10,'ACCOUNTING');
INSERT INTO DEPT VALUES(20,'ITSERVICE');

  declare
  type ty_test is RECORD(p_emp emp%rowtype,p_dept dept%rowtype);
  type ty_table is table of ty_test;
  tbl_record ty_table:=ty_table();
  v_table1 varchar2(500):='select * from emp';
  v_table2 varchar2(500):='select * from dept';
  I number:=1;
  val varchar2(300);
  c1 sys_refcursor;
  begin
  tbl_record.EXTEND;
  open c1 for v_table1;
  loop
  FETCH C1 INTO tbl_record(I).p_emp;
  EXIT WHEN C1%NOTFOUND;
  tbl_record.EXTEND;
  I:=i+1;
  END LOOP;
  CLOSE C1;
  i:=1;
  open c1 for v_table2;
  loop
  IF  I > tbl_record.COUNT  THEN  tbl_record.EXTEND; END IF;
  FETCH C1 INTO tbl_record(I).p_dept;
  EXIT WHEN C1%NOTFOUND;
  I:=i+1;
  END LOOP;
  CLOSE C1;
 DBMS_OUTPUT.PUT_LINE('TEST:'||tbl_record(I).p_emp.EMPNO);
 DBMS_OUTPUT.PUT_LINE('TEST1:'||tbl_record(1).p_emp.EMPNO);
 DBMS_OUTPUT.PUT_LINE('TEST2:'||tbl_record(2).p_emp.EMPNO);
 DBMS_OUTPUT.PUT_LINE('TEST:'||tbl_record(1).p_DEPT.DEPTNO);
 DBMS_OUTPUT.PUT_LINE('TEST1:'||tbl_record(2).p_DEPT.DEPTNO);
 -- Is it possible to get the value like this val:=tbl_record(PARAMETER1).'P_'||PARAMETER2.PARAMETER3
 end;
  
/*
my requirement is i will get a parameter of a number ,table name and column_name i should get that value from the collection tbl_record
say parameter is no : 1, table_name = 'EMP' COLUMN_NAME='ENAME'
how do i get the value of tbl_record(no).table_name.COLUMN_NAME dynamically
if i hardcode tbl_record(1).emp.ENAME and assign to variable i can get it for example ( val:=hardcode tbl_record(1).emp.ENAME) ..how do i do it dynamically based on parameter
*/

March 09, 2017 - 2:58 am UTC

No, but you could write some SQL to generate the required code to put into your PLSQL routine, eg

SQL> select 'return case ' from dual union all
  2  select
  3   'when p_tname = '''||table_name||''' and p_col = '''||column_name||''' then tbl_record(p_idx).p_'||lower(table_name)||'.'||lower(column_name)
  4  from user_tab_columns
  5  where table_name in ('EMP','DEPT')
  6  union all
  7  select 'end;' from dual;

'RETURNCASE'
----------------------------------------------------------------------------------------------------------------------------------
return case
when p_tname = 'DEPT' and p_col = 'DEPTNO' then tbl_record(p_idx).p_dept.deptno
when p_tname = 'DEPT' and p_col = 'DEPTNAME' then tbl_record(p_idx).p_dept.deptname
when p_tname = 'EMP' and p_col = 'EMPNO' then tbl_record(p_idx).p_emp.empno
when p_tname = 'EMP' and p_col = 'ENAME' then tbl_record(p_idx).p_emp.ename
when p_tname = 'EMP' and p_col = 'SAL' then tbl_record(p_idx).p_emp.sal
when p_tname = 'EMP' and p_col = 'DEPTNO' then tbl_record(p_idx).p_emp.deptno
end;

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

dynamically assign a collection value to variable

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs