| Need Help with Table Type [message #687384] |
Sun, 05 March 2023 23:05  |
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 #687390 is a reply to message #687384] |
Mon, 06 March 2023 01:05  |
 |
Michel Cadot
Messages: 68512
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:40Thank you very much for the answer and sorry for not following the rules for posting.
Michel Cadot wrote on Sat, 24 March 2012 07:17No 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'"
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
