DECLARE
   c1 NUMBER;
   c2 NUMBER;
   c3 NUMBER;
--    ressult testing_binds%ROWTYPE;
--    inttype NUMBER := 4001;
   theSQL    VARCHAR2(2000);
BEGIN
    theSQL := '
    SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel OPT_ESTIMATE(@"innerQuery", TABLE, "T_ITS", SCALE_ROWS=21196.12092) */
    c1,
    c2,
    c3
FROM
    (
        SELECT /*+ qb_name("innerQuery") INDEX( "T_ITS" "IT_TBUFFER_SEARCH_UX3_R") */
            COUNT(*) AS c1,
            4294967295 AS c2,
            SUM(
                CASE
                    WHEN("T_ITS"."INTTYPE" =:b1) THEN 1
                    ELSE 0
                END
            ) AS c3
        FROM
            ciberterminal."T_IT_TBUFFER_SEARCH" "T_ITS"
        WHERE
            ( "T_ITS"."INTTYPE" =:b1 )
    ) innerquery' ;
    EXECUTE IMMEDIATE theSQL INTO c1, c2, c3 USING 4001, 4001;
END;
/

DECLARE
    RESSULT VARCHAR(100);
BEGIN
  FOR tname IN (
    SELECT OBJECT_NAME FROM SYS.ALL_OBJECTS WHERE OWNER='SYS' AND OBJECT_TYPE='TABLE'
  )
  LOOP
    SELECT REGEXP_SUBSTR(DDL, '.*TABLESPACE.*') INTO RESSULT FROM (SELECT DBMS_METADATA.GET_DDL('TABLE', tname.object_name, 'SYS' ) DDL FROM DUAL) ;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(RESSULT));
  END LOOP;
END;
/

Sometimes you want to have feedback of the PL/SQL output. DMBSOUTPUT does not shows the ressult until the script finish, the wait time will make you run crazy. The “solution” is to use a log file and use UTLFILE which has FLUSH function for instant feedback!

A sample code for this will be:

DECLARE
    Rows_count PLS_INTEGER := 0;
    LOGFILE UTL_FILE.FILE_TYPE;
BEGIN
    LOGFILE := UTL_FILE.FOPEN('LOGDIR','testoutput.log','w',1024);
    WHILE TRUE LOOP
        Rows_count := Rows_count + 1;
        IF MOD(Rows_count, 10000) = 0 THEN
            dbms_output.put_line('Commited ' || Rows_count || ' rows');
            UTL_FILE.PUT_LINE(LOGFILE,'Commited ' || Rows_count || ' rows');
            UTL_FILE.FFLUSH (LOGFILE);
        END IF;
    END LOOP;
    COMMIT;
    UTL_FILE.FCLOSE(LOGFILE);
END;
/

Where LOGDIR is a directory defined inside oracle. You will have the DBMS_OUTPUT feedback when the script finish and log feedback instantly.