Home » SQL & PL/SQL » SQL & PL/SQL » get the value of FOR loop outside the loop (Oracle 11g, Win7)
get the value of FOR loop outside the loop [message #601757] Mon, 25 November 2013 05:59 Go to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
how to get the values of a FOR loop outside the loop.

I've a for loop, whose outputs are
1. DWK_MEM_TBL
2. DWK_CLASS_TBL


I want these table name to dump in 2 different variables (MEM, CLASS) outside the for loop.

I've to use these tables in the procedures seperately i.e. that is I can not use again any conditional statements to get
the tables name.

So I need to put the table names in MEM & CLASS variables.

How can I do it?

CREATE OR REPLACE PROCEDURE TAKE_REF_TBL (TOKEN VARCHAR2)
AS
  DWKQUERY LONG;
BEGIN 	   	
    DECLARE 		
		TYPE ARRAY_REF IS VARRAY(10) OF VARCHAR2(20);
		ARRAY ARRAY_REF := ARRAY_REF('MEM','CLASS'); 

	  BEGIN
		
		  FOR i IN 1..ARRAY.COUNT
		  LOOP

			DWKQUERY :=
      '  
      CREATE TABLE DWK_'||ARRAY(i)||'_TBL
      AS SELECT * FROM '||TOKEN||'_MM_'||ARRAY(i)||'      
      ';		
			  BEGIN EXECUTE IMMEDIATE DWKQUERY;
				  EXCEPTION WHEN OTHERS THEN
					  DBMS_OUTPUT.PUT_LINE('ERROR IN CREATING TABLE: '||SQLERRM);
			  END;
			  COMMIT;
		  END LOOP;
    END;
END;
/

-- EXEC TAKE_REF_TBL('TEST');
Re: get the value of FOR loop outside the loop [message #601758 is a reply to message #601757] Mon, 25 November 2013 06:08 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
so few lines & so many problems.
You should never COMMIT inside LOOP.
DDL (CREATE TABLE) does implicit COMMIT before & after DDL statement.
EXCEPTION handler does nothing but hide any error.
Procedure are designed & meant to run more than once; but posted code throws error during second & subsequent invocations.

>I want these table name to dump in 2 different variables (MEM, CLASS) outside the for loop.
"dump"? HUH?

what specific results do you desire
Re: get the value of FOR loop outside the loop [message #601760 is a reply to message #601758] Mon, 25 November 2013 06:19 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
Thanks for the advice. I am not getting any errors when running the code in SQLTools. The tables are created.

>> I want these table name to dump in 2 different variables (MEM, CLASS) outside the for loop.

I mean to say that this FOR loop creates two tables viz. DWK_MEM_TBL and DWK_CLASS_TBL.
Now outside the FOR loop, I want the values of the variables as:
MEM = DWK_MEM_TBL ;
CLASS = DWK_CLASS_TBL;

How can I do it?
Re: get the value of FOR loop outside the loop [message #601763 is a reply to message #601760] Mon, 25 November 2013 06:27 Go to previous messageGo to next message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
So assign the variable to the values you want. I really don't understand what problem you are having, especially since the values you are after appear to be hard-coded.

However I've got to agree with Blackswan's criticisms:
The exception handler does nothing useful and should be ditched, the caller will report errors without any code.
Commit in a for loop is generally a bad idea and serves no use here - create table auto commits.
Creating tables dynamically generally indicates bad schema design and should be avoided.
Re: get the value of FOR loop outside the loop [message #601766 is a reply to message #601763] Mon, 25 November 2013 06:38 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
Thaks for the advice. But I've some confusions still.
>>I really don't understand what problem you are having, especially since the values you are after appear to be hard-coded.

The values we need to assign to variables MEM and CLASS are not hardcoded but are the result of FOR loop.

My problem is, how to assign the results of the FOR loop to two seperate varables outside the FOR loop.
When the loop runs for the first time the result is DWK_MEM_TBL and in second time the result is DWK_CLASS_TBL;

I need to assign these two tables to the variables MEM & CLASS.

How can I do this?





Re: get the value of FOR loop outside the loop [message #601769 is a reply to message #601766] Mon, 25 November 2013 06:54 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
"design" is flawed & should be discarded.
what problem are you really trying to solve?
Re: get the value of FOR loop outside the loop [message #601770 is a reply to message #601766] Mon, 25 November 2013 06:56 Go to previous message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
nischalinn wrote on Mon, 25 November 2013 12:38

The values we need to assign to variables MEM and CLASS are not hardcoded but are the result of FOR loop.

And the array the loop loops over is hard-coded.
So you'll get the same results every time.
Previous Topic: split string in two halves
Next Topic: DBMS_LDAP Search filter
Goto Forum:
  


Current Time: Fri Aug 29 09:17:59 CDT 2014

Total time taken to generate the page: 0.10999 seconds