try to check the instance&#x27;s status

doesn't work how?

error?
no result?
wrong result?
keyboard catches fire?

v`$instance; --- incorrect view name

v`$database; --- incorrect view name

remove the apostrophe from both

Define 'doesn't work'.

>>v`$instance;

What is that tic mark between the v and $?

ASKER

v`$instance : syntaxe on powershell script.
The error is sys.dbms_utility.is_cluster_database;
*
ERROR at line 11:
ORA-06550: line 11, column 1:
PLS-00201: identifier 'SYS.DBMS_UTILITY' must be declared
ORA-06550: line 11, column 1:
PL/SQL: Statement ignored

also sys.DBMS_UTILITY.is_cluster_database is a function. not a procedure. You need to do something with the boolean return code

maybe you meant to do something like this...

IF v_status != 'MOUNTED' AND sys.DBMS_UTILITY.is_cluster_database
THEN

but I'm not sure what sort of output you're looking for since you concatenate the same value (v_db) three times

ASKER

I have always tested this syntax :
if v_status!='MOUNTED' AND sys.DBMS_UTILITY.is_cluster_database
*
ERROR at line 10:
ORA-06550: line 10, column 28:
PLS-00201: identifier 'SYS.DBMS_UTILITY' must be declared
ORA-06550: line 10, column 1:
PL/SQL: Statement ignored
Thanks
bibi

what is your instance status?

rather than checking for "not mounted", why not check for = 'OPEN'
or if you want to include OPEN MIGRATE
LIKE 'OPEN%' ?

ASKER

The instance status is mounted. Same error with 'OPEN%'
if v_status!='MOUNTED' AND sys.DBMS_UTILITY.is_cluster_database.
Maybe, there is a problem with select status into v_status from v`$instance;
Thanks
bibi

>>Maybe, there is a problem with select status into v_status from v`$instance;

First posts: remove the character between v and $

it is v$instance.

ASKER

v`$instance is the syntax on powershell script, the result is the same if I execute the script with sqlplus

I just ran the following against a 10.2.0.3 database mounted but not open using sqlplus.

What isn't 'working' for you?

declare
v_ins varchar2(200);
v_db varchar2(200);
v_status varchar2(200);
begin
select instance_name into v_ins from v$instance;
select name into v_db from v$database;
:v_key:=lower(v_ins||'.'||v_db);

select status into v_status from v$instance;

dbms_output.put_line('Instance:' || v_ins);
dbms_output.put_line('Name:' || v_db);
dbms_output.put_line('Status:' || v_status);

end;
/

Open in new window

>>>> The instance status is mounted.

you can't invoke dbms_utility while mounted

it must be open

bibi92,

Based on your previous question I understand what you are trying to do, unfortunately you cannot.

The PL/SQL gets parsed and checked for proper syntax. Therefore dbms_utility must be accessible when the code is parsed. The IF statement will not keep it from being checked at execution.

ASKER

So it isn't possible to check the status for not execute the package dbms_utility.
is it possible to check if it's a cluster db when the instance is mounted.
Thanks

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial