-
Can you use a variable as a name for Nested Table or an item in a Collections?
I'm trying to find a way to use a variable so I can use a loop.
This environment do not have 'create' permission in so that limits some of my choices.
I've included the error message in the code sample below with the line that errors marked.
Oracle Database 10g
PL/SQL 8.0.0.1480
Code:
DECLARE
-- Declare the nested table, its data type and max number of characters
TYPE MyNestedTable IS TABLE of varchar2(100);
-- Declare the 'columns' in the nested table
MyCol_1 MyNestedTable;
MyCol_2 MyNestedTable;
v_MyCol_n VARCHAR2(200);
v_LoopCounter number;
BEGIN
-- Initialize the 'columns' that are in the nested table and set their starting value to null
MyCol_1 := MyNestedTable();
MyCol_2 := MyNestedTable();
--************
--* Instead of this 'hard coded' value I would like a variable for use with a loop
--************
MyCol_1.EXTEND;
MyCol_1.EXTEND;
MyCol_1(1) := 'abc';
--For Testing
--DBMS_OUTPUT.PUT_LINE('MyCol_1 '||MyCol_1(1));
--************
--* This 'variable' method with the loop counter does not work
--************
v_LoopCounter:=1;
v_MyCol_n := 'MyCol_' ||v_LoopCounter;
execute Immediate (v_MyCol_n);
v_MyCol_n := .EXTEND; --*** <-- ERRORS ON THIS LINE ***
v_MyCol_n (v_LoopCounter) := 'abc';
--For Testing
DBMS_OUTPUT.PUT_LINE('v_MyCol_n '||v_MyCol_n (v_LoopCounter));
--************
--* The Error message received on the above code is:
--* ORA-06550: line 34, column 22:
--* PLS-00103: Encountered the symbol “.” When expecting on of the following:…
--************
MyCol_2.EXTEND;
MyCol_2(1) := 200;
--For Testing
--DBMS_OUTPUT.PUT_LINE('MyCol_2 '||MyCol_2(1));
-- Review results
DBMS_OUTPUT.PUT_LINE('MyCol_1 '|| ', '||'MyCol_2 ');
DBMS_OUTPUT.PUT_LINE(MyCol_1(1)|| ', '||MyCol_2(1));
END;
Thanks for any help.
Tom
-
PL/SQL does not know where to find .extend, it is a method on an object not a stand alone function.
From looking at this it isn't very clear where your data is coming from or what you are doing with it. If you really want to use a PL/SQL collection you need to define a rowtype if it is something other than a table, then you define a collection, then you select col1, col2, etc bulk collect into Then you can iterate through the collection and do what ever it is that you are trying to do. This isn't Java, you can't define a with block and have it implied that you are referencing a particular object.
IMHO, you need to explain what you are ultimately trying to accomplish.
-
@gandolf989
Thanks for taking a look at this.
In this beta code I’m trying to work out how to use a concatenated variable with the ‘root base’ of a field name and then take an action on that concatenated value.
The full code for the project is not shown here since building the concatenation and then taking an action on that concatenation is the part I’m stuck on.
What this sample beta code does is building the nested table, add some ‘fields’ to it and then populating those fields with values hard coded in this sample code.
By way of some back ground, this ‘hard coded’ part works:
Code:
--************
--* Instead of this 'hard coded' value for a field name I would like a variable for use with a loop
--* IMPORTANT:
--* This method below works and is included as an example of what I'd like
--* to change to a variable, i.e. the column name concatenated to a number
--************
MyCol_1.EXTEND;
MyCol_1(1) := 'abc';
--For Testing
--DBMS_OUTPUT.PUT_LINE('MyCol_1 '||MyCol_1(1));
I’ve tried (many) different version of this code, but still don’t have it working yet.
To address the issue that you’ve pointed out about ‘.extend’ I been trying to use the EXEC() and/or EXECUTE() as in this sample, but I don't have it worked out yet. Any thoughts on how to fix this?
Code:
--************
--* This new 'variable' method with the loop counter concatenated to 'MyCol_' does not work
--************
v_LoopCounter:=1;
v_MyTestCode := 'MyCol_' || v_LoopCounter || '.EXTEND' ;
--For Testing
--DBMS_OUTPUT.PUT_LINE('v_MyTestCode '||v_MyTestCode);
EXEC(v_MyTestCode) ;
v_SomeValue := 'abc;';
v_MyTestCode := 'MyCol_' || v_LoopCounter || ':= '||v_SomeValue;
--For Testing
--DBMS_OUTPUT.PUT_LINE('v_MyTestCode '||v_MyTestCode);
EXEC(v_MyTestCode) ;
Here is the full code since I’ve added some new variables to try and get this to work.
Code:
DECLARE
-- Declare the nested table, its data type and max number of characters
TYPE MyNestedTable IS TABLE of varchar2(100);
-- Declare the 'columns' in the nested table
MyCol_1 MyNestedTable;
MyCol_2 MyNestedTable;
v_MyCol_n VARCHAR2(200);
v_LoopCounter NUMBER;
v_MyTestCode VARCHAR2(200);
v_SomeValue VARCHAR2(200);
BEGIN
-- Initialize the 'columns' that are in the nested table and set their starting value to null
MyCol_1 := MyNestedTable();
MyCol_2 := MyNestedTable();
--************
--* Instead of this 'hard coded' value I would like a variable for use with a loop
--* IMPORTANT:
--* This method below works and is included as an example of what I'd like
--* to change to a variable, i.e. the column name concatenated to a number
--************
MyCol_1.EXTEND;
MyCol_1(1) := 'abc';
--For Testing
--DBMS_OUTPUT.PUT_LINE('MyCol_1 '||MyCol_1(1));
--************
--* This new 'variable' method with the loop counter concatenated to 'MyCol_' does not work
--************
v_LoopCounter:=1;
v_MyTestCode := 'MyCol_' || v_LoopCounter || '.EXTEND' ;
--For Testing
--DBMS_OUTPUT.PUT_LINE('v_MyTestCode '||v_MyTestCode);
EXEC(v_MyTestCode) ;
v_SomeValue := 'abc;';
v_MyTestCode := 'MyCol_' || v_LoopCounter || ':= '||v_SomeValue;
--For Testing
--DBMS_OUTPUT.PUT_LINE('v_MyTestCode '||v_MyTestCode);
EXEC(v_MyTestCode) ;
MyCol_2.EXTEND;
MyCol_2(1) := 200;
--For Testing
--DBMS_OUTPUT.PUT_LINE('MyCol_2 '||MyCol_2(1));
-- Review results
DBMS_OUTPUT.PUT_LINE('MyCol_1 '|| ', '||'MyCol_2 ');
DBMS_OUTPUT.PUT_LINE(MyCol_1(1)|| ', '||MyCol_2(1));
END;
Thanks again for taking a look at this.
Tom
-
I'm still not sure what you are trying to get, but I suspect that you can get
what you want with dynamic SQL based on the dba_tab_columns table.
It just seems like you are making this way too complex.
Code:
SQL> select 'mycol_'||rownum||'_'||column_name
2 from dba_tab_columns
3 where owner='DBSNMP' and table_name='BSLN_STATISTICS'
4 order by column_id;
'MYCOL_'||ROWNUM||'_'||COLUMN_NAME
----------------------------------------------------------
mycol_1_BSLN_GUID
mycol_2_METRIC_ID
mycol_3_COMPUTE_DATE
mycol_4_TIMEGROUPING
mycol_5_TIMEGROUP
mycol_6_SAMPLE_COUNT
mycol_7_AVERAGE
mycol_8_MINIMUM
mycol_9_MAXIMUM
mycol_10_SDEV
mycol_11_PCTILE_25
mycol_12_PCTILE_50
mycol_13_PCTILE_75
mycol_14_PCTILE_90
mycol_15_PCTILE_95
mycol_16_PCTILE_99
mycol_17_EST_SAMPLE_COUNT
mycol_18_EST_SLOPE
mycol_19_EST_INTERCEPT
mycol_20_EST_FIT_QUALITY
mycol_21_EST_PCTILE_999
mycol_22_EST_PCTILE_9999
22 rows selected.
Elapsed: 00:00:00.12
-
@gandolf989 – Thanks for the help and insight.
I’ll rework the logic and code to try and find someway through this.
Thanks again.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|