Oracle Statement

Working with BLOB type UTL_RAW package is important.

===============================================
CREATE TABLE Data(Data BLOB);
INSERT INTO Data VALUES (rawtohex('hello world'));
SELECT UTL_RAW.CAST_TO_VARCHAR2(Data) FROM Data;
===============================================
Important DateCalculation Function

CREATE OR REPLACE FUNCTION DateDiff( p_Interval IN CHAR, p_startDateIn IN TIMESTAMP, p_disconDateIn IN TIMESTAMP ) RETURN NUMBERAS
v_duration INTERVAL DAY TO SECOND; v_myDay NUMBER; v_myHour NUMBER; v_myMinute NUMBER; v_mySecond NUMBER; v_totalSeconds NUMBER; v_Value NUMBER;BEGIN
v_duration := (p_disconDateIn - p_startDateIn);
v_myDay := TRUNC(to_number(extract(DAY FROM v_duration))*86400); v_myHour := to_number(extract(HOUR FROM v_duration))*3600; v_myMinute := to_number(extract(MINUTE FROM v_duration))*60; v_mySecond := to_number(extract(SECOND FROM v_duration));
v_totalSeconds := v_myDay + v_myHour + v_myMinute + v_mySecond ;
IF UPPER(p_Interval) = 'MS' THEN v_Value := v_totalSeconds * 1000;
ELSIF UPPER(p_Interval) = 'S' THEN v_Value := v_totalSeconds;
ELSIF UPPER(p_Interval) = 'M' THEN
v_Value := ROUND(v_totalSeconds / 60);
ELSIF UPPER(p_Interval) = 'H' THEN v_Value := ROUND(v_totalSeconds / 3600);
ELSIF UPPER(p_Interval) = 'D' THEN
v_Value := TRUNC(v_totalSeconds / 86400);
ELSE RAISE_APPLICATION_ERROR(-20001, 'Invalid parameter value p_Interval');
END IF;
RETURN v_Value;
END;

CREATE OR REPLACE FUNCTION ca_fn_GetUTCDateRETURN TIMESTAMPAS v_UTCDateTime TIMESTAMP;BEGIN SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) INTO v_UTCDateTime from dual;
RETURN v_UTCDateTime;END;/
CREATE OR REPLACE FUNCTION ca_fn_NEWIDRETURN VARCHAR2AS v_GUID VARCHAR2(50) := ''; v_GUID1 VARCHAR2(50) := ''; BEGIN SELECT SYS_GUID INTO v_GUID1 FROM dual; v_GUID := SUBSTR(v_GUID1, 0, 8) '-' SUBSTR(v_GUID1, 8, 4) '-' SUBSTR(v_GUID1, 12, 4) '-' SUBSTR(v_GUID1, 16, 4) SUBSTR(v_GUID1, 20, 4) SUBSTR(v_GUID1, 24);
RETURN v_GUID;
END;

=================================================================
Various Syntax
-----------------
CREATE DATABASE LINK EventLink CONNECT TO cic IDENTIFIED BY cic using 'caEvents'

CREATE OR REPLACE TRIGGER tr_Delete_DvrConfigBEFORE DELETE ON DvrConfigFOR EACH ROW
DECLARE
v_Old xmltype; v_TableName nvarchar2(50) := 'DvrConfig'; v_OperatorName nVarChar2(50); v_StationName nVarChar2(50); v_Description nvarchar2(100) := 'DvrConfig ID ' TO_CHAR(:OLD.DvrID);
BEGIN SELECT XMLELEMENT("DvrConfig", XMLATTRIBUTES(:OLD.DvrID AS DvrID, :OLD.ServerName AS ServerName, :OLD.ServerIP AS ServerIP, :OLD.UserName AS UserName, :OLD.Password AS Password, :OLD.UserType AS UserType, :OLD.Active AS Active, :OLD.EnableNotification AS EnableNotification, :OLD.DVRVendor AS DVRVendor, :OLD.caObjectID AS caObjectID, :OLD.LastUpdated AS LastUpdated, :OLD.LastOperator AS LastOperator, :OLD.LastWorkStation AS LastWorkStation)) INTO v_Old FROM DUAL;
ca_sp_OperatorWorkStation(:OLD.LastWorkStation, :OLD.LastOperator, v_OperatorName, v_StationName);
INSERT INTO DBAudit(Tablename, Description, Actions, RevisionStamp, OldData, OperatorName,StationName) VALUES(v_TableName, v_Description, 'D', ca_fn_GETUTCDATE, v_Old, v_OperatorName, v_StationName);
END;
==================================================================

CREATE ROLE UMR_Role_Admin;
GRANT CONNECT, RESOURCE, CREATE USER, DROP USER, ALTER USER, GRANT ANY ROLE TO UMR_Role_Admin;
GRANT EXECUTE ANY PROCEDURE, SELECT ANY TABLE, SELECT ANY DICTIONARY TO UMR_Role_Admin;



CREATE USER proxyuser IDENTIFIED BY proxyuser;
GRANT CONNECT TO proxyuser;

grant select,insert,update,delete on system.umr_patient to public


exp userid=BockDBA/Password file='USER100.dat' owner=USER100 grants=N indexes=Y compress=Y rows=Y
imp userid=BockDBA/password file='USER100.dat' fromuser=USER100 touser=USER101 rows=y indexes=y

Based on the table of keywords, interpret the example import command given below:
imp userid=SCOTT/TIGER ignore=Y tables=(expenses, advances) full=N


CREATE OR REPLACE PUBLIC SYNONYM sp_PatientGet FOR system.sp_PatientGet;


CREATE OR REPLACE PROCEDURE sp_UserAdd( p_UserID IN nchar, p_FirstName IN nvarchar2, p_LastName IN nvarchar2, p_Visibility IN number, p_UserGroup IN number, p_Email IN nvarchar2, p_Phone IN nvarchar2, p_Password IN varchar2 )AS v_sql varchar2(2000) :=''; v_Count int :=0; v_ApplicationUserException EXCEPTION; v_DatabaseUserException EXCEPTION; v_PermissionException EXCEPTION;
BEGIN
SELECT COUNT(*) INTO v_Count FROM sys.DBA_Role_Privs R WHERE R.Grantee = (SELECT UPPER(USER) FROM DUAL WHERE UPPER(R.Granted_Role) = 'UMR_ROLE_ADMIN');
IF (v_Count <=0) THEN RAISE v_PermissionException; END IF;
SELECT COUNT(*) INTO v_Count FROM UMR_Users U WHERE UPPER(U.User_ID) = UPPER(p_UserID);
IF (v_Count > 0) THEN RAISE v_ApplicationUserException; END IF;
SELECT COUNT(*) INTO v_Count FROM sys.DBA_Users U WHERE UPPER(U.USERNAME) = UPPER(p_UserID);
IF (v_Count > 0) THEN RAISE v_DatabaseUserException; END IF;
v_sql := 'CREATE USER ' p_UserID ' IDENTIFIED BY ' p_Password;
EXECUTE IMMEDIATE v_sql;
v_Sql := 'ALTER USER ' p_UserID ' GRANT CONNECT THROUGH proxyuser' ; EXECUTE IMMEDIATE v_Sql;
IF (p_UserGroup >= 10) THEN v_Sql := 'GRANT UMR_Role_Admin TO ' p_UserID; ELSE v_Sql := 'GRANT UMR_Role_Operator TO ' p_UserID; END IF;
EXECUTE IMMEDIATE v_Sql;
INSERT INTO UMR_Users ( User_ID, First_Name, Last_Name, Visibility, User_Group, Created_On, Email, Phone ) VALUES ( p_UserID, p_FirstName, p_LastName, p_Visibility, p_UserGroup, CURRENT_TIMESTAMP, p_Email, p_Phone );
EXCEPTION WHEN v_PermissionException THEN RAISE_APPLICATION_ERROR(-20010, '');
WHEN v_ApplicationUserException THEN RAISE_APPLICATION_ERROR(-20001, '');
WHEN v_DatabaseUserException THEN RAISE_APPLICATION_ERROR(-20002, '');
END

==================================================================

No comments: