Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Unanswered: Translation in Oracle Code

    Hi There,

    I have a stored procedure which is written in SQL Server and I want to translate it in Oracle. This stored procedure find a value in whole database, all the tables and columns.
    Any help will be highly appreciated.


    CREATE PROC SearchAllTables
    (
    @SearchStr nvarchar(100)
    )
    AS
    BEGIN

    -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
    -- Purpose: To search all columns of all tables for a given search string
    -- Written by: Narayana Vyas Kondreddi
    -- Site: http://vyaskn.tripod.com
    -- Tested on: SQL Server 7.0 and SQL Server 2000
    -- Date modified: 28th July 2002 22:50 GMT


    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    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')
    AND QUOTENAME(COLUMN_NAME) > @ColumnName
    )

    IF @ColumnName IS NOT NULL
    BEGIN
    INSERT INTO #Results
    EXEC
    (
    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
    FROM ' + @TableName + ' (NOLOCK) ' +
    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
    )
    END
    END
    END

    SELECT ColumnName, ColumnValue FROM #Results
    END


    Thanks,
    mr_roomi

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Do you actually understand any of the code?
    How proficient are you in Oracle?
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    he wrote his own full text indexing thingy when something is already built in. That hilarious. I bet this runs like molasses uphill on a cold February morning on any database of significant size.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    He didn't write it - he stole it from someone else...
    EDIT: Ignore that, I know what you're getting at now
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I have a similar script which runs pretty quickly. He's not trying to do full-text indexing. He just wants a way to search for a value in the database.
    Roomi, you won't be able to lift and drop this code or logic into Oracle. Your best bet is to post on the Oracle forum to see if someone already has a tool or script to do this.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    About Translation

    OK, let me try on Oracle forum...thanks for posts.
    mr_roomi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •