— including OR REPLACE is more convenient when updating a subprogram CREATE OR REPLACE PROCEDURE award_bonus (emp_id NUMBER, bonus NUMBER) AS commission REAL; comm_missing EXCEPTION; BEGIN — executable part starts here SELECT commission_pct / 100 INTO commission FROM employees WHERE employee_id = emp_id; IF commission IS NULL THEN RAISE comm_missing; ELSE UPDATE employees SET salary […]

MERGE INTO impiegati dst USING ( select a.employee_id, a.last_name || ‘ ‘ || first_name as nome, salary, a.department_id from EMPLOYEES a where a.department_id in (select department_id from departments) ) src ON (src.employee_id = dst.impiegato_numero) WHEN NOT MATCHED THEN INSERT(impiegato_numero,impiegato_nome,stipendio,dipartimento_id) VALUES(src.employee_id, src.nome, src.salary, src.department_id ) WHEN MATCHED THEN UPDATE SET dst.dipartimento_id = src.employee_id;

REM set server output to ON to display output from DBMS_OUTPUT SET SERVEROUTPUT ON DECLARE CURSOR Cur1 IS select * from HR.COUNTRIES; Rec1 Cur1%rowtype; BEGIN FOR someone IN (SELECT * FROM hr.employees WHERE employee_id < 120 ) LOOP DBMS_OUTPUT.PUT_LINE(‘Nome = ‘ || someone.first_name || ‘, Cognome = ‘ || someone.last_name); END LOOP; open Cur1; — […]

ALTER SESSION SET CURRENT_SCHEMA = HR;   BEGIN EXECUTE IMMEDIATE ‘DROP TABLE mytable’; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END; create table mytable ( num int not null primary key, nominativo varchar(50)); set serveroutput on insert into mytable values(1,’Tizio Caio’); insert into mytable values(6,’Caio Duilio’); begin insert into mytable […]

DECLARE v_deptno NUMBER := 500; v_name VARCHAR2 (20) := ‘Acquisti’; e_invalid_dept EXCEPTION; BEGIN UPDATE HR.departments SET department_name = v_name WHERE department_id = v_deptno; IF SQL%NOTFOUND THEN RAISE e_invalid_dept; END IF; ROLLBACK; EXCEPTION WHEN e_invalid_dept THEN DBMS_OUTPUT.PUT_LINE (‘Department: [‘ || v_name || ‘] non trovato’); DBMS_OUTPUT.PUT_LINE (SQLERRM); DBMS_OUTPUT.PUT_LINE (SQLCODE); END;

Shell Unix – Script Sql PLSql / Argomenti Variabili da Shell Unix a Script Sql (e viceversa) –> Script Testati su Oracle 11g  <– Metodo 01) Richiamo Script sql da shell Unix: –>Shell #!/bin/ksh sqlplus -S /nolog <<EOF connect $USER/$PASSWD@$DB set echo on timing on time on term on; set serveroutput on; spool $FILE_TMP; WHENEVER […]

   USER – SCHEMA – Create / Profile/ Get DDL / Roles / Privileges (system e object) –> Script Testati su Oracle 11g  <– ——————- —  CREATE USER  — ——————- create user pippo     identified by        xxx default tablespace   users temporary tablespace temp quota unlimited on   users profile              default account              unlock; grant connect, resource to pippo; grant create database […]

  Advisor —————– — QUICK TUNE  — DBMS_ADVISOR —————– The procedure DBMS_ADVISOR.QUICK_TUNE is straightforward and takes as input a single SQL statement to tune. DBMS_ADVISOR.QUICK_TUNE ( advisor_name      IN VARCHAR2,         –>Name of the Advisor that will perform the analysis (sqlaccess_advisor). task_name         IN VARCHAR2,         –>Name of the task. attr1             IN CLOB,             –>Inserire lo statement   (CLOB variable) attr2             IN VARCHAR2 […]

DATA PUMP –> Script Testati su Oracle 11g <– — Help expdp help=y impdp help=y —————————– — Expdp/Impdp ESEMPI BASE — —————————– –> TABLE expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log –> SHEMA expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log –> DATABASE expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log –> INCLUDE and EXCLUDE The […]

–> Script Testati su Oracle 11g <– —————————- —-  Spazi Tablespace  —- —————————- set linesize 175 set pagesize 500 spool spazi_tablespace_Toad.log select  a.tablespace_name, round(a.bytes_alloc / 1024 / 1024) megs_alloc, round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free, round((a.bytes_alloc – nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used, round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free, 100 – round((nvl(b.bytes_free, […]