Differences

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

Link to this comparison view

oracle:oracle_sql_querys:pl_sql [2018/10/10 12:47]
dodger created
oracle:oracle_sql_querys:pl_sql [2018/10/10 12:49]
dodger
Line 4: Line 4:
  
  
 +===== Executing queries with bind variables =====
  
 <code sql> <code sql>
Line 40: Line 40:
 / /
 </​code>​ </​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.
 +
 +
 +
 +
  • oracle/oracle_sql_querys/pl_sql.txt
  • Last modified: 2018/10/10 12:49
  • by dodger