1. Console:
DBMS_OUTPUT.ENABLE
(1000000); // enable output voi 1000000
ky tu
dbms_output.put_line('Hi Binh, I can
write PL/SQL');
2. Làm việc với File:
--------------SYSDBA-----------------------
SQL> CREATE DIRECTORY MYDIR AS
'C:\TESTLOC';
Directory created.
SQL> GRANT READ, WRITE ON
DIRECTORY MYDIR TO SCOTT
Grant succeeded.
--------------SCOTT-------------------------
DECLARE
L_HANDLER UTL_FILE.FILE_TYPE;
BEGIN
L_HANDLER := UTL_FILE.FOPEN('MYDIR', 'SYS.txt', 'W');
UTL_FILE.PUTF(L_HANDLER, 'UTL_FILE write mode demonstration');
UTL_FILE.FCLOSE(L_HANDLER);
END;
PL/SQL procedure successfully
completed.
|
3. Exception
SET
SERVEROUTPUT ON
Single Exception Handler for Multiple Exceptions
CREATE OR REPLACE PROCEDURE select_item (
t_column VARCHAR2, t_name VARCHAR2 ) AUTHID DEFINER IS temp VARCHAR2(30); BEGIN temp := t_column; -- For error message if next SELECT fails -- Fails if table t_name does not have column t_column: SELECT COLUMN_NAME INTO temp FROM USER_TAB_COLS WHERE TABLE_NAME = UPPER(t_name) AND COLUMN_NAME = UPPER(t_column); temp := t_name; -- For error message if next SELECT fails -- Fails if there is no table named t_name: SELECT OBJECT_NAME INTO temp FROM USER_OBJECTS WHERE OBJECT_NAME = UPPER(t_name) AND OBJECT_TYPE = 'TABLE'; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('No Data found for SELECT on ' || temp);
WHEN others THEN
send_sms_binhtv('Error
sys.dba_op.auto_drop_partitions: ' || SQLERRM);
insert into binhtv.dbamf_log_jobs
(id,name,status,event_date, note)
values(binhtv.dbamf_log_jobs_seq.nextval,'Error
sys.dba_ct.ct_session',-1,sysdate,'1:OK');
commit;
DBMS_OUTPUT.PUT_LINE ('Unexpected error');
RAISE;
END;
END;
/ |
