Hello everyone.
It's about 2 days I'm trying to do this.
I'm trying to retrieve the values of a VARRAY inside a record by Function. Here's my code so I can explain better
I have created this VARRAY
--Creazione del VARRAY DI GENERE
CREATE OR REPLACE TYPE GENERI_G AS VARRAY(5) OF VARCHAR(30);
/
On this table there is the column "GENERI" and the type is of VARRAY type that I declared before.
CREATE TABLE VIDEOGIOCO(
NOME_G VARCHAR(40) NOT NULL,
PAR_IVA_A CHAR(11) NOT NULL,
DESCRIZIONE_G VARCHAR(150) NOT NULL,
PREZZO_L PREZZO NOT NULL,
PREZZO_V PREZZO NOT NULL,
GENERI GENERI_G, -- THIS IS THE COLUMN WHERE I WANT TO FIND THE VALUES BY FUNCTION.
MODALITA_DI_GIOCO MOD_DI_GIOCO,
CODICE_BARRE VARCHAR(20) NOT NULL,
SPAZIO_MEMORIA VARCHAR(8) NOT NULL,
IMMAGINE BLOB,
MANUALE CLOB,
CONSTRAINT PK_VIDEOGIOCO_NOME_G PRIMARY KEY(NOME_G),
CONSTRAINT FK_VIDEOGIOCO_P_IVA_A FOREIGN KEY (PAR_IVA_A) REFERENCES AZIENDA(P_IVA_A),
CONSTRAINT CH_PREZL_PREZA CHECK (PREZZO_V.VALORE_MONETA < PREZZO_L.VALORE_MONETA),
CONSTRAINT CH_PREZZO_L_M0 CHECK (PREZZO_L.VALORE_MONETA > 0.0),
CONSTRAINT CH_PREZZO_V_M0 CHECK (PREZZO_V.VALORE_MONETA > 0.0)
) LOB (IMMAGINE) STORE AS IMMAGINE_VIDEOGIOCO (TABLESPACE TS_CATENA_V_LOB DISABLE STORAGE IN ROW PCTVERSION 10 CACHE),
LOB (MANUALE) STORE AS MANUALE_VIDEOGIOCO (TABLESPACE TS_CATENA_V_LOB DISABLE STORAGE IN ROW PCTVERSION 10 CACHE),
TABLESPACE TS_CATENA_VIDEOGIOCHI;
-- This function will give me an error when a record has a VARRAY of more than 1 value
Now I created this function for find the values inside of the VARRAY.
CREATE OR REPLACE FUNCTION GENERE_V(NOME_GIOCO IN VARCHAR)
RETURN VARCHAR
IS DATI_GENERE VARCHAR(150);
BEGIN
SELECT TV.* INTO DATI_GENERE FROM CATENA_DI_NEGOZI.VIDEOGIOCO V, TABLE(V.GENERI) TV WHERE V.NOME_G=NOME_GIOCO;
RETURN DATI_GENERE;
END;
/
Here is the query that I will execute
SELECT NOME_G,GENERE_V(NOME_G) FROM VIDEOGIOCO;
When the row has only one value inside the Varray there is no problem, but when there are 2 values I got the following error:
exact fetch returns more than requested number of rows.
How can I resolve this problem?
When you use select-into as you've done here:
SELECT TV.* INTO DATI_GENERE FROM CATENA_DI_NEGOZI.VIDEOGIOCO V, TABLE(V.GENERI) TV WHERE V.NOME_G=NOME_GIOCO;
You're fetching values into exactly one variable. But when your varray has two elements, the query returns two rows.
2 into 1 doesn't go!
CREATE OR REPLACE TYPE GENERI_G AS VARRAY(5) OF VARCHAR(30);
/
declare
arr generi_g := generi_g();
val varchar2(1);
begin
arr.extend;
arr(1) := '1';
select * into val from table(arr);
dbms_output.put_line( 'Fetched ' || val );
arr.extend;
arr(2) := '2';
select * into val from table(arr);
end;
/
Fetched 1
ORA-01422: exact fetch returns more than requested number of rows
If you want to select multiple rows at the same time, you can use bulk collect. This places your results into an array:
declare
arr generi_g := generi_g();
vals dbms_sql.varchar2_table;
begin
arr.extend(2);
arr(1) := '1';
arr(2) := '2';
select * bulk collect into vals from table(arr);
for i in 1 .. vals.count loop
dbms_output.put_line( 'Fetched ' || vals(i) );
end loop;
end;
/
Fetched 1
Fetched 2