asked on

PLS-00513: PL/SQL function called from SQL must return value of legal SQL type

I am getting the PLS-00513: PL/SQL function called from SQL must return value of
legal SQL type for my call to mev_sic.get_company_sic_info(co.company_id) from
the select statement below. My table type needs to send more then one column back and it is in a package.

From my package header

TYPE CompanySicInfo_rectype IS RECORD
( sic_source sic_codes.sic_source%TYPE,
sic_code sic_codes.sic_code%TYPE,
industry_class_id sic_codes.industry_class_id%TYPE,
sic_type company_sic_codes.sic_type%TYPE,
short_description sic_codes.short_description%TYPE
);

TYPE SicCodeTblTyp IS TABLE OF CompanySicInfo_rectype
INDEX BY BINARY_INTEGER;

From my package body

PROCEDURE get_company_dbupdate
( i_attr_info_tbl IN attr_name_value_tbltype,
i_matching_criteria IN POSITIVE,
o_company_info_rec OUT company_info_rectype,
o_company_sic_info OUT SicCodeTblTyp )
IS
v_attr_id identifying_attrs.attribute_id%TYPE;
v_attr_value company_attrs.attribute_value%TYPE;
v_company_id company_attrs.company_id%TYPE;
v_last_company_id company_attrs.company_id%TYPE;
i BINARY_INTEGER;
v_cnt_matches BINARY_INTEGER DEFAULT 0;
v_coa_rowid ROWID;
KeyIDConflicts BOOLEAN DEFAULT FALSE;
BEGIN
o_company_info_rec := NULL; -- Clear the output record
set_matching_criteria(i_matching_criteria);
FOR i IN 1..i_attr_info_tbl.COUNT LOOP

logedit_attr_value(i_attr_info_tbl(i).attr_name,i_attr_info_tbl(i).attr_value,v_a
ttr_id,v_attr_value);
IF i_attr_info_tbl(i).attr_value IS NOT NULL THEN
BEGIN
SELECT
coa.ROWID,
co.company_id,
co.name,
co.address_1,
co.address_2,
co.city,
co.district,
co.country_id,
co.telephone_number,
co.business_description,
co.quotation_symbol,
co.parent_id_usage,
co.parent_id,
co.scale,
co.employees,
co.url,
igrp.industry_group_id,
igrp.industry_mnemonic,
igrp.name,
mev_sic.get_company_sic_info(co.company_id)
INTO
v_coa_rowid,
v_company_id,
o_company_info_rec.name,
o_company_info_rec.address_1,
o_company_info_rec.address_2,
o_company_info_rec.city,
o_company_info_rec.district,
o_company_info_rec.country_id,
o_company_info_rec.telephone_number,
o_company_info_rec.business_description,
o_company_info_rec.quotation_symbol,
o_company_info_rec.parent_id_usage,
o_company_info_rec.parent_id,
o_company_info_rec.scale,
o_company_info_rec.employees,
o_company_info_rec.url,
o_company_info_rec.industry_group_id,
o_company_info_rec.industry_mnemonic,
o_company_info_rec.industry_name,
o_company_sic_info
FROM
company_attrs coa,
companies co,
industry_groups igrp
WHERE coa.attribute_id = v_attr_id
AND coa.attribute_value = v_attr_value
AND coa.company_id = co.company_id
AND co.industry_group_id = igrp.industry_group_id (+);

IF UpdateReadDate THEN
UPDATE company_attrs
SET last_read_date = SYSDATE
WHERE ROWID = v_coa_rowid;
END IF;

IF MatchFirst THEN
v_cnt_matches := i;
EXIT;
ELSIF MatchAll THEN
v_cnt_matches := v_cnt_matches + 1;
IF v_last_company_id IS NULL THEN
v_last_company_id := v_company_id;
END IF;

IF v_company_id <> v_last_company_id THEN
KeyIDConflicts := TRUE;
END IF;
END IF;
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
END IF;
END LOOP;
mev_common.perform_commit;
o_company_info_rec.matching_attr := v_cnt_matches;

IF KeyIDConflicts THEN
o_company_info_rec.company_id := -1;
o_company_info_rec.name := NULL;
ELSE
o_company_info_rec.company_id := v_company_id;
END IF;
EXCEPTION
WHEN others THEN
mev_common.perform_rollback;
mev_audit.sql_error;
RAISE;
END get_company_dbupdate;

Thanks,
TannerC

fpinheiro

PLS-00513: PL/SQL function called from SQL must return value of legal SQL type

Cause: In a SQL statement, do not call a PL/SQL function having a return type that can not be handled by SQL. For example, type BOOLIAN, records and indexed-tables are not supported by SQL and functions returning such values cannot be called from SQL.

Action: Change the returned type and retry the operation.

TannerC

ASKER

Hi FPINHEIRO,

I understand what the problem is what I really need is a solution for the problem. I need to return a table and I need somekind of work around.

Thanks,

TannerC

Mark Geerlings

Oracle SQL does not support calling functions that return non-scalar datatypes (records, tables, collections, etc.)

PL\SQL supports this, so you can define an appropriate datatype in a PL\SQL block then call the function in PL\SQL to populate it after you do a select to populate the other variables. You just cannot do both in a single SQL statement.

TannerC

ASKER

Can you post some simple sample code to illustrate what you are describing with the same sort of select statement as mine.

Thanks,
TannerC

ASKER CERTIFIED SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TannerC

ASKER

markgeer,

The funny thing ten minutes ago the lightbulb went on and I came up with the same solution.

Thanks,

TannerC