[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2014-04-18
9
Medium Priority
?
999 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

656 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