Solved

Help with PL/SQL to T-SQL conversion.

Posted on 2004-09-24
36
1,257 Views
Last Modified: 2010-07-27
Hi Guys, I need some in SQL conversion from Oracle to SQL Server...Here is the procedure in T-SQL..When I run the below SQL in SQL Server, it is going in infinite loop. When I click stop, I am getting the error .......
"Invalid length parameter passed to the substring function."
at the following line
SELECT @RoleID_in = CONVERT(NUMERIC(8, 2), SUBSTRING(@UserRoles_in, 1, CHARINDEX(',', @UserRoles_in) - 1))
-------------
DECLARE @objid_in INT
DECLARE @objclass_in INT
DECLARE @userid_in INT
DECLARE @userRoles_in VARCHAR(3000)
DECLARE @RoleID_in INT
DECLARE @cnt INT

DECLARE csr CURSOR FOR
SELECT * FROM objectACL
OPEN csr
WHILE (0 = 0)
BEGIN --(

fetch NEXT FROM csr INTO @objid_in, @objclass_in, @userid_in, @userRoles_in
IF (@@FETCH_STATUS = -1)
BREAK
SELECT @UserRoles_in = SUBSTRING(@UserRoles_in, 2, LEN(@UserRoles_in))
WHILE (0 = 0)
BEGIN --(
SELECT @RoleID_in = CONVERT(NUMERIC(8, 2), SUBSTRING(@UserRoles_in, 1, CHARINDEX(',', @UserRoles_in) - 1))
SELECT @cnt = COUNT(*) FROM nodetable WHERE objtype = 21 AND id = @RoleId_in
IF ( @cnt = 0 )
BEGIN
INSERT INTO error_report VALUES( 'ObjectACL' , '0' , 'UserRoles refering to Non-existing Role : ' + CAST(@RoleID_in AS VARCHAR) )
END
SELECT @UserRoles_in = SUBSTRING(@UserRoles_in, LEN(@RoleID_in) + 2, LEN(@UserRoles_in))
IF ( @UserRoles_in is null )
BEGIN
BREAK
END
END --)
END --)
close csr
DEALLOCATE csr
GO
------------------

Corresponding procedure in Oracle
---------------
declare
cursor csr is select * from objectACL;

objid_in number;
objclass_in number;
userid_in number;
userRoles_in varchar2(3000);
RoleID_in number;
cnt number;

begin
open csr;
loop
fetch csr into objid_in, objclass_in, userid_in, userRoles_in;
exit when csr%notfound;

UserRoles_in := substr(UserRoles_in, 2);

loop
RoleID_in := to_number(substr(UserRoles_in, 1, instr(UserRoles_in, ',')-1));
select count(1) into cnt from nodetable where objtype=21 and id=RoleId_in;
if (cnt =0) then
insert into error_report values ('ObjectACL', '0', 'UserRoles refering to Non-existing Role : '||RoleId_in);
end if;

UserRoles_in := substr(userRoles_in, length(RoleId_in)+2);

if (userRoles_in is null) then
exit;
end if;
end loop;
end loop;
close csr;
end;
/
-------------------
0
Comment
Question by:kingno1
  • 14
  • 9
  • 8
  • +1
36 Comments
 
LVL 7

Expert Comment

by:ChrisFretwell
Comment Utility
SELECT @RoleID_in = CONVERT(NUMERIC(8, 2), SUBSTRING(@UserRoles_in, 1, CHARINDEX(',', @UserRoles_in) - 1))

If there is no match returned from the charindex, then your length would be 0-1 or -1, which is an invalid length. Thats my guess.

If it is, you can fix it easily with the abs function

SELECT @RoleID_in = CONVERT(NUMERIC(8, 2), SUBSTRING(@UserRoles_in, 1, abs(CHARINDEX(',', @UserRoles_in)) - 1))

If that doesnt fix it, I'd need to really see the data to know why its failing.
0
 
LVL 7

Expert Comment

by:ChrisFretwell
Comment Utility
Wait, ignore that solution, since it wouldnt return waht you want. Try

SELECT @RoleID_in = CONVERT(NUMERIC(8, 2), SUBSTRING(@UserRoles_in, 1, case when (CHARINDEX(',', @UserRoles_in)> 1 - 1)>0 then  CHARINDEX(',', @UserRoles_in)> 1 - 1) else len(@userRoles_in) end)
0
 

Author Comment

by:kingno1
Comment Utility
Hi, Thanks..
I get the error...
Server: Msg 170, Level 15, State 1, Line 22
Line 22: Incorrect syntax near '>'.
Server: Msg 156, Level 15, State 1, Line 36
Incorrect syntax near the keyword 'END'.
0
 
LVL 6

Expert Comment

by:mcp111
Comment Utility
SELECT @RoleID_in = CONVERT(NUMERIC(8, 2), SUBSTRING(@UserRoles_in, 1, case when (CHARINDEX(',', @UserRoles_in - 1)>0 then  CHARINDEX(',', @UserRoles_in - 1) else len(@userRoles_in) end)
0
 
LVL 7

Expert Comment

by:ChrisFretwell
Comment Utility
sorry, typo when I was cutting and pasting. hopefuly the other one works.

I dont know pl/sql to now if it uses cases, but if not and you want a quick explanation, let me know.
0
 

Author Comment

by:kingno1
Comment Utility
Hi, I used the above syntax and got the error after I remove end at the end
----------
Server: Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'then'.
0
 
LVL 7

Expert Comment

by:ChrisFretwell
Comment Utility
Let me check the brackets out.....okay, this should work

SELECT @RoleID_in = CONVERT(NUMERIC(8, 2), SUBSTRING(@UserRoles_in, 1, case when CHARINDEX(',', @UserRoles_in - 1)>0 then  CHARINDEX(',', @UserRoles_in - 1) else len(@userRoles_in) end)




0
 
LVL 7

Expert Comment

by:ChrisFretwell
Comment Utility
There was one extra bracket at the beginning.

Case statements are a bit like if then else for inline use. The format is

CASE
WHEN condition THEN value
WHEN condition THEN value
ELSE value
END

You dont need an else, but you do need the end. The problem in teh statemetn above wasnt with the end, it was the bracket before charindex that wasnt ended before the > symbol it didnt know what to do with the end or the then or anything else.
0
 

Author Comment

by:kingno1
Comment Utility
Hmm, Looks like the stament is correct but, there is some syntax error..
CHRIS, can you please remove that extra bracket and post the statement if you dont mind..
Thanks a lot again.
0
 
LVL 7

Expert Comment

by:ChrisFretwell
Comment Utility
SELECT @RoleID_in = CONVERT(NUMERIC(8, 2), SUBSTRING(@UserRoles_in, 1, case when (CHARINDEX(',', @UserRoles_in) - 1)>0 then  (CHARINDEX(',', @UserRoles_in) - 1) else len(@userRoles_in) end))


Tested in sql, all syntax errors are gone
0
 

Author Comment

by:kingno1
Comment Utility
Awesome, the error is gone..After executing my SQL, it returned the error..
------------
Server: Msg 8114, Level 16, State 5, Line 20
Error converting data type varchar to numeric.
Here is my DDL..Any pointers please?
-------------
CREATE TABLE [dbo].[OBJECTACL] (
      [OBJID] [numeric](28, 0) NOT NULL ,
      [OBJCLASS] [numeric](28, 0) NOT NULL ,
      [USERID] [numeric](28, 0) NOT NULL ,
      [USERROLES] [varchar] (2000)  NULL
) ON [PRIMARY]
GO
0
 
LVL 7

Expert Comment

by:ChrisFretwell
Comment Utility
The problem is because of data in our userroles field.

You're convert with the new case statement says to (in english)

take all characters up to the first comma (all characters if there is no comma) and convert them to a numeric value

This will work if your data supports it, for example the following values in userroles would work

1234,asefjs
1234556
1234,4565l
123.4,sedfwet

however these values would give you the error

123f,5
1234t
abvcd

Are there any rules for the format of the user roles column?
Will there always be a comma (we know there isnt or you wouldnt have had your first error)
Will the substring always return a numeric only string? Nope again.

If you need to, I can give you code to check to see if the value returned from the substring is numeric before you run the convert. What you need to do is specify what value you want returned if the substring isnt an acceptible value.

0
 

Author Comment

by:kingno1
Comment Utility
All the values are
,9010,
,9010,9011,
However I see some NULL values too. There are no alphabets though.
0
 
LVL 7

Accepted Solution

by:
ChrisFretwell earned 125 total points
Comment Utility
cant really see the null being a problem, but just in case.....let me think.....


SELECT @RoleID_in = case when @userroles_in is null then 0 else CONVERT(NUMERIC(8, 2), SUBSTRING(@UserRoles_in, 1, case when (CHARINDEX(',', @UserRoles_in) - 1)>0 then  (CHARINDEX(',', @UserRoles_in) - 1) else len(@userRoles_in) end)) end
0
 

Author Comment

by:kingno1
Comment Utility
Hmm, Same error..
I dont see any other data except that.
0
 
LVL 7

Expert Comment

by:ChrisFretwell
Comment Utility
Its data. its the only ting it can be. oh, if any of your numbers are more than can fit into numeric(8,2)?
0
 

Author Comment

by:kingno1
Comment Utility
Can I send you my OBJECTACL and NODETABLE that were involved in this procedure?
If I can, what will be the email address.
Thanks a lot again.
0
 
LVL 6

Expert Comment

by:mcp111
Comment Utility
why don't you just do a convert to int instead of numeric(8,2)
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 6

Expert Comment

by:mcp111
Comment Utility
I looked at your entire code again.
You can just declare @role_id_in as varchar(20) instead of int.
Then you don't need the convert(numeric(8,2))
0
 

Author Comment

by:kingno1
Comment Utility
Hi, I tried withconvert to  INT as CHRIS suggested and VARCHAR(20) as mcp111 suggested
Both are running but for a long time, after 7 minutes of run, I stopped the execution and it showed a lot of rows affected in the log.
Looks like it is going in a loop...
MCP, as you suggested, can you convert that that select statement without CONVERT if I declare roleid_in as varchar(20).
Thanks
0
 
LVL 6

Expert Comment

by:mcp111
Comment Utility
Yes, it would just be

SELECT @RoleID_in = SUBSTRING(@UserRoles_in, 1, case when (CHARINDEX(',', @UserRoles_in) - 1)>0 then  (CHARINDEX(',', @UserRoles_in) - 1) else len(@userRoles_in) end)
0
 

Author Comment

by:kingno1
Comment Utility
Got the same error, converting data type to numeric at the very next statement..
SELECT @cnt  =  COUNT(*) FROM  nodetable WHERE       objtype  = 21 AND id  = @RoleId_in
0
 
LVL 6

Expert Comment

by:mcp111
Comment Utility
That means you have some non-numeric data
try this
if isnumeric( @RoleId_in)
    SELECT @cnt  =  COUNT(*) FROM  nodetable WHERE      objtype  = 21 AND id  = @RoleId_in

0
 

Author Comment

by:kingno1
Comment Utility
Server: Msg 156, Level 15, State 1, Line 25
Incorrect syntax near the keyword 'SELECT'.
-----------------------------

WHILE (0 = 0)
            BEGIN --(
SELECT @RoleID_in = SUBSTRING(@UserRoles_in, 1, case when (CHARINDEX(',', @UserRoles_in) - 1)>0 then  (CHARINDEX(',', @UserRoles_in) - 1) else len(@userRoles_in) end)

                  --SELECT @RoleID_in  = CONVERT(NUMERIC(8, 2), SUBSTRING(@UserRoles_in, 1, CHARINDEX(',', @UserRoles_in) - 1))
                  
if isnumeric( @RoleId_in)
    SELECT @cnt  =  COUNT(*) FROM  nodetable WHERE      objtype  = 21 AND id  = @RoleId_in
0
 
LVL 9

Expert Comment

by:miron
Comment Utility
-- replace
if (userRoles_in is null) then exit

-- with
if ( @UserRoles_in IS NULL OR rtrim(ltrim(@UserRoles_in)) = '' ) break

-- cheers
0
 

Author Comment

by:kingno1
Comment Utility
Hi, Here is my whole procedure as of now..Can you please tell me where to replace?
---------------------
DECLARE @objid_in                               INT
DECLARE @objclass_in                              INT
DECLARE @userid_in                                INT
DECLARE @userRoles_in                             VARCHAR(3000)
DECLARE @RoleID_in                                varchar(20)
DECLARE @cnt                                      INT
      
DECLARE csr CURSOR FOR
      SELECT * FROM  objectACL
      OPEN csr
      WHILE (0 = 0)
      BEGIN --(
            
            fetch NEXT FROM csr INTO @objid_in, @objclass_in, @userid_in, @userRoles_in
            IF (@@FETCH_STATUS = -1)
            BREAK
            SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, 2, LEN(@UserRoles_in))
            WHILE (0 = 0)
            BEGIN --(
SELECT @RoleID_in = SUBSTRING(@UserRoles_in, 1, case when (CHARINDEX(',', @UserRoles_in) - 1)>0 then  (CHARINDEX(',', @UserRoles_in) - 1) else len(@userRoles_in) end)
if isnumeric( @RoleId_in)
    SELECT @cnt  =  COUNT(*) FROM  nodetable WHERE      objtype  = 21 AND id  = @RoleId_in

                  IF ( @cnt = 0 )
                  BEGIN
                        INSERT INTO  error_report VALUES( 'ObjectACL' , '0' , 'UserRoles refering to Non-existing Role : ' + CAST(@RoleID_in AS VARCHAR) )  
                  END
                     SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, LEN(@RoleID_in) + 2, LEN(@UserRoles_in))
                  IF ( @UserRoles_in is null )
                  BEGIN
                        BREAK
                  END
               END --)
      END --)
      close csr
DEALLOCATE csr
GO
0
 
LVL 9

Expert Comment

by:miron
Comment Utility
DECLARE @objid_in                               INT
DECLARE @objclass_in                              INT
DECLARE @userid_in                                INT
DECLARE @userRoles_in                             VARCHAR(3000)
DECLARE @RoleID_in                                varchar(20)
DECLARE @cnt                                      INT
     
DECLARE csr CURSOR FOR
     SELECT * FROM  objectACL
     OPEN csr
     WHILE (0 = 0)
     BEGIN --(
         
          fetch NEXT FROM csr INTO @objid_in, @objclass_in, @userid_in, @userRoles_in
          IF (@@FETCH_STATUS = -1)
          BREAK
          SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, 2, LEN(@UserRoles_in))
          WHILE (0 = 0)
          BEGIN --(
SELECT @RoleID_in = SUBSTRING(@UserRoles_in, 1, case when (CHARINDEX(',', @UserRoles_in) - 1)>0 then  (CHARINDEX(',', @UserRoles_in) - 1) else len(@userRoles_in) end)
if isnumeric( @RoleId_in)
    SELECT @cnt  =  COUNT(*) FROM  nodetable WHERE      objtype  = 21 AND id  = @RoleId_in

               IF ( @cnt = 0 )
               BEGIN
                    INSERT INTO  error_report VALUES( 'ObjectACL' , '0' , 'UserRoles refering to Non-existing Role : ' + CAST(@RoleID_in AS VARCHAR) )  
               END
                  SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, LEN(@RoleID_in) + 2, LEN(@UserRoles_in))

--- replace here ==============>>>>>               IF ( @UserRoles_in is null )  <<<<<< ============== replace here
--==============>>>>>                 BEGIN
--==============>>>>>                      BREAK
--==============>>>>>                 END
             
             END --)
     END --)
     close csr
DEALLOCATE csr
GO
0
 

Author Comment

by:kingno1
Comment Utility
Didnt help me
0
 
LVL 9

Expert Comment

by:miron
Comment Utility
-- check if this still produces  error

DECLARE @objid_in                               INT
DECLARE @objclass_in                              INT
DECLARE @userid_in                                INT
DECLARE @userRoles_in                             VARCHAR(3000)
DECLARE @RoleID_in                                varchar(20)
DECLARE @cnt                                      INT
     
DECLARE csr CURSOR FOR
     SELECT * FROM  objectACL
     OPEN csr
     WHILE (0 = 0)
     BEGIN --(
         
          fetch NEXT FROM csr INTO @objid_in, @objclass_in, @userid_in, @userRoles_in
          IF (@@FETCH_STATUS = -1)
          BREAK
          SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, 2, LEN(@UserRoles_in))
          WHILE (0 = 0)
          BEGIN --(
                    SELECT @RoleID_in = SUBSTRING(@UserRoles_in, 1, CHARINDEX(',', @UserRoles_in+',') - 1))
                   if isnumeric( @RoleId_in)
                           SELECT @cnt  =  COUNT(*) FROM  nodetable WHERE      objtype  = 21 AND id  = @RoleId_in
                   else   -- need set the @cnt to ZERO! as per oracle procedure
                           set @cnt = 0
                   IF ( @cnt = 0 )
                   BEGIN
                                INSERT INTO  error_report VALUES( 'ObjectACL' , '0' , 'UserRoles refering to Non-existing Role : ' + CAST(@RoleID_in AS VARCHAR) )  
                   END
                  END
                  SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, LEN(@RoleID_in) + 2, LEN(@UserRoles_in))
              if ( @UserRoles_in IS NULL OR rtrim(ltrim(@UserRoles_in)) = '' ) break
             END --)
     END --)
     close csr
DEALLOCATE csr
GO
0
 
LVL 9

Expert Comment

by:miron
Comment Utility
also, make cursor look more like sql server

DECLARE @objid_in                               INT
DECLARE @objclass_in                              INT
DECLARE @userid_in                                INT
DECLARE @userRoles_in                             VARCHAR(3000)
DECLARE @RoleID_in                                varchar(20)
DECLARE @cnt                                      INT
     
DECLARE csr CURSOR FOR
     SELECT * FROM  objectACL
     OPEN csr
      fetch NEXT FROM csr INTO @objid_in, @objclass_in, @userid_in, @userRoles_in    
     WHILE (@@FETCH_STATUS = 0 )
     BEGIN --(
          SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, 2, LEN(@UserRoles_in))
          WHILE (0 = 0)
          BEGIN --(
                    SELECT @RoleID_in = SUBSTRING(@UserRoles_in, 1, CHARINDEX(',', @UserRoles_in+',') - 1))
                   if isnumeric( @RoleId_in)
                           SELECT @cnt  =  COUNT(*) FROM  nodetable WHERE      objtype  = 21 AND id  = @RoleId_in
                   else   -- need set the @cnt to ZERO! as per oracle procedure
                           set @cnt = 0
                   IF ( @cnt = 0 )
                   BEGIN
                                INSERT INTO  error_report VALUES( 'ObjectACL' , '0' , 'UserRoles refering to Non-existing Role : ' + CAST(@RoleID_in AS VARCHAR) )  
                   END
                  END
                  SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, LEN(@RoleID_in) + 2, LEN(@UserRoles_in))
              if ( @UserRoles_in IS NULL OR rtrim(ltrim(@UserRoles_in)) = '' ) break
             END --)
     END --)
     close csr
DEALLOCATE csr
GO
0
 
LVL 9

Expert Comment

by:miron
Comment Utility
-- correction

DECLARE @objid_in                               INT
DECLARE @objclass_in                              INT
DECLARE @userid_in                                INT
DECLARE @userRoles_in                             VARCHAR(3000)
DECLARE @RoleID_in                                varchar(20)
DECLARE @cnt                                      INT
     
DECLARE csr CURSOR FOR
     SELECT * FROM  objectACL
     OPEN csr
      fetch NEXT FROM csr INTO @objid_in, @objclass_in, @userid_in, @userRoles_in    
     WHILE (@@FETCH_STATUS = 0 )
     BEGIN --(
          SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, 2, LEN(@UserRoles_in))
          WHILE (0 = 0)
          BEGIN --(
                    SELECT @RoleID_in = SUBSTRING(@UserRoles_in, 1, CHARINDEX(',', @UserRoles_in+',') - 1))
                   if isnumeric( @RoleId_in)
                           SELECT @cnt  =  COUNT(*) FROM  nodetable WHERE      objtype  = 21 AND id  = @RoleId_in
                   else   -- need set the @cnt to ZERO! as per oracle procedure
                           set @cnt = 0
                   IF ( @cnt = 0 )
                   BEGIN
                                INSERT INTO  error_report VALUES( 'ObjectACL' , '0' , 'UserRoles refering to Non-existing Role : ' + CAST(@RoleID_in AS VARCHAR) )  
                   END
                  END
                  SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, LEN(@RoleID_in) + 2, LEN(@UserRoles_in))
              if ( @UserRoles_in IS NULL OR rtrim(ltrim(@UserRoles_in)) = '' ) break
             END --)
            fetch NEXT FROM csr INTO @objid_in, @objclass_in, @userid_in, @userRoles_in    
     END --)
     close csr
DEALLOCATE csr
GO
0
 
LVL 9

Expert Comment

by:miron
Comment Utility
-- correction

DECLARE @objid_in                               INT
DECLARE @objclass_in                              INT
DECLARE @userid_in                                INT
DECLARE @userRoles_in                             VARCHAR(3000)
DECLARE @RoleID_in                                varchar(20)
DECLARE @cnt                                      INT
     
DECLARE csr CURSOR FOR
     SELECT * FROM  objectACL
     OPEN csr
      fetch NEXT FROM csr INTO @objid_in, @objclass_in, @userid_in, @userRoles_in    
     WHILE (@@FETCH_STATUS = 0 )
     BEGIN --(
          SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, 2, LEN(@UserRoles_in))
          WHILE (0 = 0)
          BEGIN --(
                    SELECT @RoleID_in = SUBSTRING(@UserRoles_in, 1, CHARINDEX(',', @UserRoles_in+',') - 1))
                   if isnumeric( @RoleId_in)
                           SELECT @cnt  =  COUNT(*) FROM  nodetable WHERE      objtype  = 21 AND id  = @RoleId_in
                   else   -- need set the @cnt to ZERO! as per oracle procedure
                           set @cnt = 0
                   IF ( @cnt = 0 )
                   BEGIN
                                INSERT INTO  error_report VALUES( 'ObjectACL' , '0' , 'UserRoles refering to Non-existing Role : ' + CAST(@RoleID_in AS VARCHAR) )  
                   END
                  SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, LEN(@RoleID_in) + 2, LEN(@UserRoles_in))
                  if ( @UserRoles_in IS NULL OR rtrim(ltrim(@UserRoles_in)) = '' ) break
             END --)
            fetch NEXT FROM csr INTO @objid_in, @objclass_in, @userid_in, @userRoles_in    
     END --)
     close csr
DEALLOCATE csr
GO
0
 
LVL 9

Assisted Solution

by:miron
miron earned 125 total points
Comment Utility
-- correction

DECLARE @objid_in                               INT
DECLARE @objclass_in                              INT
DECLARE @userid_in                                INT
DECLARE @userRoles_in                             VARCHAR(3000)
DECLARE @RoleID_in                                varchar(20)
DECLARE @cnt                                      INT

DECLARE csr CURSOR FOR
     SELECT * FROM  objectACL
      OPEN csr
       fetch NEXT FROM csr INTO @objid_in, @objclass_in, @userid_in, @userRoles_in
      WHILE (@@FETCH_STATUS = 0 )
      BEGIN --(
           SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, 2, LEN(@UserRoles_in))
           WHILE (0 = 0)
           BEGIN --(
                     SELECT @RoleID_in = SUBSTRING(@UserRoles_in, 1, CHARINDEX(',', @UserRoles_in+',') - 1 )
                    if (  isnumeric( @RoleId_in ) = 1 )
                            SELECT @cnt  =  COUNT(*) FROM  nodetable WHERE      objtype  = 21 AND id  = @RoleId_in
                    else   -- need set the @cnt to ZERO! as per oracle procedure
                            set @cnt = 0
                    IF ( @cnt = 0 )
                    BEGIN
                                 INSERT INTO  error_report VALUES( 'ObjectACL' , '0' , 'UserRoles refering to Non-exing Role : ' + CAST(@RoleID_in AS VARCHAR) )
                    END
                   SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, LEN(@RoleID_in) + 2, LEN(@UserRoles_in))
                   if ( @UserRoles_in IS NULL OR rtrim(ltrim(@UserRoles_in)) = '' ) break
              END --)
             fetch NEXT FROM csr INTO @objid_in, @objclass_in, @userid_in, @userRoles_in
      END --)
      close csr
 DEALLOCATE csr

0
 
LVL 9

Expert Comment

by:miron
Comment Utility
do you have any other questions
0
 

Author Comment

by:kingno1
Comment Utility
Hi, guys, I really appreciate your help and input.
Have a question..
Do I still need to put the below statement. I didnt see this in oracle procedure.

 else   -- need set the @cnt to ZERO! as per oracle procedure
                            set @cnt = 0
0
 

Author Comment

by:kingno1
Comment Utility
Never mind, I guess I need the above statements.
I will test this with some more cases just to make sure. Thanks a lot guys.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now