pl/sqL add line to file once before for loop using dbms_output.put_line

danidar_4309334 0
Viewed 139

I have a script that is run from a bash script and produces a flat file on disk. I need to add some arbitrary text before the loop

set serveroutput on
whenever sqlerror exit sql.sqlcode;
set echo off
SET VERIFY OFF
set heading off
SET FEEDBACK OFF
set serveroutput on size unlimited
set linesize 300
alter session set current_schema = SCOTT;
ALTER session SET TIME_ZONE='GMT';
BEGIN
FOR policy IN (
SELECT
     "A3"."POLICY_NAME" as policy_name,
     count("A1"."CERT_SERIAL_NUM") as total
FROM
    (
      SELECT POLICY_ID FROM POLICY_DOMAIN_PATH_MAP_TBL
      MINUS
      SELECT POLICY_ID FROM POLICY_DOMAIN_PATH_MAP_TBL WHERE ACTION=1 AND PATH_ID IN (-4,-2)
    ) A0
    join "POLICY_TBL"               "A3"
        on A0.POLICY_ID = "A3"."POLICY_ID"
    left join "CERTIFICATE_TBL"          "A2"
        on "A3"."POLICY_ID" = "A2"."POLICY_ID"
        and "A2"."ISSUE_DATE" >=  CURRENT_TIMESTAMP - (10/1440)
    left join "CERT_STATUS_CHECK_TBL"    "A1"
        on "A1"."CERT_SERIAL_NUM" = "A2"."CERT_SERIAL_NUM"
        AND "A1"."STATUS" = '0'
GROUP BY
    policy_name
order by 2 desc
    )
    LOOP
    dbms_output.put_line('cert{policy=' ||'"' ||policy.policy_name || '"' || ',instance="SCOTT"} ' || policy.total);
    END LOOP;
END;
/

this produces the following output:

cert{policy="CUS CAO",instance="SCOTT"} 0
cert{policy="CUS RAX",instance="SCOTT"} 0
cert{policy="COL Application Encryption (SOFT) V1.3",instance="SCOTT"} 0
cert{policy="COL Application Signing (SOFT) V1.2",instance="SCOTT"} 0

I would like to add a line once before the beginning of the loop so the file looks like this

# line added
cert{policy="CUS CAO",instance="SCOTT"} 0
cert{policy="CUS RAX",instance="SCOTT"} 0
cert{policy="COL Application Encryption (SOFT) V1.3",instance="SCOTT"} 0
cert{policy="COL Application Signing (SOFT) V1.2",instance="SCOTT"} 0

this pl/sql script is called from a bash script. Should I add the line manually in bash using awk? I prefer to do it in sql (if possible)

this is the part of the bash calling the pl/sql:

{
$ORACLE_HOME/bin/sqlplus -s /nolog      <<EOF1
connect SCOTT/${DBPASSWD}@${Oracle_sid}
        whenever sqlerror exit sql.sqlcode;
        set echo off
        set heading off
        set serveroutput on
        @cert3.sql
EOF1
} > "${prom_file}"

I tried adding a begin/end block before the for loop:

...
ALTER session SET TIME_ZONE='GMT';
BEGIN
  dbms_output.put_line('# add line');
END;
BEGIN
FOR policy IN (
....
END;
/

but I get errors on the joins in the main loop.

BEGIN

  • ERROR at line 4: ORA-06550: line 4, column 1: PLS-00103: Encountered the symbol "BEGIN" ORA-06550: line 15, column 5: PLS-00103: Encountered the symbol "JOIN" when expecting one of the following: ) , for group having intersect minus order start union where connect
plsql oracle
2 Answers

Just use the bash printf utility inside that block:

{
printf '%s\n' "# line added"                    # <---- added line
$ORACLE_HOME/bin/sqlplus -s /nolog      <<EOF1
connect SCOTT/${DBPASSWD}@${Oracle_sid}
        whenever sqlerror exit sql.sqlcode;
        set echo off
        set heading off
        set serveroutput on
        @cert3.sql
EOF1
} > "${prom_file}"

Since you already have the braces to create a grouping, adding the printf at the beginning will prepend that output before the sqlplus output.

user_49575080

Your PL/SQL was close, but you can put the dbms_output call inside the existing BEGIN..END anonymous block.

...
ALTER session SET TIME_ZONE='GMT';
BEGIN
dbms_output.put_line('# add line');
FOR policy IN (
....
END;
/

The error you're getting is because when you have multiple anonymous blocks in a SQL script, you need a / after each one to actually execute it. Otherwise it thinks the second block is still part of the first one's definition. So this would also work:

...
ALTER session SET TIME_ZONE='GMT';
BEGIN
  dbms_output.put_line('# add line');
END;
/
BEGIN
FOR policy IN (
....
END;
/
user_30618520
Related