we are trying to run a SQL script that will give us some database information that we can then put into our next piece. It will take the results of this query from our inventory table, and then query databases on the server vi gv$services to make sure what is running matches our inventory. However, we are having trouble getting the output we’d like to start with before proceeding further. We are trying this via sqlplus on Oracle 19c databases.
We tried this as a test to assign the variable vThisDB for example:
BEGIN
FOR mydb in (
SELECT DISTINCT db_name, hostname, service_name FROM inventory
)
LOOP
mydb.db_name := vThisDB;
DBMS_OUTPUT.PUT_LINE('current DB is: ' || vThisDB);
END LOOP;
END;
/
The result we are getting is this:
SQL> set serveroutput on size unlimited
SQL> @services.sql
current DB is:
It is blank, when what we want is:
current DB is: TheDatabaseName (which is the db_name column of the above query)
We think it has to do with the fact that the query is pulling 3 columns, which are necessary to plug into our next piece. How can we either get all 3 columns to work at once or how can we output the query results into separate variables? Thank you!
Output is empty because you did it vice versa.
Sample inventory table:
SQL> select * From inventory;
DB_N HOSTNAME SERV
---- --------- ----
ORCL MY_SERVER ORCL
Your script, fixed (added local variable declaration, missing right parenthesis and line #7 which shows what you should have done):
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 vThisDB inventory.db_name%type;
3 BEGIN
4 FOR mydb in (SELECT DISTINCT db_name, hostname, service_name FROM inventory)
5 LOOP
6 -- mydb.db_name := vThisDB; --> not like this,
7 vThisDB := mydb.db_name; --> but like this
8 DBMS_OUTPUT.PUT_LINE('current DB is: ' || vThisDB);
9 END LOOP;
10 END;
11 /
current DB is: ORCL --> here's the output
PL/SQL procedure successfully completed.
SQL>