Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

RECORD TYPE rows from multiple tables

Posted on 2014-07-20
5
475 Views
Last Modified: 2014-07-21
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
Comment
Question by:Sudees
5 Comments
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 50 total points
ID: 40208013
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
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 200 total points
ID: 40208235
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
 

Author Comment

by:Sudees
ID: 40209590
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
 
LVL 20

Accepted Solution

by:
flow01 earned 250 total points
ID: 40209614
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
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40209641
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question