Hi,
i have some customers still running oracle 11g databases and we are planning to upgrade & migrate to 19c/21c.
I want to retrieve the application schemas details (default tablespace, temporary tablespace, profile...) depending of the version: version 12c, then i am using orcacle_maintained != 'Y' to filter. Every thing works find as single SELECT but when using a PL/SQL Block, i got the error:
SP2-0552: Bind variable "MI" not declared.
I am trying to use dynamic sql.
The idea is:
check get database version in a variable: db_version_nbr
If db_version_nbr is greater than 11
Then execute the select using oracle-maintained to filter
Else: using the list of oracle users in username not in ('SYS','SYSTEM'....)
print output
Does anyone have an idea, how to implement this?
Regards
Ousseini Ouamoru
Here is my the script:
SYS@K11UVAVE> !cat check_version_plsql-5.sql
set serveroutput on
col USERNAME format a30
col ACCOUNT_STATUS for a20
col DEFAULT_TABLESPACE for a20
col TEMPORARY_TABLESPACE for a15
col PROFILE format a20
col CREATED format a25
col CREATED_AT format a25
declare
vbuild varchar2(4000);
vtext varchar2(100);
db_version_nbr PLS_INTEGER;
BEGIN
select substr(version,1,2) INTO db_version_nbr from v$instance;
IF( db_version_nbr > 11 )
THEN
EXECUTE IMMEDIATE
'select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,ACCOUNT_STATUS,PROFILE,to_char(CREATED, 'dd.mm.yyyy hh24:mi:ss') created_at
from dba_users
where oracle_maintained != 'Y'
order by 1 ' INTO vbuild;
ELSE
EXECUTE IMMEDIATE
'select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,ACCOUNT_STATUS,PROFILE,to_char(CREATED, 'dd.mm.yyyy hh24:mi:ss') created_at
from dba_users
where username not in ('ANONYMOUS','APPQOSSYS','AUDSYS','DBSFWUSER','DIP','DBSNMP','GGSYS','GSMADMIN_INTERNAL','GSMCATUSER','GSMUSER','ORACLE_OCM','OUTLN','REMOTE_SCHEDULER_AGENT',
'SYS','SYSBACKUP','SYSDG','SYSKM','SYSTEM','SYSRAC','SYS$UMF','WMSYS','XDB','XS$NULL')
order by 1 ' into vbuild;
END IF;
DBMS_OUTPUT.PUT_LINE('Listing non oracle users/Applications Schemas: ' || vbuild);
END;
/
set serveroutput off
SYS@K11UVAVE> @check_version_plsql-5.sql
SP2-0552: Bind variable "MI" not declared.
SYS@K11UVAVE>
But Checking the version works fine:
SYS@K11UVAVE> !cat check_version_plsql.sql
set serveroutput on
col USERNAME format a30
col ACCOUNT_STATUS for a20
col DEFAULT_TABLESPACE for a20
col TEMPORARY_TABLESPACE for a15
col PROFILE format a20
col CREATED format a25
col CREATED_AT format a25
declare
vbuild varchar2(4000);
vtext varchar2(100);
db_version_nbr int;
BEGIN
select substr(version,1,2) INTO db_version_nbr
from v$instance;
DBMS_OUTPUT.PUT_LINE('Listing oracle database version: ' || db_version_nbr);
END;
/
SYS@K11UVAVE> @check_version_plsql.sql
Listing oracle database version: 11
PL/SQL procedure successfully completed.
SYS@K11UVAVE>