Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2005
    Posts
    5

    Unanswered: Help me in converting Oracle procedure to SQL Server

    Hi All

    Can any one help me in converting this from WHICH IN ORACLE to MS SQL SERVER


    sqlplus -s $UserId/$PassWord@$DataBase <<EOSQL> $LogFile (This is the Connection String)

    set serveroutput on
    Declare
    tempCnt Number:=0;
    totDelCnt Number:=0;
    Begin
    Loop
    $DelStmt
    tempCnt := tempCnt+ SQL%ROWCOUNT ;
    totDelcnt := totDelCnt+ SQL%ROWCOUNT ;

    If SQL%NOTFOUND Then
    Exit;
    End if;

    If tempCnt >= 50000 Then
    Commit ;
    tempCnt:=0 ;
    End if ;

    End Loop;
    Commit ;
    dbms_output.put_line('No of Recs Deleted From $TableName: '|| totDelcnt);
    End;
    /
    exit
    EOSQL

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,595
    Provided Answers: 1
    What is the sql for your delete statement, and can you briefly explain what the code is supposed to do?
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Oct 2005
    Posts
    5
    Thanks

    We started moving all the procedures we have ( in oracle) to SQL Server

    So this is the first one.

    Other Procedures we have the rest of the required statements

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,595
    Provided Answers: 1
    Uhmm.....okay.

    You're welcome?

    Now what?
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by oraj
    Hi All

    Can any one help me in converting this from WHICH IN ORACLE to MS SQL SERVER


    sqlplus -s $UserId/$PassWord@$DataBase <<EOSQL> $LogFile (This is the Connection String)

    set serveroutput on
    Declare
    declare
    tempCnt Number:=0;
    @tempCnt numeric
    set @tempCnt = 0
    totDelCnt Number:=0;
    Begin
    Loop
    $DelStmt
    tempCnt := tempCnt+ SQL%ROWCOUNT ;
    totDelcnt := totDelCnt+ SQL%ROWCOUNT ;

    If SQL%NOTFOUND Then
    if @@rowcount = 0
    Exit;
    End if;

    If tempCnt >= 50000 Then
    Commit ;
    tempCnt:=0 ;
    End if ;

    End Loop;
    Commit ;
    dbms_output.put_line('No of Recs Deleted From $TableName: '|| totDelcnt);
    End;
    /
    exit
    EOSQL

    How are you going to connect to sql server from a UNIX shell script ? Is there a unix client for sql server that I do not know about ?

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by oraj
    Hi All

    Can any one help me in converting this from WHICH IN ORACLE to MS SQL SERVER


    sqlplus -s $UserId/$PassWord@$DataBase <<EOSQL> $LogFile (This is the Connection String)

    set serveroutput on
    Declare
    declare
    tempCnt Number:=0;
    @tempCnt numeric
    set @tempCnt = 0
    totDelCnt Number:=0;
    Begin
    Loop
    $DelStmt
    tempCnt := tempCnt+ SQL%ROWCOUNT ;
    totDelcnt := totDelCnt+ SQL%ROWCOUNT ;

    If SQL%NOTFOUND Then
    if @@rowcount = 0
    Exit;
    End if;

    If tempCnt >= 50000 Then
    Commit ;
    tempCnt:=0 ;
    End if ;

    End Loop;
    Commit ;
    dbms_output.put_line('No of Recs Deleted From $TableName: '|| totDelcnt);
    End;
    /
    exit
    EOSQL

    How are you going to connect to sql server from a UNIX shell script ? Is there a unix client for sql server that I do not know about ? (only kidding)

  7. #7
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by blindman
    What is the sql for your delete statement, and can you briefly explain what the code is supposed to do?
    He's deleting rows in blocks of 50000, so he does not hose his rollback segment(s). This is a sh/ksh script though. He will need to convert it to a DOS cmd file, unless he is using MKS or something like that in Windows.
    Last edited by PMASchmed; 09-15-06 at 16:31.

  8. #8
    Join Date
    Mar 2005
    Posts
    1
    Try;

    declare @count integer
    set @count = ( select count(*) from <tablename> )

    truncate table <tablename>
    --or delete * from <tablename>
    print 'Deleted '+cast(@count as varchar(20))+' rows'

    Why are you deleting in blocks of 50000??

    just a thought. . . . .

    GKramer
    The Netherlands

  9. #9
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by GKramer
    Try;

    declare @count integer
    set @count = ( select count(*) from <tablename> )

    truncate table <tablename>
    --or delete * from <tablename>
    print 'Deleted '+cast(@count as varchar(20))+' rows'

    Why are you deleting in blocks of 50000??

    just a thought. . . . .

    GKramer
    The Netherlands
    His rollback segment(s) are probably not that large, and he is probably low on DASD.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by PMASchmed
    His rollback segment(s) are probably not that large, and he is probably low on DASD.
    DASD...good lord man, your mainframe is showing

    As far as converting Oracle Packages and Sprocs, it will not be a straight conversion.

    What for example do you plan to do with all those Cursors?

    Convert them into SQL Server Cursors?

    There is no such thing in SQL server as reference Cursors...they just push out a SELECT to a result set.

    And what is the front end application code?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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