Solved

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

Posted on 2014-04-18
9
984 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 31

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

746 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

11 Experts available now in Live!

Get 1:1 Help Now