Common Oracle System packages and common methods

Common Oracle System packages and common methods

1. dbms_metadata.get_ddl: generate the ddl information of the database object:

2. dbms_stats is used to collect, view, and modify the optimization statistics of database objects.

Exec dbms_stats.gather_schema_stats ('stud ');

Exec dbms_stats.gather_index_stats ('stud', 'emp_idx ');

3. DBMS_OUTPUT: used for input and output information

First, use DBMS_OUTPUT.ENABLE to activate this package. If it is not activated, other processes and functions of this package cannot be called.

However, if you use the SERVEROUTPUT option in SQL * PLUS, you do not need to use this process.

4. DBMS_JOB: Used to arrange and manage job queues. By using jobs, the Oracle database can regularly execute specific tasks.

Set the task to use dbms_job.submit (job out int, what in varchar2 next_date in date, interval)

V. DBMS_UTILITY: Execute ddl statements during the process or obtain the dependency between various database objects.

Execute ddl statements

6. DBMS_DDL: provides methods to recompile functions and packages (alter_complie) in PL/SQL blocks, as well as analysis tables, indexes, clusters, and statistical data (analyze_object.

VII. UTL_INADDR: used to obtain the Host Name and IP address

Used to obtain the ip address of the specified host: select utl_inaddr.get_host_address ('sjz06 ') ip from dual;

8. DBMS_RANDOM: a built-in random number generator can be used to quickly generate random numbers.

9. UTL_FILE: provides the ability to read and write files in the file system at the operating system level.

When referencing a file, you must use a file handle to read or write the file. The file handle is defined by a public variable named UTL_FILE.FILE_TYPE in the package UTL_FILE.

DIRECTORY references on the file system are implemented by using the DIRECTORY name or the alias assigned to the DIRECTORY by the create directory command.

Create directory source AS 'd:/source ';

Create directory target AS 'd:/target ';

Grant read, write on directory source to ods_es;

Grant read, write on directory target to ods_es;

 

DECLARE v_getfile UTL_FILE.FILE_TYPE;

V_sou_dir VARCHAR2 (40): = 'source ';

V_sou_file VARCHAR2 (30): = 'source.csv ';

V_tar_dir VARCHAR2 (40): = 'source ';

V_tar_file VARCHAR2 (30): = 'target.csv ';

V_eachline VARCHAR2 (400 );

V_count INTEGER: = 0;

 

BEGIN

 

UTL_FILE.FCOPY (v_sou_dir, v_sou_file, v_tar_dir, v_tar_file );

V_getfile: = UTL_FILE.FOPEN (v_tar_dir, v_tar_file, 'w ');

DBMS_OUTPUT.PUT_LINE ('Copy file content below: ''' | v_tar_file | '''');

 

LOOP

UTL_FILE.GET_LINE (v_getfile, v_eachline );

DBMS_OUTPUT.PUT_LINE (v_eachline );

V_count: = v_count + 1;

End loop;

 

EXCEPTION

WHEN NO_DATA_FOUND THEN UTL_FILE.FCLOSE (v_getfile );

DBMS_OUTPUT.PUT_LINE (v_count | 'row record ');

 

When others then DBMS_OUTPUT.PUT_LINE ('sqlerrm: '| SQLERRM );

DBMS_OUTPUT.PUT_LINE ('sqlcode: '| SQLCODE );

END;
 

10. DBMS_PIPE: used for communication between different sessions of the same routine (instance. For example, two independent sessions connected to the same database can communicate through pipelines, or between the stored procedure and Pro * C, this greatly enhances the processing capability of PL/SQL.

-------------------------------------- Split line --------------------------------------

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

-------------------------------------- Split line --------------------------------------