Tuesday, May 8, 2012

Oracle 11g2 Database Replay


Oracle 11g2 Database Replay Features
Before system changes are made, such as hardware and software upgrades, extensive testing is usually performed in a test environment to validate the changes. However, despite the testing, the new system often experiences unexpected behavior when it enters production because the testing was not performed using a realistic workload. The inability to simulate a realistic workload during testing is one of the biggest challenges when validating system changes
Database Replay enables realistic testing of system changes by essentially re-creating the production workload environment on a test system. Using Database Replay, you can capture a workload on the production system and replay it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload. This enables you to fully assess the impact of the change, including undesired results, new contention points, or plan regressions. Extensive analysis and reporting is provided to help identify any potential problems, such as new errors encountered and performance divergence.
Database Replay performs workload capture of external client workload at the database level and has negligible performance overhead. Capturing the production workload eliminates the need to develop simulation workloads or scripts, resulting in significant cost reduction and time savings. By using Database Replay, realistic testing of complex applications that previously took months using load simulation tools can now be completed in days. This enables you to rapidly test changes and adopt new technologies with a higher degree of confidence and at lower risk.
You can use Database Replay to test any significant system changes, including:
Database and operating system upgrades
Configuration changes, such as conversion of a database from a single instance to an Oracle Real Application Clusters (Oracle RAC) environment
Storage, network, and interconnect changes
Operating system and hardware migrations

You can use Database Replay to capture a workload on the production system and replay it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload. This enables you to test the effects of a system change without affecting the production system.
Database Replay supports workload capture on a system running Oracle Database 10g Release 2 and newer releases. In order to capture a workload on a system running Oracle Database 10g Release 2, the database version can be 10.2.0.4 or higher. Workload replay is only supported on systems running Oracle Database 11g Release 1 and newer releases.
Before capturing a database workload, carefully consider the following options:
*Restarting the Database
–to avoid capture partial transaction
*Defining the Workload Filters
–inclusion filters or exclusion filters, not both
*Setting Up the Capture Directory



 Oracle 11g2 Database Replay Steps
 
Create Tablespace “USERS”   SQL>CREATE TABLESpace users DATAFILE ‘/u01/oracle/dbdata/tstd11s/data/user01.dbf’ size 500m autoextend on maxsize 1g;  Create DIRECTORY to store Replay related files. 
 SQL>CREATE OR REPLACE DIRECTORY dbreplay_capture_dir AS ‘/u01/oracle/dbdata/dbreplay_capture/’;  Start Capture process 
 SQL>
BEGIN
  DBMS_WORKLOAD_CAPTURE.start_capture (name     => ‘tst_capture_7′, 

                                       dir
      => ‘dbreplay_CAPTURE_DIR’,                                       duration => NULL);
END;
 
These below activities will be capture in replay process 
 SQL>CREATE USER dbreplay_test IDENTIFIED BY dbreplay_test  QUOTA UNLIMITED ON users;SQL>GRANT CONNECT, CREATE TABLE TO dbreplay_test;SQL>CONN dbreplay_test/dbreplay_test 
SQL>CREATE TABLE dbreplay_test_tab ( 
  id
           NUMBER,
  description
  VARCHAR2(50),  CONSTRAINT dbreplay_test_tab_pk PRIMARY KEY (id)
) tablespace users; 

SQL>
BEGIN
  FOR i IN 1 .. 100000 LOOP    INSERT INTO dbreplay_test_tab (id, description)    VALUES (i, ‘Description for ‘ || i);
  END 
LOOP;  COMMIT;
END;
To stop the replay process 
 SQL>CONN / AS SYSDBA
SQL>
BEGIN
  DBMS_WORKLOAD_CAPTURE.finish_capture;
END;

To find a Capture ID, Name 
 SQL>conn / as sysdbaSQL> SELECT DBMS_WORKLOAD_CAPTURE.get_capture_info(‘dbreplay_CAPTURE_DIR’)
FROM
   dual;
DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO(‘dbreplay_CAPTURE_DIR’)
—————————————————————
                                                             8
SQL> COLUMN name FORMAT A30
SELECT id, name FROM dba_workload_captures;
         8 tst_capture_7 
     
 
To get report 
 DECLARE
  l_report
  CLOB;
BEGIN
  l_report := DBMS_WORKLOAD_CAPTURE.report(capture_id => 8,format
     => DBMS_WORKLOAD_CAPTURE.TYPE_HTML);
END;
To export the replay work 
 BEGIN  DBMS_WORKLOAD_CAPTURE.export_awr (capture_id => 8);
END;
 – At Test Database —– 
 To create tablespace “USER” 
 SQL>CREATE TABLESpace users DATAFILE ‘/u01/oracle/dbdata/dbat01/data/dbat01_user01.dbf’ size 500m autoextend on maxsize 1g;  Create DIRECTORY to copy replay files 
 SQL>CREATE OR REPLACE DIRECTORY dbreplay_capture_dir AS ‘/u01/oracle/dbdata/dbreplay_capture/’;  To initial and prepare 
SQL>
BEGIN
  DBMS_WORKLOAD_REPLAY.process_capture(‘dbreplay_CAPTURE_DIR’);    DBMS_WORKLOAD_REPLAY.initialize_replay (replay_name => ‘tst_capture_7′,
                                          replay_dir
  => ‘dbreplay_CAPTURE_DIR’);    DBMS_WORKLOAD_REPLAY.prepare_replay (synchronization => TRUE);
END;
 
To calibrate the replay   #wrc mode=calibrate replaydir=/u01/oracle/dbdata/dbreplay_capture
Workload Replay Client: Release 11.2.0.1.0 – Production on Tue Jan 18 15:43:05 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Report for Workload in: /u01/oracle/dbdata/dbreplay_capture
———————–
Recommendation:
Consider using at least 1 clients divided among 1 CPU(s)
You will need at least 3 MB of memory per client process.
If your machine(s) cannot match that number, consider using more clients.
Workload Characteristics:
- max concurrency: 1 sessions
- total number of sessions: 5

Assumptions:
- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- 256 KB of memory cache per concurrent session
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE

/u01/oracle/dbdata/dbreplay_capture/cap
  
To Start Replay process at Test Database 
 #wrc system/xxxx@dbat01  mode=replay replaydir=/u01/oracle/dbdata/dbreplay_capture
Workload Replay Client: Release 11.2.0.1.0 – Production on Tue Jan 18 16:02:48 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Wait for the replay to start (16:02:51)
…….
 
Open another new session at test Database 
————-
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL>
BEGIN
  DBMS_WORKLOAD_REPLAY.start_replay;
END;

PL/SQL procedure successfully completed.
————–Back to 1st session at test Database 

…….
Wait for the replay to start (16:02:51)

Replay started (16:33:26)

Replay finished (17:10:39)
  
To check replay work at test enviroment  
 
SQL> CONN sys/password@test AS SYSDBA
Connected.
SQL> SELECT table_name FROM dba_tables WHERE owner = ‘dbreplay_TEST’;

TABLE_NAME
——————————
dbreplay_TEST_TAB

SQL> SELECT COUNT(*) FROM dbreplay_test.dbreplay_test_tab;

  COUNT(*)
———-
    100000

 
SQL> set pagesize 0 long 30000000 longchunksize 3000
SQL> set line 2000
SQL> select dbms_workload_replay.report(8,’TEXT’) from dual;
DB Replay Report for test_capture_8
—————————————————————————
| DB Name | DB Id
     | Release    | RAC | Replay Name
    | Replay Status |
—————————————————————————
| DBAT01
  | 313434807 | 11.2.0.1.0 | NO  | test_capture_3 | COMPLETED
     |
—————————————————————————

Replay Information
——————————————————————————————-|
   Information    | Replay                               | Capture
                              |
——————————————————————————————-| Name
             | test_capture_3                       | test_capture_3
                       |
——————————————————————————————-| Status
           | COMPLETED                            | COMPLETED
                            |
——————————————————————————————-| Database Name
    | DBAT01                               | tstd11s
                              |
——————————————————————————————-| Database Version | 11.2.0.1.0
                           | 11.2.0.1.0
                           |
——————————————————————————————-| Start Time
       | 18-01-11 21:33:26                    | 18-01-11 15:46:02
                    |
——————————————————————————————-| End Time
         | 18-01-11 22:07:49                    | 18-01-11 16:20:29
                    |
——————————————————————————————-| Duration
         | 34 minutes 23 seconds                | 34 minutes 27 seconds
                |
——————————————————————————————-| Directory Object | dbreplay_CAPTURE_DIR
                | dbreplay_CAPTURE_DIR
                |
————————————————————————————————–
| Directory Path
   | /u01/oracle/dbdata/dbreplay_ca | /u01/oracle/dbdata/db_replay_ca |
|
                  | pture/                               | pture/
                               |
————————————————————————————————–

Replay Options
———————————————————
|
       Option Name       | Value
                       |
———————————————————
| Synchronization
         | SCN
                         |
———————————————————
| Connect Time
            | 100%
                        |
———————————————————
| Think Time
              | 100%
                        |
———————————————————
| Think Time Auto Correct | TRUE 
                       |
———————————————————
| Number of WRC Clients
   | 1 (1 Completed, 0 Running ) |
———————————————————

Replay Statistics
—————————————————————
|
        Statistic        | Replay           | Capture
        |
—————————————————————
| DB Time
                 |
   76.383 seconds | 56.847 seconds |
—————————————————————
| Average Active Sessions |
              .04 |
            .03 |
—————————————————————
| User calls
              |               95 |
             95 |
—————————————————————
| Network Time
            |    1.101 seconds | .
              |
—————————————————————
| Think Time
              | 1992.558 seconds | .
              |
—————————————————————

Replay Divergence Summary
——————————————————————-
|
                Divergence Type
                | Count | % Total |
——————————————————————-
| Session Failures During Replay
                |     0 |
    0.00 |
——————————————————————-
| Errors No Longer Seen During Replay
           |     0 |
    0.00 |
——————————————————————-
| New Errors Seen During Replay
                 |     0 |
    0.00 |
——————————————————————-
| Errors Mutated During Replay
                  |     1 |
    1.05 |
——————————————————————-
| DMLs with Different Number of Rows Modified
   |     0 |
    0.00 |
——————————————————————-
| SELECTs with Different Number of Rows Fetched |
     4 |
    4.21 |
——————————————————————-

——————————————————————————————-Workload Profile Top Events
————————————————–
| No data exists for this section of the report. |
————————————————–
Top Service/Module/Action
————————————————–
| No data exists for this section of the report. |
————————————————–
Top SQL with Top Events
————————————————–
| No data exists for this section of the report. |
————————————————–
Top Sessions with Top Events
————————————————–
| No data exists for this section of the report. |
————————————————–
Replay Divergence Session Failures By Application
————————————————–
| No data exists for this section of the report. |
————————————————–
Error Divergence By Application
——————————————————————————————-| Service
   | Module    | Action    | Capture   | Replay
    | Count | First Occ | Last Occu |
| Name
      | Name      | Name      | Error     | Error     |       | urrence   | rrence
    |
——————————————————————————————- SYS$USERS | SQL*Plus
  | UNNAMED   | ORA-00904 | ORA-15566 | 1
     | 2011-01-1 | 2011-01-1 |
|
           |           |           |           |           |
       | 8T16:38:0 | 8T16:38:0 |
|
           |           |           |           |           |
       | 5.689633- | 5.689633- |
|
           |           |           |           |           |       | 05:00     | 05:00
     |
——————————————————————————————-
By SQL
————————————————–
| No data exists for this section of the report. |
————————————————–
By Session
——————————————————————————————-
| Session ID | Session
      | Capture      | Replay Error | Count | First        | Last
         |
|
            | Serial       | Error        |              |       | Occurrence   | Occurrence
   |
——————————————————————————————-
| 34
         | 293          | ORA-00904    | ORA-15566    | 1
     | 2011-01-18T1 | 2011-01-18T1 |
|
            |              |              |              |
       | 6:38:05.6896 | 6:38:05.6896 |
|
            |              |              |              |       | 33-05:00     | 33-05:00
     |
——————————————————————————————-DML Data Divergence By Application
————————————————–
| No data exists for this section of the report. |
————————————————–
By SQL
————————————————–
| No data exists for this section of the report. |
————————————————–
By Divergence magnitude
————————————————–
| No data exists for this section of the report. |
————————————————–
SELECT Data Divergence By Application
——————————————————————————————-| Service
  | Module   | Action   | Avg Rows | Avg      | Number
   | Count | First Oc | Last Occ |
| Name
     | Name     | Name     | Affected | Absolute | of       |       | currence | urrence
  |
|
          |          |          |          | Rows     | Distinct |       |          |
          |
|
          |          |          |          | Affected | Sessions |       |          |
          |
——————————————————————————————-| SYS$USER | sqlplus@ | UNNAMED
  | -3       | 3        | 2        | 4
     | 2011-01- | 2011-01- |
| S
        | artp6    |          |          |          |          |
       | 18T16:38 | 18T16:56 |
|
          | (TNS     |          |          |          |          |
       | :48.0546 | :08.9631 |
         | V1-V3)   |          |          |          |          |
       | 23-05:00 | 96-05:00 |
——————————————————————————————-By Divergence magnitude
——————————————————————
| Max divergence magnitude | Divergence distribution (%) | Count |
——————————————————————
| no rows affected
         | 100                         | 4
     |
——————————————————————
Replay Clients Alerts
————————————————–
| No data exists for this section of the report. |
————————————————–
Replay Filters
————————————————–
| No data exists for this section of the report. |
————————————————–

End of Report. 
 

Monday, May 7, 2012

Table Reorganization, Rebuild

Tables in Oracle database become fragmented after mass deletion, or after so many delete and/or insert operations.

BEGIN
 FOR cur_rec IN (SELECT distinct table_name
                  FROM   dba_tables WHERE OWNER NOT in ('DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB','SYSMAN')) LOOP
    BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE '|| cur_rec.table_name ||' ENABLE ROW MOVEMENT';
EXECUTE IMMEDIATE 'ALTER TABLE '|| cur_rec.table_name ||' SHRINK SPACE COMPACT';
EXECUTE IMMEDIATE 'ALTER TABLE '|| cur_rec.table_name ||' SHRINK SPACE';
EXECUTE IMMEDIATE 'ALTER TABLE '|| cur_rec.table_name ||' DISABLE ROW MOVEMENT';
EXECUTE IMMEDIATE 'ANALYZE TABLE '|| cur_rec.table_name ||' COMPUTE STATISTICS';
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
 FOR cur_rec IN (SELECT distinct index_name
                  FROM   dba_indexes) LOOP
    BEGIN
       EXECUTE IMMEDIATE 'ALTER INDEX '|| cur_rec.index_name ||' REBUILD' ;
      EXECUTE IMMEDIATE 'ANALYZE INDEX '|| cur_rec.index_name ||' COMPUTE STATISTICS' ;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
    BEGIN
     SYS.UTL_RECOMP.recomp_serial('LDBO');
    END;
END;
/


Friday, May 4, 2012

Purge Queue Table


BEGIN
FOR cur_rec IN (SELECT owner,queue_table FROM dba_queue_tables where owner not in ('DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) LOOP
DECLARE
 po_t dbms_aqadm.aq$_purge_options_t;
    BEGIN
DBMS_AQADM.PURGE_QUEUE_TABLE('' || cur_rec.owner || '' || '.' || '' || cur_rec.queue_table || '', NULL, po_t);
    END;
  END LOOP;
END;
/


----------------Schema Tables---------

BEGIN
FOR cur_rec IN (SELECT queue_table FROM user_queue_tables) LOOP
DECLARE
 po_t dbms_aqadm.aq$_purge_options_t;
    BEGIN
DBMS_AQADM.PURGE_QUEUE_TABLE('' || cur_rec.queue_table || '', NULL, po_t);
    END;
  END LOOP;
END;
/

------------Purge Specific Table

BEGIN
FOR cur_rec IN (SELECT queue_table FROM user_queue_tables) LOOP
DECLARE
 po_t dbms_aqadm.aq$_purge_options_t;
    BEGIN
      DBMS_AQADM.PURGE_QUEUE_TABLE('TBLRKQUEUE', NULL, po_t);
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/

Drop All Database Objects


BEGIN
  FOR cur_rec IN (SELECT table_owner, table_name, constraint_name FROM dba_constraints
                  WHERE  constraint_type = 'R' and table_owner not in ('DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE ' || cur_rec.table_owner|| '.'  || cur_rec.table_name || ' DROP CONSTRAINT ' || cur_rec.constraint_name;
  END LOOP;
  FOR cur_rec IN (SELECT owner, object_name, object_type
                  FROM   dba_objects where owner not in ('DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'DROP ' || cur_rec.owner|| '.'  || cur_rec.object_type || ' ' || cur_rec.object_name;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
FOR cur_rec IN (SELECT owner,synonym_name,table_owner FROM  dba_synonyms where table_owner not in ('DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) LOOP
BEGIN
      EXECUTE IMMEDIATE 'DROP ' || cur_rec.owner || ' SYNONYM ' || cur_rec.table_owner || '.' || cur_rec.synonym_name || ' FORCE';
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
FOR cur_rec IN (SELECT owner, queue_table FROM dba_queue_tables where owner not in ('DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) LOOP
    BEGIN
    DBMS_AQADM.DROP_QUEUE_TABLE('' || cur_rec.owner || '' || '.' || '' || cur_rec.queue_table || '',force =>true);
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/

Track User password change Audit Activity


if you want to track password change or other activity with sql then

ALTER SYSTEM SET AUDIT_TRAIL=DB_EXTENDED SCOPE=SPFILE;

THERE IS SQL_TEXT COLUMN IN DBA_AUDIT_TRAIL VIEW.

Thursday, May 3, 2012

Foxpro Error Error building key for index Operation is invalid for a Memo, Blob, General or Picture field.


Problem :
System Run time Error messages :
Error building key for index "c:\docume~1\admini~1\locals~1\temp\0000yqe1000z.cdx" tag "Clrisk".                                  
Operation is invalid for a Memo, Blob, General or Picture field.                                                                   

Solution :
Alter system set cursor_sharing=exact scope=both ;

Foxpro Error: Operation is invalid for a Memo, Blob, General or Picture field.


Error: Operation is invalid for a Memo, Blob, General or Picture field
Message: Foxcode table not found, is not correct version or is incompatible.
METHOD: beforeopentables
PROGRAM: frmCheckerCreateUser


There may be following reasons for above error

1) An INDEX command cannot specify a Memo or Blob field as its key expression.

2) There was not enough memory (shared pool) for Visual FoxPro to complete an operation.

3) Unions between differing column types are not allowed when one of the columns is of Memo or General type unless UNION ALL is used.

4) Visual FoxPro cannot update the specified cursor because it is read-only. This can occur when attempting to write to a file created or accessed for read-only purposes.


Solution;

CURSOR_SHARING (Default value: Exact): Share the plan only if text of SQL matches exactly with the text of SQL lying in shared pool.

Sqlplus>show parameter cursor_sharing

Exact is the default value for cursor_sharing.

Sqlplus>ALTER SYSTEM SET cursor_sharing='EXACT';

-- verify default value of EXACT
Sqlplus>show parameter cursor_sharing

-- clear all cached SQL statements
Sqlplus>ALTER SYSTEM FLUSH SHARED_POOL;
Sqlplus>ALTER SYSTEM FLUSH SHARED_POOL;

Wednesday, May 2, 2012

Oracle 11g Embed Text to Sound translator



CREATE TABLE EMP (EMPNO VARCHAR2(10),ENAME VARCHAR2(20), SAL VARCHAR2(10), JOB VARCHAR2(10) ,HIREDATE DATE,DEPTNO VARCHAR2(10));


 INSERT INTO EMP VALUES('10','KSHITIJ','100','DBA','01-DEC-2011','12');
COMMIT;

------------------------------------------------------------------------------------------------


begin
        dbms_network_acl_admin.create_acl (
                acl             => 'utlpkg.xml',
                description     => 'Normal Access',
                principal       => 'CONNECT',
                is_grant        => TRUE,
                privilege       => 'connect',
                start_date      => null,
                end_date        => null
        );
  dbms_network_acl_admin.add_privilege (
  acl => 'utlpkg.xml',
  principal => 'DPCDSL',
  is_grant => TRUE,
  privilege => 'connect',
  start_date => null,
  end_date => null);

  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'utlpkg.xml',
    host        => 'translate.google.com',
    lower_port  => 80,
    upper_port  => NULL);

  COMMIT;
END;
/

--------------------------------------------------------------------------------------------------------------------------------
create or replace
package mytranslator
as

function getMP3
( p_text in varchar2
, p_language in varchar2 default 'en'
) return blob
;

procedure play_sound
( p_text in varchar2
);

procedure play_employee
( p_empno    in number
, p_language in varchar2 default 'en'
);

procedure soundcheck;
end;
/

------------------

create or replace
package body mytranslator
as

function load_binary_from_url (p_url  IN  VARCHAR2)
return blob
AS
  l_http_request   UTL_HTTP.req;
  l_http_response  UTL_HTTP.resp;
  l_blob           BLOB;
  l_raw            RAW(32767);
BEGIN
  -- Initialize the BLOB.
  DBMS_LOB.createtemporary(l_blob, FALSE);
  -- Make a HTTP request and get the response.
  l_http_request  := UTL_HTTP.begin_request(p_url);
  l_http_response := UTL_HTTP.get_response(l_http_request);
  -- Copy the response into the BLOB.
  BEGIN
    LOOP
      UTL_HTTP.read_raw(l_http_response, l_raw, 32767);
      DBMS_LOB.writeappend (l_blob, UTL_RAW.length(l_raw), l_raw);
    END LOOP;
  EXCEPTION
    WHEN UTL_HTTP.end_of_body
THEN
      UTL_HTTP.end_response(l_http_response);
  end;
  return l_blob;
end load_binary_from_url;


function getMP3
( p_text in varchar2
, p_language in varchar2 default 'en'
) return blob
is
  l_base_url varchar2(2000):= 'http://translate.google.com/translate_tts?tl=';
begin
  return load_binary_from_url
  ( p_url=> l_base_url||p_language||chr(38)||'q='
  ||utl_url.escape(p_text)
  );
 -- note: chr(38) is the ampersand
end getMP3;

procedure download_blob
( p_blob in out nocopy blob
) as
  l_mime_type varchar2(30):= 'audio/mpeg';
  l_blob_size number(10);
begin
l_blob_size := dbms_lob.getlength(p_blob);
owa_util.mime_header(l_mime_type, FALSE, NULL);
    htp.p('Content-length: '|| l_blob_size);
  owa_util.http_header_close;
  -- download BLOB
wpg_docload.download_file(p_blob);
end download_blob;

procedure play_sound
( p_text in varchar2
) is
  l_blob blob;
begin
  l_blob:= getMP3 ( p_text);
  download_blob (l_blob);
end play_sound;

procedure play_employee
( p_empno    in number
, p_language in varchar2 default 'en'
) is
  l_blob blob;
  l_blob2 blob;
begin

  l_blob:= getMP3 ( 'Details on Employee', p_language);
 
  for emp in (select ename, sal, job, to_char(hiredate, 'MONTH YYYY') hireyear, deptno
              from   emp
              where  empno = p_empno
              ) loop
    l_blob2:= getMP3 ( 'Employee is called '||emp.ename||' his job is '||emp.job, p_language);
    DBMS_LOB.APPEND
    ( dest_lob => l_blob
    , src_lob  => l_blob2
    );
    l_blob2:= getMP3 ( 'He works in department '||emp.deptno||' and earns  '||emp.sal||' dollar', p_language);
    DBMS_LOB.APPEND
    ( dest_lob => l_blob
    , src_lob  => l_blob2
    );
    l_blob2:= getMP3 ( 'He was hired back in '||emp.hireyear, p_language);
    DBMS_LOB.APPEND
    ( dest_lob => l_blob
    , src_lob  => l_blob2
    );
  end loop;
 
  download_blob (l_blob);  
exception
  when others
  then
    l_blob:= getMP3 ( 'Exception occurred '||sqlerrm, p_language);
    download_blob (l_blob);  
end play_employee;


procedure soundcheck
is
  l_blob blob;
  space varchar2(4):='%20';
begin

  l_blob:= getMP3 ( 'Test one two three test', p_language=> 'en');
  download_blob (l_blob);  
exception
  when others
  then
    l_blob:= getMP3 ( 'Exception'||space||'occurred', p_language =>'en');
    download_blob (l_blob);  
end soundcheck;


end;

------------------

select mytranslator.getmp3('hello how r u what is your name. my name is john') from dual

select mytranslator.play_sound('hello how r u what is your name. my name is john') from dual


BEGIN
  DBMS_EPG.create_dad
  ( dad_name => 'hrmreport'
  , path => '/hrm/*'
  );
  DBMS_EPG.AUTHORIZE_DAD('hrmreport','DPCDSL');
end;
/


http://blog.mclaughlinsoftware.com/2009/12/01/oracle-11g-xdb-dads/

http://www.oracle-base.com/articles/10g/dbms_epg_10gR2.php

http://psoug.org/reference/dbms_epg.html

SELECT * FROM dba_epg_dad_authorization;

Friday, April 27, 2012

How to do FTP using PL SQL

How to do FTP using PL SQL



Load the attached FTP Package in Oracle. Once the API is loaded into the appropriate schema simple FTP commands can be initiated as follows:

CREATE OR REPLACE DIRECTORY my_docs AS '/u01/app/oracle/';
SET SERVEROUTPUT ON SIZE 1000000

http://www.oracle-base.com/dba/miscellaneous/ftp.pkb
http://www.oracle-base.com/dba/miscellaneous/ftp.pks

@c:\ftp.pks
@c:\ftp.pkb

-- Retrieve an ASCII file from a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.ascii(p_conn => l_conn);
  ftp.get(p_conn      => l_conn,
          p_from_file => '/u01/app/oracle/test.txt',
          p_to_dir    => 'MY_DOCS',
          p_to_file   => 'test_get.txt');
  ftp.logout(l_conn);
END;
/

-- Send an ASCII file to a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.ascii(p_conn => l_conn);
  ftp.put(p_conn      => l_conn,
          p_from_dir  => 'MY_DOCS',
          p_from_file => 'test_get.txt',
          p_to_file   => '/u01/app/oracle/test_put.txt');
  ftp.logout(l_conn);
END;
/

-- Retrieve a binary file from a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.binary(p_conn => l_conn);
  ftp.get(p_conn      => l_conn,
          p_from_file => '/u01/app/oracle/product/9.2.0.1.0/sysman/reporting/gif/jobs.gif',
          p_to_dir    => 'MY_DOCS',
          p_to_file   => 'jobs_get.gif');
  ftp.logout(l_conn);
END;
/

-- Send a binary file to a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.binary(p_conn => l_conn);
  ftp.put(p_conn      => l_conn,
          p_from_dir  => 'MY_DOCS',
          p_from_file => 'jobs_get.gif',
          p_to_file   => '/u01/app/oracle/jobs_put.gif');
  ftp.logout(l_conn);
END;
/

-- Get a directory listing from a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
  l_list  ftp.t_string_table;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.list(p_conn   => l_conn,
           p_dir   => '/u01/app/oracle',
           p_list  => l_list);
  ftp.logout(l_conn);
 
  IF l_list.COUNT > 0 THEN
    FOR i IN l_list.first .. l_list.last LOOP
      DBMS_OUTPUT.put_line(i || ': ' || l_list(i));
    END LOOP;
  END IF;
END;
/

-- Get a directory listing (file names only) from a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
  l_list  ftp.t_string_table;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.nlst(p_conn   => l_conn,
           p_dir   => '/u01/app/oracle',
           p_list  => l_list);
  ftp.logout(l_conn);
 
  IF l_list.COUNT > 0 THEN
    FOR i IN l_list.first .. l_list.last LOOP
      DBMS_OUTPUT.put_line(i || ': ' || l_list(i));
    END LOOP;
  END IF;
END;
/

-- Rename a file on a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.rename(p_conn => l_conn,
             p_from => '/u01/app/oracle/dba/shutdown',
             p_to   => '/u01/app/oracle/dba/shutdown.old');
  ftp.logout(l_conn);
END;
/

-- Delete a file on a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.delete(p_conn => l_conn,
             p_file => '/u01/app/oracle/dba/temp.txt');
  ftp.logout(l_conn);
END;
/

-- Create a directory on a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.mkdir(p_conn => l_conn,
            p_dir => '/u01/app/oracle/test');
  ftp.logout(l_conn);
END;
/

-- Remove a directory from a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.rmdir(p_conn => l_conn,
            p_dir  => '/u01/app/oracle/test');
  ftp.logout(l_conn);
END;
/
The basic functions are implemented using LOBs to allow FTP without having to access files on the local filesystem. The get and put procedures string these together to form a complete job using all the functions. If a straight forward FTP to, or from, the local filesystem is required it is more efficient to use the GET_DIRECT and PUT_DIRECT procedures as they avoid the temporary LOBs.

The current implementation has the following issues:

The mput and mget operations are not supported directly, but can be implemented using a combination of the list/nlst and get/put operations.
The implementation of binary transfers relies on UTL_FILE features only available in Oracle9i Release 2 upwards.
There is no support for ASCII mode in the PUT_DIRECT procedure.
Thanks to Hans van Doormalen for noticing I wasn't closing my passive connections. I do now :)

If you are using Oracle 11g you will need to configure an access control list (ACL) to allow UTL_TCP to access the network. The examples above work correctly with the following basic ACL. You will need to amend the FTP server and username details to match your FTP server address and the Oracle username running the FTP API.

DECLARE
  l_acl_name    VARCHAR2(30) := 'utl_tcp.xml';
  l_ftp_server  VARCHAR2(20) := '192.168.2.131';
  l_username    VARCHAR2(30) := 'TEST';
BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => l_acl_name,
    description  => 'Allow connections using UTL_TCP',
    principal    => l_username,
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  COMMIT;

  DBMS_NETWORK_ACL_ADMIN.add_privilege (
    acl         => l_acl_name,
    principal   => l_username,
    is_grant    => FALSE,
    privilege   => 'connect',
    position    => NULL,
    start_date  => NULL,
    end_date    => NULL);

  COMMIT;

  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => l_acl_name,
    host        => l_ftp_server,
    lower_port  => NULL,
    upper_port  => NULL);

  COMMIT;
END;
/

Followers