Home » SQL & PL/SQL » SQL & PL/SQL » Need Help with Table Type (Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0)
Need Help with Table Type [message #687384] Sun, 05 March 2023 23:05 Go to next message
vinodkumarn
Messages: 60
Registered: March 2005
Member
I have the following code

CREATE OR REPLACE TYPE SEG_TYPE AS OBJECT (
segmentType VARCHAR2(50),
segmentValue VARCHAR2(50),
segmentDesc VARCHAR2(100),
startDate VARCHAR2(10),
endDate VARCHAR2(10),
enabledFlag varchar2(5) );
/
create or replace TYPE SEG_ARRAY AS TABLE OF SEG_TYPE;
/
create or replace PACKAGE SEG_PROCESS_PKG AS
PROCEDURE ins_accounts(SEG_REC IN SEG_ARRAY, STATUS_TYPE OUT VARCHAR2);
END SEG_PROCESS_PKG;
/
create or replace PACKAGE BODY SEG_PROCESS_PKG AS
PROCEDURE ins_accounts(SEG_REC IN SEG_ARRAY, STATUS_TYPE OUT VARCHAR2) IS

V_REC_CNT Number;
I PLS_INTEGER;
BEGIN
IF NVL(SEG_REC.COUNT,0) > 0 THEN
FOR i IN SEG_REC.FIRST .. SEG_REC.LAST LOOP
INSERT INTO ACCT_SUB_ACCOUNTS VALUES (
SEG_REC(i).segmentValue,
SEG_REC(i).segmentDesc,
SEG_REC(i).enabledFlag,
USER,
SYSDATE,
'JSON',
USER,
SYSDATE,
'JSON',
SEG_REC(i).startDate,
SEG_REC(i).endDate);
COMMIT;
END LOOP;
END IF;

STATUS_TYPE:= 200; -- Success --Response Status Code:
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, 'Error Inserting SEGMENT: ' || sqlerrm);
STATUS_TYPE:= 500; -- Success --Response Status Code:
END ins_accounts;
END SEG_PROCESS_PKG;
/

Now I try to execute by the following Input and I am getting errors

set serveroutput on
DECLARE
status VARCHAR2(10);
seg_arr varchar(100):= 'Company,1001,Samrun,2023-01-08,2023-10-10,Yes';
BEGIN
OPS$TEST.SEG_PROCESS_PKG.ins_accounts(seg_arr,status);
dbms_output.enable;
dbms_output.put_line(status);
END;
/

Error report -
ORA-06550: line 5, column 3:
PLS-00306: wrong number or types of arguments in call to 'INS_ACCOUNTS'
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

Please need help here to on what might be the issue.

Thanks in Advance.
Re: Need Help with Table Type [message #687385 is a reply to message #687384] Sun, 05 March 2023 23:38 Go to previous messageGo to next message
mathguy
Messages: 106
Registered: January 2023
Senior Member
Cross-posted on Oracle Forums, I answered your question there.

https://forums.oracle.com/ords/apexds/post/need-help-with-table-type-3710
Re: Need Help with Table Type [message #687390 is a reply to message #687384] Mon, 06 March 2023 01:05 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Sat, 01 August 2020 19:29

Michel Cadot wrote on Fri, 23 March 2012 20:54
...
Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
...
vinodkumarn wrote on Fri, 23 March 2012 21:40
Thank you very much for the answer and sorry for not following the rules for posting.
Michel Cadot wrote on Sat, 24 March 2012 07:17
No problem for this time but you will have no excuse for the next question.

Thanks for the feedback,
Michel
...


Error at "ins_accounts(seg_arr,status);"

Compare:
ins_accounts(SEG_REC IN SEG_ARRAY...
seg_arr varchar(100)

So "wrong number or types of arguments in call to 'INS_ACCOUNTS'"


Previous Topic: Optional parameter in Where clause
Next Topic: how to incrementally get all numbers incremented by n between 2 columns values
Goto Forum:
  


Current Time: Thu Mar 28 19:45:05 CDT 2024