Sqldeveloper Startup error

---------------------------
Oracle SQL Developer
---------------------------
Unable to find a Java Virtual Machine.
To point to a location of a Java Virtual Machine, please refer to the Oracle9i JDeveloper Install Guide (jdev\install.html).
---------------------------
OK  
---------------------------

Problem:
Your machine might be 64 bit and JDK "java.bin" you are point out, that mean you have installed with 64bit JDK.

Solution:
Deinstall 64 bit JDK java and install 32 bit JDK.


How To reset the pop error by sqldeveloper?
Go to Path "\app\******\product\11.2.0\dbhome_1\sqldeveloper\sqldeveloper\bin\sqldeveloper.conf"

Go to last line and "Edit the path for SetJavaHome accordingly".

You can edit sqldeveloper.conf file if you open notepad with administrator rights only.

Search String in all tables Sql Server

Anonymous block which can search any string from all tables. This block is SQL Server specific.

declare @SearchString nvarchar(100);
SET @SearchString = 'any string want to search';
BEGIN
 IF OBJECT_ID('tempdb..#StrResults') IS NOT NULL DROP TABLE #StrResults
    CREATE TABLE #StrResults (ColumnName nvarchar(100), ColumnValue nvarchar(500))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(100), @ColumnName nvarchar(100), @SearchString2 nvarchar(100)
    SET  @TableName = ''
    SET @SearchString2 = QUOTENAME('%' + @SearchString + '%','''')

    WHILE @TableName IS NOT NULL

    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA)
      + '.' +
      QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )

            IF @ColumnName IS NOT NULL

            BEGIN
                INSERT INTO #StrResults
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 500) 
                    FROM ' + @TableName + 'WITH (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchString2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue FROM #StrResults
END

To make above Anonymous block as named procedure just replace first two lines of procedure with below lines:

CREATE PROC search_string
(
@SearchString nvarchar(100)
)
AS

Invalid length parameter passed to the substring function

Cause

This is because of negative length parameter pass to substring function.

select SUBSTRING('abc' ,1,charindex('@','abc')-1)

In above query, @ char is not find in string "abc", hence charindex return "-1" as value for length parameter.
So it cause error:
Server: Msg 536, Level 16, State 3, Line 4
Invalid length parameter passed to the substring function.

Solution:

We can use absolute "abs" function for resolution of above problem. In below examples, we are using "abs" over the charindex function so that it will always return positive length parameter to substring/left/right.

select SUBSTRING('abc @ 3' ,1,charindex('@','abc @ 3')-2)

select SUBSTRING('abc @ 3' ,1,abs(charindex('@','abc @ 3')-2))



OR

Workaround can be using reverse function:
1. first reverse the string using "reverse" function
2. then substring the string using all positive length parameter
e.x.
select  reverse(
substring(reverse('abc @ 3'), charindex('@',reverse('abc @ 3'))+2,len('abc @ 3'))
)

3. again reverse the string, to bring string in actual shape.

Universe Design tool, Unable to connect Datasource

1. Open Universe Design tool and open project efashion universe
2. I click on "detect join"
or From Webi facing below error:

Error

Database error: [Microsoft] [ODBC Driver Manager] Data source name not found and no default driver specified.
(IES 10901) (WIS 10901)

Problem:

No correct database connection defined to the universe

Solution

Go to File, click on parameters.

Create a new connection or edit existing connection pointing to the correct database.

It works!!

Universe Design Tool - Oracle jdbc connection error

Facing error while creating jdbc connection with oracle from "Universe Design Tool"

1. Open "Universe Design Tool" and create a new connection.

2. Fill correct details about connection and test it.

Error:

CS: Java Class not found in classpath : oracle.jdbc.OracleDriver

Cause : Missing JDBC driver 

Resolve:

1. Copy Oracle JDBC driver from oracle server/client installtion path and paste it somewhere on machine at safe place.
oracle installation path - "\app\******\product\11.2.0\dbhome_1\jdbc\lib"
or
Download the ojdbc driver from internet

It is good practice to paste the jdbc driver in C drive "C:\Users\******"

2. Now move to the installation directory of BO
"SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\"

3. Edit the file "oracle.sbo", open with notepad++.

Edit tab classpath, add new line highligheted in yellow color, which is pointing to the ojdbc driver (place in c drive as above).

4. Restart "universe design tool"

5. Try to create same connection again.