Call sqlcl script hosted in a private repo in github

Question

I can successfully do this:

sql my_connection_string @script1.sql

However, I am struggling to make this work, using a script hosted in a private repo.

sql my_connection_string https://my_private_repo_url/script1.sql

How can I make this work in a single line?

Answer 1

You've got it.

sql user/password@server:ip/service @https://internet.com/script.sql

Here's a public SQL file, courtesy Kris.

c:\sqlcl\22.4\sqlcl\bin>sql hr/oracle@localhost:1523/orclpdb1 @https://gist.githubusercontent.com/krisrice/68e23a2101fe10c8efc26371b8c59d5c/raw/e5a2512d4f74dc79d37e4328e36b81fba15d36a3/dbms_cloud_metric_sql_pkg.sql


SQLcl: Release 22.4 Production on Mon Feb 13 08:18:39 2023

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Mon Feb 13 2023 08:18:20 -05:00

Connected to:
Oracle Database 23c Enterprise Edition Release 23.0.0.0.0 - Beta
Version 23.1.0.0.0


login.sql found in the CWD. DB access is restricted for login.sql.
Adjust the SQLPATH to include the path to enable full functionality.

Package OCI_METRICS compiled


Package Body OCI_METRICS compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
0/0       PL/SQL: Compilation unit analysis terminated
12/10     PLS-00201: identifier 'DBMS_CLOUD_TYPES.RESP' must be declared
Errors: check compiler log

Error starting at line : 149 File @ https://gist.githubusercontent.com/krisrice/68e23a2101fe10c8efc26371b8c59d5c/raw/e5a2512d4f74dc79d37e4328e36b81fba15d36a3/dbms_cloud_metric_sql_pkg.sql
In command -
begin
oci_metrics.setup('OCI$RESOURCE_PRINCIPAL', 'us-phoenix-1');

  for r in (   /*  MAIN QUERY for metrics Should only need to adjust this section to adhere to this structure

                        Expected Columns:
                            NAMESPACE      - The namespace of the metric                    :  'my_namespace'
                            COMPARTMENT_ID - The OCID of the compartment to post the metric :  'ocid1.compartment....'
                            RESOURCE_GROUP - The resource group of the metric               :  'my_resource_group'
                            NAME           - The name of the metric                         :  'my_metric_name'
                            DIMENSIONS     - The dimensions of the metric in json format    :  '{"factioid":"this","other":"else"}'
                            VALUE          - The numeric value of the metric               :  123.45
                    */
                    select 'a_namespace' namespace,
                            'ocid1.compartment.oc1..aaaaaaaacw2ft7eu33tlaoppsu6mck7qn2wsqefuixcjhza6xhhsbnhvjorq' compartment_id,
                            'sample_resource_group' resource_group,
                            'A_SAMPLE_METRIC'  name,
                            '{"machine":"'|| machine ||'","username":"' ||username ||'"}' dimensions,
                            count(1) value
                        from v$session
                        group by username,machine
                    /* END  MAIN QUERY for metrics */
                ) loop

                oci_metrics.addMetric(r.namespace, r.compartment_id, r.resource_group, r.name, r.dimensions, r.value, systimestamp);
    end loop;
    -- send any remaining metrics
    oci_metrics.sendBatch;
end;
Error report -
ORA-04063: package body "HR.OCI_METRICS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "HR.OCI_METRICS"
ORA-06512: at line 2
04063. 00000 -  "%s has errors"
*Cause:    Attempt to execute a stored procedure or use a view that has
           errors.  For stored procedures, the problem could be syntax errors
           or references to other, non-existent procedures.  For views,
           the problem could be a reference in the view's defining query to
           a non-existent table.
           Can also be a table which has references to non-existent or
           inaccessible types.
*Action:   Fix the errors and/or create referenced objects as necessary.
SQL>
1:0 ¦ HR ¦ localhost:1523/orclpdb1 ¦ viins ¦ None ¦ 00:00:00.347
      p_comments       => 'see if we can say Hello!',
      p_source         =>
'select ''Aloha!'' HI_GREETING from dual'
      );


  COMMIT;
END;
SQL>