Solved

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

Posted on 2014-04-18
9
987 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

911 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now