Solved

RECORD TYPE rows from multiple tables

Posted on 2014-07-20
5
473 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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

778 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