dba:oracle:oracle_sql_querys:pl_sql
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revision | |||
dba:oracle:oracle_sql_querys:pl_sql [2022/02/11 11:36] – external edit 127.0.0.1 | 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; | ||
- | | ||
- | BEGIN | ||
- | theSQL := ' | ||
- | SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel OPT_ESTIMATE(@" | ||
- | c1, | ||
- | c2, | ||
- | c3 | ||
- | FROM | ||
- | ( | ||
- | SELECT /*+ qb_name(" | ||
- | COUNT(*) AS c1, | ||
- | 4294967295 AS c2, | ||
- | SUM( | ||
- | CASE | ||
- | WHEN(" | ||
- | ELSE 0 | ||
- | END | ||
- | ) AS c3 | ||
- | FROM | ||
- | ciberterminal." | ||
- | WHERE | ||
- | ( " | ||
- | ) innerquery' | ||
- | execute immediate theSQL into c1, c2, c3 using 4001, 4001; | ||
- | end; | ||
- | / | ||
- | </ | ||
- | |||
- | |||
- | |||
- | ===== very simple loop ===== | ||
- | <code sql> | ||
- | DECLARE | ||
- | RESSULT VARCHAR(100); | ||
- | BEGIN | ||
- | FOR tname IN ( | ||
- | SELECT OBJECT_NAME FROM SYS.ALL_OBJECTS WHERE OWNER=' | ||
- | ) | ||
- | LOOP | ||
- | SELECT REGEXP_SUBSTR(DDL, | ||
- | DBMS_OUTPUT.PUT_LINE(TO_CHAR(RESSULT)); | ||
- | END LOOP; | ||
- | END; | ||
- | / | ||
- | </ | ||
- | |||
- | ===== 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 " | ||
- | |||
- | 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(' | ||
- | WHILE true LOOP | ||
- | Rows_count := Rows_count + 1; | ||
- | IF MOD(Rows_count, | ||
- | dbms_output.put_line(' | ||
- | UTL_FILE.PUT_LINE(LOGFILE,' | ||
- | 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. | ||
- | |||
- | |||
- | |||
dba/oracle/oracle_sql_querys/pl_sql.1644579407.txt.gz · Last modified: 2022/02/11 11:36 by 127.0.0.1