Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    53

    Unanswered: desperate for help with converting a t-sql sp to a pl/sql sp

    I am trying to convert this t-sql stored procedure to an pl/sql sp. Unfortunately, I dont have the best tools, I'm using SQL Plus. I got some help sorta with a converstion tool, I had to change a few things...this one's a doozy so if anyone can provide me any help as to what the errors mean or how I can better rewrite the oracle sp, anything, I'd greatly appreciate it



    WORKING T_SQL SP:
    alter procedure spl_getequipmentconflicts
    @cases varchar (1000),
    @fserial int,
    @vstart int,
    @vstop int,
    @total int,
    @conflictqty int,
    @itemno varchar(10)

    as

    set nocount on


    create table #equipment (serial int,
    itemno varchar(40),
    qty int,
    room varchar(20),
    starttime int,
    cstart varchar(20),
    endtime int,
    cstop varchar(20),
    length int,
    casesite varchar(20),
    equipsite varchar(20),
    totalqty int)

    create table #equipment2 (serial int)

    DECLARE @vSQL varchar(600)

    set @vsql = 'SELECT sh_serial,schi_itemno, schi_qty,sh_room,sh_istart,sh_start,sh_stop,sh_ist op,sh_estlen,sh_site, schi_site
    FROM casemas, schilin
    WHERE (schi_shser = sh_serial)
    and (schi_itemno = ' + @itemno + ')'+
    'and sh_serial IN (' + @cases + ')'


    select 'vsql' = @vsql

    insert into #equipment (serial, itemno, qty, room, starttime, cstart, cstop,endtime, length, casesite, equipsite)


    EXEC(@vSQL)


    while @vstart <= @vstop
    begin

    if (select count(serial) from #equipment where @vstart between starttime and endtime ) > 0
    begin

    if (((select sum(qty)
    from #equipment

    where @vstart not in(select starttime from #equipment where starttime in (select endtime from #equipment))
    AND @vstart between starttime and endtime) + @total) > @conflictqty)
    begin

    insert into #equipment2(serial)
    select serial
    from #equipment
    where @vstart between starttime and endtime
    end
    end

    select @vstart = @vstart + 1

    end

    SELECT sh_serial,
    schi_itemno,
    schi_qty,
    sh_room,
    sh_istart,
    sh_start,
    sh_stop,
    sh_istop,
    sh_estlen,
    sh_site,
    schi_site
    FROM casemas, schilin
    WHERE (schi_shser = sh_serial)
    and (schi_itemno = @itemno)
    and sh_serial in (select distinct serial from #equipment2)

    select * from #equipment
    select * from #equipment2
    drop table #equipment
    drop table #equipment2

    GO
    --------------------------------------------
    ATTEMPT to convert to ORACLE:

    DROP TABLE equipment
    /


    CREATE GLOBAL TEMPORARY table equipment
    (
    serial int ,
    itemno VARCHAR2 (40) ,
    qty int ,
    room VARCHAR2 (20) ,
    starttime int ,
    cstart VARCHAR2 (20) ,
    endtime int ,
    cstop VARCHAR2 (20) ,
    length int ,
    casesite VARCHAR2 (20) ,
    equipsite VARCHAR2 (20) ,
    totalqty int
    )
    /


    DROP TABLE equipment2
    /


    CREATE GLOBAL TEMPORARY table equipment2
    (
    serial int
    )
    /


    CREATE OR REPLACE PACKAGE spl_getequipmentconflicts
    AS
    TYPE ref_cur IS REF CURSOR;
    PROCEDURE spl_getequipmentconflicts
    (
    cases IN VARCHAR2 DEFAULT NULL,
    fserial IN INT DEFAULT NULL,
    vstart IN OUT INT,
    vstop IN INT DEFAULT NULL,
    total IN INT DEFAULT NULL,
    conflictqty IN INT DEFAULT NULL,
    itemno IN VARCHAR2 DEFAULT NULL,
    RC1 IN OUT REF_CUR
    );
    END;
    /
    CREATE OR REPLACE PACKAGE BODY spl_getequipmentconflicts
    AS
    PROCEDURE spl_getequipmentconflicts
    (
    cases IN VARCHAR2 DEFAULT NULL,
    fserial IN INT DEFAULT NULL,
    vstart IN OUT INT,
    vstop IN INT DEFAULT NULL,
    total IN INT DEFAULT NULL,
    conflictqty IN INT DEFAULT NULL,
    itemno IN VARCHAR2 DEFAULT NULL,
    RC1 IN OUT REF_CUR
    )
    AS
    vSQL VARCHAR2(600);
    ASSIGNMENTVARIABLE0 INTEGER := 0;
    ASSIGNMENTVARIABLE1 INTEGER := 0;
    CountRows INT;
    BEGIN

    EXECUTE IMMEDIATE 'TRUNCATE TABLE equipment';

    EXECUTE IMMEDIATE 'TRUNCATE TABLE equipment2';

    vsql := 'SELECT sh_serial,schi_itemno, schi_qty,sh_room,sh_istart,sh_start,sh_stop,sh_ist op,sh_estlen,sh_site, schi_site
    FROM casemas, schilin
    WHERE (schi_shser = sh_serial)
    and (schi_itemno = ' || itemno || ')' || 'and sh_serial IN (' || cases || ')';
    OPEN RC1 FOR
    SELECT spl_getequipmentconflicts.vsql "vsql"
    FROM SYS.DUAL;
    scope_identity_variable := ScopeIdentityValueGenerator('equipment_rownumber_S EQ', 1);
    INSERT INTO equipment
    ( equipment_rownumber_SEQ.NEXTVAL, serial ,
    itemno ,
    qty ,
    room ,
    starttime ,
    cstart ,
    cstop ,
    endtime ,
    length ,
    casesite ,
    equipsite );
    SELECT equipment_rownumber_SEQ.CURRVAL INTO GLOBALPKG.IDENTITY FROM DUAL;
    --selecting all these columns to maintain ability to debug
    --necessary colums: sh_serial, schi_qty, sh_istart, sh_istop
    EXECUTE IMMEDIATE vSQL;
    WHILE vstart <= vstop
    LOOP
    BEGIN

    BEGIN
    SELECT COUNT(serial) INTO ASSIGNMENTVARIABLE0
    FROM equipment
    WHERE spl_getequipmentconflicts.vstart between starttime and endtime;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    ASSIGNMENTVARIABLE0 := NULL;

    WHEN TOO_MANY_ROWS THEN
    NULL;
    END;
    IF( ASSIGNMENTVARIABLE0) > 0 THEN
    BEGIN
    BEGIN
    SELECT SUM(qty) INTO ASSIGNMENTVARIABLE1
    FROM equipment
    WHERE spl_getequipmentconflicts.vstart not in
    (
    SELECT starttime
    FROM equipment
    WHERE starttime in
    (
    SELECT endtime
    FROM equipment
    )
    )
    AND spl_getequipmentconflicts.vstart between starttime and endtime;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    ASSIGNMENTVARIABLE1 := NULL;

    WHEN TOO_MANY_ROWS THEN
    NULL;
    END;
    IF((( ASSIGNMENTVARIABLE1) + total) > conflictqty) THEN
    BEGIN

    SELECT COUNT(*) INTO CountRows
    FROM equipment
    WHERE :vstart between starttime and endtime

    scope_identity_variable := ScopeIdentityValue('equipment2_rownumber_SEQ', CountRows);
    INSERT INTO equipment2
    ( ROWNUMBER ,
    serial )
    SELECT
    equipment2_rownumber_SEQ.NEXTVAL,
    serial
    FROM equipment
    WHERE spl_getequipmentconflicts.vstart between starttime and endtime;
    SELECT equipment2_rownumber_SEQ.CURRVAL INTO GLOBALPKG.IDENTITY FROM DUAL;
    END;
    END IF;
    END;
    END IF;
    spl_getequipmentconflicts.vstart := spl_getequipmentconflicts.vstart + 1;
    END;

    END LOOP;
    OPEN RC1 FOR
    SELECT
    sh_serial,
    schi_itemno,
    schi_qty,
    sh_room,
    sh_istart,
    sh_start,
    sh_stop,
    sh_istop,
    sh_estlen,
    sh_site,
    schi_site
    FROM casemas,
    schilin
    WHERE (schi_shser = sh_serial)
    AND (schi_itemno = spl_getequipmentconflicts.itemno)
    AND sh_serial in
    (
    SELECT DISTINCT serial
    FROM equipment2
    );
    OPEN RC1 FOR
    SELECT *
    FROM equipment;
    OPEN RC1 FOR
    SELECT *
    FROM equipment2;

    --DROP TABLE equipment
    --DROP TABLE equipment2
    END;
    END;
    /
    show errors;

    I get the following errors below:

    Warning: Package Body created with compilation errors.

    Errors for PACKAGE BODY SPL_GETEQUIPMENTCONFLICTS:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    44/16 PLS-00103: Encountered the symbol ";" when expecting one of the
    following:
    ( select values

    92/13 PLS-00049: bad bind variable 'VSTART'
    94/6 PLS-00103: Encountered the symbol "SCOPE_IDENTITY_VARIABLE" when
    expecting one of the following:
    . ( * @ % & - + ; / at for mod rem <an exponent (**)> and or
    group having intersect minus order start union where connect
    ||
    The symbol ";" was substituted for "SCOPE_IDENTITY_VARIABLE" to
    continue.

  2. #2
    Join Date
    Apr 2004
    Posts
    69
    even I had been working on such database conversions for sometime now. can you tell me the name of the tool you had tried out ? May be it could help me too.

    Anyhow, I see the following issues with the converted code as is :

    1. I think the tool has tried using some variable which it forgot to attach in the declaration secion. The variable isn't doing any good logic either. So you can safely comment out the statements which are using this variable.

    2. You may have to remove the ":" in the where clause where the local variable vstart is used.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I can't help with this, because I don't know T-SQL. But my advice would be that instead of trying to "blindly" convert T-SQL syntax to the nearest equivalent PL/SQL syntax, you should work out what the T-SQL does (not how it does it), and then write the most appropriate PL/SQL to achieve that goal.

Posting Permissions

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