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

    Red face 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 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Did YOU try to translate it to Oracle?

    BTW, in my opinion, you'd learn more if you write your own procedure instead of translating a copyrighted one.

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

    Couldn't do it

    I couldn't do it in oracle, thats why I am trying to translate this one.
    mr_roomi

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If you need such a procedure, then I feel very sorry for you & hope that I never get near the folks who made such a procedure necessary.
    For the life of I can NOT concieve of how data could be "misplaced" to such a degree that such a procedure would be the only way to find the desired data within the DB.

    You're On Your Own (YOYO)!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Start by looking at DBA_TAB_COLUMNS or ALL_TAB_COLUMNS. It should contain all the information that you need there. Then, you don't need a procedure, just a query like

    Code:
    select *
    from all_tab_columns
    where column_name like '%BLAH%';
    There's also DBA_TABLES or ALL_TABLES if you want to search table names.

    --=Chuck

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Chuck, he wants to find all occurences of a string "chuck_forbes" in the whole database.

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    Right, I see it now. That really doesn't make any sense.

    I guess if you wanted to recreate this for yourself you could use ALL_TAB_COLUMNS. As other's have stated, you have to ask yourself "Why?" first.

    --=Chuck

Posting Permissions

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