摘要: Starting from Oracle8i one can use the "EXECUTE IMMEDIATE" statement to execute dynamic SQL and PL/SQL statements (statements created at run-time). Look at these examples. Note that the statements within quotes are NOT semicolon terminated:EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)&#
阅读全文posted @ 2011-05-12 22:30 William.Lu 阅读(19) 评论(0)
编辑摘要: One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the "EXECUTE IMMEDIATE" statement (native SQL). Examples:begin EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)';end;begin execute Immediate 'TRUNCATE TABLE emp'; end;DECLARE var VARCHAR2(100);BEGIN
阅读全文posted @ 2011-05-12 22:27 William.Lu 阅读(16) 评论(0)
编辑摘要: The UTL_FILE database package can be used to read and write operating system files. A DBA user needs to grant you access to read from/ write to a specific directory before using this package. Here is an example: CONNECT / AS SYSDBACREATE OR REPLACE DIRECTORY mydir AS '/tmp';GRANT read, write
阅读全文posted @ 2011-05-12 22:22 William.Lu 阅读(16) 评论(0)
编辑摘要: One can use the DBMS_OUTPUT package to write information to an output buffer. This buffer can be displayed on the screen from SQL*Plus if you issue the SET SERVEROUTPUT ON; command. For example:set serveroutput onbegin dbms_output.put_line('Look Ma, I can print from PL/SQL!!!');end;/DBMS_OUT
阅读全文posted @ 2011-05-12 21:57 William.Lu 阅读(11) 评论(0)
编辑摘要: The following query gives the list of all wrapped PL/SQL code:select owner, name, typefrom dba_sourcewhere line = 1 and instr(text, ' wrapped'||chr(10))+instr(text, ' wrapped '||chr(10)) > 0order by 1, 2, 3/
阅读全文posted @ 2011-05-12 20:29 William.Lu 阅读(5) 评论(0)
编辑摘要: Oracle provides a binary wrapper utility that can be used to scramble PL/SQL source code. This utility was introduced in Oracle7.2 (PL/SQL V2.2) and is located in the ORACLE_HOME/bin directory.The utility use human-readable PL/SQL source code as input, and writes out portable binary object code (som
阅读全文posted @ 2011-05-12 20:28 William.Lu 阅读(8) 评论(0)
编辑摘要: One can build a history of PL/SQL code changes by setting up an AFTER CREATE schema (or database) level trigger (available from Oracle 8.1.7). This will allow you to easily revert to previous code should someone make any catastrophic changes. Look at this example:CREATE TABLE SOURCE_HIST -- Create h
阅读全文posted @ 2011-05-12 20:25 William.Lu 阅读(7) 评论(0)
编辑摘要: The following query is handy if you want to know where certain tables, columns and expressions are referenced in your PL/SQL source code.SELECT type, name, line FROM user_source WHERE UPPER(text) LIKE UPPER('%&KEYWORD%');If you run the above query from SQL*Plus, enter the string you are
阅读全文posted @ 2011-05-12 20:20 William.Lu 阅读(14) 评论(0)
编辑