Differences

This shows you the differences between two versions of the page.

--- oracle:oracle_sql_querys:pl_sql [2018/10/10 10:47] – created dodger
+++ dba:oracle:oracle_sql_querys:pl_sql [2022/02/11 11:36] – external edit 127.0.0.1
@@ Line 4: / Line 4: @@
+===== Executing queries with bind variables =====
 <code sql>
@@ Line 32: / Line 32: @@
             ) AS c3
         FROM
-            voxel."T_IT_TBUFFER_SEARCH" "T_ITS"
+            ciberterminal."T_IT_TBUFFER_SEARCH" "T_ITS"
         WHERE
             ( "T_ITS"."INTTYPE" =:b1 )
@@ Line 40: / Line 40: @@
 /
 </code>
+===== very simple loop =====
+<code sql>
+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;
+/
+</code>
+===== logging output in pl =====
+Sometimes you want to have feedback of the PL/SQL output.
+DMBS_OUTPUT 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 UTL_FILE which has FLUSH function for instant feedback!
+A sample code for this will be:
+<code sql>
+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;
+/
+</code>
+Where LOGDIR is a directory defined inside oracle.
+You will have the DBMS_OUTPUT feedback when the script finish and log feedback instantly.