I am using Oracle 19c. I created a procedure and use it in a sql script file and call the procedure from shell script. There are variables which need to be substituted from the shell script to procedure.
The script execute with "PL/SQL procedure successfully completed." but it does not gather stats without printing the message from DBMS_OUTPUT.
Procedure :
CREATE OR REPLACE PROCEDURE gather_schema_stats_proc (
p_schema_name IN VARCHAR2
) IS
BEGIN
-- Print message before gathering stats
DBMS_OUTPUT.PUT_LINE('Gathering statistics for schema ' || p_schema_name || '...');
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname => p_schema_name,
cascade => TRUE
);
-- Print message after gathering stats
DBMS_OUTPUT.PUT_LINE('Statistics gathering for schema ' || p_schema_name || ' completed.');
END gather_schema_stats_proc;
/
SQL Script : gstat.sql
SET SERVEROUTPUT ON
DEFINE schema='$owner' --variable to be substituted
EXEC gather_schema_stats_proc('&schema');
Shell script :
#!/bin/bash
owner="SCOTT" #variable for substitution
export ORACLE_SID=abcdb
export ORACLE_HOME=/u01/app/oracle/product/db_home
exit|sqlplus -S '/ as sysdba' @gstat.sql
Thanks in advance
exit|for?SET SERVEROUTPUT ON EXEC gather_schema_stats_proc('&1');and then change the bash script to use:sqlplus -S '/ as sysdba' @gstat.sql "${owner}"to pass the shell variable into SQL*Plus as a substitution variable and then to use the substitution variable in the procedure call.