Thursday, July 7, 2016

Unix Variable to sqlplus and sqplus to UNIX different scenarios

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;
/

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

PostgreSql: Useful Commands-

 1)   ************************* Users ***************************  -- List of users with roles assigned: SELECT usename AS role_name,   CASE...