Differences

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

--- dba:oracle:oracle_sql_querys:pl_sql [2022/02/11 11:12] – ↷ Page moved from oracle:oracle_sql_querys:pl_sql to dba:oracle:oracle_sql_querys:pl_sql dodger
+++ dba:oracle:oracle_sql_querys:pl_sql [2023/01/31 08:15] (current) – removed dodger
@@ Line 1: / Line 1: @@
-====== ORACLE SQL QUERIES : PL/SQL ======
-===== Executing queries with bind variables =====
-<code sql>
-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
-            voxel."T_IT_TBUFFER_SEARCH" "T_ITS"
-        WHERE
-            ( "T_ITS"."INTTYPE" =:b1 )
-    ) innerquery' ;
-    execute immediate theSQL into c1, c2, c3 using 4001, 4001;
-end;
-/
-</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.