Monday, August 24, 2015

Tuesday, August 18, 2015

Create and Drop a Job SCHEDULER (DBMS_SCHEDULER AND DBMS_JOB) IN ORACLE DATABASE 10G




create  table honnikery as select * from emp where 1=2


select * from honnikery

select job,last_date,next_date,broken from dba_jobs



CREATE OR REPLACE PROCEDURE SCHEDULER_TEST
AS
BEGIN
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE honnikery';
END;
BEGIN
INSERT INTO honnikery
SELECT * FROM emp;
COMMIT;
END;
END;



declare
  l_jobno number;

begin

 dbms_job.submit(
  l_jobno,
   what=>'Proc_Test;',
   next_date=>sysdate, -- start now
   interval=>'sysdate+5/(24*60)'
 ); 
 commit;
end;



BEGIN 
  DBMS_SCHEDULER.create_job ( 
  job_name => 'SCHEDULER_JOB',
   job_type => 'PLSQL_BLOCK', 
   job_action => 'BEGIN SCHEDULER_TEST ;  END;', 
   start_date => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=5; bysecond=0;', 
    enabled => TRUE); 
    END;

  Drop a Job SCHEDULER

begin
dbms_scheduler.drop_job(job_name => 'SCHEDULER_JOB');
end;

Enable / Disable a job

BEGIN 
  DBMS_SCHEDULER.DISABLE('SCHEDULER_JOB');
END;



BEGIN 
  DBMS_SCHEDULER.ENABLE('SCHEDULER_JOB');
END;
 
 

Monitoring jobs 

 SELECT * FROM user_scheduler_jobs WHERE job_name = 'SCHEDULER_JOB';
 SELECT * FROM user_scheduler_job_log WHERE job_name = 'SCHEDULER_JOB';


Wednesday, August 12, 2015

ORA-00439: feature not enabled: Materialized view rewrite

   When trying to create a materialized view in SQL*Plus an error is returned:



 create materialized view mvprab
  refresh fast on commit
  enable query rewrite
  as
    select
     a    ,
   b  ,
     c ,
     ,
 
    from
      p, p1
    where
      p.a= p1.a
;



  Check the Materialized view rewrite option is enable or disable


PARAMETER                                              VALUE
------------------                                             -------------------
Materialized view rewrite                                FALSE
Materialized view warehouse refresh              FALSE


standard  edition not allow to use the Materialized view Query rewrite option

Tuesday, August 11, 2015

MySQL: how can I see ALL constraints on a table and database

select * from information_schema.table_constraints
where table_name='honni';





select column_name, constraint_name, referenced_column_name, referenced_table_name
from information_schema.key_column_usage
where table_name = 'table to be checked';



select *
from information_schema.table_constraints
where constraint_schema = 'honnikery'


Tuesday, August 4, 2015

Permissions for creating a trigger in mysql (normal user)

create trigger without SUPER privilege by adding the following line to my.cnf or my.ini    

 log_bin_trust_function_creators = 1

OR 

Give SUPER privilege on database 

grant super on *.* to 'honni'@'localhost';

revoke it

revoke super on *.* from 'my_user'@'localhost';

Wednesday, July 22, 2015

How to save data in mysql database with sails.Js

Added Connection Configuration


Open /config/connections.js

******************************



 module.exports.connections = {

mysql: {

    module    : 'sails-mysql',

    host      : 'localhost',

    user      : 'root',

    password  : 'password',

    database  : 'test'

      }



Go to  /config/models.js

***************************


module.exports.models = {


  connection: 'mysql',


GO to api/models/User.js

****************************



module.exports = {

  connection: 'mysql',

  attributes: {

   

    name:{

     type:"string",

     required:true,

      minLength: 2

    },

    empnum:{

     type:"string",

     required:true

    },

    email:{

     type:"email",

     required:"true",

     unique: true

    }

   

  }


};


GO to api/controllers/UserController.js

*****************************************


module.exports = {


'registration': function(req,res){


    res.view();

},


create: function(req,res,next){


    User.create(req.params.all(), function userCreated(err,User){


        if(err){

            return next(err);

        }

        else{

 return res.send("Data Saved Successfully");


        }


    });

 }


 }


Type : http://localhost:1337/User