Home » SQL & PL/SQL » SQL & PL/SQL » FUNCTION problem
FUNCTION problem [message #237443] Mon, 14 May 2007 09:03 Go to next message
Murray Sobol
Messages: 4
Registered: May 2007
Location: Kitchener, ON
Junior Member
I am trying to create a FUNCTION that will return a result set, but I get the following error:
Warning: execution completed with warning
FUNCTION f_sop_invoice_reid Compiled.
LINE/COL TEXT
345 PLS-00428: an INTO clause is expected in this SELECT statement



Attached is the FUNCTION.


1 rows selected
Re: FUNCTION problem [message #237450 is a reply to message #237443] Mon, 14 May 2007 09:27 Go to previous messageGo to next message
lavallee_g
Messages: 22
Registered: May 2005
Location: Quebec
Junior Member
Its normal, when a select statement is used in a pl/sql, an into must be used.

function test
return varchar2
is
dummy varchar2(1);
begin

select x
into dummy
from dual;

return dummy;

end;

If you want to return a result set you can use the bulk collect into an db type...

Its fast and very easy to use.
Re: FUNCTION problem [message #237451 is a reply to message #237450] Mon, 14 May 2007 09:30 Go to previous messageGo to next message
Murray Sobol
Messages: 4
Registered: May 2007
Location: Kitchener, ON
Junior Member
Can you show me an example of how to user "Bulk collect"?

I am currently running 9.2.0.4.

THANKS IN ADVANCE
Re: FUNCTION problem [message #237454 is a reply to message #237451] Mon, 14 May 2007 09:36 Go to previous messageGo to next message
lavallee_g
Messages: 22
Registered: May 2005
Location: Quebec
Junior Member
No problem...

Let say you declare the following types in a package:

TYPE code_rec IS RECORD(id VARCHAR2(50)
,code_text VARCHAR2(4000)
);

TYPE code_table IS TABLE OF code_rec INDEX BY BINARY_INTEGER;


Then do something like:

FUNCTION populate_code_table
(p_id IN VARCHAR2
)
RETURN code_table
IS
tab code_table;
BEGIN
SELECT cos_id id
,cos_code_text code_text
BULK COLLECT
INTO
tab
FROM code_specifications
WHERE cos_id = p_id;

RETURN tab;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END populate_code_table;

Does it helps ??
Re: FUNCTION problem [message #237534 is a reply to message #237454] Mon, 14 May 2007 15:46 Go to previous messageGo to next message
Murray Sobol
Messages: 4
Registered: May 2007
Location: Kitchener, ON
Junior Member
I am having trouble with BULK COLLECT.
(Please see attached file)
My requirements are as follows:
3 parameters are passed as INPUTS
3 additional results are passed as OUTPUTS
The part I am not understanding is how to pass the full RESULT SET, in my case 112 columns per row.
The first 3 parameters must be verified and rejected (RETURN of -1, -2 or -3) if improper values are supplied.
Successfull query exection would RETURN 1.
Any assistance would be appreciated.
Re: FUNCTION problem [message #237561 is a reply to message #237443] Mon, 14 May 2007 21:55 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> The part I am not understanding is how to pass the full RESULT SET, in my case 112 columns per row.
As sop_invoice_reid record type contains 89 fields and you SELECT (at least from the first one) 90 columns, I do not see where did you get 112 columns. However there is no problem, when record fields and select columns match in order and type. Your ones do not, so you have to correct it.

Another issue: what is the type of sop_invoice_reid_table (the IN OUT procedure parameter)? You mix OPEN CURSOR and SELECT INTO constructions. However you can use only one of them, either
OPEN sop_invoice_reid_table FOR SELECT <...> FROM <...>;
(for CURSOR type, eg. REF CURSOR) or
SELECT <...> BULK COLLECT INTO sop_invoice_reid_table FROM <...>;
(for collection type, eg. pack_sop_invoice_reid.sop_invoice_reid_table).
Re: FUNCTION problem [message #237671 is a reply to message #237534] Tue, 15 May 2007 07:47 Go to previous messageGo to next message
lavallee_g
Messages: 22
Registered: May 2005
Location: Quebec
Junior Member
I don't know if you have some liberty with you parameters but you could pass the table as an out parameter. See attatched file, I gave you an exemple of what you could also do...

Then, to read from this table, you could do something like

    FOR i IN NVL(p_dynamic_codes.FIRST,1)..NVL(p_dynamic_codes.LAST,0) LOOP
      cose_id := p_dynamic_codes(i).cose_id;
      .
      .
      .
    END LOOP;


I hope it can help you a little..

Genevieve
Re: FUNCTION problem [message #237765 is a reply to message #237671] Tue, 15 May 2007 11:37 Go to previous messageGo to next message
Murray Sobol
Messages: 4
Registered: May 2007
Location: Kitchener, ON
Junior Member
Thanks again for the assistance; I'm getting closer to the solution.
After implementing your suggestions, I now get the following error:
FUNCTION f_sop_invoice_reid Compiled.
LINE/COL TEXT
---------- ---------------------------------------------------------------------------
937 PLS-00103: Encountered the symbol ")" when expecting one of the following:

<an identifier> <a double-quoted delimited-identifier> LONG_
double ref nocopy char time timestamp interval date binary
national character nchar
The symbol "<an identifier>" was substituted for ")" to continue.



1 rows selected

I think that I am not specifying the IN OUT parameters correctly.
See the NEW attached file.

Thanks
Re: FUNCTION problem [message #237785 is a reply to message #237765] Tue, 15 May 2007 13:11 Go to previous message
lavallee_g
Messages: 22
Registered: May 2005
Location: Quebec
Junior Member
In the specification of your procedure add NOCOPY with your type sop_invoice_reid_table. You must use your type like any other type like varchar2 or number.

CREATE PROCEDURE f_sop_invoice_reid (av_oe_plc_id          IN varchar2,
                                           ai_oe_order_nbr       IN number,
                                           ac_laser_type         IN char,
                                           ac_include_freight    OUT char,
                                           al_null               OUT number,
                                           as_null               OUT varchar2,
                                           sop_invoice_reid_tab  IN OUT NOCOPY sop_invoice_reid_table                                           )



Plus, with the nocopy, its usualy faster. You might whant to test your queries individualy first to veryfy if they don't have any errors.

[Updated on: Tue, 15 May 2007 13:14]

Report message to a moderator

Previous Topic: Table Partition
Next Topic: HOW TO SUM , AVERAGE , COUNT Column data0 to data155 in an oracle 10 database table
Goto Forum:
  


Current Time: Sat Dec 10 22:06:48 CST 2016

Total time taken to generate the page: 0.08215 seconds