I am trying to create a stored procedure for ORACLE SQL but it keeps throwing the error:
Error(1,1): PLS-00410: duplicate fields in RECORD,TABLE or argument list are not permitted
I do not see any duplicate fields so I was wondering why this was happening (procedure is below). Also stored procedures only seem to allow queries to return one row, is there any way to make it return more than one row?
I saw a lot of questions pertaining to returning multiple rows but none of them were too clear. I also need multiple stored procedures so I was wondering if there could be any clashing of variables and whatnot.
CREATE OR REPLACE PROCEDURE ARTIST_CHECK(
p5_checkartist IN VARCHAR2,
p5_artist OUT TESTTABLE.artist%TYPE,
p5_thisweekpos OUT TESTTABLE.thisweekpos%TYPE,
p5_lastweekpos OUT TESTTABLE.lastweekpos%TYPE,
p5_title OUT TESTTABLE.title%TYPE,
p5_artist OUT TESTTABLE.artist%TYPE,
p5_entrydate OUT TESTTABLE.entrydate%TYPE,
p5_entrypos OUT TESTTABLE.entrypos%TYPE,
p5_peakpos OUT TESTTABLE.peakpos%TYPE,
p5_totalweek OUT TESTTABLE.totalweek%TYPE,
p5_thisweekdate OUT TESTTABLE.thisweekdate%TYPE)
IS
BEGIN
select t.THISWEEKPOS ,t.LASTWEEKPOS ,t.TITLE ,t.ARTIST ,t.ENTRYDATE ,t.ENTRYPOS ,t.PEAKPOS ,t.TOTALWEEK ,t.THISWEEKDATE
into p5_thisweekpos, p5_lastweekpos, p5_title, p5_artist, p5_entrydate, p5_entrypos, p5_peakpos, p5_totalweek, p5_thisweekdate
from(select artist as match, max(thisweekdate) as recent from testtable where upper(artist) like '%p5_checkartist%' group by artist), testtable t
where t.ARTIST = match and t.THISWEEKDATE = recent;
END;
below is there twice .. try omitting one per your code
p5_artist OUT TESTTABLE.artist%TYPE,
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With