cursor using select from subquery 
Author Message
 cursor using select from subquery

I have a select that works fine:
select aup.usid, aup.program, obj.object_name
 from (select usid, program
        from users, programs ) aup,
      program_objects obj
 where obj.program = aup.program;

However, if I want to use this query with a cursor within a
stored procedure:

 cursor c_usprobj is
  select aup.usid, aup.program, obj.object_name
   from (select usid, program
          from users, programs ) aup,
        program_objects obj
   where obj.program = aup.program;

the stored procedure gives compilation errors on the first "(".

Does anybody know what's wrong?  Is it not allowed to use
selects from subqueries in PL/SQL?

Kind reGards,
     \ /   |
      x    s
     / \
     Gerard



Mon, 19 Jul 1999 03:00:00 GMT
 cursor using select from subquery

Are you sure that this syntax is good?  Have you run this exact same
query in SQL Plus?

Quote:
>select aup.usid, aup.program, obj.object_name
> from (select usid, program
>        from users, programs ) aup,
>      program_objects obj
> where obj.program = aup.program

I have never seen a subquery used in place of a table name.

On Thu, 30 Jan 1997 20:22:13 +0100, "Gerard H. Pille"

Quote:

>I have a select that works fine:
>select aup.usid, aup.program, obj.object_name
> from (select usid, program
>        from users, programs ) aup,
>      program_objects obj
> where obj.program = aup.program;

>However, if I want to use this query with a cursor within a
>stored procedure:

> cursor c_usprobj is
>  select aup.usid, aup.program, obj.object_name
>   from (select usid, program
>          from users, programs ) aup,
>        program_objects obj
>   where obj.program = aup.program;

>the stored procedure gives compilation errors on the first "(".

>Does anybody know what's wrong?  Is it not allowed to use
>selects from subqueries in PL/SQL?

>Kind reGards,
>     \ /   |
>      x    s
>     / \
>     Gerard



Tue, 20 Jul 1999 03:00:00 GMT
 cursor using select from subquery

It doesn't work on PL/SQL 2.1 (maybe even 2.2), meaning Oracle 7.1.
It is a pain in the neck. I also have several queries like this to check on
tablespaces and other things and to change to PL/SQL I have to
rewrite the logic.

I have seen several references that this work in later versions.

Richard



Quote:
> I have a select that works fine:
> select aup.usid, aup.program, obj.object_name
>  from (select usid, program
>         from users, programs ) aup,
>       program_objects obj
>  where obj.program = aup.program;

> However, if I want to use this query with a cursor within a
> stored procedure:

>  cursor c_usprobj is
>   select aup.usid, aup.program, obj.object_name
>    from (select usid, program
>           from users, programs ) aup,
>         program_objects obj
>    where obj.program = aup.program;

> the stored procedure gives compilation errors on the first "(".

> Does anybody know what's wrong?  Is it not allowed to use
> selects from subqueries in PL/SQL?

> Kind reGards,
>      \ /   |
>       x    s
>      / \
>      Gerard



Tue, 20 Jul 1999 03:00:00 GMT
 cursor using select from subquery

Shouldn't you be using the syntax SELECT x,y,z INTO v_x,v_y,v_z ...

v_colums are variables you've declared.

Quote:
>> However, if I want to use this query with a cursor within a
>> stored procedure:

>>  cursor c_usprobj is
>>   select aup.usid, aup.program, obj.object_name
>>    from (select usid, program
>>           from users, programs ) aup,
>>         program_objects obj
>>    where obj.program = aup.program;



Sat, 24 Jul 1999 03:00:00 GMT
 cursor using select from subquery

Why no try using the dbms_sql package. The SQL engine and PL/SQL engine
differ in what they can do (sometimes). The PL/SQL engine has to catch
up with the SQL engine most of the time

Justin



Sat, 24 Jul 1999 03:00:00 GMT
 cursor using select from subquery

Quote:

> Starting 7.2 it is perfectly legal to use subquery in FROM clause
> both in SQL and PL/SQL.

Not in PL/SQL in 7.2!

Oracle7 Server Release 7.2.3.0.0 - Production Release
PL/SQL Release 2.2.3.0.0 - Production

SQL> declare x varchar2;
  2  begin
  3    select dummy into x from
  4  ( select * from dual);
  5  end;
  6  /
( select * from dual);
*
ERROR at line 4:
ORA-06550: line 4, column 1:
PLS-00103: Encountered the symbol "(" when expecting one of the
following:
<an identifier> <a double-quoted delimited-identifier>



Sun, 25 Jul 1999 03:00:00 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. Insert using a subquery or Cursor

2. Subquery in Select list item aliased and used in expression

3. Using Union subquery in select clause

4. Using a Variable as Column name in Select statement in a Cursor

5. Update while within Select cursor using VB and ODBC API

6. problem using cursor to write select statement

7. Selecting into cursor using dynamic SQL

8. SQL Select/Update VS using Cursors

9. Using vairables in SELECT statement for a Cursor:

10. fetch error when using convert statement in cursor select

11. SELECT Multiple Rows of a Table INTO a PL/Sql TableRecord Without Using Cursor

12. nested cursor select using database link gives ORA-00600


 
Powered by phpBB® Forum Software