SharePoint Feature

SharePoint allows developers to create solutions and deploy to its platform. One way to deploy a solution and manage it is using feature. Feature is a combination of several elements that can be turned on or off at various levels or scope.

• Farm scope (Farm)

• Web application scope (WebApplication)

• Site Collection scope (Site )

• Web site  scope (Web)

 

Feature is physically a xml file named feature.xml. Feature is accompanied by an elements file, commonly named elements.xml. This file name can be anything as long as it is reflected properly in feature.xml file. The feature.xml file specifies name of the feature and its scope as well as the name of the elements file.  Elements file can have custom actions and modules that are part of this feature to be deployed. A feature can also receive events such as feature activated or deactivated.

Following are the steps to create a feature and deploy it manually -

1. Create a class library project
2. Create appropriate folder structure, for example
   • Templates
     • Feature
       • CustomFeature
3. Add two files, example below
   1. Feature.xml

         1: <Feature 

      
      
      
      

         2:   Id="EEC24C3D-B14A-4c4b-8673-5E196E7DDA17"

      
      
      
      

         3:   Title="My Custom Feature"

      
      
      
      

         4:   Description=""

      
      
      
      

         5:   Scope="Web"

      
      
      
      

         6:   Hidden="false"

      
      
      
      

         7:   ImageUrl="TPG\binoculars.gif"  

      
      
      
      

         8:   xmlns="http://schemas.microsoft.com/sharepoint/">

      
      
      
      

         9:   <ElementManifests>

      
      
      
      

        10:     <ElementManifest Location="elements.xml"/>

      
      
      
      

        11:   </ElementManifests>  

      
      
      
      

        12: </Feature>

      
      

      
      
      
      
   
   
   
   2. Elements.xml
      
      
      

      
      

         1: <Elements xmlns="http://schemas.microsoft.com/sharepoint/">

      
      
      
      

         2:   <Module Path="PageTemplates" Url="SitePages" >

      
      
      
      

         3:     <File Url="MyPage.aspx" Type="Ghostable" />    

      
      
      
      

         4:   </Module>  

      
      
      
      

         5: </Elements>

      
      

      
      
      
      
   
   
   
   



4. Add a aspx page mypage.aspx and modify as you wish.



5. Sign the project and build



6. deploy the dll to GAC



7. deploy the feature using stsadm
   
   
   

   
   

      1: stsadm.exe -o installfeature -filename customfeature\feature.xml -force

   
   

   
   
   
   

An Oracle Primer for SQL Server Developers

Recently I had the opportunity to get my hands on Oracle. Coming from a SQL server b background it certainly seemed strange at first but got easier over time. The biggest challenge was to find resources that use Oracle to SQL server comparison to teach the material. So I decided a write this one to help out anybody who is interested.

Oracle Database

Oracle database consists of an instance and database files. An Oracle Instance is the process in memory that manages the database files. As opposed to SQL server, Oracle does not have a concept of individual databases. Every user in Oracle owns a schema and database objects such as tables are created under the schema. Schema owners own all objects on their schema. Different types of permissions are assigned to users that do not own the objects if desired.

Connecting to Oracle using SQL Developer

SQL Developer is one of the free and more popular Oracle management tool. There are various ways to connect to an Oracle database server using SQL Developer–

1. Basic – With basic, user will need to provide following fields –

a. HostName

b. Port

c. UserName

d. Password

2. TNS – One only needs to point to the Network alias. The list of Network Alias comes from a .ora file. Sql Developer looks for tnsnames.ora file in following locations -

a. $HOME/.tnsnames.ora

b. $TNS_ADMIN/tnsnames.ora

c. TNS_NAMES lookup key in the registry

d. /etc/tnsnames.ora ( non-windows )

e. $ORACLE_HOME/network/admin/tnsnames.ora

f.LocalMachine\SOFTWARE\ORACLE\ORACLE_HOME_KEY

g. LocalMachine\SOFTWARE\ORACLE\ORACLE_HOME

You can add a environment variable entry called TNS_ADMIN with folder location to the tnsnames.ora file. A typical entry looks like following –

   1: dev = 

   2: (DESCRIPTION = 

   3: (ADDRESS = (PROTOCOL = TCP)(HOST = 

   4: 192.168.XXX.XXX)(PORT = XXXX)) 

   5: (CONNECT_DATA = 

   6: (SERVER = DEDICATED) 

   7:  

   8: (SERVICE_NAME = idpdev2) 

   9: ) 

  10: )

As you can see, the connection string is a little more detailed in the ora file.

3. LDAP – Oracle connection information can be stored in a directory that implements LDAP directory services. If Oracle Internet Directory is setup on the network, one can use its host name and browse to a DB Service from the list of DB services.

Connecting to SQL Server from SQL Developer

SQL Developer cannot connect to SQL server out of the box is some configuration is necessary. Follow this steps to connect to a SQL Server instance –

1. Enable TCP/IP from SQL server configuration manager
   
2. Look for current port used in SQL server configuration manager in TCP/IP node
   
3. In this scenario, Dynamic port is set to 50270. You can clear it and set TCP Port so SQL server always listens to this port. Otherwise, from SQL developer you will need to use 50270 as port number.
   
4. Download JTDS from http://sourceforge.net/projects/jtds/files/jtds/
   
5. Extract jtds and copy ntlmauth.dll from \x86\SSO or x64\SSO folder to \sqldeveloper\jdk\jre folder
   
6. Start SQL Developer and go to Tools -> Preferences -> Database -> Third Party JDBC Drivers
   
7. Click Add Entry and point to jtds-[version].jar file. The Add Entry button allows to add directory as well so be careful to the file instead of the directory it is in.
   
8. Click Ok and restart SQL Developer
   
9. Click on Add connection
   
10. You will see a new tab beside Oracle and Access.
    
11. Type in your host name and port and click retrieve database. Note, I connected to SQL Express .\sqlexpress instance, but in Host I had to enter localhost (without the instance name).
    
12. You will see a connection on the left pane as below –

 

Hello World in Oracle

Following is an example of Hello World –

   1: set serveroutput on;

   2: begin

   3: DBMS_OUTPUT.PUT_LINE('Hello World');

   4: end;

You will notice that unlike SQL Server, Oracle statements must run in an anonymous block (BEGIN/END). In addition, server output is set to on so output appears in Script Output window.

Roles and Privileges

Privileges are the rights to perform some task in the Oracle system. Roles are groups of privileges and roles.

Role: Oracle comes with predefined roles. Users can create more roles. Here are some examples of some of the predefined roles –

1. Connect – can create session
   
2. Resource – create table, type, trigger
   
3. DBA – all system privileges

Following tables hold roles and their mapping to users –

1. DBA_ROLES – All roles
   
2. DBA_ROLE_PRIVS – All roles mapped to users

Privileges: There are two kinds of privileges –

• System privileges: Create session, tables, procedures
  
• Object privileges: Select/Insert/Delete in Tables/Views etc. Following is an example of grant on hr.candidates package to user amyo. The with grant option setting also allows amyo to grant execute to other users.

   1: grant execute on hr.candidates to amyo with grant option

Stored Procedures and Packages

Oracle can have stand-alone stored procedures just like SQL Servers. In addition, Oracle has packages. Packages are similar to C++ classes where users declare and define private or public data types and methods. The syntax to define a procedure is similar to SQL Server. However, Oracle does not support following in procedures-

   1: SELECT col1, col2 from table_name

You will need to declare a cursor and select into the cursor to return data as following –

   1: PROCEDURE MY_PROCUDURE(p_cursor OUT sys_refcursor) AS 

   2: BEGIN

   3: OPEN p_cursor FOR

   4: SELECT col1, col2 FROM table_name

   5: END MY_PROCDURE;

An Oracle package has a specification and body. A package can have types, variables and procedures.

· Package with type in it –

   1: CREATE PACKAGE trans_data AS

   2: TYPE TimeRec IS RECORD (

   3: minutes SMALLINT,

   4: hours SMALLINT);

   5: TYPE TransRec IS RECORD (

   6: category VARCHAR2(10),

   7: account INT,

   8: amount REAL,

   9: time_of TimeRec);

  10: minimum_balance CONSTANT REAL := 10.00;

  11: number_processed INT;

  12: insufficient_funds EXCEPTION;

  13: END trans_data;

· Package that contains a procedure –

Spec –

   1: CREATE OR REPLACE

   2: PACKAGE CANDIDATES AS 

   3: PROCEDURE GET_ALL_CANDIDATES(p_cursor OUT SYS_REFCURSOR);

   4: END CANDIDATES;

Body –

   1: create or replace PACKAGE BODY CANDIDATES AS 

   2: PROCEDURE GET_ALL_CANDIDATES(p_cursor OUT SYS_REFCURSOR) AS 

   3: BEGIN

   4: OPEN p_cursor FOR

   5: SELECT * FROM PERSON WHERE IS_EMPLOYEE = 0;

   6: END;

   7: END CANDIDATES;

To execute the package from SQL Developer using code -

   1: declare p_cursor sys_refcursor;

   2: person_id number;

   3: full_name VARCHAR2(100 BYTE);

   4: begin

   5: candidates.get_all_candidates(p_cursor) ;

   6: LOOP 

   7: EXIT WHEN p_cursor%NOTFOUND; 

   8: FETCH p_cursor INTO person_id, full_name; 

   9: DBMS_OUTPUT.PUT_LINE(person_id || ',' || full_name); 

  10: END LOOP;

To execute a package visually –

1. Right click on package name from Connection pane
   
2. The Run PL/SQL windows shows
   
3. Select Target procedure update any parameters with value and click ok.

4.  The result will show in Output variables window

 

Oracle and SQL Server data type comparison

Please see the comparison table to see the data type mapping between the two -

http://msdn.microsoft.com/en-us/library/ms151817.aspx

Oracle Synonym

Oracle allows to create names for many types of objects including Table, View, Procedures, functions and other synonyms. Synonyms can be private or public. Private synonyms are accessible to Oracle users who own the synonym and public synonyms are accessible to all users.

   1: CREATE SYNONYM offices 

   2: FOR hr.locations;CREATE PUBLIC SYNONYM emp_table  FOR hr.employees@remote.us.oracle.com;