Re: Is PL/SQL good for one thing only?
Date: 1995/09/18
Message-ID: <NEWTNews.811473753.22900.sesame_at_sesame.scruznet.com>#1/1
In Article<jpanicoDEyC5t.L96_at_netcom.com>, <jpanico_at_netcom.com> writes:
>
> All the PL/SQL example programs that use loops have exactly the same
> structure-- using the cursor, step through the rows of the target table
> and compute some value based on its columns. In other words, all the
> examples look like transaction processing. Is PL/SQL a real language,
> or it more like some kind of a macro for TP. For instance, can
> I use PL/SQL to set up a loop that will: step through the rows of a table,
use the values
> of one of the columns as a another table name on which to perform a
differenct query
> on each iteration of the loop. I would like to be able to step through the
> USER_TABLES view and print out the structure of each USER_TABLE that meets
> certain criteria. But I want control of the formatting of the output, so
that I could see
> something like the output of the DESCRIBE statement for each table.
>
> Can someone at Oracle say whether this is possible with the base PL/SQL
package
> and comment on the flexibility of PL/SQL in general?
>
> Thanks
> --
>
> Joe Panico
> NeXTStep/OpenStep Developer
> BLaCKSMITH Inc.
>
> jpanico_at_netcom.com
>
The following PL/SQL function will return a character string for each column of a table that looks like the DESCRIBE statement output. Imagine the possibilities...
function col_ddl return char is
col all_tab_columns%ROWTYPE;
ddl varchar2(30) := NULL;
CURSOR c_column IS
SELECT *
FROM all_tab_columns
WHERE owner = :owner
AND table_name = :table_name
AND column_name = :column_name;
begin
if c_column%ISOPEN then
close c_column;
end if;
OPEN c_column;
FETCH c_column
INTO col;
--* Data type
ddl := col.data_type;
--* Length, precision
if col.data_type = 'DATE' then
null;
elsif nvl(col.data_length,0) > 0 then
ddl := ddl || '(' || to_char(col.data_length) || ')';
elsif col.data_scale is not null then
if col.data_precision is not null then
ddl := ddl || '(' || to_char(col.data_scale) ||
','|| to_char(col.data_precision) ||')';
else
ddl := ddl || '(' || to_char(col.data_scale) || ')';
end if;
end if;
--* Nullable
if col.nullable = 'N' then
ddl := ddl || ' NOT NULL';
end if;
return (ddl);
end;
Received on Mon Sep 18 1995 - 00:00:00 CEST