Thursday, February 28, 2013

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

Wednesday, February 27, 2013

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 ;

Friday, February 22, 2013

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;

Friday, February 15, 2013

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;
/
web stats