Home » SQL & PL/SQL » SQL & PL/SQL » Ora-06540 error by the pipelined function at runtime
Ora-06540 error by the pipelined function at runtime [message #49126] Mon, 17 January 2005 05:56 Go to next message
Hasan Yilmaz
Messages: 1
Registered: January 2005
Junior Member
Hi Guys,

Altough my package has no compile error, my pipelined function produces ORA-06540 error always at runtime after piping a few rows ORA-06540 (Compile error). Does anybody have idea , how can I solve this problem ?

Thanks,

Osman
Re: Ora-06540 error by the pipelined function at runtime [message #111326 is a reply to message #49126] Tue, 15 March 2005 12:00 Go to previous messageGo to next message
alanpol
Messages: 4
Registered: March 2005
Junior Member
OK..."ME TOO"...I get the same issue. Have you had any luck getting more information, or solving it?
Any help would be much appreciated.
Re: Ora-06540 error by the pipelined function at runtime [message #111333 is a reply to message #111326] Tue, 15 March 2005 12:30 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
It would help if you could provide us with the following info:
- DB Version
- From where the call was made
- Sample code
- quantification of "a few rows"

With the little info I've seen I can't lay my finger on it.

MHE
Re: Ora-06540 error by the pipelined function at runtime [message #111341 is a reply to message #111333] Tue, 15 March 2005 13:17 Go to previous messageGo to next message
alanpol
Messages: 4
Registered: March 2005
Junior Member
Sure:

DB Version - 9.2.0.5

From where the call was made - function is in package called from SQL*Plus (or TOAD - exhibits same behavior)

Sample code - following is "anonymized" form:

package body abc is...
....
function get_some_items
(
in_p1 in varchar2,
in_p2 in varchar2 default null,
in_p3 in varchar2 default null
) return anomaly_rt pipelined
is

the_return_row abcObj := abcObj();

type cur_typ is ref cursor;
the_cur cur_typ;

the_rec abc_view%rowtype;
the_sql varchar2(2000);

begin
the_sql := <some SQL string>;

open the_cur for the_sql using in_p1;
loop
fetch the_cur into the_rec;
exit when the_cur%NOTFOUND;
the_return_row.<element1> := the_rec.<element1>;
....
the_return_row.<elementN> := the_rec.<elementN>;
pipe row (the_return_row);
end loop;
close the_cur;
return;
exception
when others then
close the_cur;
raise_application_error(-20999, sqlerrm);
end get_anomaly_items;
....
end abc;


Quantification of "a few rows" - 26, without fail!

Any insight would be appreciated...particularly if it's a usage issue :-/
Re: Ora-06540 error by the pipelined function at runtime [message #111346 is a reply to message #111341] Tue, 15 March 2005 13:29 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Is this mysterious row that always seems to fail identifiable? If so, look at it. Perhaps there are some size issues between your table (sql) definition and the return type...

What I'd do is try to sort/reverse sort my sql and see whether it's the data of this row that's causing this error.

You also have no clue what the real error is (in the stack). So, I suggest that you ommit your Exception block (just for now, to test) and see what your error stack can learn. There should be some PLS- error message too.

MHE
Re: Ora-06540 error by the pipelined function at runtime [message #111370 is a reply to message #111346] Tue, 15 March 2005 17:11 Go to previous messageGo to next message
alanpol
Messages: 4
Registered: March 2005
Junior Member
OK, forget numbers of rows etc...

The behavior appears related to whether there is an exception block with an insert.

The following code will generate content
ERROR_MESSAGE,ERROR_TIMESTAMP
ORA-06540: PL/SQL: compilation error, 3/15/2005 4:36:00.890000 PM

in the table_function_error table:

    function get_test_items
    (
        in_filter             in     varchar2 default null,
        in_sort               in     varchar2 default null
    ) return table_function_rt pipelined
    is
           
        pragma autonomous_transaction;
        the_return_row tableFunctionObj := tableFunctionObj();
        the_time       timestamp;
        the_error      varchar2(4000);

    begin

        for each_row in (select * from table_function_test where 1=1) loop
           the_return_row.value := each_row.value;
           pipe row (the_return_row);
        end loop;
        return;
    exception
        when others then
            the_error := sqlerrm;
            the_time  := systimestamp;
            insert into table_function_error (error_message, error_timestamp) values (the_error, the_time);
            commit;
    end get_test_items;


but if I just comment the exception handler out:
    function get_test_items
    (
        in_filter             in     varchar2 default null,
        in_sort               in     varchar2 default null
    ) return table_function_rt pipelined
    is
           
        pragma autonomous_transaction;
        the_return_row tableFunctionObj := tableFunctionObj();
        the_time       timestamp;
        the_error      varchar2(4000);

    begin

        for each_row in (select * from table_function_test where 1=1) loop
           the_return_row.value := each_row.value;
           pipe row (the_return_row);
        end loop;
        return;
    /*exception
        when others then
            the_error := sqlerrm;
            the_time  := systimestamp;
            insert into table_function_error (error_message, error_timestamp) values (the_error, the_time);
            commit;*/
    end get_test_items;


then it does not. Further, if the exception block just contains a "raise_application_error", no exception is trapped.

Any idea why the presence of an exception handler with an insert would lead to ORA-06540 getting generated?
Re: Ora-06540 error by the pipelined function at runtime [message #113025 is a reply to message #111370] Wed, 30 March 2005 11:48 Go to previous messageGo to next message
alanpol
Messages: 4
Registered: March 2005
Junior Member
Have resolved this one....issue was because the command was issued by a client that did not fetch the full resultset returned by the table function.

For example, the table function returns 1000 rows, but the client (which communicates using ODBC) only fetches 200 at a time. If all the rows are fetched, then there is no issue; however, if only a subset are fetched before another command is executed, the exception gets raised. The issue is not observed with SQL Plus since it always fetches the entire resultset; this behavior also explains the observed behavior that seemed to be related to the number of rows returned.

This is a known 9i bug - it is fixed in 10g, when the correct informational (ora-06548) is raised.

Hope this may help other people...
Re: Ora-06540 error by the pipelined function at runtime [message #247336 is a reply to message #113025] Mon, 25 June 2007 11:01 Go to previous message
anhvu
Messages: 1
Registered: June 2007
Junior Member
Hi AP,

I have the same problem(Log_Msg Error -6548: ORA-06548: no more rows needed) and I'm using Oracle 10g.
I'm running my procedure using TOAD.

This is my table function:

FUNCTION dup_lines(p_in_cursor IN sys_refcursor) RETURN card_table_type
PIPELINED
IS
l_rec vw_card_member_file%ROWTYPE;
BEGIN

LOOP
FETCH p_in_cursor into l_rec;

EXIT WHEN (p_in_cursor%NOTFOUND);

dbms_output.put_line('NUMBER OF CARDS'||TO_NUMBER(l_rec.cards));

IF TO_NUMBER(l_rec.cards)>1 THEN

FOR i IN 1..TO_NUMBER(l_rec.cards)
LOOP
dbms_output.put_line('NUMBER OF CARDS'||TO_NUMBER(l_rec.cards));
pipe row(l_rec);
END LOOP;
ELSIF TO_NUMBER(l_rec.cards)=1 THEN
dbms_output.put_line('NUMBER OF CARDS'||TO_NUMBER(l_rec.cards));
pipe row(l_rec);
END IF;

END LOOP;
RETURN;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('ERROR IN dup_lines');
dbms_output.put_line(SUBSTR('Log_Msg Error '||TO_CHAR(SQLCODE)||': '||SQLERRM,1,255));
END;

If I just do a select like this,
select *
FROM TABLE(packagename.dup_lines(CURSOR(SELECT * FROM vw_card WHERE card_id=151)))
This works great, but when I call it inside another procedure it crashes with the error, no more rows needed.

Any feedbacks would be greatly appreciated.

Thanks in advance,
AV
Previous Topic: Recently updated records.
Next Topic: How to pass data file name as a parameter to the control file ?
Goto Forum:
  


Current Time: Tue Dec 06 04:29:07 CST 2016

Total time taken to generate the page: 0.08604 seconds