Solved

How to use variable name inside the PL/SQL loop?

Posted on 2014-04-18
9
994 Views
Last Modified: 2014-04-21
I am trying to compile below PL/SQL code but keep getting below syntax error

" ORA-00942 (52: 12): PL/SQL: ORA-00942: table or view does not exist"

code is following:

CREATE OR REPLACE PROCEDURE ADWSTG.DLY_CALL_SRV_PRC AS

--define variables
g_stmt                varchar2(2000);
v_tbl                   all_tab_columns.default_length%type;
v_tbl1                 all_tab_columns.table_name%type;
v_err_code          number;
v_err_msg           varchar2(1000);
i                         int;


--define cursor
    cursor c_survey is
        select surveyid
        from surveys@genip
        where upper(trim(name)) not like '%TEST%'
        and trim(description) is not null
        order by surveyid;
       
    c_survey_var                  c_survey%rowtype;
   
   
begin

    DBMS_OUTPUT.PUT_LINE('Extracting data from GENSURVEY@GENIP environment' );
    DBMS_OUTPUT.PUT_LINE('CONTACTS_X, ATTRIBUTES_X, RESPONSES_X will be staged in  ADWSTG@ADWPRD environment' );
   
     g_stmt := 'truncate table adwstg.tmp_contact_stg';
     execute immediate (g_stmt);
   
    i := 0;
    open c_survey;
     fetch c_survey into c_survey_var;
      while c_survey%found loop
       
      v_tbl := c_survey_var.surveyid;
      v_tbl1 := 'contacts_'||to_char(v_tbl)||'@genip';
       
      insert into adwstg.tmp_contact_stg (survey_id, contact_id, identification, type, status,    duration, datetime)
      select c_survey_var.surveyid, contactid, identification, type, status, duration, datetime
      from v_tbl1;
         
      fetch c_survey into c_survey_var;    
      end loop;
     
      commit;    
   
    close c_survey;
   
END DLY_CALL_SRV_PRC;
/

Basically, above code should extract data from CONTACTS_8, CONTACTS_9, CONTACTS_10 and so on tables from GENIP environment and load into ADWSTG.TMP_CONTACT_STG table in a ADWPRD environment. But the compiler is complaining about below sql statement as table "v_tbl1" doesn't exist in GENIP environment.

 insert into adwstg.tmp_contact_stg (survey_id, contact_id, identification, type, status, duration, datetime)
 select c_survey_var.surveyid, contactid, identification, type, status, duration, datetime
 from v_tbl1;

Please advise how I resolve this issue.
0
Comment
Question by:IT_ETL
  • 5
  • 3
9 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40009340
>>c_survey_var                  c_survey%rowtype;

I don't think you can do this.

See if this test case helps you:
drop table tab1 purge;
create table tab1(
	col1 char(1),
	col2 char(1)
);

insert into tab1 values('a','1');
insert into tab1 values('b','2');
commit;


declare
	cursor c_survey is
		select col1, col2 from tab1;

	v_col1 char(1);
	v_col2 char(1);
begin

	open c_survey;
	fetch c_survey into v_col1, v_col2;
	while c_survey%found loop
		dbms_output.put_line('Got: ' || v_col1 || ' and ' || v_col2);
		fetch c_survey into v_col1, v_col2;
	end loop;
end;
/

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40009354
>> datetime from v_tbl1;

You definitely cannot do this.

You need to use dynamic SQL to have a variable name be used as an object name.

Here is an updated test case:
drop table tab1 purge;
create table tab1(
	col1 char(1),
	col2 char(1)
);


drop table tab2 purge;
create table tab2(
	col1 char(1),
	col2 char(1)
);


insert into tab1 values('a','1');
insert into tab1 values('b','2');
commit;


declare
	cursor c_survey is
		select col1, col2 from tab1;

	v_col1 char(1);
	v_col2 char(1);

	v_tab2 varchar2(10) := 'tab2';
begin

	open c_survey;
	fetch c_survey into v_col1, v_col2;
	while c_survey%found loop
		dbms_output.put_line('Got: ' || v_col1 || ' and ' || v_col2);
		execute immediate 'insert into ' || v_tab2  || ' values(:x, :y) ' using v_col1, v_col2;
		fetch c_survey into v_col1, v_col2;
	end loop;
end;
/


select * from tab2;

Open in new window

0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 40009372
Once you declare a variable to hold the survey_id you code should look something like:

execute immediate '   insert into adwstg.tmp_contact_stg (survey_id, contact_id, identification, type, status,    duration, datetime)
      select :mysurveyvar, contactid, identification, type, status, duration, datetime
      from ' || v_tbl1 using v_mysurveyvar;
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 32

Assisted Solution

by:awking00
awking00 earned 250 total points
ID: 40009506
As stated, you can't do this -
>>select c_survey_var.surveyid, contactid, identification, type, status, duration, datetime
      from v_tbl1;<<

You can do something like this -
sql_stmt := 'select '||c_survey_var.surveyid||' , contactid, identification, type, status, duration, datetime from '||v_tbl1;
execute immediate sql_stmt; ==> note this will have to be selected into something like a ref cursor
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40009741
>>You can do something like this -

???  Isn't that pretty much what I posted above with the insert statement?

I don't like concatenating columns.  Better to use the bind variable.  Saves a parse.
0
 

Author Comment

by:IT_ETL
ID: 40013512
Slightwv,

'c_survey_var                  c_survey%rowtype;

I don't think you can do this."

Yes, you can do this, you won't get any syntax error.

Basically, I got a syntax error in the above code. As both of you suggested, use execute immediate statement will work fine in this case as long as no syntax error, for example

g_stmt := 'insert into adwstg.tmp_contact_stg (survey_id, contact_id, identification, type, status, duration, datetime)
                      select '|| c_survey_var.surveyid||', contactid, identification, type, status, duration, datetime from '||v_tbl1;  --this was causing all the problems
     
      execute immediate (g_stmt);
      commit;
0
 

Author Comment

by:IT_ETL
ID: 40013519
select '|| c_survey_var.surveyid||', contactid, identification, type, status, duration, datetime from '||v_tbl1;  --this was causing all the problems v_tbl1 as this variable should be outside of the single quote
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40013537
>>select '|| c_survey_var.surveyid||', contactid

I strongly encourage you to use bind variables with a USING on the execute immediate.  It is just a good habit to get into doing.
0
 

Author Comment

by:IT_ETL
ID: 40013545
Sure, Thanks Slightwv.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question