Solved

RECORD TYPE rows from multiple tables

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

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ā€¦
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

729 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