?
Solved

RECORD TYPE rows from multiple tables

Posted on 2014-07-20
5
Medium Priority
?
481 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
[X]
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
5 Comments
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 200 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 800 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 1000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

770 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