Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

RECORD TYPE rows from multiple tables

Hi,


I have to query 50 columns from different tables in PL-SQL block. (only one row will be selected)
So in INTO clause i have to declare 50 variables or otherwise I have to create RECORD TYPE of that specified query so is there any way we can define the dynamic RECORD TYPE depending on multiple table columns.

THANKS AND REGARDS
0
Sudees
Asked:
Sudees
3 Solutions
 
MikeOM_DBACommented:
Your question is not clear, please provide sample of the source table definitions, state the requirements clearly and post a sample of the expected results.
0
 
Wasim Akram ShaikCommented:
Re-read your question and understood what you meant..
you want the record type to be defined dynamically, this is not directly possible, however there is a work around for this kind of scenario

where in you have to define a weak cursor


Say you have a function returning a ref cursor like:
CREATE FUNCTION record_type_cursor (v_sql) RETURN SYS_REFCURSOR AS
   l_cur SYS_REFCURSOR;
BEGIN
   OPEN l_cur FOR
      v_sql;
   RETURN l_cur;
END;You can then call it like:
SQL> DECLARE
  2     v_sql varchar2(1000):='select * from emp';
  3
 4    
  5     l_rec rec_model%ROWTYPE;
  6     l_cur SYS_REFCURSOR;
  7  BEGIN
  8     l_cur := record_type_cursor (v_sql);
  9     LOOP
 10        FETCH l_cur INTO l_rec;
 11        EXIT WHEN l_cur%NOTFOUND;
 12        DBMS_OUTPUT.Put_Line('First name: '||l_rec.first_name);
 13     END LOOP;
 14  END;
 15  /
0
 
SudeesSoftware DeveloperAuthor Commented:
Hi Wasim,

First of all I would like to say thanks for giving time to understand my requirement, your code is looking good but what is the purpose of this line of code
     l_rec rec_model%ROWTYPE;
This line is declaring REC_MODEL named table or view ROWTYPE and it shows its not dynamic.
And in case, if V_SQL may have more than one table to query like DEPTNO and EMP then this procedure may not work..

Thanks and Regards,
0
 
flow01Commented:
if the 50 columns are always of the same type you can  use 1 definition example


declare
-- hardcode 1 example query as cursor definition
   cursor c1
   is
   select object_name col1, OBJECT_ID col2, created col3 from user_objects where rownum = 1;
   g_rec c1%rowtype;  -- create a record definition
begin
--   open c1;
--   fetch c1 into g_rec;
--   close c1;
--   dbms_output.put_line(g_rec.col1 || ';' || g_rec.col2 || ';' || g_rec.col3);
   execute immediate 'select object_name, OBJECT_ID, created from user_objects where rownum = 1'
   into g_rec;
   dbms_output.put_line(g_rec.col1 || ';' || g_rec.col2 || ';' || g_rec.col3);
   execute immediate 'select column_name, column_id, sysdate from user_tab_cols where rownum = 1'
   into g_rec;
   dbms_output.put_line(g_rec.col1 || ';' || g_rec.col2 || ';' || g_rec.col3);
   execute immediate q'{select 'a', 2, sysdate from dual}'
   into g_rec;
   dbms_output.put_line(g_rec.col1 || ';' || g_rec.col2 || ';' || g_rec.col3);
end;
/
0
 
Wasim Akram ShaikCommented:
Yes.. I agree.. You cannot make everything dynamic.. You need to have a definition somewhere.. As plsql is a compiler based language it expects declaration, definition and then only execution part will work successfully.. If you have simple data types then go for a varchar2 declaration of all variables and declare a record set based on that.. If at all any number data types are there they will get converted implicitly.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now