Re: select from (select...)

From: Detlev Goebel <detlev.goebel_at_gzs.de>
Date: 1997/06/24
Message-ID: <33AF74C9.4687@gzs.de>#1/1

Oren Nakdimon wrote:
> =
 

> Hi.
> I'm trying to do a query with sub-query in the FROM clause, in PL/SQL
> (Oracle 7.3.3, PL/SQL 2.3.2.3.1 on Windows NT 4).
> The sub-query is using some variables, and it's not working.
> =
 

> This is a simple example:
> =
 

> declare
> a number;
> r dual%rowtype;
> begin
> select *
> into r
> from ( select *
> from dual
> where a=3Da );
> end;
> /
> =
 

> declare
> *
> ERROR at line 1:
> ORA-06550: line 7, column 9:
> PLS-00320: the declaration of the type of this expression is incomplete=
 

> or
> malformed
> ORA-06550: line 5, column 4:
> PL/SQL: SQL Statement ignored
> =
 

> When I don't use variables in the sub-query it works.
> It works also when I use the sub-query in the FROM clause of other
> statements (e.g. DELETE).
> =
 

> Can someone help me ?
> Thanks, Oren.
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
 =3D=3D=3D=3D=3D
> Oren Nakdimon
> Golden Screens Ltd.
> email: oren_at_gsit.co.il
> tel: +972-3-7510836
> fax: +972-3-7518221
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D

Oren,

creating sql with variable table names is possible with dynamic SQL.

look at following SQL_statement :

CREATE OR REPLACE PROCEDURE count_rows (table_name IN VARCHAR2) IS   cur INTEGER;
  dummy INTEGER;
  rows NUMBER;
BEGIN
  cur :=3D dbms_sql.open_cursor;
  dbms_sql.parse(cur, 'select count(*) from '=A6=A6table_name,

         dbms_sql.v7);
  dbms_sql.define_column(cur, 1, rows);
  dummy :=3D dbms_sql.execute(cur);
  LOOP
    IF dbms_sql.fetch_rows(cur) > 0 THEN       dbms_sql.column_value(cur, 1, rows);     ELSE

      exit;
    END IF;
  END LOOP;

  dbms_sql.close_cursor(cur);
  dbms_output.put_line('Table: '=A6=A6table_name=A6=A6' Rows: '=A6=A6rows= );
EXCEPTION
  WHEN OTHERS THEN
    dbms_sql.close_cursor(cur);
END;
/

set serveroutput on
execute count_rows ('emp');
drop PROCEDURE count_rows;

Change that code for your need.

good luck

Detlev

• =

Detlev Goebel

mailto:detlev.goebel_at_gzs.de

---

Received on Tue Jun 24 1997 - 00:00:00 CDT