INITCAP Function in SQL Server

INITCAP Function in SQL Server

Create a user-defined function for SQL Server 2005:

CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) ) 
RETURNS VARCHAR(4000)
AS
BEGIN

DECLARE @Index          INT
DECLARE @Char           CHAR(1)
DECLARE @PrevChar       CHAR(1)
DECLARE @OutputString   VARCHAR(255)

SET @OutputString = LOWER(@InputString)
SET @Index = 1

WHILE @Index <= LEN(@InputString)
BEGIN
    SET @Char     = SUBSTRING(@InputString, @Index, 1)
    SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                         ELSE SUBSTRING(@InputString, @Index - 1, 1)
                    END

    IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
    BEGIN
        IF @PrevChar != '''' OR UPPER(@Char) != 'S'
            SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
    END

    SET @Index = @Index + 1
END

RETURN @OutputString

END
GO

Used/Free space in a tablespace Oracle

SELECT 

df.tablespace_name "Tablespace",

df.bytes / (1024 * 1024) "Size (in MB)",

SUM(fs.bytes) / (1024 * 1024) "Free (in MB)",

Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free Space",

Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used Space"

FROM 

dba_free_space fs,

(

SELECT 

tablespace_name

,SUM(bytes) bytes

FROM 

dba_data_files

GROUP BY 

tablespace_name

) df

WHERE 

fs.tablespace_name (+) = df.tablespace_name

GROUP BY 

df.tablespace_name,df.bytes

UNION ALL

SELECT 

df.tablespace_name tspace,

fs.bytes / (1024 * 1024),

SUM(df.bytes_free) / (1024 * 1024),

Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),

Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)

FROM

dba_temp_files fs,

(

SELECT 

tablespace_name

,bytes_free,bytes_used

FROM 

v$temp_space_header

GROUP BY tablespace_name,bytes_free,bytes_used

) df

WHERE 

fs.tablespace_name (+) = df.tablespace_name

GROUP BY 

df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used

ORDER BY 4 DESC;

Query 2 

col "Tablespace" for a22

col "Used MB" for 99,999,999

col "Free MB" for 99,999,999

col "Total MB" for 99,999,999

select 

df.tablespace_name "Tablespace",

totalusedspace "Used MB",

(df.totalspace - tu.totalusedspace) "Free MB",

df.totalspace "Total MB",

round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))

"Pct. Free"

from

(

select 

tablespace_name,

round(sum(bytes) / 1048576) TotalSpace

from 

dba_data_files 

group by 

tablespace_name

) df,

(select 

round(sum(bytes)/(1024*1024)) totalusedspace

, tablespace_name

from 

dba_segments 

group by 

tablespace_name

) tu

where 

df.tablespace_name = tu.tablespace_name ;

schema / user Logon logoff track oracle

We design a trigger that can be used for single table to record both logon and logoff events in ORACLE. This table will contain distinct username , session ID , Host name , including Log ON/OFF time. Trigger will check the existence for user, if user already exists in table then it will update only its Log ON/OFF time depending upon the happening.


CREATE TABLE STATS_USER
(
USER_ID VARCHAR2(30),
SESSION_ID NUMBER(8),
HOST VARCHAR2(30),
LOGON_TIME TIMESTAMP,
LOGOFF_TIME TIMESTAMP
);


CREATE OR REPLACE TRIGGER logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
DECLARE
user_v VARCHAR2(20) := USER;
count_v NUMBER(2);
BEGIN
SELECT COUNT(1) INTO count_v FROM stats_user WHERE user_id = user_v;
IF count_v = 1 THEN
UPDATE stats_user SET LOGOFF_TIME = SYSDATE WHERE user_id= user_v;
ELSE
INSERT INTO stats_user VALUES(
USER,
SYS_CONTEXT('USERENV','SESSIONID'),
SYS_CONTEXT('USERENV','HOST'),
NULL,
SYSDATE
);
END IF;
END;
/


CREATE OR REPLACE TRIGGER logon_audit_trigger
BEFORE LOGOFF ON DATABASE
DECLARE
user_v VARCHAR2(20) := USER;
count_v NUMBER(2);
BEGIN
SELECT COUNT(1) INTO count_v FROM stats_user WHERE user_id = user_v;
IF count_v = 1 THEN
UPDATE stats_user SET LOGON_time = SYSDATE WHERE user_id= user_v;
ELSE
INSERT INTO stats_user VALUES(
USER,
SYS_CONTEXT('USERENV','SESSIONID'),
SYS_CONTEXT('USERENV','HOST'),
SYSDATE,
NULL
);
END IF;
END;
/



This also help to track the user i.e. ever logged on in database, and you can delete the extra users in database. 
SQL> select username from all_users where user_id >100 and username !='XS$NULL'
2 minus
3 select user_id from stats_user;

Upload file to BLOB type column/field

CREATE TABLE t (id NUMBER(10),doc BLOB );


CREATE SEQUENCE t_seq;


CREATE OR REPLACE DIRECTORY documents AS 'd:\app';

Create procedure:
CREATE OR REPLACE PROCEDURE upload_t (file_name_v IN varchar) AS
v_bfile BFILE;
v_blob BLOB;
BEGIN
INSERT INTO t (id, doc) vALUES (t_seq.NEXTVAL, empty_blob()) RETURN doc INTO v_blob;
v_bfile := BFILENAME('DOCUMENTS', file_name_v);
Dbms_Lob.Fileopen(v_bfile, Dbms_Lob.File_Readonly);
Dbms_Lob.Loadfromfile(v_blob, v_bfile, Dbms_Lob.Getlength(v_bfile));
Dbms_Lob.Fileclose(v_bfile);

COMMIT;
end;
/

SQL> EXEC upload_t('dp.log');

--PL/SQL procedure successfully completed.



If You want to update the column data, go with below procedure:
CREATE OR REPLACE PROCEDURE update_t (file_name_v IN varchar) AS
  v_bfile  BFILE;
  v_blob   BLOB;
BEGIN
  SELECT DOC INTO v_blob FROM t WHERE  id = 3 FOR UPDATE;

  v_bfile := BFILENAME('DOCUMENTS',file_name_v );
  Dbms_Lob.Fileopen(v_bfile, Dbms_Lob.File_Readonly);
  Dbms_Lob.Loadfromfile(v_blob, v_bfile, Dbms_Lob.Getlength(v_bfile));
  Dbms_Lob.Fileclose(v_bfile);
END;
/