1) passing variable to sqlplus
Variable_IN="'ABC_VAR_1','ABC_VAR_2'"
2) how to store sqlplus result to a variable back to unix variable.
v_count1=$(sqlplus -L -S sys/$passd@$PDB as sysdba <<EOF ---(for 12c)
set feed off heading off echo off verify off termout off
select count(*) from dba_tab_statistics where stale_stats = 'YES';
exit
EOF
)
3) how to pass your own variables to use in the sqlplus...
Variable_IN="'ABC_VAR_1','ABC_VAR_2'"
declare
type_p_abc_dba integer;
begin
select count(*) into type_p_abc_dba from dba_types where type_name='STORE_VAL_abc_DBA';
if (type_p_abc_dba = 0) then
execute immediate 'create type store_val_abc_dba as table of varchar2(100);';
end if;
end;
/
Variable_IN="'ABC_VAR_1','ABC_VAR_2'"
2) how to store sqlplus result to a variable back to unix variable.
v_count1=$(sqlplus -L -S sys/$passd@$PDB as sysdba <<EOF ---(for 12c)
set feed off heading off echo off verify off termout off
select count(*) from dba_tab_statistics where stale_stats = 'YES';
exit
EOF
)
3) how to pass your own variables to use in the sqlplus...
Variable_IN="'ABC_VAR_1','ABC_VAR_2'"
type_p_abc_dba integer;
begin
select count(*) into type_p_abc_dba from dba_types where type_name='STORE_VAL_abc_DBA';
if (type_p_abc_dba = 0) then
execute immediate 'create type store_val_abc_dba as table of varchar2(100);';
end if;
end;
/
declare
type pa_tablename is table of all_tables.table_name%type
index by binary_integer;
va_tablename pa_tablename;
cursor c_f is (select column_value from table(store_val_abc_dba($Variable_IN)));
BEGIN
FOR ind IN c_f
LOOP
begin
.............
.............
end;
end loop;
end;
Hope this helps.......
No comments:
Post a Comment