Oracle DBMS_SCHEDULER

Oracle >  SYS.DBMS_SCHEDULER 

The DBMS_SCHEDULER package provides a collection of scheduling functions and procedures.

• Create job

Oracle create job example

Oracle >  SYS.DBMS_SCHEDULER > CREATE_JOB

The CREATE_JOB procedure creates a single job. You can create multiple jobs using the CREATE_JOBS procedure.

Example:

Oracle create job example

BEGIN

    SYS.DBMS_SCHEDULER.CREATE_JOB (

            job_name => '"db"."job"',

            job_type => 'PLSQL_BLOCK',

            job_action => 'delete from item',

            number_of_arguments => 0,

            start_date => TO_TIMESTAMP('19-FEB-14 04.51.50.316000000 PM', 'DD-MON-RR HH.MI.SS.FF AM'),

            repeat_interval => 'FREQ=DAILY;BYHOUR=3',

            end_date => NULL,

            job_class => 'DEFAULT_JOB_CLASS',

            enabled => false,

            auto_drop => true,

            comments => NULL);

END;

sys.schemas SQL Server example

SQL Server > Catalog Views > sys.schemas

Gets information about database schema.

Example:

select * from sys.schemas

Result

Timestamp to date Oracle

Oracle > Timestamp > To Date

select * from your_table
where
TO_DATE (TO_CHAR (timestamp_date, 'YYYY-MON-DD'),'YYYY-MON-DD') = '18-FEB-2014'
 

sys.data_spaces SQL Server

SQL Server > Catalog Views > sys.data_spaces

Return info for each data space.

Example

select * from sys.data_spaces

Add Image Column to Telerik Radgrid

Telerik > RadGridView > GridViewImageColumn

GridViewImageColumn displays read-only images for database columns of image data





GridViewImageColumn imageColumn = new GridViewImageColumn();
imageColumn.Name = "imgCol";
imageColumn.FieldName = "dbPhoto";
imageColumn.HeaderText = "Picture";
imageColumn.ImageLayout = ImageLayout.Zoom; // Zoom - Image is zoomed but the aspect ratio is preserved:
radGridView1.MasterTemplate.Columns.Insert(3, imageColumn);

Using Joins SQL Server Examples

SQL SERVER > QUERY > JOIN

Using joins you can retrieve data from two or more tables based on logical relationships between the tables.

create table #category(name varchar(20), description  varchar(20))

insert into #category(name,description)
values('cat 1','cat 1 description')
insert into #category(name,description)
values('cat 2','cat 2 description')
insert into #category(name,description)
values('cat 3','cat 3 description')

create table #product(id int, name varchar(20), category varchar(20))

insert into #product(id,name,category)

values(1,'product 1','cat 1')
insert into #product(id,name,category)
values(2,'product 2','cat 2')
insert into #product(id,name,category)
values(2,'product 3',null)

INNER JOIN  returns all the columns in both tables, and returns only the rows for which there is an equal value in the join column. 

select p.id, p.name, c.description as category

from #product p
inner join #category c
       on p.category = c.name

LEFT OUTER JOIN includes all rows in the a table  whether or not there is a match on the another table.

select p.id, p.name, c.description as category

from #product p
left  join #category c
       on p.category = c.name

RIGHT OUTER JOIN indicates all rows in the second table are to be included in the results, regardless of whether there is matching data in the first table.

select p.id, p.name, c.description as category

from #product p
right  join #category c
       on p.category = c.name

FULL OUTER JOIN includes all rows from both tables, regardless of whether or not the other table has a matching value.

select p.id, p.name, c.description as category

from #product p
full  join #category c
       on p.category = c.name

CROSS JOIN produces the Cartesian product of the tables involved in the join.

 select p.id, p.name, c.description as category
from #product p
cross join #category c

      

drop table #product

drop table #category