?
Solved

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

Posted on 2014-04-18
9
Medium Priority
?
998 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 77

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 77

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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Assisted Solution

by:awking00
awking00 earned 1000 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 77

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 77

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

777 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