Converting Oracle Stored Procedure to SQL Server Procedure

Question

I am have created a stored procedure in oracle. Now I need to convert the same procedure to SQL Server procedure. Since I am not good at SQL procedure, please help me.

Following is the Oracle Procedure:

    create or replace
PROCEDURE "FTSREMOVESPECIALCHAR"
  AS strquery     VARCHAR2(4000 byte); 
  stmt         VARCHAR2(1000); 
  l_cursor     SYS_REFCURSOR; 
  TYPE result_rec IS RECORD (iid NUMBER(10, 0), 
    fulltextdetails VARCHAR2(4000 byte), 
    regex VARCHAR2(4000 byte)); 
  l_result_rec RESULT_REC; 
  idValue NUMBER(10, 0);
  fulltextdetailsValue VARCHAR2(4000 byte);
  fulltextWithoutSplChr VARCHAR2(4000 byte);
  regexValue VARCHAR2(4000 byte);
  minmatchValue NUMBER(10, 0);
  strQueryinsert  VARCHAR2(4000 byte);
BEGIN
     dbms_output.ENABLE(1000000);
    FOR c IN (SELECT table_name 
              FROM   user_tables 
              WHERE  table_name LIKE 'FULLTEXTLOOKTABLE_%')            
    LOOP 
        dbms_output.Put_line(c.table_name); 
        strquery := 'select ID, FullTextDetails, Regex  from ' || c.table_name; 
        BEGIN 
            OPEN l_cursor FOR strquery; 
            LOOP 
                FETCH l_cursor INTO l_result_rec;
                Exit when l_cursor%NOTFOUND;
                fulltextdetailsValue := l_result_rec.fulltextdetails;
                regexValue := l_result_rec.regex;

                dbms_output.Put_line('Before :' ||fulltextdetailsValue);
                fulltextdetailsValue := regexp_replace(fulltextdetailsValue, '[^[:alnum:] ]', NULL);
                dbms_output.Put_line('After : '||fulltextdetailsValue);
                dbms_output.Put_line('Before regexValue:' ||regexValue);
                regexValue := replace(regexValue, '([\~\-])', '([\~\-])?');
                regexValue := replace(regexValue, '(\!)', '(\!)?');
                regexValue := replace(regexValue, '([\@])', '([\@])?');
                regexValue := replace(regexValue, '(\#)', '(\#)?');
                regexValue := replace(regexValue, '([\$s\&amp;])', '([\$s\&amp;])?');
                regexValue := replace(regexValue, '(\%)', '(\%)?');
                regexValue := replace(regexValue, '(\^)', '(\^)?');
                regexValue := replace(regexValue, Q'[']',Q'['']');

                strQueryinsert := 'update '||c.table_name||' set fulltextdetails='''||fulltextdetailsValue||''' where id='||l_result_rec.iid;
                dbms_output.Put_line('strQueryinsert : ' ||strQueryinsert);
                EXECUTE IMMEDIATE
                strQueryinsert;

                strQueryinsert := 'update '||c.table_name||' set regex='''||regexValue||''' where id='||l_result_rec.iid;
                EXECUTE IMMEDIATE
                strQueryinsert;               
            END LOOP;
            EXECUTE IMMEDIATE
            'commit';
            close l_cursor;
        END; 
    END LOOP; 
END;

This procedure will get the all tables from the DB that starts with "FULLTEXTLOOKTABLE_". The table has 4 columns(ID(int), FullTextDetails(nvarchar), Regex(nvarchar), MinMatchCount(int)). For each table it will take the value of "FullTextDetails" column and remove all the special characters and also take the

"Regex" and replace
([\~\-]) with ([\~\-])?
(\!) with (\!)?
([\@]) with ([\@])?
(\#) with (\#)?
([\$s\&amp;]) with ([\$s\&amp;])?
(\%) with (\%)?
(\^) with (\^)?

And update the columns "FullTextDetails" and "Regex" with the new values. Finally the changes are committed.

score 1 · Answer 1 · answered Jul 30 '18 at 06:17

The most difficult thing here is how to handle the regex replacement as in T-SQL there is not such build in function. Fortunately, you can implement such as I am showing here using SQL CLR integration - basically, you are allowed to write .net objects, to mapped them to SQL objects and use them. You need to do some effort but that's your only chance.

Then, the other things are pretty easy. From this system object you can get and loop through the table names:

SELECT *
FROM [sys].[tables];

You can use cursor but I prefer to skip them. It will be something like this:

DECLARE @Tables TABLE
(
    [name] SYSNAME
);

DECLARE @CurrentTableName SYSNAME;

INSERT INTO @Tables ([name])
SELECT [name]
FROM [sys].[tables]
--WHERE [name] LIKE 'FULLTEXTLOOKTABLE_%';

WHILE EXISTS(SELECT 1 FROM @Tables)
BEGIN;

    SELECT TOP 1 @CurrentTableName = [name]
    FROM @Tables;

    SELECT @CurrentTableName;

    DELETE FROM @Tables
    WHERE [name] = @CurrentTableName;

END;

For the building and executing of the dynamic T-SQL statement you have two options. You can build the whole statement and execute it for each loop like this:

DECLARE @DynamicTSQLStatement NVARCHAR(MAX);

SET @DynamicTSQLStatement = N'UPDATE [dbo].[table] SET [text] = ''test'' WHERE [ID] = 1';

EXEC sp_executesql @DynamicTSQLStatement;

or you can use the sp_executesql with parameters and create a template which is populated with the current values.

I hope this points will be enough to handle the task.

Converting Oracle Stored Procedure to SQL Server Procedure

Following is the Oracle Procedure:

1 Answers1