Home » SQL & PL/SQL » SQL & PL/SQL » Table type error (oracle application , windows)
Table type error [message #342625] Sat, 23 August 2008 06:04 Go to next message
sudharshan
Messages: 48
Registered: November 2006
Member
Can someone throw light on how to solve this probelm


CREATE OR REPLACE PROCEDURE get_attachment_notes (
p_order_header_id IN NUMBER,
p_order_line_id IN NUMBER,
p_order_notes OUT order_tab,
p_ret_code OUT NUMBER,
p_ret_msg OUT VARCHAR2
)
IS
TYPE order_table_type IS TABLE OF VARCHAR2 (4000)
INDEX BY BINARY_INTEGER;

order_tab order_table_type;

CURSOR c1 (p_order_id IN NUMBER)
IS
SELECT fdc.NAME, fds.short_text, NULL long_text
FROM fnd_attached_documents fad,
fnd_documents fd,
fnd_document_categories_tl fdc,
fnd_document_datatypes fdd,
fnd_documents_tl fdl,
fnd_documents_short_text fds
WHERE entity_name IN ('OE_ORDER_HEADERS', 'OE_ORDER_LINES')
AND fad.pk1_value = TO_CHAR (p_order_id)
AND fdd.NAME = ('SHORT_TEXT')
AND fdl.LANGUAGE = 'US'
AND fad.document_id = fd.document_id
AND fdc.category_id = fd.category_id
AND fdd.datatype_id = fd.datatype_id
AND fdl.document_id = fd.document_id
AND fds.media_id(+) = fdl.media_id
UNION ALL
SELECT fdc.NAME, NULL short_text, fdlt.long_text
FROM fnd_attached_documents fad,
fnd_documents fd,
fnd_document_categories_tl fdc,
fnd_document_datatypes fdd,
fnd_documents_tl fdl,
fnd_documents_long_text fdlt
WHERE entity_name IN ('OE_ORDER_HEADERS', 'OE_ORDER_LINES')
AND fad.pk1_value = TO_CHAR (p_order_id)
AND fdd.NAME IN ('LONG_TEXT')
AND fdl.LANGUAGE = 'US'
AND fad.document_id = fd.document_id
AND fdc.category_id = fd.category_id
AND fdd.datatype_id = fd.datatype_id
AND fdl.document_id = fd.document_id
AND fdlt.media_id(+) = fdl.media_id;

v_count NUMBER := 0;
l_order_tab order_tab;
BEGIN
p_ret_code := 0;
p_ret_msg := 'SUCCESS';

IF p_order_header_id IS NOT NULL
THEN
FOR my_rec IN c1 (p_order_header_id)
LOOP
IF my_rec.long_text IS NOT NULL
THEN
v_count := v_count + 1;
l_order_tab (v_count) := my_rec.long_text;
END IF;

IF my_rec.short_text IS NOT NULL
THEN
v_count := v_count + 1;
l_order_tab (v_count) := my_rec.short_text;
END IF;
END LOOP;
END IF;

IF p_order_line_id IS NOT NULL
THEN
FOR my_rec IN c1 (p_order_line_id)
LOOP
IF my_rec.long_text IS NOT NULL
THEN
v_count := v_count + 1;
l_order_tab (v_count) := my_rec.long_text;
END IF;

IF my_rec.short_text IS NOT NULL
THEN
v_count := v_count + 1;
l_order_tab (v_count) := my_rec.short_text;
END IF;
END LOOP;
END IF;

p_order_notes := l_order_tab;
EXCEPTION
WHEN OTHERS
THEN
p_ret_code := SQLERRM;
p_ret_msg := SUBSTR (SQLERRM, 1, 4000);
RETURN;
END get_attachment_notes;


error:
PLS-00201: identifier 'ORDER_TAB' must be declared


Regards

sudharshan


Re: Table type error [message #342627 is a reply to message #342625] Sat, 23 August 2008 06:25 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> Can someone throw light on how to solve this probelm
> error:
> PLS-00201: identifier 'ORDER_TAB' must be declared

CREATE OR REPLACE PROCEDURE get_attachment_notes (
p_order_header_id IN NUMBER,
p_order_line_id IN NUMBER,
p_order_notes OUT ORDER_TAB,--occurence 1
p_ret_code OUT NUMBER,
p_ret_msg OUT VARCHAR2
)
IS
TYPE order_table_type IS TABLE OF VARCHAR2 (4000)
INDEX BY BINARY_INTEGER;

ORDER_TAB order_table_type;--occurence 2

CURSOR c1 (p_order_id IN NUMBER)
IS
SELECT <...>

v_count NUMBER := 0;
l_order_tab ORDER_TAB;--occurence 3

Is not the error self-explanatory?
Do you have ORDER_TAB type (as it is referenced in occurences 1 and 3).
By the way, what is the meaning of variable ORDER_TAB (in occurence 2), as it is never used further?
Re: Table type error [message #342632 is a reply to message #342625] Sat, 23 August 2008 07:10 Go to previous message
sudharshan
Messages: 48
Registered: November 2006
Member
thanks a lot

i have understood the problem


regards

sudharshan
Previous Topic: Problem in creating abcdic format file
Next Topic: Difference between self join and natural join
Goto Forum:
  


Current Time: Mon Dec 05 10:57:52 CST 2016

Total time taken to generate the page: 0.09990 seconds