?
Solved

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

Posted on 2003-02-25
6
Medium Priority
?
894 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:TannerC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 

Expert Comment

by:fpinheiro
ID: 8019050
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.
0
 

Author Comment

by:TannerC
ID: 8019085
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
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 8019139
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

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

Thanks,
TannerC
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 300 total points
ID: 8019587
It requires only a very small change.  Just remove the call of this function from the select ... into... (I commented it out in this sample) and call the function separately after the select:

       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 (+);
-- Now get this value:
       o_company_sic_info := mev_sic.get_company_sic_info(v_company_id);
0
 

Author Comment

by:TannerC
ID: 8019618
markgeer,

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

Thanks,

TannerC
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question