IT_ETL
asked on
How to use variable name inside the PL/SQL loop?
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_le ngth%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('Extr acting data from GENSURVEY@GENIP environment' );
DBMS_OUTPUT.PUT_LINE('CONT ACTS_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.
" 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_le
v_tbl1 all_tab_columns.table_name
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('Extr
DBMS_OUTPUT.PUT_LINE('CONT
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
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.
>> 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:
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>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.
??? 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.
ASKER
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;
'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;
ASKER
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
>>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.
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.
ASKER
Sure, Thanks Slightwv.
I don't think you can do this.
See if this test case helps you:
Open in new window