I'm trying to create a SQL statement which contains basic information from DBA_USERS for each schema and also select from a specific table in each schema as part of the same statement.
I have part of a statement cobbled together from other answers to similar questions on StackExchange:
DECLARE
v_sql varchar2(4000);
cursor c1 is
select o.owner
, o.object_name
, u.created
, TO_CHAR(round(sum(ds.bytes)/1024/1024/1024,'0000'))||' GB'
from dba_users u
, dba_objects o
, dba_segments ds
WHERE u.account_status = 'OPEN'
and u.DEFAULT_TABLESPACE not in ('SYSAUX','SYSTEM')
and u.username=o.owner
and o.object_name='MASTER'
and o.object_type='TABLE'
and ds.owner =o.owner;
BEGIN
for REC in c1 loop
v_sql := 'select VERSION from '||REC.owner||'.'||REC.object_name;
EXECUTE IMMEDIATE v_sql;
end loop;
END;
/
This statement runs but won't show me any results as I believe it should be using a bulk collector and printing the output using DBMS_OUTPUT.PUT_LINE
The output should be something like this:
USERNAME CREATED SIZE VERSION
SchemaA 2021-01-01 20GB 1.1
SchemaB 2021-01-02 22GB 1.2.2
SchemaC 2021-01-03 18GB 1.5.8
Firstly, how should I rewrite the statement above to output to the session, and secondly, is it possible to return the results I'm expecting?
CodePudding user response:
One option to get a result as you want would be to use pipelined functions
. They deliver results in the form of a table.
By the way, your query is not completely right, as you need to join more elements. That is why is always best to use ANSI
syntax. However, I would keep your syntax to make easier for you the explanation.
Let me show you an example. I don't have this field version
, so I am using the counter of rows:
First we need to create the two types, one as an object
and the other as table of
. The first is the row, the second is the table construction.
SQL> CREATE OR REPLACE TYPE t_tf_row AS OBJECT ( username varchar2(40), created_date date, size_mb varchar2(10), counter number );
/
Type created.
SQL> CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;
/
Type created.
Now, we create a pipelined function
very similar to yours.
SQL> CREATE OR REPLACE FUNCTION get_schema_details RETURN t_tf_tab PIPELINED
AS
v_sql varchar2(4000);
v_counter pls_integer;
BEGIN
for h in
(
select o.owner
, o.object_name
, u.created
, round(ds.bytes/1024/1024/1024) as table_size
from dba_users u
, dba_objects o
, dba_segments ds
WHERE u.account_status = 'OPEN'
and u.DEFAULT_TABLESPACE not in ('SYSAUX','SYSTEM')
and u.username=o.owner
and u.username=ds.owner
and o.object_name = ds.segment_name
and o.object_type = ds.segment_type
and o.object_name='ODSPOSTING'
and o.object_type='TABLE'
)
loop
v_sql := 'select count(*) from '||h.owner||'.'||h.object_name;
EXECUTE IMMEDIATE v_sql into v_counter;
PIPE ROW(t_tf_row(h.owner,h.created,h.table_size,v_counter));
end loop;
END;
/
Function created.
SQL> select * from table(get_schema_details());
USERNAME CREATED_D SIZE_MB COUNTER
---------------------------------------- --------- ---------- ----------
ODSVIEWS 24-MAR-20 14 71853408
ALFAODS 20-DEC-19 14 71853408
You can make the function as dynamic as you want, for example introducing input parameters instead of hardcoding the values.
CodePudding user response:
The reason you are not 'seeing' any result is that PL/SQL operates entirely within the server. It has no connection to the client, and no means of accessing the client's output display. You need to use the DBMS_OUTPUT.PUT_LINE procedure (look it up in the docs). That procedure writes to a buffer that is then returned to the client when the procedure completes. It is then up to the client to deal with that buffer. If using sqlplus, you configure it to display that output by 'set serverout on' as a session setting before invoking any procedures.
Also, I'd rewrite your procedure to eliminate the explicit cursor and use a CURSOR FOR loop: (I'd also convert to user ANSI standard JOIN syntax, but I'm not going to spend time here analyzing the query to figure out exactly how to convert that_). Also, I don't see how the procedure runs at all, given that your SELECT inside the loop needs an INTO clause to have a place to put the result.
DECLARE
v_sql varchar2(4000);
v_version varchar2(80);
BEGIN
for REC in (select o.owner
,o.object_name
,u.created
,TO_CHAR(round(sum(ds.bytes)/1024/1024/1024,'0000'))||' GB'
from dba_users u
,dba_objects o
,dba_segments ds
WHERE u.account_status = 'OPEN'
and u.DEFAULT_TABLESPACE not in ('SYSAUX','SYSTEM')
and u.username=o.owner
and o.object_name='MASTER'
and o.object_type='TABLE'
and ds.owner =o.owner;)
loop
v_sql := 'select VERSION from '||REC.owner||'.'||REC.object_name into v_version;
EXECUTE IMMEDIATE v_sql;
dbms_output.put_line('Version is '||v_version);
end loop;
END;
/
CodePudding user response:
The problem is
execute immediate v_sql;
which has no output. It needs an into
clause, and something to display it:
declare
demo_text varchar2(50);
begin
execute immediate 'select 2 2 as demo from dual'
into demo_text;
dbms_output.put_line(demo_text);
end;
4
By the way, I recommend deciding between end;
and END;
(bearing in mind this isn't COBOL).