SSMA: Converted stored procedure using oracle function EMPTY_BLOB()

Question

Hi, I have just migrated Oracle database to SQL Server 2008R2 database using SSMA5.2. I need expert help on the following stored procedure which was converted from orcle to SQL Server. Can somebody please suggest me if the sp in sql server will work fine? I am not sure of Empty_blob() and the syntax of INSERT statement in the following SQL Server Script.

Appreciate any inputs/suggestions on this query.

Oracle Script:

CREATE OR replace PROCEDURE "Sp_add_ctmfg_mfr_document" (
p_record_id        OUT INTEGER,
p_mfr_id           IN INTEGER,
p_document_name    IN VARCHAR2,
p_document_comment IN VARCHAR2,
p_last_updated_by  IN VARCHAR2)
AS
BEGIN
  INSERT INTO contract_mfg.ctmfg_mfr_document
              (mfr_id,
               document_name,
               document_comment,
               last_updated_by,
               document_content)
  VALUES      ( p_mfr_id,
               p_document_name,
               p_document_comment,
               p_last_updated_by,
               Empty_blob())
  RETURNING record_id INTO p_record_id;
END; 

_________________________________

Converted SQL Server Script:

  CREATE PROCEDURE [dbo].[Sp_add_ctmfg_mfr_document] @P_RECORD_ID        INT OUTPUT,
                                                   @P_MFR_ID           INT,
                                                   @P_DOCUMENT_NAME    VARCHAR(MAX),
                                                   @P_DOCUMENT_COMMENT VARCHAR(MAX),
                                                   @P_LAST_UPDATED_BY  VARCHAR(MAX)
AS
  /*Generated by SQL Server Migration Assistant for Oracle version 5.2.1259.*/
  BEGIN
      SET @P_RECORD_ID = NULL

      DECLARE @temp_table TABLE (
        p_record_id INT )

      INSERT dbo.ctmfg_mfr_document
             (mfr_id,
              document_name,
              document_comment,
              last_updated_by,
              document_content)
      OUTPUT inserted.record_id
      INTO @temp_table(p_record_id)
      VALUES ( @P_MFR_ID,
               @P_DOCUMENT_NAME,
               @P_DOCUMENT_COMMENT,
               @P_LAST_UPDATED_BY,
               ( NULL ))

      SELECT @P_RECORD_ID = NULL

      SELECT @P_RECORD_ID = p_record_id
      FROM   @temp_table
  END 

Answer 1

Try this -

CREATE PROCEDURE [dbo].[Sp_add_ctmfg_mfr_document] @P_RECORD_ID        INT OUTPUT, 
                                                   @P_MFR_ID           INT, 
                                                   @P_DOCUMENT_NAME    VARCHAR(MAX), 
                                                   @P_DOCUMENT_COMMENT VARCHAR(MAX), 
                                                   @P_LAST_UPDATED_BY  VARCHAR(MAX) 
AS 
  /*Generated by SQL Server Migration Assistant for Oracle version 5.2.1259.*/ 
  BEGIN 
      SET @P_RECORD_ID = NULL 

   

      INSERT dbo.ctmfg_mfr_document 
             (mfr_id, 
              document_name, 
              document_comment, 
              last_updated_by, 
              document_content) 
      VALUES ( @P_MFR_ID, 
               @P_DOCUMENT_NAME, 
               @P_DOCUMENT_COMMENT, 
               @P_LAST_UPDATED_BY, 
               NULL ) 

      SELECT @P_RECORD_ID = SCOPE_IDENTITY() 

  END 

---

http://uk.linkedin.com/in/ramjaddu

Answer 2

Hi,

Validated the code faced following error:

Msg 515, Level 16, State 2, Procedure InsteadOfInsertOn$CTMFG_MFR_DOCUMENT, Line 64

Cannot insert the value NULL into column 'RECORD_ID', table 'CONTRACT_MFG.dbo.CTMFG_MFR_DOCUMENT'; column does not allow nulls. INSERT fails.

The statement has been terminated.

I also want to mention here is that the SQL server script mentioned by me was running fine but the only problem was the P_RECORD_ID column value, this was not getting loaded the inserted value in sp.May be the OUTPUT clause.

Answer 3

Hi rajan,

For Empty_blob() in oracle, when it is migrated to SQL Server, MS SQL Server will convert it to NULL type.

There is no concept of EMPTY_BLOB in SQL Server. You can default the same to NULL or as '' when using the same. According to the script you have, we can see that:

Oracle:  p_last_updated_by,
               Empty_blob())

SQL Server: @P_LAST_UPDATED_BY,
               ( NULL )

I can execute the SQL Server script successfully in my SQL Server 2008 R2 machine.

Did you get any problem when after migrate from oracle? If yes, please post or describe it.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 4

Thanks for help.

It worked.