Home » SQL & PL/SQL » SQL & PL/SQL » cursor with parameter
cursor with parameter [message #283121] Mon, 26 November 2007 03:01 Go to next message
senganga
Messages: 15
Registered: November 2007
Location: South Africa
Junior Member

Hi, Please help me!!!! I want to return a record from my cursor and I am getting the following error Error(9,58): PLS-00201: identifier 'LOAD_TBL' must be declared

This is my function...

CREATE OR REPLACE
FUNCTION FIND_LOAD (p_route_in IN VARCHAR2,
--p_load_rec OUT load_tbl%ROWTYPE
p_Bundles OUT NUMBER,
p_Loose_copies OUT NUMBER,
p_Label_supply OUT NUMBER,
p_Bundles_Pack OUT NUMBER,
p_Roll_pack OUT NUMBER,
p_Number_of_label OUT NUMBER) RETURN load_tbl%ROWTYPE
IS
v_load_rec load_tbl%ROWTYPE;
l_Loose_copies number;
l_Label_supply number;
l_Bundles_Pack number;
l_Roll_pack number;
l_Number_of_label number;

CURSOR load_cur RETURN load_tbl%ROWTYPE
is
select *
from
oe_order_lines_all load_tbl;

BEGIN
OPEN load_cur;
FETCH load_cur INTO v_load_rec;

CLOSE load_cur;
RETURN v_load_rec;

END;
Re: cursor with parameter [message #283129 is a reply to message #283121] Mon, 26 November 2007 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First,
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Then,
Always copy and paste the execution not just describe it.

Finally, I think the message is clear "LOAD_TBL" does not exist or is not accessible to you.

Regards
Michel

Re: cursor with parameter [message #283139 is a reply to message #283121] Mon, 26 November 2007 03:28 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Are you referring to the table load_tbl Or oe_order_lines_all on saying 'load_tbl%ROWTYPE' ?


Thumbs Up
Rajuvan.

[Updated on: Mon, 26 November 2007 03:29]

Report message to a moderator

Re: cursor with parameter [message #283172 is a reply to message #283139] Mon, 26 November 2007 05:03 Go to previous messageGo to next message
senganga
Messages: 15
Registered: November 2007
Location: South Africa
Junior Member

I have decided to use an alius to refere to order_lines_all

The reason I did that is because in my cursor select, I have create a table on my from statement using select this is how it realy looks like...

 
select *
from      
      xxpub_pkg_size_spec_v xps,
    (select ffv.flex_value_id   publication_id
      ,ffv.flex_value      publication_code
      ,ffvt.description    pub_description
from   fnd_flex_value_sets ffvs
      ,fnd_flex_values     ffv
      ,fnd_flex_values_tl  ffvt
where  ffvs.flex_value_set_name = 'M24_INV_Item_Code'
and    ffvs.flex_value_set_id = ffv.flex_value_set_id
and    ffv.flex_value_id = ffvt.flex_value_id
and    ffvt.language = 'US'
and    0 < (
             select count(1)
             from   mtl_system_items_b 
             where  organization_id = (select organization_id
                                       from   mtl_parameters
                                       where  organization_id = master_organization_id
                                       and    rownum < 2)
             and    segment1  = ffv.flex_value
             and    item_type = 'NW'
           )) load_tbl
WHERE
       load_tbl.publication_id = xps.publication_id
      
Re: cursor with parameter [message #283188 is a reply to message #283172] Mon, 26 November 2007 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The alias is only known inside the query and not outside it (how could it be?).

Regards
Michel
Re: cursor with parameter [message #283199 is a reply to message #283188] Mon, 26 November 2007 06:19 Go to previous messageGo to next message
senganga
Messages: 15
Registered: November 2007
Location: South Africa
Junior Member

is there other ways that I can use to reference a table type in this regard?
Re: cursor with parameter [message #283207 is a reply to message #283199] Mon, 26 November 2007 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use the real table name, not an alias.
SQL> create or replace function f return t%rowtype is 
  2   rec t%rowtype;
  3  begin
  4   return rec;
  5  end;
  6  /

Function created.


Regards
Michel
Re: cursor with parameter [message #283208 is a reply to message #283121] Mon, 26 November 2007 06:30 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Yes .

In case of Multi table Select in cursor you can use Cursor%rowtype using package

Thumbs Up
Rajuvan.
Previous Topic: Complete tree
Next Topic: CREATE A TRIGGER
Goto Forum:
  


Current Time: Sat Dec 03 14:18:38 CST 2016

Total time taken to generate the page: 0.11611 seconds