Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    84

    Unanswered: help with SQL procedure conversion

    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;
    /
    -------------------
    Kishore

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Dear Lord, PSQL is a sucky language. That is pretty near unreadable.

    Do yourself a favor and don't even try to convert this into TSQL directly. Oracle developers love cursors, but set-based operations are almost always easier to debug and run faster. I'd better dollars to doughnut holes you don't even need a cursor for this.

    Post your table layout and a description of what you are trying to do.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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