Difference between PL/SQL and SQL. Select into

Question

I have a problem with compiling simple piece of code in PL/SQL. Here comes the code:

DECLARE
    zm VARCHAR(20);
BEGIN
    SELECT TEA_FIRST_NAME  into zm
    from students join teachers on STU_TEA_ID = TEA_ID
    where STU_ID = (select k.STU_ID from students k where k.STU_FIRST_NAME = 
    :BLOCK9.TEXT_ITEM11);
END;

When I try to compile this I see that error: Encountered the symbol error

However, when I run this piece of code in SQL Navigator:

SELECT TEA_FIRST_NAME
from students join teachers on STU_TEA_ID = TEA_ID
where STU_ID = (select k.STU_ID from students k where k.STU_FIRST_NAME = 
'Lukasz');

it runs fine and returns one record. What is happening?

I am using Oracle Forms 10g (10.1.2.3.0) PL/SQL (10.1.0.5.0). Database version 11.2.0.3.0

Which version of Forms are you using? Which version of the database? — APC
– APC, Commented Jul 5, 2013 at 15:29

Alex Poole · Accepted Answer · 2013-07-05 15:34:32Z

1

From the error it appears to be interpreting the keyword 'join' as a table alias, which is odd and maybe implies you're using a version that pre-dates Oracle adding ANSI joins - I don't use Forms so I don't know how old that would have to be. You can run the same anonymous block in your non-Forms client to see that it should work.

Explicitly aliasing the tables ought to remove the confusion:

DECLARE
    zm VARCHAR(20);
BEGIN
    SELECT TEA_FIRST_NAME  into zm
    from students s join teachers t on s.STU_TEA_ID = t.TEA_ID
    where STU_ID = (select k.STU_ID from students k where k.STU_FIRST_NAME = 
    :BLOCK9.TEXT_ITEM11);
END;

... but as it doesn't seem to understand join it still won't like that. If it really is that old then you might have to revert to the old join syntax:

DECLARE
    zm VARCHAR(20);
BEGIN
    SELECT TEA_FIRST_NAME  into zm
    from students, teachers
    where STU_TEA_ID = TEA_ID
    and STU_ID = (select k.STU_ID from students k where k.STU_FIRST_NAME = 
    :BLOCK9.TEXT_ITEM11);
END;

... though aliasing the tables would still make it clearer.

answered Jul 5, 2013 at 15:34

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kukis Over a year ago

Thank you very much. Weird thing that it seems doesn't accept "join" keyword. Also I had aliases and I've cleared them just to post code here.

Collectives™ on Stack Overflow

Difference between PL/SQL and SQL. Select into

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related