Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00201: identifier 'ORDERSTOREDATA' must be declared (merged)
PLS-00201: identifier 'ORDERSTOREDATA' must be declared (merged) [message #203691] Thu, 16 November 2006 01:08 Go to next message
kishan_nag
Messages: 19
Registered: June 2005
Location: Mumbai
Junior Member

Hi All,

Can u pls let me know what is the problem in below code.There are no errors while compiling, but I am getting below error while executing..

ERROR at line 1:
ORA-06550: line 1, column 297:
PLS-00201: identifier 'ORDERSTOREDATA' must be declare
ORA-06550: line 1, column 297:
PL/SQL: Statement ignored
ORA-06512: at "FLOWUSER.TEST_FETCH_REF_CUR", line 36
ORA-06512: at line 1

Code starts here...

CREATE OR REPLACE PROCEDURE test_fetch_ref_cur (p_query VARCHAR2 :=
NULL) IS
l_statement VARCHAR2 (32000);
orderstoredata FMOrders.v_ordersdata%TYPE


FUNCTION process_def RETURN VARCHAR2 IS
l_process_def VARCHAR2 (32000);
BEGIN

l_process_def := 'DBMS_OUTPUT.PUT_LINE (';

FOR i IN 1 .. dyn_fetch.g_count LOOP
l_process_def := l_process_def || ' l_record.col_' || i || ' || ''>>'' || ';
END LOOP;

l_process_def := RTRIM (l_process_def, ' || ''>>'' || ') || ');';
RETURN l_process_def;

END;
BEGIN
dbms_output.enable(100000);
test.set_query (p_query);
dyn_fetch.describe_columns;

--test.get_detail(dyn_fetch.record_def);

l_statement :=
' DECLARE' ||
' TYPE record_t IS RECORD (' ||
dyn_fetch.record_def || ');' ||
' l_record record_t;' ||
' l_ref_cur dyn_fetch.ref_cur_t;' ||

' BEGIN' ||
' l_ref_cur := test.ref_cur;' ||
' LOOP' ||
' FETCH l_ref_cur INTO l_record;' ||
' EXIT WHEN l_ref_cur%NOTFOUND;' ||
' END LOOP;' ||
' CLOSE l_ref_cur;' ||
' orderstoredata(1) := l_record.col_1;' ||
--'DBMS_OUTPUT.PUT_LINE(l_record.col_1);' ||
' END;';

EXECUTE IMMEDIATE l_statement;

--DBMS_OUTPUT.PUT_LINE(orderstoredata(1));
END;


/
Re: PLS-00201: identifier 'ORDERSTOREDATA' must be declared [message #203711 is a reply to message #203691] Thu, 16 November 2006 02:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In the dynamic SQL that you are executing, you refer to OrderStoreData, but no variable of that name is defined in the dynamic SQL.
The code executed by the Execute Immediate execute in it's own scope, and everything not defined explicitly as part of the NDS or passed in as a bind variable will be out of scope.
Re: PLS-00201: identifier 'ORDERSTOREDATA' must be declared [message #203741 is a reply to message #203711] Thu, 16 November 2006 03:14 Go to previous messageGo to next message
kishan_nag
Messages: 19
Registered: June 2005
Location: Mumbai
Junior Member

Thanks for your help.

Pls let me know that Is there any way to store the values of l_record, b'ce we need the values of l_record outside of the loop.

It would be helpful if you can able to give some example.

Thanks,
Ram
Problem in storing values out of loop [message #203923 is a reply to message #203691] Thu, 16 November 2006 23:41 Go to previous messageGo to next message
kishan_nag
Messages: 19
Registered: June 2005
Location: Mumbai
Junior Member

Hi All,

I want to print the values of l_record after EXECUTE IMMEDIATE l_statement. Can u pls some can guide me for this problem.


Thanks in adv..

Regards,
Ram

Code starts here

CREATE OR REPLACE PROCEDURE test_fetch_ref_cur (p_query VARCHAR2 :=
NULL) IS
l_statement VARCHAR2 (32000);
orderstoredata FMOrders.v_ordersdata%TYPE


FUNCTION process_def RETURN VARCHAR2 IS
l_process_def VARCHAR2 (32000);
BEGIN

l_process_def := 'DBMS_OUTPUT.PUT_LINE (';

FOR i IN 1 .. dyn_fetch.g_count LOOP
l_process_def := l_process_def || ' l_record.col_' || i || ' || ''>>'' || ';
END LOOP;

l_process_def := RTRIM (l_process_def, ' || ''>>'' || ') || ');';
RETURN l_process_def;

END;
BEGIN
dbms_output.enable(100000);
test.set_query (p_query);
dyn_fetch.describe_columns;

--test.get_detail(dyn_fetch.record_def);

l_statement :=
' DECLARE' ||
' TYPE record_t IS RECORD (' ||
dyn_fetch.record_def || ');' ||
' l_record record_t;' ||
' l_ref_cur dyn_fetch.ref_cur_t;' ||

' BEGIN' ||
' l_ref_cur := test.ref_cur;' ||
' LOOP' ||
' FETCH l_ref_cur INTO l_record;' ||
' EXIT WHEN l_ref_cur%NOTFOUND;' ||
' END LOOP;' ||
' CLOSE l_ref_cur;' ||
' orderstoredata(1) := l_record.col_1;' ||
--'DBMS_OUTPUT.PUT_LINE(l_record.col_1);' ||
' END;';

EXECUTE IMMEDIATE l_statement;

--DBMS_OUTPUT.PUT_LINE(orderstoredata(1));
END;




Re: Problem in storing values out of loop [message #203934 is a reply to message #203923] Fri, 17 November 2006 00:21 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Your second topic covers the same problem as previous one, so I merged them.

Please, do not open new ones for the same problem.
Previous Topic: how can increment one record in cursor
Next Topic: Analyzing Tables to Improve performance.
Goto Forum:
  


Current Time: Tue Dec 06 12:05:39 CST 2016

Total time taken to generate the page: 0.09939 seconds