Needed some help in creating the below PROC. my goal is to copy the latest todays data from s3 files and load into tables. Few error handling i have added, like when file is not present then it would add it into log table, when file is present but load into table failed, i have marked entry in log, when its success the same has been added into log table. But its unable to do so. I am facing error while calling the proc.

I think this portion might be the issue as it might not be able to get the count even if file is present:(needed some help with this)

file_count_1 := (
SELECT COUNT(*)
FROM TABLE(
FUNCTION(‘LIST’, ‘@@DB_NAME.SCHEMA_NAME.S3_SF_DEV/Test/FILE/DATA’, { PATTERN => dynamic_file_pattern })
)

CREATE OR REPLACE PROCEDURE COPY_DATA_TO_RAW()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS OWNER
AS
$$
DECLARE
  dynamic_file_pattern VARCHAR;
  dynamic_file_pattern_1 VARCHAR;
  copy_command VARCHAR;
  copy_command_1 VARCHAR;
  file_count_1 INTEGER;
  file_count_2 INTEGER;
BEGIN

  -- Construct the dynamic file patterns
  dynamic_file_pattern := '.*EQ_TEM_TST' || TO_VARCHAR(CURRENT_DATE(), 'YYYYMMDD') || 'T.*[.]csv';
  dynamic_file_pattern_1 := '.*EQ_TST' || TO_VARCHAR(CURRENT_DATE(), 'YYYYMMDD') || 'T.*[.]csv';

  -- Check if files exist using LIST command
  file_count_1 := (
    SELECT COUNT(*)
    FROM TABLE(
      FUNCTION('LIST', '@@DB_NAME.SCHEMA_NAME.S3_SF_DEV/Test/FILE/DATA', { PATTERN => dynamic_file_pattern })
    )
  );

  file_count_2 := (
    SELECT COUNT(*)
    FROM TABLE(
      FUNCTION('LIST', '@@DB_NAME.SCHEMA_NAME.S3_SF_DEV/Test/FILE/DATA', { PATTERN => dynamic_file_pattern_1 })
    )
  );


  -- If any file is not found, log the error and stop execution
  IF (file_count_1 = 0 OR file_count_2 = 0) THEN
    INSERT INTO AUDIT_TABLE (TABLE_NAME, STATUS, DATE_MODIFIED)
    VALUES ('TABLE1', 'Latest files not found.', CURRENT_TIMESTAMP);
    RETURN 'Latest files not found.';
  END IF;

  -- Truncate tables before loading data
  TRUNCATE TABLE TABLE1;
  TRUNCATE TABLE TABLE2;

  -- Construct the COPY INTO command dynamically for TABLE1
  copy_command := '
    COPY INTO TABLE1 (
      INTERNAL, NAME, DESCRIPTION, START, ACTION, CLASS, VISIBILITY, ATTRIBUTES, ACATTRIBUTES
    )
    FROM (
      SELECT
        t.$1 AS INTERNAL,
        t.$2 AS NAME,
        t.$3 AS DESCRIPTION,
        t.$4 AS START,
        CASE
          WHEN t.$5 = ''Create'' THEN 1
          WHEN t.$5 = ''Modify'' THEN 2
          WHEN t.$5 = ''Delete'' THEN 3
        END AS ACTION,
        t.$6 AS CLASS,
        t.$7 AS VISIBILITY,
        t.$8 AS ATTRIBUTES,
        t.$9 AS ACATTRIBUTES
      FROM @@DB_NAME.SCHEMA_NAME.S3_SF_DEV/Test/FILE/DATA
      (PATTERN => ''' || dynamic_file_pattern || ''') AS t
    )
    FILE_FORMAT = (
      TYPE = ''CSV'',
      COMPRESSION = ''NONE'',
      SKIP_HEADER = 1,
      FIELD_DELIMITER = ''|'',
      FIELD_OPTIONALLY_ENCLOSED_BY = ''"''
    )
  ';
  -- Construct the COPY INTO command dynamically for TABLE2
  copy_command_1 := '
    COPY INTO TABLE2 (
      INTERNAL, NAME, DESC, ORG, EQUTN, ISEQUA, ACTIVE, FILTER, ATTRIBUTES, ACATTRIBUTES
    )
    FROM (
      SELECT
        t.$1 AS INTERNAL,
        t.$2 AS NAME,
        IFF(t.$3 = '''', ''<NULL>'', t.$3) AS DESC,
        t.$4 AS ORG,
        t.$5 AS EQUTN,
        CASE WHEN t.$6 = ''YES'' THEN 1 ELSE 0 END AS ISEQUA,
        CASE WHEN t.$7 = ''YES'' THEN 1 ELSE 0 END AS ACTIVE,
        t.$8 AS FILTER,
        t.$9 AS ATTRIBUTES,
        t.$10 AS ACATTRIBUTES
      FROM @@DB_NAME.SCHEMA_NAME.S3_SF_DEV/Test/FILE/DATA
      (PATTERN => ''' || dynamic_file_pattern_1 || ''') AS t
    )
    FILE_FORMAT = (
      TYPE = ''CSV'',
      COMPRESSION = ''NONE'',
      SKIP_HEADER = 1,
      FIELD_DELIMITER = ''|'',
      FIELD_OPTIONALLY_ENCLOSED_BY = ''"''
    )
  ';

  -- Try loading data, and handle any failures
  BEGIN
    EXECUTE IMMEDIATE copy_command;
    EXECUTE IMMEDIATE copy_command_1;

    INSERT INTO AUDIT_TABLE (TABLE_NAME, STATUS, DATE_MODIFIED)
    VALUES ('TABLE1', 'SUCCESS', CURRENT_TIMESTAMP);
  EXCEPTION WHEN OTHER THEN
    INSERT INTO AUDIT_TABLE (TABLE_NAME, STATUS, DATE_MODIFIED)
    VALUES ('TABLE1', 'LOAD FAILED', CURRENT_TIMESTAMP);
    RETURN 'LOAD FAILED';
  END;

  RETURN 'LOAD COMPLETED SUCCESSFULLY';
END;
$$;```