我创建了一个程序:
PROCEDURE GetDataCrossSelling (
p_branchcode IN MST_BRANCH.BRANCH_ID%TYPE,
p_result OUT DATA_CURSOR
)
IS
BEGIN
OPEN p_result FOR
SELECT
STNK.NO_PENGAJUAN NoPengajuan,
STNK.DO_ID DoNo,
A.ATTRIBUTE2 stnkName,
CUST.CUST_NAME CustomerName,
STNK.PRG_TAX ProgressiveTax,
STNK.BIAYA_LAIN_LAIN BiayaLainLain,
STNK.BPKB_NO BPKBNo,
STNK.STNK_NO STNKno,
STNK.POLICE_NO PoliceNo,
--STNK.NOTICE,
STNK.STATUS_UPLOAD statusupload,
STNK.MESSAGE_UPLOAD messageupload,
STNK.UPLOAD_STNK_DATE tglupload
FROM
trx_sales_order A
LEFT JOIN trx_delivery_order B ON A.so_id = B.so_id
LEFT JOIN trx_stnk STNK ON B.DO_ID = STNK.DO_ID
LEFT JOIN mst_branch_finance_company C ON A.branch_finance_id = C.branch_finance_id
LEFT JOIN mst_customer CUST ON A.CUST_ID = CUST.CUST_ID
LEFT JOIN trx_pengajuan_bbnstnk Peng ON PENG.NO_PENGAJUAN = STNK.NO_PENGAJUAN
LEFT JOIN mst_storage_detail stor ON STOR.STOCK_ID = STNK.STOCK_ID
WHERE
A.BRANCH_ID = p_branchcode
--AND STNK.NO_PENGAJUAN LIKE
-- TRIM (UPPER('%' || p_nopengajuan || '%'))
--AND PENG.TANGGAL_PENGAJUAN >= p_datefrom
--AND PENG.TANGGAL_PENGAJUAN <= p_dateto
-- AND STOR.CHASIS_NO LIKE
-- TRIM(UPPER('%' || p_chasisno || '%'))
--AND STOR.ENGINE_NO LIKE
-- TRIM(UPPER('%' || p_engineno || '%'))
AND C.FIN_COMPANY_ID = '26'
AND B.delivery_status = 'D'
AND STNK.FAKTUR_PRINT_DATE IS NULL
AND STNK.FAKTUR_RECEIVE_DATE IS NULL
AND STNK.PROCESS_DATE IS NULL
AND A.ATTRIBUTE5 <> 0;
-- ORDER BY peng.TANGGAL_PENGAJUAN DESC;
END;
在C#中,我这样调用它:
public List<GetDataSTNK> GetDataCrossSelling(
string kodeCabang, DateTime dariTgl, DateTime sampaiTgl, string biroJasa, string noPengajuan, string chasisNo, string engineNo)
{
List<GetDataSTNK> lst = new List<GetDataSTNK>();
OracleParameter cursor = new OracleParameter("p_result", OracleType.Cursor);
cursor.Direction = ParameterDirection.Output;
using (IDataReader reader = OracleHelper.ExecuteReader(
connectionString,
CommandType.StoredProcedure,
"TRACKING_STNK.GetDataCrossSelling",
new OracleParameter("p_branchcode", kodeCabang),
new OracleParameter("p_datefrom", dariTgl),
new OracleParameter("p_dateto", sampaiTgl),
new OracleParameter("p_birojasa", biroJasa),
new OracleParameter("p_nopengajuan", noPengajuan),
new OracleParameter("p_chasisno", chasisNo),
new OracleParameter("p_engineno", engineNo),
cursor))
{
while (reader.Read())
{
var r = new GetDataSTNK
{
NoPengajuan = Convert.ToString(reader["NoPengajuan"]),
DONo = Convert.ToString(reader["DONo"]),
SPKNo = Convert.ToString(reader["STNKName"]),
CustomerName = Convert.ToString(reader["CustomerName"]),
//Amount = !string.IsNullOrEmpty(Convert.ToString(reader["Flag_centang"].ToString())) ? (reader["Amount_DariIdent"] is DBNull ? 0 : Convert.ToDecimal(reader["Amount_DariIdent"])) : (reader["Amount_DariCust"] is DBNull ? 0 : Convert.ToDecimal(reader["Amount_DariCust"])),
AmountDetail = !string.IsNullOrEmpty(reader["AmountDetail"].ToString()) ? Convert.ToDouble(reader["AmountDetail"]) : 0,
ProgressiveTax = !string.IsNullOrEmpty(reader["ProgressiveTax"].ToString()) ? Convert.ToDecimal(reader["ProgressiveTax"]) : 0,
BiayaLainLain = !string.IsNullOrEmpty(reader["BiayaLainLain"].ToString()) ? Convert.ToDecimal(reader["BiayaLainLain"]) : 0,
BPKBNo = Convert.ToString(reader["BPKBNo"]),
STNKNo = Convert.ToString(reader["STNKNo"]),
PoliceNo = Convert.ToString(reader["PoliceNo"]),
//ChasisNo = Convert.ToString(reader["CHASIS_NO"]),
//EngineNo = Convert.ToString(reader["ENGINE_NO"]),
StatusUpload = Convert.ToString(reader["StatusUpload"]),
Ket = Convert.ToString(reader["Ket"]),
TglUpload = !string.IsNullOrEmpty(reader["TglUpload"].ToString()) ? Convert.ToDateTime(reader["TglUpload"]) : new DateTime(1900, 1, 1)
};
lst.Add(r);
}
}
return lst;
}
我收到错误:
System.Data.OracleClient.OracleException:ORA-06550:第1行,第21列:
PLS-00302:必须声明组件“GETDATACROSSSELLING”
ORA-06550:第1行,第7列:
PL/SQL:忽略语句
我做错了什么?
1条答案
按热度按时间atmip9wb1#
您似乎已将新过程添加到包体中,但未添加到其规范中。这意味着该过程是包的私有过程-同一包中的其他过程和函数可以调用它,但外部的任何东西都不能,包括您的C#代码。
如果将过程声明也添加到包规范中,则作为
那么这个过程将是公开的。
然后,您的代码将得到一个不同的错误,“PLS-00306:在调用'GETDATACROSSSELLING'时参数的数量或类型错误“,因为正如注解中指出的,该过程只有两个参数,但您提供了8个参数。然后,您需要更改调用以仅传递这两个预期参数,或者(可能,但可能不太可能,因为您似乎不需要它们)修改过程声明-在包规范和主体中-以预期所有八个参数。
fiddle显示了两个错误,使用和不使用规范中声明的过程,以及使用两个参数的成功调用-使用等效的匿名PL/SQL块代替C#代码。