Re: Portando Procedimiento de Oracle a Postgresql

On 7/28/05, Marco Villagrán <marco_villg(at)yahoo(dot)com> wrote:
> Si ya sabía que no se usan commit y rollback... pero he tratado de
> solucionar el problemas para reemplazar las funcionalidades del
> procedimiento pero siempre me manda a la exception diciendo que la
> transaction es de sólo lectura. Y al hacerle otras modificaciones me dice
> que se retorna datos cuando no se esperaba nada como retorno
>
> el procedimiento en pl/pgsql me quedó de la siguiente manera que es la
> primera transformación que he hecho.
>
>
> CREATE OR REPLACE function M_Production_Run
> (
> PInstance_ID NUMERic
> )
> returns void
> AS $$
> declare
> -- Logistice
> ResultStr VARCHAR(2000);
> Message VARCHAR(2000);
> Record_ID NUMERic;
> p record;
> pp record;
> pl record;
> bom record;
> -- Parameter
> Cur_Parameter CURSOR (PInstance NUMERic) IS
> SELECT i.Record_ID, p.ParameterName, p.P_String, p.P_Number, p.P_Date
> FROM AD_PInstance i, AD_PInstance_Para p
> WHERE i.AD_PInstance_ID=PInstance
> AND i.AD_PInstance_ID=p.AD_PInstance_ID(+)
^^^^^^
que esto? estoy seguro que no debe ir alli!!!

> ORDER BY p.SeqNo;
> -- Parameter Variables
> MustBeStocked CHAR(1);
> IsCreated CHAR(1);
> Processed CHAR(1);
> Client_ID NUMERic;
> Org_ID NUMERic;
> --
> Line NUMERic;
> NextNo NUMERic;
> CountNo NUMERic;
> -- ProductionPlan
> /* CUR_PP CURSOR IS
> SELECT *
> FROM M_ProductionPlan
> WHERE M_Production_ID=Record_ID
> ORDER BY Line, M_Product_ID;*/
> -- BOM Lines
> CUR_BOM CURSOR (Product_ID NUMERic) IS
> SELECT *
> FROM M_Product_BOM
> WHERE M_Product_ID=Product_ID
> &nbs p;ORDER BY Line;
> -- ProductionLines which are non-stocked BOMs (need to be resolved)
> CUR_PLineBOM CURSOR (ProductionPlan_ID NUMERic) IS
> SELECT pl.M_ProductionLine_ID, pl.Line, pl.M_Product_ID, pl.MovementQty
> FROM M_ProductionLine pl, M_Product p
> WHERE pl.M_ProductionPlan_ID=ProductionPlan_ID
> AND pl.M_Product_ID=p.M_Product_ID
> AND pl.Line<>100 -- Origin Line
> AND p.IsBOM='Y' AND p.IsStocked='N';
> -- Posting
> /*CUR_PL_Post CURSOR IS
> SELECT pl.M_ProductionLine_ID, pl.AD_Client_ID, pl.AD_Org_ID,
> p.MovementDate,
> pl.M_Product_ID, pl.M_AttributeSetInstance_ID, pl.MovementQty,
> pl.M_Locator_ID
> FROM M_Production p, M_ProductionLine pl, M_ProductionPlan pp
> WHERE p.M_Production_ID=pp.M_Production_ID
> AND pp.M_ProductionPlan_ID=pl.M_ProductionPlan_ID
> AND pp.M_Production_ID=Record_ID
> ORDER BY pp.Line, pl.Line;*/
>
> BEGIN
> -- Update AD_PInstance
> DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing ' || PInstance_ID);
> ResultStr := 'PInstanceNotFound';
> UPDATE AD_PInstance
> SET Created = CURRENT_DATE,
> IsProcessing = 'Y'
> WHERE AD_PInstance_ID=PInstance_ID;
> RAISE NOTICE 'Primer Update';
> -- Get Parameters
> ResultStr := 'ReadingParameters';
> FOR p IN Cur_Parameter (PInstance_ID) LOOP
> Record_ID := p.Record_ID;
> IF (p.ParameterName = 'MustBeStocked') THEN
> MustBeStocked := p.P_String;
> DBMS_OUTPUT.PUT_LINE(' MustBeStocked=' || MustBeStocked);
> ELSE
> DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || p.ParameterName);
> END IF;
> END LOOP; -- Get Parameter
> DBMS_OUTPUT.PUT_LINE(' Record_ID=' || Record_ID);
> -- Processing??? Lock ????
> -- TODO
> /**
> * Get Info + Lock
> */
> ResultStr := 'ReadingRecord';
> SELECT IsCreated, Processed, AD_Client_ID, AD_Org_ID
> INTO IsCreated, Processed, Client_ID, Org_ID
> FROM M_Production
> WHERE M_Production_ID=Record_ID
> FOR UPDATE;
> /**
> * No Action
> */
> IF (Processed <> 'N') THEN
> Message := '@AlreadyPosted@';
> GOTO FINISH_PROCESS;
^^^^^^^^^^
existe goto en plpgsql? no lo creo!!!

> END IF;
>
> /**************************************************************************
> * Create Lines
> */
> IF (IsCreated <> 'Y') THEN
> -- For every Production Plan
> FOR pp IN SELECT *
> FROM M_ProductionPlan
> WHERE M_Production_ID=Record_ID
> ORDER BY Line, M_Product_ID LOOP
> -- Delete prior lines
> DELETE M_ProductionLine
> WHERE M_ProductionPlan_ID=pp.M_ProductionPlan_ID;
> -- DBMS_OUTPUT.PUT_LINE('ProductionPlan=' ||
> pp.M_ProductionPlan_ID);
> -- Create BOM Line
> ResultStr := 'Crea tingLine BOM';
> Line := 100; -- OriginLine
> AD_Sequence_Next('M_ProductionLine', pp.AD_Client_ID,
> NextNo);
> INSERT INTO M_ProductionLine
> (M_ProductionLine_ID, M_ProductionPlan_ID, Line,
>
> AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,
> M_Product_ID, MovementQty, M_Locator_ID, Description)
> VALUES
> (NextNo, pp.M_ProductionPlan_ID, Line,
>
> pp.AD_Client_ID,pp.AD_Org_ID,'Y',CURRENT_DATE,0,CURRENT_DATE,0,
> pp.M_Product_ID, pp.ProductionQty, pp.M_Locator_ID, pp.Description);
> -- Create First Level
> FOR bom IN CUR_BOM (pp.M_Product_ID) LOOP
> ResultStr := 'CreatingLine Products';
> Line := Line + 100;
> AD_Sequence_Next('M_ProductionLine', pp.AD_Client_ID,
> NextNo);
> INSERT INTO M_ProductionLine
> (M_ProductionLine_ID, M_ProductionPlan_ID, Line,
>
> AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,
> M_Product_ID, MovementQty, M_Locator_ID)
> VALUES
> (NextNo, pp.M_ProductionPlan_ID, Line,
>
> pp.AD_Client_ID,pp.AD_Org_ID,'Y',CURRENT_DATE,0,CURRENT_DATE,0,
>
> bom.M_ProductBOM_ID, -pp.ProductionQty*bom.BOMQty, pp.M_Locator_ID);
> END LOOP;
> -- While we have BOMs
> LOOP
> -- Are there non-stored BOMs to list details?
> ResultStr := 'CreatingLine CheckBOM';
> SELECT COUNT(*) INTO CountNo
> FROM M_ProductionLine pl, M_Product p
> WHERE pl.M_Product_ID=p.M_Product_ID
> AND pl.M_ProductionPlan_ID=pp.M_ProductionPlan_ID
> AND pl.Line<>100 -- Origin Line
> AND p.IsBOM='Y' AND p.IsStocked='N';
> -- Nothing to do
> EXIT WHEN (CountNo = 0);
> --
> -- Resolve BOMs in ProductLine which are not stocked
> FOR pl IN CUR_PLineBOM (pp.M_ProductionPlan_ID) LOOP
> ResultStr := 'CreatingLineBOM Resolution';
> Line := pl.Line;
> -- Resolve BOM Line in product line
> FOR bom IN CUR_BOM (pl.M_Product_ID) LOOP
> ResultStr := 'CreatingLine Products2';
> Line := Line + 10;
> AD_Sequence_Next('M_ProductionLine', pp.AD_Client_ID,
> NextNo);
> INSERT INTO M_ProductionLine
> (M_ProductionLine_ID, M_ProductionPlan_ID, Line,
>
> AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,
> M_Product_ID, MovementQty, M_Locator_ID)
> VALUES
> (NextNo, pp.M_ProductionPlan_ID, Line,
>
> pp.AD_Client_ID,pp.AD_Org_ID,'Y',CURRENT_DATE,0,CURRENT_DATE,0,
> bom.M_ProductBOM_ID, pl.MovementQty*bom.BOMQty, pp.M_Locator_ID);
> END LOOP;
> -- Delete BOM line
> DELETE M_ProductionLine
> WHERE M_ProductionLine_ID=pl.M_ProductionLine_ID;
> END LOOP;
> END LOOP; -- While we have BOMs
> END LOOP; -- For every Production Plan
> -- Modifying locator to have sufficient stock
>
> -- Indicate that it is Created
> UPDATE M_Production
> SET IsCreated='Y'
> WHERE M_Production_ID=Record_ID;
> /**************************************************************************
> * Post Lines
> */
> ELSE
> -- All Production Lines
> FOR pl IN SELECT pl.M_ProductionLine_ID, pl.AD_Client_ID, pl.AD_Org_ID,
> p.MovementDate,
> pl.M_Product_ID, pl.M_AttributeSetInstance_ID, pl.MovementQty,
> pl.M_Locator_ID
> FROM M_Production p, M_ProductionLine pl, M_ProductionPlan pp
> WHERE p.M_Production_ID=pp.M_Production_ID
> AND pp.M_ProductionPlan_ID=pl.M_ProductionPlan_ID
> AND pp.M_Production_ID=Record_ID
> ORDER BY pp.Line, pl.Line LOOP
> IF (pl.MovementQty < 0 AND MustBeStocked <> 'N'
> AND bomQtyOnHand(pl.M_Product_ID, NULL, pl.M_Locator_ID)+pl.MovementQty
> < 0) THEN
> ROLLBACK;
^^^^^^^^^^^^^^^^^^^`

> SELECT '@NotEnoughStocked@: ' || Name INTO Message
> FROM M_Product WHERE M_Product_ID=pl.M_Product_ID;
> GOTO FINISH_PROCESS;`
^^^^^^^^^

> END IF;
> -- Adjust Quantity at Location
> UPDATE M_Storage
> SET QtyOnHand = QtyOnHand + pl.MovementQty,
> Updated = CURRENT_DATE
> WHERE M_Locator_ID = pl.M_Locator_ID
> AND M_AttributeSetInstance_ID =
> COALESCE(pl.M_AttributeSetInstance_ID,0)
> AND M_Product_ID = pl.M_Product_ID;
> -- Product not on Stock yet
> IF (SQL%ROWCOUNT = 0) THEN^
^^^^^^^^^^^^^^^^^^^^^^^^^^^
tampoco puedes hacer esto... esto lo haces con
GET DIAGNOSTICS integer_var = ROW_COUNT; y luego if con la variable

> INSERT INTO M_Storage
> (M_Product_ID, M_Locator_ID, M_AttributeSetInstance_ID,
> AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated,
> UpdatedBy,
> QtyOnHand, QtyReserved, QtyOrdered)
> VALUES
> (pl.M_Product_ID, pl.M_Locator_ID,
> COALESCE(pl.M_AttributeSetInstance_ID,0),
> pl.AD_Client_ID, pl.AD_Org_ID, 'Y', CURRENT_DATE, 0, CURRENT_DATE, 0,
> pl.MovementQty, 0, 0);
> END IF;
>
> -- Create Transaction Entry
> ResultStr := 'CreateTransaction';
> AD_Sequence_Next('M_Transaction', pl.AD_Org_ID, NextNo);
> INSERT INTO M_Transaction
> (M_Transaction_ID, M_ProductionLine_ID,
> AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated,
> UpdatedBy,
> MovementType, M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID,
> MovementDate, MovementQty)
> VALUES
> (NextNo, pl.M_ProductionLine_ID,
> pl.AD_Client_ID, pl.AD_Org_ID, 'Y', CURRENT_DATE, 0, CURRENT_DATE, 0,
> 'P+', pl.M_Locator_ID, pl.M_Product_ID,
> COALESCE(pl.M_AttributeSetInstance_ID,0), -- not distinguishing between
> assemby/disassembly
> pl.MovementDate, pl.MovementQty);
> --
> UPDATE M_ProductionLine
> SET Processed='Y'
> WHERE M_ProductionLine_ID=pl.M_ProductionLine_ID;
> END LOOP;
> -- Indicate that we are done
> UPDATE M_Production
> SET Processed='Y'
> WHERE M_Production_ID=Record_ID;
> UPDATE M_ProductionPlan
> SET Processed='Y'
> WHERE M_Production_ID=Record_ID;
> END IF;
>
> RAISE NOTICE 'Entire job successful';
> --<<FINISH_PROCESS>>
> -- Update AD_PInstance
> DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || Message);
> UPDATE AD_PInstance
> SET Updated = CURRENT_DATE,
> IsProcessing = 'N',
> Result = 1, -- success
> ErrorMsg = Message
> WHERE AD_PInstance_ID=PInstance_ID;
> RAISE NOTICE 'Updated AD_PIntance';
> RETURN;
> EXCEPTION
> WHEN OTHERS THEN
> ResultStr := ResultStr || ': ' || 'SQLERRM' || ' - ' || Message;
> -- DBMS_OUTPUT.PUT_LINE(ResultStr);
> UPDATE AD_PInstance
> SET Updated = CURRENT_DATE,
> IsProcessing = 'N',
> Result = 0, -- failure
> ErrorMsg = ResultStr
> WHERE AD_PInstance_ID=PInstance_ID;
> RAISE NOTICE 'Exception';
> RETURN;
> END ;
> $$ LANGUAGE plpgsql;
>
> Ya he logrado transformar varios procedimientos almacenados y esos funcionan
> a la perfección. Para lo cual si he leído la documentación tanto de
> postgresql 7 como del 8 que es este ultimo en el que me encuentro trabajando
> hoy. Pero para este caso no he encontrado una solución por eso decidí
> consultar si alguien podía ayudarme
>
> Bueno, gracias de todas maneras
>
>

No revise el todo porque tampoco puedo pasar tiempo revisando codigo
ajeno... tengo que trabajar, aunque sea a veces ;)
Sin embargo es obvio que se te han pasado algunas cosas del
procedimiento oracle original... revisalo otra vez y luego nos cuentas

--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)