RICE components

RICE components

DDL Triggers

CREATE OR REPLACE TRIGGER <trigger_name>
<BEFORE | AFTER> <triggering_action>
ON <SCHEMA | DATABASE>

DECLARE
 -- variable declarations
BEGIN
  -- trigger code
EXCEPTION
  -- exception handler
END <trigger_name>;
/





CREATE OR REPLACE TRIGGER bcs_trigger
BEFORE CREATE
ON SCHEMA

DECLARE
 oper ddl_log.operation%TYPE;
BEGIN
  INSERT INTO ddl_log
  SELECT ora_sysevent, ora_dict_obj_owner,
  ora_dict_obj_name, NULL, USER, SYSDATE
  FROM DUAL;
END bcs_trigger;
/

col object_name format a20

SELECT object_name, object_type
FROM user_objects;

col triggering_event format a20
SELECT trigger_name, trigger_type, 
triggering_event, base_object_type
FROM user_triggers;

SELECT * FROM ddl_log;

CREATE SEQUENCE s1_test;

CREATE TABLE t1_test (
testcol VARCHAR2(20));

CREATE OR REPLACE VIEW v_test AS
SELECT * FROM t_test;

set linesize 150

SELECT operation, obj_owner, object_name
FROM ddl_log;

TRUNCATE TABLE ddl_log;

conn system/manager

CREATE TABLE uwclass.xyz (
testcol VARCHAR2(20));

conn uwclass/uwclass

SELECT operation, obj_owner, object_name
FROM ddl_log;


TRUNCATE TABLE ddl_log;



















CREATE OR REPLACE TRIGGER <trigger_name>
<BEFORE | AFTER> <triggering_action>
ON <SCHEMA | DATABASE>

DECLARE
 -- variable declarations
BEGIN
  -- trigger code
EXCEPTION
  -- exception handler
END <trigger_name>;
/




conn system/manager

GRANT administer database trigger TO uwclass;

conn uwclass/uwclass

CREATE OR REPLACE TRIGGER bcd_trigger
BEFORE CREATE
ON DATABASE

DECLARE
 oper ddl_log.operation%TYPE; 
BEGIN
  INSERT INTO ddl_log
  SELECT ora_sysevent, ora_dict_obj_owner, 
  ora_dict_obj_name, NULL, USER, SYSDATE
  FROM DUAL;
END bcd_trigger;
/

col object_name format a20
SELECT object_name, object_type
FROM user_objects;

col triggering_event format a20
SELECT trigger_name, trigger_type, 
triggering_event, base_object_type
FROM user_triggers;

SELECT * FROM ddl_log;

CREATE SEQUENCE s2_test;

CREATE TABLE t2_test (
testcol VARCHAR2(20));

CREATE OR REPLACE VIEW v_test AS
SELECT * FROM t_test;

set linesize 150

SELECT operation, obj_owner, object_name
FROM ddl_log;

TRUNCATE TABLE ddl_log;

conn system/manager

CREATE TABLE uwclass.xyz (
testcol VARCHAR2(20));

conn uwclass/uwclass

SELECT operation, obj_owner, object_name
FROM ddl_log;

DROP TRIGGER bcd_trigger;


TRUNCATE TABLE ddl_log;

Decimal to Binary Conversion

DECLARE

X NUMBER := '&Number';
Y NUMBER := 0;
Z VARCHAR2(1000);
BEGIN
WHILE X > 0 LOOP
Y := MOD(X,2);
Z := Y||Z;
X := TRUNC(X/2);
END LOOP;

DBMS_OUTPUT.put_line('Bianry Output for this decimal '||X ||' number is '||Z);
END;

Spool with Oracle

Step1 Open Notepad and write the following lines. Then save it as myscript.sql in c:

spool c:\tajfile1

select * from emp;

spool off;

exit;

Step2.Open another notepad and write the following lines and save it as tajbatch.cmd file in thec:\

sqlplus scott/tiger@orclnew @c:\myscript.sql

Step3.Now you can double click on the tajbach.cmd in c:\ and you will find the output inc:\tajfile1.lst file.Step4.How to schedule this script.

Go to Control Panel ->Scheduled Tasks and make a new Schedule to call the tajbatch.cmd file.This will run this file and execute the script at the specified interval.

Step 5.How to Obtain output in Excel

set term off 

set echo off 

set pages 0

head on

emb on

newp noneset

markup html on

spool on

spool c:\emp.xls

select * from emp;

spool off

set markup html off

spool off

exit;

Step6.How to dynamically Generate a file NameWrite the following lines of code in script file say,.. myscript2.sql, then make a call thisscript from the .cmd file as shown in Step 2

set termout off;

set echo off;

set pagesize 9999;

col sys_date new_value curr_date;

select to_char(sysdate,'yyyy_ddmon_hh24_miss')||'.log' sys_date 

from dual;

spool ArevaSystemSnag_&curr_date;

set termout on;

select * from emp;

spool off;

exit;

Step7.Finally, combining all these options, we will now write a complete script to generate data inan Excel file with the file name of the output file having the timestamp in the name itself.Please note that the same has been used for generating the data on the live Areva Systemin a Snap file.

set termout off;

set echo off;

set pagesize 9999;

col sys_date new_value curr_date;

select to_char(sysdate,'yyyy_ddmon_hh24_mi_ss')||'.xls' sys_date

from dual;

set pages 0

head on

emb on

newp noneset markup html on

spool on

spool ArevaSystemSnag_&curr_date;set termout on;

select * from emp;

spool off;

exit;

SQL Loader SQLLDR

http://www.youtube.com/watch?v=O0dDQZFH4VM

SQL*Plus

SQL*Plus is the most basic Oracle Database utility, with a basic command-line interface, commonly used by users, administrators, and programmers.

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database.

SQL*Loader supports various load formats, selective loading, and multi-table loads.

we can start it from  Run > sqlplusw.exe

THE MOST IMPORTANT COMMANDS AVAILABLE IN SQL * PLUS:

ACCEPT – read a variable input from the user;

DEFINE – declares a variable (DEF);

DESCRIBE – lists the attributes of the tables or other objects (DESC);

EDIT - opens an editor in which you can change the last command;

EXIT or QUIT – disconnects the user and SQL * Plus session;

GET - searches for a file in SQL and submits SQL buffer;

HOST – running an operating system command (!);

LIST - shows the last command executed SQL buffer (L);

PROMPT - displays a text on the screen;

RUN - lists and runs the command that is saved in the SQL buffer (/);

SAVE – save buffer command into a SQL script file;

SET - change environment variables specific to SQL * Plus;

SHOW - displays the environment variables settings in SQL * Plus;

SPOOL - copies the output of a command to a file;

START – run a SQL script.

THE MOST IMPORTANT SQL * PLUS SET COMMANDS

- SET RECSEP {WR [APPED] | SHE [CH] | OFF} controls the print record delimiters. The three possible values have the following meanings:
WRAPPED prints a separator after each line of a record (even if it spans multiple lines);
EACH doing the same thing, but only at the end of a record;
OFF cancels print a separator.

- SET RECSEPCHAR {_ | c} sets the character that separates the records;

- SPACE {1 | n} sets the number of spaces between the columns.

- SET WRA [P] {OFF | ON} specifies how to display records (truncated or on a line).

- SET NULL text specifies the text displayed in place of NULL value.

- HEADS [EP] {| | c | OFF | ON} specifies the characters that separate the column names of records.

- SET UND [ERLINE] {_ | c | OFF | ON the specific character used} for underlining the names of the columns.

- SET NEWP [AGE] {1 | n | NONE} sets the number of blank lines left at top of every page.

- SET TERM [OUT] {OFF | ON the display of output controls –} generated commands executed from a script.

- SET PAGES [IZE] {24 | n} specifies the number of lines displayed per page (default is 24; may be set to a value of 0 to suppress the display of column names, the title of a report).

- SET FEED [BACK] {6 | n | OFF | ON the display number} controls the record supplied as a result of a SELECT command.

- ECHO {OFF | – Controls the display ON} commands from a script launched with the START command SQL, as they are executed.

The SPOOL command allows saving the results of a query to a file specified as a parameter. She has the following syntax:

ENHANCING COMPETITIVENESS [OL] [file_name [. ext] | OFF | OUT]

For more examples 

http://www.sql-plus.com/example/example.php

BSNL Tamil Nadu Tariff Vouchers

http://www.tamilnadu.bsnl.co.in/CM/AdCirculars/STV[Boosters].pdf