Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle SQL Stored Procedures Duplicate Error and Multiple Rows

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;
like image 808
Mocking Avatar asked Feb 11 '23 11:02

Mocking


1 Answers

below is there twice .. try omitting one per your code

p5_artist OUT TESTTABLE.artist%TYPE,
like image 180
Shantanu Avatar answered Feb 14 '23 01:02

Shantanu