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

SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
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 /* + RULE */ 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;


by :- http://waseemslearning.blogspot.in/2011/03/check-freeused-space-per-tablespace.html

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