LOOP/FOR statement on oracle script

I’m running an ansible playbook that runs an sqlplus script to an Oracle DB.
Basically the script creates a CSV file with some server info. The query is pretty much autogenerated, so it will be difficult change it.

set markup csv on
spool 'playbook-dir/files/servers.csv'

SELECT *
FROM   (SELECT DISTINCT server.primary_name                  SERVER_NAME,
                        server.arpa_domain                   ARPA_DOMAIN,
                        server.impact                        IMPACT,
                        instance_definition.category         SOLUTION_CATEGORY,
                        instance_definition.instance_name    SOLUTION_NAME,
                        instance_on_server.ins_instance_name INSTANCE_NAME
        FROM   server_db.instance_definition, server_db.instance_on_server, server_db.business, server_db.server_customer, server_db.server
        WHERE  ( server_db.instance_definition.app_id(+) = server_db.instance_on_server.app_id )
               AND ( server_db.server.system_id = server_db.instance_on_server.system_id(+) )
               AND ( ( instance_definition.instance_name LIKE '%windows%' )
                      OR ( instance_definition.instance_name LIKE '%centos%' ) )
               AND ( instance_on_server.status LIKE 'in production' )
               AND ( server_db.business.business_id(+) = server_db.server_customer.business_id )
               AND ( server_db.server_customer.system_id(+) = server_db.server.system_id )
               AND (( instance_definition.category LIKE 'os' ))
               AND (( server.primary_name||'.'||server.arpa_domain LIKE '%' ))
               AND business.secure_access_r <> 1)
WHERE  ROWNUM <= 600000 + 1
ORDER  BY server_name;  

spool off

The problem is that this query brings all the 5000 server and I need only 200.
I want to add a LOOP/FOR statement with only the servers I need, but I think I’m doing something wrong.
This is the query with the LOOP:

declare
  type table_varchar  is table of varchar2(10);

  var_table_varchar  table_varchar;
begin
  var_table_varchar  := table_varchar('server1', 'server2', 'server3', 'server4');

  for elem in 1 .. var_table_varchar.count loop
    SELECT *
    FROM   (SELECT DISTINCT server.primary_name                  SERVER_NAME,
                            server.arpa_domain                   ARPA_DOMAIN,
                            server.impact                        IMPACT,
                            instance_definition.category         SOLUTION_CATEGORY,
                            instance_definition.instance_name    SOLUTION_NAME,
                            instance_on_server.ins_instance_name INSTANCE_NAME
            FROM   server_db.instance_definition, server_db.instance_on_server, server_db.business, server_db.server_customer, server_db.server
            WHERE  ( server_db.instance_definition.app_id(+) = server_db.instance_on_server.app_id )
                   AND ( server_db.server.system_id = server_db.instance_on_server.system_id(+) )
                   AND ( ( instance_definition.instance_name LIKE '%windows%' )
                          OR ( instance_definition.instance_name LIKE '%centos%' ) )
                   AND ( instance_on_server.status LIKE 'in production' )
                   AND ( server_db.business.business_id(+) = server_db.server_customer.business_id )
                   AND ( server_db.server_customer.system_id(+) = server_db.server.system_id )
                   AND (( instance_definition.category LIKE 'os' ))
                   AND (( server.primary_name||'.'||server.arpa_domain LIKE '%' ))
                   AND (( server.primary_name LIKE '%var_assoc_varchar(elem)%' ))
                   AND business.secure_access_r <> 1)
    WHERE  ROWNUM <= 600000 + 1
    ORDER  BY server_name; 
  end loop;
end;

When I run it I get this error:

Error report –
ORA-06550: line 9, column 5:
PLS-00428: an INTO clause is expected in this SELECT statement
06550. 00000 – “line %s, column %s:n%s”
*Cause: Usually a PL/SQL compilation error.
*Action:

Sadly, I’m not much of a DBA and my knowledge got me so far. So suggestion will be appreciated

Answer

You can not use plain select sql without into clause like this in PL/SQL.

What are you expecting out of the query in PL/SQL?

Your first query executed and filled csv because it is single query and not anonymous block.

You can simply add condition into where clause of your first query as follows:

server.primary_name in ('server1', 'server2', 'server3', 'server4')

Advertisement

Answer