Re: [PL/SQL] Help - Select into from a variable tablename?

From: Vyacheslav Rossov <slava_at_cbank.kz>
Date: 1997/04/19
Message-ID: <01bc4c9f$0204eec0$158ae2c2@rv>#1/1

Hi,

You can do it via dbms_sql package. It's my function which use one.

---

CREATE OR REPLACE FUNCTION admin_SQL(p_SQL IN VARCHAR2) RETURN NUMBER IS

  v_Cursor   NUMBER;
  v_Command  VARCHAR2(200) := Upper(RTrim(LTrim(p_SQL)));
  v_Result   NUMBER;
  v_NumRows  INTEGER;

BEGIN
  v_Cursor := DBMS_SQL.Open_Cursor;
  DBMS_SQL.Parse(v_Cursor, v_Command, DBMS_SQL.V7);   DBMS_SQL.Define_Column(v_Cursor, 1, v_Result);

  v_NumRows := DBMS_SQL.Execute(v_Cursor);
  IF DBMS_SQL.Fetch_Rows(v_Cursor) > 0 THEN
    DBMS_SQL.Column_Value(v_Cursor, 1, v_Result);

  ELSE
    v_Result := -1;
  END IF;
  DBMS_SQL.Close_Cursor(v_Cursor);
  RETURN (v_Result);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_SQL.Close_Cursor(v_Cursor);
    RETURN (-1);
END admin_SQL;
/

---

Example of usage:

DECLARE
  numOfRows NUMBER;
BEGIN
  numOfRows := admin_SQL('select count(*) from my_table');   dbms_output.put_line('Number of rows for table my_table is '||to_Char(numOfRows));
END;

---

Have a nice day,
Slava

> From: David & Janet Harris <music_at_waymark.com>
> Newsgroups: comp.databases.oracle.misc
> Subject: [PL/SQL] Help - Select into from a variable tablename?
> Date: 19 ÁÐÒÅÌÑ 1997 Ç. 07:35
>
> I need some help selecting into from a variable tablename.
> Example:
>
> Procedure CountTable(tbl char) is
> cnt number;
> BEGIN
> select count(*) into cnt from tbl;
> dbms_output.put_line (tbl || ' ' || cnt);
> end;
>
> PL/SQL interprets tbl as an existing table and fails. I would like to
 have a
> loop call this routine for each table in a user's schema yielding these
> results:
>
> Tablename Cnt
> --------- ---
> TABLE1 234
> TABLE2 823
> .
> .
> .
>
> Any ideas? Thanks in advance.
>
> --David H.
>
Received on Sat Apr 19 1997 - 00:00:00 CDT