using REGEXP_LIKE and REGEXP_INSTR

SELECT 1

FROM DUAL

WHERE REGEXP_LIKE ('ANAND_1983@LIVE.in', '^ [a-zA-Z0-9+_+.]+@[a-zA-Z0-9]+\.[a-zA-Z]{2,4}$');

Query to find the Last DML Operation:

(1).exec dbms_stats.flush_database_monitoring_info;

SELECT * FROM sys.DBA_TAB_MODIFICATIONS WHERE TABLE_NAME like 'TABLE_4'

(2).SELECT scn_to_timestamp(ora_rowscn) FROM EMP;

Ref Cursor

Ref Cursor:

A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).

Ref Cursor is of two types:

1. Strong ref cursor: This has a return type defined.

2. Weak ref cursor: This doesn’t have a return type.

Simple Example of Ref Cursor:

Example 1:

DECLARE

TYPE R_CURSOR IS REF CURSOR;

C_EMP R_CURSOR;

EN EMP.ENAME%TYPE;

BEGIN

OPEN C_EMP FOR SELECT ENAME FROM EMP;

LOOP

FETCH C_EMP INTO EN;

EXIT WHEN C_EMP%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(EN);

END LOOP;

CLOSE C_EMP;

END;

Example 2:

DECLARE

TYPE EMP_TAB IS REF CURSOR;

EMP_REC EMP_TAB;

EMP_SRC EMP%ROWTYPE;

DEPTID NUMBER(2):=10;

SQL_STMT Varchar2(200);

BEGIN

SQL_STMT:='SELECT * FROM EMP WHERE DEPTNO=:D';

OPEN EMP_REC FOR SQL_STMT USING DEPTID;

LOOP

FETCH EMP_REC INTO EMP_SRC;

EXIT WHEN EMP_REC%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(EMP_SRC.ENAME);

END LOOP;

CLOSE EMP_REC;

END;

EXAMPLE 3:

DECLARE

TYPE R_CURSOR IS REF CURSOR;

C_EMP R_CURSOR;

TYPE REC_EMP IS RECORD

(

NAME VARCHAR2(20),

SAL NUMBER(6)

);

ER REC_EMP;

BEGIN

OPEN C_EMP FOR SELECT ENAME,SAL FROM EMP;

LOOP

FETCH C_EMP INTO ER;

EXIT WHEN C_EMP%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(ER.NAME || ' - ' || ER.SAL);

END LOOP;

CLOSE C_EMP;

END;

HOW TO FIND THE LAST EXECUTION TIME OF THE PROCEDURE?

Select systimestamp, owner, obj_name, action_name from dba_audit_trail;

NOTE: Auditing is disabled by default

CONNECT sys/password AS SYSDBA;

--ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

AUDIT ALL BY MIS BY ACCESS;

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY MIS BY ACCESS;

AUDIT EXECUTE PROCEDURE BY MIS BY ACCESS;

Materialized View

Materialized View:

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables are also, know as snapshots.

A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).

Syntax

CREATE MATERIALIZED VIEW mv_name

[ REFRESH [ FAST | COMPLETE | FORCE ]

[ON DEMAND | COMMIT]

[START WITH DATE] [NEXT DATE]

[WITH (PRIMARY KEY | ROW ID)]

]

AS

SELECT CLAUSE;

EXPLANATION:

Refresh Method - FAST Clause

The FAST refreshes use the materialized view logs (as seen above) to send the rows that have changed from master tables to the materialized view.

You should create a materialized view log for the master tables if you specify the REFRESH FAST clause.

SQL> CREATE MATERIALIZED VIEW LOG ON emp;

Materialized view log created.

Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function.

Refresh Method - COMPLETE Clause

The complete refresh re-creates the entire materialized view. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible.

Refresh Method - FORCE Clause

When you specify a FORCE clause, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), FORCE is the default.

PRIMARY KEY and ROWID Clause

WITH PRIMARY KEY is used to create a primary key materialized view i.e. the materialized view is based on the primary key of the master table instead of ROWID (for ROWID clause). PRIMARY KEY is the default option. To use the PRIMARY KEY clause you should have defined PRIMARY KEY on the master table or else you should use ROWID based materialized views.

Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.

Rowid materialized views should have a single master table and cannot contain any of the following:

Distinct or aggregate functions

GROUP BY Subqueries , Joins & Set operations

ON COMMIT

Refresh occurs automatically on the next COMMIT performed at the master table. Can be used with materialized views on single table aggregates and materialized views containing joins only.

ON DEMAND

Refresh occurs when a user manually executes one of the available refresh procedures contained in the DBMS_MVIEW package (REFRESH, REFRESH_ALL_MVIEWS, REFRESH_DEPENDENT).

Timing the refresh

The START WITH clause tells the database when to perform the first replication from the master table to the local base table. It should evaluate to a future point in time. The NEXT clause specifies the interval between refreshes

SQL> CREATE MATERIALIZED VIEW mv_emp_pk

REFRESH FAST

START WITH SYSDATE

NEXT SYSDATE + 2

WITH PRIMARY KEY

AS SELECT * FROM emp@remote_db;

In the above example, the first copy of the materialized view is made at SYSDATE and the interval at which the refresh has to be performed is every two days.

Run Report from Sql

DECLARE

myplist srw_paramlist;

v_rep_dest VARCHAR2(1000) := NULL;

myident srw.job_ident;

BEGIN

myplist := srw_paramlist(srw_parameter('', ''));

srw.ADD_PARAMETER(

myplist,

'GATEWAY',

'http://as_server/reports/rwservlet'

);

srw.ADD_PARAMETER(myplist, 'SERVER', 'rep_as_server_frhome1');

srw.ADD_PARAMETER(

myplist,

'REPORT',

'\\as_server\roadmap\7.2.1\fe10g\HRF3010.RDF'

);

srw.ADD_PARAMETER(myplist, 'USERID', 'RMVER721/RMVER721@ORCL');

srw.ADD_PARAMETER(myplist, 'DESTYPE', 'FILE');

srw.ADD_PARAMETER(myplist, 'DESFORMAT', 'PDF');

v_rep_dest := '\\as_server\reportshares\HRF3010' || '.PDF';

srw.ADD_PARAMETER(myplist, 'DESNAME', v_rep_dest);

srw.ADD_PARAMETER(myplist, 'BU', 'MAP');

myident := srw.run_report(myplist);

srw.clear_parameter_list(myplist);

END;