dba:oracle:oracle_sql_querys:pl_sql
Differences
This shows you the differences between two versions of the page.
Next revision | Previous revisionNext revisionBoth sides next revision | ||
oracle:oracle_sql_querys:pl_sql [2018/10/10 10:47] – created dodger | 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 | ||
---|---|---|---|
Line 4: | Line 4: | ||
+ | ===== Executing queries with bind variables ===== | ||
<code sql> | <code sql> | ||
Line 40: | Line 40: | ||
/ | / | ||
</ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ===== 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. | ||
+ | |||
+ | |||
+ | |||
+ |