Home » SQL & PL/SQL » SQL & PL/SQL » Cursor Fetch For More than one tables
Cursor Fetch For More than one tables [message #431673] Thu, 19 November 2009 01:39 Go to next message
sen_sam86
Messages: 33
Registered: August 2009
Location: Chennai
Member
DECLARE
   variable declare; -- Variable which should belong to al 3 tables
   
   CURSOR c1
   IS
      SELECT *
        FROM emp_1, emo_2, emp_3;
BEGIN
   OPEN cl;

   FETCH c1
    INTO variable;
   --Calling a Procedure using that Variable  
   pr_pass(variable);
   
END;


here i want to declare a variable which should be the row type of all this three tables, and after fetching the data to that variable i need to call a procedure using that variable.

Help me please

[Updated on: Thu, 19 November 2009 02:04] by Moderator

Report message to a moderator

Re: Cursor Fetch For More than one tables [message #431681 is a reply to message #431673] Thu, 19 November 2009 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ code tags are for code not for the whole post. Use Preview button before posting.

2/
Quote:
i want to declare a variable which should be the row type of all this three tables

You have to manually declare/create a type.

3/ DON'T use "select *", list the columns you want.

Regards
Michel
Re: Cursor Fetch For More than one tables [message #431684 is a reply to message #431673] Thu, 19 November 2009 02:18 Go to previous messageGo to next message
sen_sam86
Messages: 33
Registered: August 2009
Location: Chennai
Member
DECLARE
   variable declare; -- Variable which should belong to al 3 tables
   
   CURSOR c1
   IS
      SELECT a.name, a.id, a.dob, b.name, b.id, c.name, c.id, c.postion
        FROM emp_1 a, emo_2 b, emp_3 c
        where a.id = b.id
        and a.id = c.id ;
BEGIN
   OPEN cl;

   FETCH c1
    INTO variable;
   --Calling a Procedure using that Variable  
   pr_pass(variable);
   
END;


how to declare a variable type for this are i need to go for record type...?
Re: Cursor Fetch For More than one tables [message #431686 is a reply to message #431673] Thu, 19 November 2009 02:31 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
sen_sam86 wrote on Thu, 19 November 2009 08:39
here i want to declare a variable which should be the row type of all this three tables, and after fetching the data to that variable i need to call a procedure using that variable.

Help me please

Create a type which matches (in number and data types) the result of that SELECT statement.
It may be record type declared inside procedure:
SQL> declare
  2    type tt1 is record( c1 varchar2(1), c2 varchar2(1), c3 varchar2(1) );
  3    v1 tt1;
  4    cursor c1 is
  5      select t1.dummy, t1.dummy, t3.dummy
  6      from dual t1, dual t2, dual t3;
  7  begin
  8    open c1;
  9    fetch c1 into v1;
 10    close c1;
 11  end;
 12  /

PL/SQL procedure successfully completed.
Or object type declared on SQL level:
SQL> create type tt1_ty is object ( c1 varchar2(1), c2 varchar2(1), c3 varchar2(1) );
  2  /

Type created.

SQL> declare
  2    v1 tt1_ty;
  3    cursor c1 is
  4      select tt1_ty( t1.dummy, t1.dummy, t3.dummy )
  5      from dual t1, dual t2, dual t3;
  6  begin
  7    open c1;
  8    fetch c1 into v1;
  9    close c1;
 10  end;
 11  /

PL/SQL procedure successfully completed.
Note the usage of object constructor. You may also create object type in procedure body. Further details are available in PL/SQL User's Guide and Reference, available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/.

Before any "generic" requirements to this type, firstly answer this question: what would be PR_PASS supposed to do, when its input variable had "generic" type. How should it treat e.g.
- three VARCHAR2s (which are in my example)
- one NUMBER and one DATE
- four VARCHAR2s and one RAW
?
Re: Cursor Fetch For More than one tables [message #431691 is a reply to message #431686] Thu, 19 November 2009 02:54 Go to previous messageGo to next message
sen_sam86
Messages: 33
Registered: August 2009
Location: Chennai
Member
 cursor c1 is
 select t1.dummy, t1.dummy, t3.dummy
from dual t1, dual t2, dual t3;


Hi,
If the total column we using this three means i ll go good.
But i want to nearly some 20 columns from al the tables, at that time i tried like creating a record type like

TYPE blk_rec IS RECORD (
      temp_1   t1%rowtype,
      twmp_2   t2%rowtype);

   dummy_recd   blk_rec;

i tried like this but it is showing errors
help me please
Re: Cursor Fetch For More than one tables [message #431695 is a reply to message #431691] Thu, 19 November 2009 03:04 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
sen_sam86 wrote on Thu, 19 November 2009 09:54
If the total column we using this three means i ll go good.
But i want to nearly some 20 columns from al the tables, at that time i tried like creating a record type like

TYPE blk_rec IS RECORD (
      temp_1   t1%rowtype,
      twmp_2   t2%rowtype);

   dummy_recd   blk_rec;

i tried like this but it is showing errors
help me please

Have a look into PL/SQL User's Guide and Reference for correct syntax. I doubt, something like this is possible.

How much time did you spend when using 3 columns? Thirty seconds? One minute?
For 20 columns, you will need at most 7 times more time. Instead of this, you want to spend hours searching for something that probably is not available?
Re: Cursor Fetch For More than one tables [message #431696 is a reply to message #431691] Thu, 19 November 2009 03:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Option 1: Do some typing - it's only 20 columns per table. Select the column names from user_tab_columns and usea text editor.

Option 2: Use Cursor Rowtypes
create table test_101(col_1 date, col_2 date);

create table test_102(col_3 number, col_4 number);

insert into test_101 values (sysdate, sysdate+5);
insert into test_102 values (2,7);

declare
  cursor c1 is 
    select t1.*,t2.*
    from   test_101 t1
          ,test_102 t2;
          
  r_data_2 c1%rowtype;          
begin
  open c1;
  fetch c1 into r_data_2;
  close c1;
end;
/
Re: Cursor Fetch For More than one tables [message #431697 is a reply to message #431696] Thu, 19 November 2009 03:25 Go to previous messageGo to next message
sen_sam86
Messages: 33
Registered: August 2009
Location: Chennai
Member
thanks
JRowbottom

JRowbottom wrote on Thu, 19 November 2009 14:38


r_data_2 c1%rowtype;
begin
open c1;
fetch c1 into r_data_2;
close c1;
end;
/[/code]

1. Is it possible to pass this r_data_2 variable to call a procedure
2. If so what that procedure parameter data type should be

thanks
senthil
Re: Cursor Fetch For More than one tables [message #431701 is a reply to message #431697] Thu, 19 November 2009 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
2. If so what that procedure parameter data type should be

The same one you use to declare the variable.

Regards
Michel
Re: Cursor Fetch For More than one tables [message #431712 is a reply to message #431697] Thu, 19 November 2009 04:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To pass this parameter around within a package, you should declare the cursor and the cursor rowtype as global to the package body.

If you want to pass parameters of this type around between packages, then the cursor and the type will need to be declared in a package header.
Re: Cursor Fetch For More than one tables [message #431716 is a reply to message #431691] Thu, 19 November 2009 04:15 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Declare your cursor in a package specification, along with a record of <your_cursor>%rowtype
Then you can use both outside the package.
A better way would be to hide it all inside a package, of course, so you would declare both as global inside the package body.
Previous Topic: PL/SQL - Passing PARAMETER as a TABLE
Next Topic: Session Status
Goto Forum:
  


Current Time: Wed Sep 28 15:57:48 CDT 2016

Total time taken to generate the page: 0.09450 seconds