Copying files from AWS S3 to Snowflake Table using Snowflake Stored Procedure

When I try to execute the procedure to copy the files from S3 bucket to Snowflake table dynamically using the stored procedure, it is throwing a below error :

SQL compilation error:
Unknown function UDF_GET_COPY_COLUMNS
At Statement.execute, line 4 position 20

Code:

CREATE OR REPLACE PROCEDURE SF_COPY_LEAD_COLLECT_STAGE()
RETURNS VARIANT NOT NULL
LANGUAGE JAVASCRIPT
AS
$$
var row_as_json = {};
var udfstmt = snowflake.createStatement({sqlText: "SELECT UDF_GET_COPY_COLUMNS();"})
var udfrs = udfstmt.execute();
udfrs.next();
var COL_NAMES = udfrs.getColumnValue(1);
var command = "COPY INTO SF_ADVEN_COLLECT_DB.PRODUCT_HUB.PRODUCT FROM @DEV_NP_ADV_RAW_DB.PRODUCT_HUBS.NP_ADVEN_EXT_STG/PRODUCT/PRODUCT_202401121_231143.txt; ";
var stmt = snowflake.createStatement({sqlText: command});
var rs = stmt.execute();
//Move to the First Row Returned 
rs.next();
//check for error 
var errors_seen = rs.getColumnValue(6);
if (errors_seen > 0) 
{
throw new Error("Copy Command encountered Errors: "+errors_seen);
}
// Loop through the columns retured by the copy command.push each as key-value pair into a json object to return
for (var col_num = 0; col_num < COL_NAMES.length; col_num = col_num + 1)
{
var col_name = COL_NAMES[col_num];
row_as_json[col_name] = rs.getColumnValue(col_num + 1);
}
return row_as_json;
$

$

There could be few possible causes causing the function UDF_GET_COPY_COLUMNS() to error out

Function Scope: The function UDF_GET_COPY_COLUMNS() may be defined in a different schema, and you're not referencing it with the appropriate schema name.

Context Issue: If the function is defined in a different role or if it's a different type of object (e.g., a stored procedure or view), Snowflake might not be able to resolve the function properly.

You can check few things :

Check if the function exists: Run the following query to check if the function UDF_GET_COPY_COLUMNS() exists in the current database/schema:

SHOW FUNCTIONS LIKE 'UDF_GET_COPY_COLUMNS';

If the function doesn't exist, you may need to define it or ensure it's available in the correct schema. If it does exist, ensure the schema is correct.

Explicitly reference the schema: If the function is in a different schema, ensure you're explicitly referencing it by its schema name. For example:

var udfstmt = snowflake.createStatement({sqlText: "SELECT <schema_name>.UDF_GET_COPY_COLUMNS();"});

Check for access privileges: Ensure the role you're using has the necessary permissions to access the function UDF_GET_COPY_COLUMNS().

Alternate option :

If you don't need the UDF and simply want to dynamically get column names for your COPY INTO operation, you could alternatively query the metadata for the table you're copying into. For example, you could dynamically retrieve column names using the INFORMATION_SCHEMA.COLUMNS table:

var udfstmt = snowflake.createStatement({
    sqlText: `SELECT COLUMN_NAME 
              FROM INFORMATION_SCHEMA.COLUMNS 
              WHERE TABLE_NAME = 'PRODUCT' 
              AND TABLE_SCHEMA = 'PRODUCT_HUB' 
              AND TABLE_CATALOG = 'SF_ADVEN_COLLECT_DB'`
});

var udfrs = udfstmt.execute();
var COL_NAMES = [];
while (udfrs.next()) {
    COL_NAMES.push(udfrs.getColumnValue(1));
}