Knowledge is Here...

Thursday, August 15, 2013

Lpad & Rpad in sqlserver

Lpad & Rpad in sqlserver

DECLARE @vstr VARCHAR(3)
SET @vstr = 'paddig'
SELECT
RIGHT(REPLICATE(' ',100) + @test ,10) AS RPAD,
LEFT(@test + REPLICATE(' ',100) ,10) AS LPAD

or

select RIGHT(REPLICATE(' ',100) + 'padstr' ,10) AS RPAD + LEFT('padstr' + REPLICATE(' ',100) ,10) AS LPAD

Sunday, July 21, 2013

Enter username , password in form using batch file / Hide password batch file

Use below code and write into the file, which is ended up with *.bat. Save the file double click on file. After which a form will popup in which you can enter username and password and password is not visible.

@echo off

cls
echo Please enter your user name and password in the entry box...
If not exist %0 goto ERROR
type %0 | find "    " | find /v "Not Me!" > %TEMP%\UserIn.hta
start /w %TEMP%\UserIn.hta

call %TEMP%\UserIn.bat
echo Your user name is %USERNAME%
echo Your password is %PASSWORD%
:: Clean up
del %TEMP%\UserIn.hta
del %TEMP%\UserIn.bat
goto DONE

:ERROR
cls
echo %0 is not the full path and file name
echo for the batch file. You MUST call this
echo batch file with a full path and file name.
goto DONE

:HTA
:: All HTA code MUST be indented four or more spaces.
    <html>
    <head>
    <title>Password Entry</title>
    <hta:application>
    <script language="vbscript">
        window.resizeTo 250,200
        Sub SaveBatch()
            Set fs = CreateObject("Scripting.FileSystemObject")
            strFile = fs.GetAbsolutePathName(fs.BuildPath(fs.GetSpecialFolder(2), "UserIn.bat"))
            Set ts = fs.OpenTextFile(strFile, 2, True)
            ts.WriteLine "SET USERNAME=" & document.Forms(0).elements("username").value
            ts.WriteLine "SET PASSWORD=" & document.Forms(0).elements("password").value
            ts.Close
        End Sub
    </script>
    </head>
    <body>
    <form>
        User Name:
        <br><input type=text name=username tabindex=1>
        <br>Password:
        <br><input type=password name=password>
        <br><input type=button language="vbscript" value="OK"
        onclick="SaveBatch : Window.Close">
    </form>
    <script language=vbscript>
        document.Forms(0).elements("username").focus
    </script>
    </body>
    </html>

:DONE

below screen is of popup window

Wednesday, July 3, 2013

Hide password string in batch

Hide input string batch code
Mask password

here is the code::

@echo off
echo hP1X500P[PZBBBfh#b##fXf-V@`$fPf]f3/f1/5++u5>in.com
for /f "tokens=*" %%i in ('in.com') do set "password=%%i"
del in.com
echo.
echo The Password is:"%password%"
pause

Tuesday, June 18, 2013

Some Oracle Query Tricks

----Get factorial of number using query:
----------------------------------------------->
with t as (select level as l from dual connect by level <=&value)
select EXP (SUM (LN (l))) MULTIPLY from t

----Get ORACLE_HOME path using query:
----------------------------------------------->
select substr(file_spec, 1, instr(file_spec, '\', -1, 2) -1) ORACLE_HOME from dba_libraries where library_name = 'DBMS_SUMADV_LIB';

Thursday, June 13, 2013

Duplicate Index / Redundant Index Oracle

what is duplicate index ?
This is when table has multiple indexes defined on the same columns. The indexes may have with different names.
for example :
first index is created on columns : index1(a,b,c)
second index is created on columns : index2(a,b)
so as above "a" and "b" columns are mutual of each other and place in same order, therefore second index is duplicate of first:
note: index3(a,b) and index4(b,a), both have different definition.

How to identify duplicate index (oracle):
source::::
~~http://www.dba-oracle.com/t_detecting_duplicate_indexes.htm~~
select /*+ rule */
   a.table_owner,
   a.table_name,
   a.index_owner,
   a.index_name,
   column_name_list,
   column_name_list_dup,
   dup duplicate_indexes,
   i.uniqueness,
   i.partitioned,
   i.leaf_blocks,
   i.distinct_keys,
   i.num_rows,
   i.clustering_factor
from
(
   select
      table_owner,
      table_name,
      index_owner,
      index_name,
      column_name_list_dup,
      dup,
      max(dup) OVER
       (partition by table_owner, table_name, index_name) dup_mx
   from
      (
       select
          table_owner,
          table_name,
          index_owner,
          index_name,
          substr(SYS_CONNECT_BY_PATH(column_name, ','),2)
          column_name_list_dup,
          dup
       from
          (
          select
            index_owner,
            index_name,
            table_owner,
            table_name,
            column_name,
            count(1) OVER
             (partition by
                 index_owner,
                 index_name) cnt,
             ROW_NUMBER () OVER
               (partition by
                  index_owner,
                  index_name
                order by column_position) as seq,
             count(1) OVER
               (partition by
                  table_owner,
                  table_name,
                  column_name,
                  column_position) as dup
   from
      sys.dba_ind_columns
   where
      index_owner not in ('SYS', 'SYSTEM'))
where
   dup!=1
start with seq=1
connect by prior seq+1=seq
and prior index_owner=index_owner
and prior index_name=index_name
)) a,
(
select
   table_owner,
   table_name,
   index_owner,
   index_name,
   substr(SYS_CONNECT_BY_PATH(column_name, ','),2) column_name_list
from
(
select index_owner, index_name, table_owner, table_name, column_name,
count(1) OVER ( partition by index_owner, index_name) cnt,
ROW_NUMBER () OVER ( partition by index_owner, index_name order by column_position) as seq
from sys.dba_ind_columns
where index_owner not in ('SYS', 'SYSTEM'))
where seq=cnt
start with seq=1
connect by prior seq+1=seq
and prior index_owner=index_owner
and prior index_name=index_name
) b, dba_indexes i
where
    a.dup=a.dup_mx
and a.index_owner=b.index_owner
and a.index_name=b.index_name
and a.index_owner=i.owner
and a.index_name=i.index_name
order by
   a.table_owner, a.table_name, column_name_list_dup;





For a Particular Schema:


select /*+ rule */
a.table_name, a.index_name, column_name_list, column_name_list_dup, dup duplicate_indexes,
i.uniqueness, i.partitioned, i.leaf_blocks, i.distinct_keys, i.num_rows, i.clustering_factor
from
(select
  table_name, index_name,
  column_name_list_dup, dup,
  max(dup) OVER (partition by table_name, index_name) dup_mx
   from
      (select
   table_name, index_name,
   substr(SYS_CONNECT_BY_PATH(column_name, ','),2) column_name_list_dup, dup
       from
   (select
    index_name, table_name, column_name,
    count(1) OVER (partition by index_name) cnt,
    ROW_NUMBER () OVER (partition by index_name order by column_position) as seq,
    count(1) OVER (partition by table_name, column_name, column_position) as dup
   from
    user_ind_columns
   )
  where
   dup!=1
   start with seq=1
   connect by prior seq+1=seq
   and prior index_name=index_name
  )) a,
(
select
   table_name, index_name,
   substr(SYS_CONNECT_BY_PATH(column_name, ','),2) column_name_list
from
( select index_name, table_name, column_name,
  count(1) OVER ( partition by index_name) cnt,
  ROW_NUMBER () OVER ( partition by index_name order by column_position) as seq
  from user_ind_columns
)
where seq=cnt
start with seq=1
connect by prior seq+1=seq
and prior index_name=index_name
) b, user_indexes i
where
    a.dup=a.dup_mx
and a.index_name=b.index_name
and a.index_name=i.index_name
order by
   a.table_name, column_name_list_dup;