Friday, 5 July 2013

Oracle : Different types of Objects

PL/SQL stands for Procedural Language extension of SQL. It is a combination of SQL along with the procedural features of programming languages.

Following are the some objects in oracle .We must know at least each object definition. So, provided here.


Functions :
------------------
Functions are the group of the SQL / PL-SQL statements which performs the task and return value/values to the calling environment.

Procedures:
-----------------
It is a named PL/SQL block which performs one or more specific task.

Difference between procedure and functions -
Function must returns value while procedure may or may not returns value.
Function can be called in SQL statements we cannot call procedure in the SQL statements.

Packages :
---------------
A package is a object that groups logically related PL/SQL types, items, and subprograms.

Triggers :
-------------
A trigger is a pl/sql block structure which is fired automatically when Insert, Delete, Update is executed on a database table.

Cursors :
-----------
A cursor is a temporary work area created in the memory when a SQL statement is executed. A cursor can hold more than one row, but can process only one row at a time.

 Views:
-----------
A view is a virtual table consisting of a stored query, it contains no data.

Materialized views :
-----------------------
Every time we use a view oracle has to execute the sql statement defined for that view (called view resolution), it must be done each time the view is used. If the view is complex this can take sometime, this is where a materialized views comes in, unlike a view it contains space and storage just like a regular table.

 Sequences :
--------------------
Sequences are database objects from which multiple users can generate unique integers. You can use sequences to automatically generate primary key values.

Synonyms :
----------------
A synonym is an alias for a schema object.

Constraints :
------------------
Constraints apply specific rules to data, ensuring the data is in required format. There are a number of different kinds of constraints -
  • Check
  • Not NULL
  • Primary key
  • Unique
  • Foreign Key
  • Default
 Indexes :
-------------
Indexes are optional structures associated with tables and clusters that allow SQL statements to execute more quickly against a table.

Monday, 1 July 2013

Coldfusion : How to check that Coldfusion scheduled task is already created or not?

Question :

 Is there any way to check that scheduled task is already created? If not already exists create new one.

Code :

<!--- 1. By using cfobject --->
<cfobject type="JAVA" action="Create" name="Service_factory" class="coldfusion.server.ServiceFactory">
<cfset Service_factory = CreateObject("java", "coldfusion.server.ServiceFactory") />
<cfset Scheduled_Tasks_Array = Service_factory.CronService.listAll() />
<cfdump var="#Scheduled_Tasks_Array#" />
<!--- 2. By Using create object function --->
<cfdump var="#createobject("java","coldfusion.server.ServiceFactory").CronService.listAll()#" />
<!--- 3.By reading the XML file neo-cron.xml--->
<cffile action="Read"
      file="#Server.ColdFusion.RootDir#\lib\neo-cron.xml"
      variable="TaskXML">
<!--- Convert the WDDX to CFML - and array of structs --->
<cfwddx action="WDDX2CFML" input="#TaskXML#" output="Scheduled_Tasks_Array">
<!--- Dump all the schedule tasks in CF Admin in Struct format. --->
<cfdump var="#Scheduled_Tasks_Array#" /> 
<cfif arraylen(Scheduled_Tasks_Array) EQ 0>
  <cfschedule   
    action = "update"  
    task = "test schedule 1"  
    startDate = "01/01/2013"
    startTime = "00:00:00"  
    url = "www.google.com"   
    interval = "900"
    operation = "HTTPRequest"     
  >
</cfif>

Output :

 All the scheduled task data get stored in the XML file at the following location -
#Server.ColdFusion.RootDir#\lib\neo-cron.xml

One more thing, for coldfusion schedule task we can't give interval less than 60 seconds. Otherwise it will throw error as -


  • The task interval must be greater then 60 seconds.

  • But I get to know that through the "neo-cron.xml" we can achieve this.Hope this help !!!