oracle database - DBMS_CLOUD.SEND_REQUEST with wrong types of arguments - Stack Overflow

admin管理员组

文章数量:1345031

I'm trying to send data from an autonomous database in OCI over this function to a log created in OCI console:

DECLARE
  log_endpoint VARCHAR2(4000);
  log_body CLOB;
  CURSOR audit_cursor IS
    SELECT EVENT_TIMESTAMP, DBUSERNAME, ACTION_NAME, RETURN_CODE 
    FROM UNIFIED_AUDIT_TRAIL
    WHERE EVENT_TIMESTAMP > SYSTIMESTAMP - INTERVAL '1' DAY;
BEGIN
  log_endpoint := '.log.oc1.eu-frankfurt-1.mysecretocid';
  FOR rec IN audit_cursor LOOP
    log_body := '{
      "specversion": "1.0",
      "source": "autonomous-database",
      "eventType": "db-audit-log",
      "data": {
        "timestamp": "' || TO_CHAR(rec.EVENT_TIMESTAMP, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') || '",
        "dbusername": "' || rec.DBUSERNAME || '",
        "action": "' || rec.ACTION_NAME || '",
        "status": "' || CASE WHEN rec.RETURN_CODE = 0 THEN 'SUCCESS' ELSE 'FAILURE' END || '"
      }
    }';
    DBMS_CLOUD.SEND_REQUEST(
      credential_name => 'MY_CRED',
      uri             => log_endpoint,
      method          => DBMS_CLOUD.METHOD_POST,
      body            => log_body
    );
  END LOOP;
END;
/

The credential for this function is already created.

When I execute this function I'm getting

ERROR at line 29:
ORA-06550: line 29, column 5:
PLS-00306: wrong number or types of arguments in call to 'SEND_REQUEST'
ORA-06550: line 29, column 5:
PL/SQL: Statement ignored

From documentation from Oracle everything seems fine with DBMS_CLOUD.SEND_REQUEST definition.

Any suggestions here?

I'm trying to send data from an autonomous database in OCI over this function to a log created in OCI console:

DECLARE
  log_endpoint VARCHAR2(4000);
  log_body CLOB;
  CURSOR audit_cursor IS
    SELECT EVENT_TIMESTAMP, DBUSERNAME, ACTION_NAME, RETURN_CODE 
    FROM UNIFIED_AUDIT_TRAIL
    WHERE EVENT_TIMESTAMP > SYSTIMESTAMP - INTERVAL '1' DAY;
BEGIN
  log_endpoint := 'https://logging.eu-frankfurt-1.oci.oraclecloud/20200531/logs/ocid1.log.oc1.eu-frankfurt-1.mysecretocid';
  FOR rec IN audit_cursor LOOP
    log_body := '{
      "specversion": "1.0",
      "source": "autonomous-database",
      "eventType": "db-audit-log",
      "data": {
        "timestamp": "' || TO_CHAR(rec.EVENT_TIMESTAMP, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') || '",
        "dbusername": "' || rec.DBUSERNAME || '",
        "action": "' || rec.ACTION_NAME || '",
        "status": "' || CASE WHEN rec.RETURN_CODE = 0 THEN 'SUCCESS' ELSE 'FAILURE' END || '"
      }
    }';
    DBMS_CLOUD.SEND_REQUEST(
      credential_name => 'MY_CRED',
      uri             => log_endpoint,
      method          => DBMS_CLOUD.METHOD_POST,
      body            => log_body
    );
  END LOOP;
END;
/

The credential for this function is already created.

When I execute this function I'm getting

ERROR at line 29:
ORA-06550: line 29, column 5:
PLS-00306: wrong number or types of arguments in call to 'SEND_REQUEST'
ORA-06550: line 29, column 5:
PL/SQL: Statement ignored

From documentation from Oracle everything seems fine with DBMS_CLOUD.SEND_REQUEST definition.

Any suggestions here?

• oracle-database
• oracle-autonomous-db

Share Improve this question Follow asked yesterday gurbelundergurbelunder 30766 silver badges1414 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

The SEND_REQUEST Expects body as BLOB. Not as CLOB.

Try this:

DECLARE
  log_endpoint VARCHAR2(4000);
  log_body CLOB;
  log_body_blob BLOB;
  CURSOR audit_cursor IS
    SELECT EVENT_TIMESTAMP, DBUSERNAME, ACTION_NAME, RETURN_CODE 
    FROM UNIFIED_AUDIT_TRAIL
    WHERE EVENT_TIMESTAMP > SYSTIMESTAMP - INTERVAL '1' DAY;
BEGIN
  log_endpoint := 'https://logging.eu-frankfurt-1.oci.oraclecloud/20200531/logs/ocid1.log.oc1.eu-frankfurt-1.mysecretocid';

  FOR rec IN audit_cursor LOOP
    log_body := '{
      "specversion": "1.0",
      "source": "autonomous-database",
      "eventType": "db-audit-log",
      "data": {
        "timestamp": "' || TO_CHAR(rec.EVENT_TIMESTAMP, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') || '",
        "dbusername": "' || rec.DBUSERNAME || '",
        "action": "' || rec.ACTION_NAME || '",
        "status": "' || CASE WHEN rec.RETURN_CODE = 0 THEN 'SUCCESS' ELSE 'FAILURE' END || '"
      }
    }';

    log_body_blob := TO_BLOB(UTL_RAW.CAST_TO_RAW(log_body));

    DBMS_CLOUD.SEND_REQUEST(
      credential_name => 'MY_CRED',
      uri             => log_endpoint,
      method          => DBMS_CLOUD.METHOD_POST,
      body            => log_body_blob
    );
  END LOOP;
END;
/

Also, check the structure of the SEND_REQUEST parameters from Oracle’s DBMS_CLOUD.SEND_REQUEST documentation.

本文标签： oracle databaseDBMSCLOUDSENDREQUEST with wrong types of argumentsStack Overflow