sql - 获取 PLS-00103:在创建包时遇到符号“/”错误
问题描述
出现错误错误(8,1):PLS-00103:遇到符号“/”
尝试在包定义末尾插入 / 符号给出错误 -Error(9,1): PLS-00103: Encountered the symbol "CREATE" 我想要在 CreateShipment 中进行验证并将结果存储在验证数组中并将数组传递给程序 2 我将在其中进行插入
create or replace package WSH_Delivery_Detail_Shipment as
type Result IS VARRAY(8) OF INTEGER;
PROCEDURE CreateShipment(p_delivery_detail_interface_id IN WSH_DEL_DETAILS_INTERFACE.DELIVERY_DETAIL_INTERFACE_ID%TYPE,p_status OUT String) ;
PROCEDURE CreateShipmentLines(v_result IN Result);
END WSH_Delivery_Detail_Shipment;
/
CREATE OR REPLACE package body WSH_Delivery_Detail_Shipment as
PROCEDURE CreateShipment(
p_delivery_detail_interface_id IN WSH_DEL_DETAILS_INTERFACE.DELIVERY_DETAIL_INTERFACE_ID%TYPE)
IS
CURSOR wddi_cur IS SELECT * FROM WSH_DEL_DETAILS_INTERFACE WHERE DELIVERY_DETAIL_INTERFACE_ID=p_delivery_detail_interface_id;
wddi_record WSH_DEL_DETAILS_INTERFACE%ROWTYPE;
type Result IS VARRAY(8) OF INTEGER;
v_result result:=result();
l_uniqueRecords INTEGER;
l_organizationId INTEGER;
l_actionType INTEGER;
l_orderType INTEGER;
l_customerNumber INTEGER;
l_orderQuantity INTEGER;
l_orderquantityUom INTEGER;
l_updateAction INTEGER;
orgId INTEGER;
BEGIN
OPEN wddi_cur;
LOOP
FETCH wddi_cur into wddi_record;/* Validation2 : Check if Organization Id exists and store Organization_Id corresponding to Organization_Code*/
EXIT when wddi_cur%NOTFOUND;
BEGIN
select Organization_Id INTO l_organizationId from inv_org_parameters where Organization_Code=wddi_record.Organization_Code;
EXCEPTION
WHEN no_data_found THEN
l_organizationId:=0;
END;
IF (l_organizationId > 0) then
orgId:=l_organizationId;
l_organizationId:=1;
END IF;
/*validaion1 : Check for uniqueness of record with Organization_Code,Organization_Code,Organization_Id,SalesOrderNumber,SalesOrderLineNumber as unique */
SELECT COUNT(*) INTO l_uniqueRecords FROM WSH_DELIVERY_DETAILS WHERE SALES_ORDER_NUMBER=wddi_record.SALES_ORDER_NUMBER AND SALES_ORDER_LINE_NUMBER=wddi_record.SALES_ORDER_LINE_NUMBER
AND Organization_Id=orgId;
/*Validation3 : Check ActionType should be either CREATE, UPDATE, CANCEL */
IF (wddi_record.Line_Action_Type = 'CREATE' or wddi_record.Line_Action_Type = 'UPDATE' or wddi_record.Line_Action_Type = 'CANCEL') THEN
l_actionType:=1;
ELSE
l_actionType:=0;
END IF;
/* validation 4: Check OrderType should be a valid Order Type Lookup*/
select COUNT(1) INTO l_orderType from fnd_lookups where Lookup_Code = wddi_record.Source_Line_Type AND lookup_type='ORA_WSH_ORDER_LINE_TYPE';
/*Validation5 : CustomerNumber should be a valid Party Number based on OrderType*/
select COUNT(1) INTO l_customerNumber from HZ_PARTIES where Party_id=wddi_record.Ship_To_Party_Id;
/*Validation6 : OrderQuantity should be greater than 0 when ActionType is CREATE. When ActionType is UPDATE then OrderQuantity 0 is treated as CANCEL*/
IF(wddi_record.Line_Action_Type = 'CREATE' and wddi_record.SRC_REQUESTED_QUANTITY IS NULL or wddi_record.SRC_REQUESTED_QUANTITY <= 0 ) THEN
l_orderQuantity:=0;
else
l_orderQuantity:=1;
END IF;
/*Validation7 : OrderQuantityUOM should be a valid UOM Code in Units of Measure table*/
select COUNT(1) INTO l_orderquantityUom from inv_units_of_measure where UOM_CODE=wddi_record.SRC_REQUESTED_QUANTITY_UOM;
/*Validation8 : UPDATE action is allowed when Shipment Line Released Status is not Shipped or Interfaced.
RELEASED_STATUS != R
*/
select COUNT(1) INTO l_updateAction from WSH_DELIVERY_DETAILS where wddi_record.Line_Action_Type = 'UPDATE' AND SALES_ORDER_NUMBER=wddi_record.SALES_ORDER_NUMBER AND SALES_ORDER_LINE_NUMBER=wddi_record.SALES_ORDER_LINE_NUMBER
AND source_shipment_number=wddi_record.Source_Shipment_Number
AND source_shipment_id=wddi_record.Source_Shipment_Id
AND Organization_Id=orgId
AND RELEASED_STATUS != 'R';
/*1 - Pass
0-Fail
*/
v_result:=result(l_uniqueRecords,l_organizationId,l_actionType,l_orderType,l_customerNumber,l_orderQuantity,l_orderquantityUom,l_updateAction);
/*PROCEDURE CreateShipmentLines(v_result);*/
FOR i in 1.. 8 LOOP
DBMS_OUTPUT.PUT_LINE(v_result(i));
END LOOP;
END LOOP;
CLOSE wddi_cur;
END CreateShipment;
/*create or replace procedure CreateShipmentLines(v_result IN res)
END CreateShipmentLines;*/
END WSH_Delivery_
Detail_Shipment;
解决方案
从我之前发布的评论中:
虽然不是真正的“答案”,但我发布的代码实际上可以编译并显示应该如何完成。而不是 NULL 过程的主体,将您自己的代码放在那里。一步一步做,经常测试。
SQL> CREATE TABLE wsh_del_details_interface(
2 delivery_detail_interface_id NUMBER
3 );
Table created.
SQL> CREATE OR REPLACE PACKAGE wsh_delivery_detail_shipment AS
2 TYPE result IS
3 VARRAY(8)OF INTEGER;
4 PROCEDURE createshipment(
5 p_delivery_detail_interface_id IN wsh_del_details_interface.delivery_detail_interface_id%TYPE,
6 p_status OUT STRING
7 );
8
9 PROCEDURE createshipmentlines(
10 v_result IN result
11 );
12 END wsh_delivery_detail_shipment;
13 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY wsh_delivery_detail_shipment AS
2 PROCEDURE createshipment(
3 p_delivery_detail_interface_id IN wsh_del_details_interface.delivery_detail_interface_id%TYPE,
4 p_status OUT STRING
5 )
6 AS
7 BEGIN
8 NULL;
9 END;
10
11 PROCEDURE createshipmentlines(
12 v_result IN result
13 )
14 AS
15 BEGIN
16 NULL;
17 END;
18 END wsh_delivery_detail_shipment;
19 /
Package body created.
SQL>
推荐阅读
- javascript - 对如何使用 axios 正确处理 javascript 承诺有些困惑
- android - 当用户单击任何应用程序的卸载按钮时如何显示窗口
- c# - 从集合中过滤数据
- reactjs - 如何在 React Native 中加密数据(使用 Expo)
- math - 为什么我没有将十进制精度提高到 1 位?
- java - Java SHA-256 程序提供了错误的哈希
- c++ - C++ 中 Vector 的 std::upper_bound 和 std::lower_bound 的复杂性是多少?
- javascript - 我的 d3 折线图中的画笔功能未按预期工作
- node.js - 如果 www 不在反应 js Web 应用程序中的 URL 中,则将 www 添加到安全 URL
- jquery - 选择第一个元素的多选