1 / 20

Advanced Tools and Techniques

Advanced Tools and Techniques. Objectives. After completing this lesson, you should be able to: Generate scripts using SQL queries Use Dynamic SQL to automate script generation Create an external table Retrieve user context variables

raisie
Download Presentation

Advanced Tools and Techniques

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Advanced Tools and Techniques

  2. Objectives • After completing this lesson, you should be able to: • Generate scripts using SQL queries • Use Dynamic SQL to automate script generation • Create an external table • Retrieve user context variables • Issue statements that can be suspend upon encountering space condition errors

  3. Using SQL to Generate Scripts • Query dictionary tables • Spool output to a file • Use SQL*Plus or iSQL*Plus settings to remove unneeded text • Use literals and concatenation characters to create SQL commands SET HEAD OFF SET FEEDBACK OFF SPOOL drop_user_tables.sql SELECT 'DROP TABLE ' || table_name || ';' FROM user_tables;

  4. Example: Compiling Invalid Objects SET HEAD OFF SET FEEDBACK OFF SPOOL compile_invalid_objects.sql SELECT 'ALTER ' || object_type || ' ' || owner||'.'||object_name||' COMPILE;' FROM DBA_OBJECTS WHERE status = 'INVALID' AND object_type NOT IN ('PACKAGE BODY','TYPE BODY', 'SYNONYM'); SELECT 'ALTER PACKAGE ' || owner || '.' || object_name || ' COMPILE BODY;' FROM DBA_OBJECTS WHERE status != 'VALID' AND object_type = 'PACKAGE BODY';

  5. What Is a Procedure? • A procedure is a type of subprogram that performs an action. • A procedure can be stored in the database, as a schema object, for repeated execution. CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, ...)] IS | AS <PL/SQL Block>;

  6. PL/SQL Procedures Procedure IN parameter OUT parameter IN OUT parameter Callingenvironment (DECLARE) BEGIN EXCEPTION END;

  7. Using IN and OUT Parameters CREATE OR REPLACE PROCEDURE gen_rebuild_idx_script (schema IN VARCHAR2) IS BEGIN FOR idx IN (SELECT owner, index_name FROM ALL_INDEXES WHERE owner = schema) LOOP DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' || idx.owner || '.' || idx.index_name || ' REBUILD;'); END LOOP; END gen_rebuild_idx_script; / SET head OFF SET feedback OFFSPOOL rebuild_indexes.sql EXEC gen_rebuild_idx_script('HR');

  8. Using Native Dynamic SQL • Dynamic SQL: • Is a SQL statement that contains variables that can change during runtime • Is a SQL statement with placeholders and is stored as a character string • Enables general-purpose code to be written • Enables data-definition, data-control, or session-control statements to be written and executed from PL/SQL • Is written using either DBMS_SQL or native dynamic SQL

  9. The EXECUTE IMMEDIATE Statement • INTO is used for single-row queries and specifies the variables or records into which column values are retrieved. • USING is used to hold all bind arguments. The default parameter mode is IN. EXECUTE IMMEDIATE dynamic_string [INTO {define_variable [, define_variable] ... | record}] [USING [IN|OUT|IN OUT] bind_argument [, [IN|OUT|IN OUT] bind_argument] ... ];

  10. Dynamic SQL Using EXECUTEIMMEDIATE CREATE PROCEDURE del_rows (p_table_name IN VARCHAR2, p_rows_deld OUT NUMBER) IS BEGIN EXECUTE IMMEDIATE 'DELETE FROM '||p_table_name; p_rows_deld := SQL%ROWCOUNT; END; / VARIABLE deleted NUMBER EXECUTE del_rows('test_employees', :deleted) PRINT deleted

  11. Dynamic SQL Example DECLARE sql_stmt VARCHAR2(1024); CURSOR get_obj IS SELECT object_name, owner FROM ALL_OBJECTS WHERE owner = UPPER('&schema_name') AND object_type IN ('PACKAGE','FUNCTION','PROCEDURE','TYPE'); BEGIN FOR rec IN get_obj LOOP sql_stmt := 'CREATE PUBLIC SYNONYM '||rec.object_name||' FOR '||rec.owner||'.' ||rec.object_name; DBMS_OUTPUT.PUT_LINE(sql_stmt); EXECUTE IMMEDIATE sql_stmt; END LOOP; END; /

  12. External Tables • The Oracle Database enables the transparent access, processing, and integration of external flat files: • Transparent access of external data from within the database • Highly efficient procedural processing of complex data structures • Optimal integration into SQL for bulk processing

  13. PRODUCTS_EXT PROD_ID PROD_NAME ... PRICE DISCOUNT 10 50 ml Beaker 22.00 5.00 20 50 ml Conical Flask 25.50 8.50 30 50 ml Measuring Cylinder 50.00 9 .00 ... External Tables and the Oracle Database CREATE TABLE PRODUCTS_EXT…; 10, 50 ml Beaker, .., 22.00, 5.00 20, 50 ml Conical Flask, ... ,25.50, 8.50 30, 50 ml Measuring Cylinder, ...,50.00, 9.00 40, ... Data in the external file SELECT .. FROM PRODUCTS_EXT; Oracle Database

  14. External Tables: Example CREATE OR REPLACE DIRECTORY trace_files AS '/u01/app/oracle/admin/orcl/bdump'; CREATE TABLE alert_log ( line_text VARCHAR2(80) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY trace_files ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE BADFILE 'rejects.bad' LOGFILE 'output.log' FIELDS (line_text CHAR(80)) ) LOCATION ('alert_orcl.log') ) REJECT LIMIT UNLIMITED;

  15. Retrieving User Context Attributes • Retrieve the following information: • The name of the user who logged onto the database • The method by which that user was authenticated SQL> SELECT SYS_CONTEXT ('USERENV', 2> 'SESSION_USER') "USER",3> SYS_CONTEXT ('USERENV', 4> 'AUTHENTICATION_TYPE') Auth_meth 5> FROM DUAL; USER AUTH_METH ----------------- -------------------- OE DATABASE

  16. Resumable Statements A resumable statement: • Allows you to suspend large operations instead of receiving an error • Gives you a chance to fix the problem while the operation is suspended, rather than starting over • Is suspended for these conditions: • Out of space • Maximum extents reached • Space quota exceeded

  17. Using Resumable Space Allocation • Queries, DML operations, and certain DDL operations can be resumed if they encounter an out-of-space error. • A resumable statement can be issued through SQL, PL/SQL, SQL*Loader, or the Oracle Call Interface (OCI). • To run a resumable statement, you must first enable resumable statements for your session. ALTER SESSION ENABLE RESUMABLE; INSERT INTO sales_new SELECT * FROM sh.sales; ALTER SESSION DISABLE RESUMABLE;

  18. EMPLOYEES table EMPLOYEES table Used block Free space Resuming Suspended Statements SQL Statement Suspended Continue SQL operation AFTER SUSPENDtrigger ABORT

  19. Summary • In this lesson, you should have learned how to: • Generate scripts using SQL queries • Use Dynamic SQL to automate script generation • Create an external table • Retrieve user context variables • Issue statements that can be suspend upon encountering space condition errors

  20. Practice 17 Overview: Using Dynamic SQL • This practice covers the following topics: • Using Dynamic SQL • Retrieving user context information

More Related