Comparison of PL/SQL and T-SQL commands

OracleSqlServerCheat.md

# toc

• privs
• users
• scheduling
• meta data

##Privs ###Set privs

Standard SQL:

GRANT SELECT|UPDATE|INSERT ON schema.object TO user/role

to allow the grantee to create views based off an object you need to also add with grant option which only works for a specific user (not roles).

###Remove privs

Standard SQL:

REVOKE SELECT|UPDATE|INSERT ON schema.object FROM user

###View privs

####ora

SELECT * FROM SYS.table_privileges;

###sql

slightly more complicated answer...

source: http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions.aspx

USE master;
SELECT dp.NAME AS principal_name
	,dp.type_desc AS principal_type_desc
	,o.NAME AS object_name
	,p.permission_name
	,p.state_desc AS permission_state_desc
FROM sys.database_permissions p
	LEFT OUTER JOIN sys.all_objects o ON p.major_id = o.OBJECT_ID
	INNER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id

###Further reading

• https://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=222
• http://www.dba-oracle.com/t_convent_sql_server_tsql_oracle_plsql.htm

top

##Job scheduling

###Oracle

• dbms_jobs is deprecated, but you'll keep finding it all over the place. jog interval and date arithmetic is a pita to get right. so use this page as an aid: http://dbataj.blogspot.co.uk/2007/04/dbmsjob.html

####Evaluating dbms_job interval strings

SET SERVEROUTPUT ON;

DECLARE   
  V_DATE_STRING VARCHAR2(200);
  V_DATE_VALUE VARCHAR2(200);

  
BEGIN
  -- this could easily be a table i.e. select bar into V_DATE_STRING from foo where fizz=buzz;
  V_DATE_STRING := 'TRUNC(SYSDATE-1)'; 
    
  EXECUTE IMMEDIATE 'SELECT ' || V_DATE_STRING || ' FROM DUAL' 
  INTO V_DATE_VALUE;
  
    
  DBMS_OUTPUT.PUT_LINE('date string: ' || V_DATE_STRING || ' actual value: ' || V_DATE_VALUE);
END;  

####interval

SELECT TO_CHAR(SYSDATE, 'HH24:Mi:SS') "nao", TO_CHAR(SYSDATE + INTERVAL '10' SECOND , 'HH24:MI:SS') "future", 'seconds' "period" FROM DUAL
UNION ALL
SELECT TO_CHAR(SYSDATE, 'HH24:Mi:SS') "nao", TO_CHAR(SYSDATE + INTERVAL '5' MINUTE, 'HH24:MI:SS') "future", 'minutes' "period" FROM DUAL
UNION ALL
SELECT TO_CHAR(SYSDATE, 'HH24:Mi:SS') "nao", TO_CHAR(SYSDATE + INTERVAL '1' HOUR, 'HH24:MI:SS') "future", 'hours' "period" FROM DUAL
UNION ALL
SELECT TO_CHAR(SYSDATE, 'DD-MON-YY HH24:Mi:SS') "nao", TO_CHAR(SYSDATE + INTERVAL '1' DAY, 'DD-MON-YY HH24:MI:SS') "future", 'day' "period" FROM DUAL
UNION ALL
SELECT TO_CHAR(SYSDATE, 'DD-MON-YY HH24:Mi:SS') "nao", TO_CHAR(SYSDATE + INTERVAL '1' MONTH, 'DD-MON-YY HH24:MI:SS') "future", 'month' "period" FROM DUAL
UNION ALL
SELECT TO_CHAR(SYSDATE, 'DD-MON-YY HH24:Mi:SS') "nao", TO_CHAR(SYSDATE + INTERVAL '1' YEAR, 'DD-MON-YY HH24:MI:SS') "future", 'year' "period" FROM DUAL;

###SQL Server

-- view all jobs enabled
exec msdb.dbo.sp_help_job @enabled = 1

-- view steps in a specific job
exec msdb.dbo.sp_help_job @job_name = N'world domination', @job_aspect = N'ALL'

###Further reading

• http://msdn.microsoft.com/en-us/library/ms187763(v=sql.105).aspx
• http://stackoverflow.com/questions/4152111/dbms-job-vs-dbms-scheduler#4152120
• http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_job.htm
• http://docs.oracle.com/cd/B19306_01/server.102/b14231/jobtosched.htm#ADMIN037
• http://www.orafaq.com/wiki/DBMS_JOB
• http://www.orafaq.com/wiki/DBMS_SCHEDULER
• http://www.orafaq.com/wiki/DBMS_JOB_complex_scheduling

top

##Users

###Deletion or modification where profile has to be changed

####Oracle

drop user FOO cascade;

###Creation or modification where profile has to be changed

if this is a brand new user, then you'll need to create a new role and assign privs (you are using roles right?). also don't enable a password unless you hate yourself. as sys:

create role SPECIAL_ROLE not identified;
grant create synonym to SPECIAL_ROLE;

as sys (asumes that table spaces and profiles already exist):

create user FOO profile "service_account" identified by SEKRItPASSWORD
  default tablespace FOO_DATA
  temporary tablespace temp
  quota unlimited on "FOO_DATA" 
  quota unlimited on "FOO_IND"     
  account unlock;

grant create session to FOO;

grant SPECIAL_ROLE to FOO;

finally (re)create grants or synonyms as the new user. top

##meta data

###oracle

desc TABLE|VIEW; -- returns column names and details
desc PACKAGE; -- returns sprocs and their parameters
desc SPROC; -- returns their parameters

###sql

sp_help 'TABLE|VIEW|SPROC' -- gives you multiple rowsets of data including column info

sp_helptext 'VIEW|SPROC' -- gives you DDL

top