TRUNC ON DATE FIELD

TRUNC ( DATAFIELD,'<format>')

Default is 'dd' -- Truncate at the date level , meaning start of the day (Time: 00:00:00)


1. 'dd'  (First hour of the day) 

-- Start of the day

Example:
Select TRUNC(SYSDATE,'dd') from dual;

2. 'mm' (First day of the month) + (First hour of the day)

-- Start of the month

Example:
Select TRUNC(SYSDATE,'mm') from dual;


3. 'yyyy' (First month of the year With First Date) + (First day of the month) + (First hour of the day)

-- Start of the year

Example:
Select TRUNC(SYSDATE,'yyyy') from dual;


4. 'hh' ( Minutes part will be truncated)


 -- Start of the hour

Example:
Select to_char(TRUNC(SYSDATE,'hh'),'mm/dd/yyyy hh24:mi:ss') from dual;

5. 'mi'  (Seconds part will be truncated) 

-- Start of the minute

Example:
Select to_char(TRUNC(SYSDATE,'mi'),'mm/dd/yyyy hh24:mi:ss') from dual;

DBMS_APPLICATION_INFO (Package)

The DBMS_APPLICATION_INFO package allows programs to add information to the V$SESSION to make tracking of session activities more accurate.

Select module, action from v$session;


procedure set_module(module_name varchar2, action_name varchar2);

--  Sets the name of the module that is currently running to a new module.

procedure set_action(action_name varchar2);

-- Sets the name of the current action within the current module.

procedure read_module(module_name out varchar2, action_name out varchar2);

--  Reads the values of the module and action fields of the current session.

procedure set_client_info(client_info varchar2);

-- Sets the client info field of the v$session.

procedure read_client_info(client_info out varchar2);

-- Reads the value of the client_info field of the current session.


Example:

BEGIN
  DBMS_APPLICATION_INFO.set_module(module_name => 'add_order',
                                   action_name => 'insert into orders');
 
  -- Do insert into table.
END;
/


BEGIN
  DBMS_APPLICATION_INFO.set_action(action_name => 'insert into orders');
  DBMS_APPLICATION_INFO.set_client_info(client_info => 'Issued by Web Client');
 
  -- Do insert into ORDERS table.
END;
/

SQL DEVELOPER TOOL (FREE)

Oracle has released the free SQL DEVELOPER TOOL & can be download from oracle website.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b31695/intro.htm

Installation:

SQL DEVELOPER installs by simple "unzipping" the file downloaded from oracle.

Note: Not an windows installed program.


Run: Execute the sqldeveloper.exe in the unzipped folder.


1. Connection:

We can open multiple connections in a single instance.

Click New connection & then enter the connection name, host,port,sid,username,password.


2. After connecting to a schema (user):

we see three subpanels

1. Expandable list of objects associated with the user.
2. SQL Worksheet.
3. Results


Snippets: Drag & drop or code completion.


DBMS_OUTPUT : Will be displayed in DBMS_OUTPUT tab.


SQL Developer allows:

Setting and removal of breakpoints.

Complie for Debug : Must be executed to make an object available for debugging.

Once compiled for Debugging,When executed in Debug mode the code will stop at the break point.


ShortCut Keys:

F5 -- Execute as a script.

F9 -- Execute statement.

F10 -- Explain Plan

F11 -- Commit

F12 -- Rollback

Ctrl + F7 -- Formating.

F6 -- Auto Trace.

F7 -- Step by Step execution.

Shift + f4 -- Describe object in popup.

Ctrl + D -- Clear SQL WorkSheet.