CODEWITHSUNDEEP
sqloracle-databasejoinplsqlcursor
user2604667
user2604667

Reputation: 1

How to access columns on a cursor which is a join on all elements of two tables in Oracle PL/SQL

I am trying to run a cursor on full join of two tables but having problem accessing the columns in cursor. 

CREATE  TABLE APPLE(
    MY_ID           VARCHAR(2) NOT NULL,
    A_TIMESTAMP     TIMESTAMP,
    A_NAME          VARCHAR(10)
);

CREATE  TABLE BANANA(
    MY_ID           VARCHAR(2) NOT NULL,
    B_TIMESTAMP     TIMESTAMP,
    B_NAME          VARCHAR(10)
);

I have written a Full join to return all related rows from tables A and B where any of the two timestamps are in future. i.e. if a row in table APPLE has timestamp in future then fetch row from APPLE joined with row from BANANA on MY_ID Similarly, if a row in table BANANA has timestamp in future then fetch row from BANANA joined with row from APPLE on MY_ID This full join works for me. 

select * from APPLE a full join BANANA b on  a.MY_ID = b.MY_ID where 
    (
      a.A_TIMESTAMP > current_timestamp
      or b.B_TIMESTAMP > current_timestamp 
    );

Now I want to iterate over each joined record and do some processing. I am able to access the columns which are only present in one tables but getting error when trying to access the column names which are same in both tables. For ex. ID in this case.

create or replace 
PROCEDURE testProc(someDate IN DATE)
AS
CURSOR c1 IS
    select * from APPLE a full join BANANA b on  a.MY_ID = b.MY_ID where 
    (
      a.A_TIMESTAMP > current_timestamp
      or b.B_TIMESTAMP > current_timestamp 
    );

BEGIN
    FOR rec IN c1
    LOOP    
        DBMS_OUTPUT.PUT_LINE(rec.A_NAME);
        DBMS_OUTPUT.PUT_LINE(rec.A_TIMESTAMP);
        DBMS_OUTPUT.PUT_LINE(rec.MY_ID);
    END LOOP;
END testProc;

I get this error when I compile the above proc:

Error(16,28): PLS-00302: component 'MY_ID' must be declared

and I am not sure how would I access the MY_ID element. I am sure it will be pretty straight forward but I am new to database programming and have been trying but unable to find the right way to do it. Any help is appreciated. Thanks

Upvotes: 0

Views: 8849

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50057

One other thing you can do in this case is to join the tables with the USING clause instead of using ON, as in:

select *
  from APPLE a
  full join BANANA b
    USING (MY_ID)
where a.A_TIMESTAMP > current_timestamp or
      b.B_TIMESTAMP > current_timestamp

USING can be used if the columns on both tables have the same name, and the comparison of the key values is made using the equality ('=') operator. In the result set there will be one column named MY_ID along with the other columns from both table (A_TIMESTAMP, B_TIMESTAMP, etc).

Share and enjoy.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270733

I assume the problem is that MY_ID is defined in both tables, so * gets both of them. Try defining the cursor using this query:

select coalesce(A.MY_ID, B.MY_ID) as MY_ID,
       A_TIMESTAMP, A_NAME, B_TIMESTAMP, B_NAME
from APPLE a full join
     BANANA b
     on a.MY_ID = b.MY_ID
where a.A_TIMESTAMP > current_timestamp or b.B_TIMESTAMP > current_timestamp;

EDIT:

You have two issues with conflicting columns. If this were just an inner join, you could do:

select A.*, B_TIMESTAMP, B_NAME

That is, you can select the columns from one table using * and the rest individually. However, this is a full outer join, so there is a set of columns where you want to use coalesce().

So, the best answer is that you should list out all the columns. This is good coding practice anyway, and helps protect the code from inadvertent mistakes when columns are added or removed from the table.

Upvotes: 1

Related Questions