如何在PL/SQL中用serviceAccountKey.json生成FCM访问令牌

算力阿峰

2026-6-12

在PL/SQL中生成FCM HTTP v1 API的Authorization令牌

要通过FCM HTTP v1 API发送推送通知，需借助服务账号生成OAuth2访问令牌，下面是可直接使用的PL/SQL实现方案：

完整PL/SQL存储过程

CREATE OR REPLACE PROCEDURE GET_FCM_ACCESS_TOKEN(
    P_CLIENT_EMAIL IN VARCHAR2,
    P_PRIVATE_KEY IN VARCHAR2,
    P_TOKEN_URI IN VARCHAR2,
    P_ACCESS_TOKEN OUT VARCHAR2,
    P_EXPIRES_IN OUT NUMBER
) AS
    L_HEADER VARCHAR2(200);
    L_PAYLOAD VARCHAR2(1000);
    L_JWT_BASE VARCHAR2(2000);
    L_SIGNATURE RAW(2000);
    L_JWT VARCHAR2(4000);
    L_HTTP_RESPONSE UTL_HTTP.RESPONSE;
    L_HTTP_REQUEST UTL_HTTP.REQUEST;
    L_RESPONSE_BODY VARCHAR2(32767);
    L_PRIVATE_KEY RAW(2000);

    -- Base64URL编码（适配JWT要求）
    FUNCTION BASE64URL_ENCODE(P_DATA IN VARCHAR2) RETURN VARCHAR2 IS
        L_BASE64 VARCHAR2(2000);
    BEGIN
        L_BASE64 := UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(P_DATA));
        -- 替换特殊字符并移除末尾的填充符=
        RETURN RTRIM(REPLACE(REPLACE(L_BASE64, '+', '-'), '/', '_'), '=');
    END BASE64URL_ENCODE;

    -- 清理私钥格式（移除首尾标记和换行）
    FUNCTION CLEAN_PRIVATE_KEY(P_KEY IN VARCHAR2) RETURN RAW IS
        L_CLEAN_KEY VARCHAR2(4000);
    BEGIN
        L_CLEAN_KEY := REPLACE(REPLACE(P_KEY, '-----BEGIN PRIVATE KEY-----', ''), '-----END PRIVATE KEY-----', '');
        L_CLEAN_KEY := REPLACE(REPLACE(L_CLEAN_KEY, CHR(10), ''), CHR(13), '');
        RETURN UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(L_CLEAN_KEY));
    END CLEAN_PRIVATE_KEY;

BEGIN
    -- 1. 构造JWT头部
    L_HEADER := '{"alg":"RS256","typ":"JWT"}';

    -- 2. 构造JWT载荷（有效期1小时）
    L_PAYLOAD := '{"iss":"' || P_CLIENT_EMAIL || '",' ||
                 '"scope":"https://www.googleapis.com/auth/firebase.messaging",' ||
                 '"aud":"' || P_TOKEN_URI || '",' ||
                 '"exp":' || TO_CHAR(ROUND(SYSDATE - TO_DATE('1970-01-01', 'YYYY-MM-DD')) * 86400 + 3600) || ',' ||
                 '"iat":' || TO_CHAR(ROUND(SYSDATE - TO_DATE('1970-01-01', 'YYYY-MM-DD')) * 86400) || '}';

    -- 3. 编码头部和载荷并拼接
    L_JWT_BASE := BASE64URL_ENCODE(L_HEADER) || '.' || BASE64URL_ENCODE(L_PAYLOAD);

    -- 4. 处理私钥为RAW格式
    L_PRIVATE_KEY := CLEAN_PRIVATE_KEY(P_PRIVATE_KEY);

    -- 5. 用RSA-SHA256签名
    L_SIGNATURE := DBMS_CRYPTO.SIGN(
        SRC => UTL_RAW.CAST_TO_RAW(L_JWT_BASE),
        KEY => L_PRIVATE_KEY,
        ALG => DBMS_CRYPTO.SHA256_WITH_RSA
    );

    -- 6. 编码签名并组装完整JWT
    L_JWT := L_JWT_BASE || '.' || BASE64URL_ENCODE(UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(L_SIGNATURE)));

    -- 7. 发送POST请求获取访问令牌
    UTL_HTTP.SET_TRANSFER_TIMEOUT(30);
    L_HTTP_REQUEST := UTL_HTTP.BEGIN_REQUEST(P_TOKEN_URI, 'POST', 'HTTP/1.1');
    UTL_HTTP.SET_HEADER(L_HTTP_REQUEST, 'Content-Type', 'application/x-www-form-urlencoded');
    UTL_HTTP.WRITE_TEXT(L_HTTP_REQUEST, 'grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Ajwt-bearer&assertion=' || UTL_URL.ESCAPE(L_JWT));

    -- 解析响应
    L_HTTP_RESPONSE := UTL_HTTP.GET_RESPONSE(L_HTTP_REQUEST);
    UTL_HTTP.READ_TEXT(L_HTTP_RESPONSE, L_RESPONSE_BODY);
    UTL_HTTP.END_RESPONSE(L_HTTP_RESPONSE);

    -- 提取令牌和有效期（Oracle 12c+推荐用JSON_VALUE替代字符串截取）
    P_ACCESS_TOKEN := JSON_VALUE(L_RESPONSE_BODY, '$.access_token');
    P_EXPIRES_IN := JSON_VALUE(L_RESPONSE_BODY, '$.expires_in');

EXCEPTION
    WHEN OTHERS THEN
        IF UTL_HTTP.GET_RESPONSE_STATUS_CODE(L_HTTP_RESPONSE) IS NOT NULL THEN
            RAISE_APPLICATION_ERROR(-20001, 'HTTP错误: ' || UTL_HTTP.GET_RESPONSE_STATUS_CODE(L_HTTP_RESPONSE) || ' - ' || SQLERRM);
        ELSE
            RAISE_APPLICATION_ERROR(-20001, '执行错误: ' || SQLERRM);
        END IF;
END GET_FCM_ACCESS_TOKEN;
/

使用方法

先给执行用户授予必要权限：

GRANT EXECUTE ON UTL_HTTP TO YOUR_USER;
GRANT EXECUTE ON UTL_ENCODE TO YOUR_USER;
GRANT EXECUTE ON DBMS_CRYPTO TO YOUR_USER;

调用存储过程传入你的服务账号参数：

DECLARE
    V_ACCESS_TOKEN VARCHAR2(2000);
    V_EXPIRES_IN NUMBER;
    -- 从你的serviceAccountKey.json中提取对应值
    V_CLIENT_EMAIL VARCHAR2(200) := 'firebase-adminsdk-8d9eo@flutter-push-439fd.iam.gserviceaccount.com';
    V_PRIVATE_KEY VARCHAR2(4000) := '-----BEGIN PRIVATE KEY-----
MIIEvAIBADANBgkqhkiG9w0BAQEFAASCBKYwggSiAgEAAoIBAQDHLrQXWA3RG333
UJJJ3k4gRsetPIqUze8z0DCa3fshgwl2cLd3Tbo683FuHfmb4o2xnQI40eIbPTKj
2JwV/TKYFxjmFpbB0sQiIwQFMFflFHlQHkLzQ5FBtd2O92ZVCyAkdnpFihXjo3Lg
Q70W6DGhtdffbTLDVWWjMorwELkCSkDUfLIFvQwcvtw/djQDJUhlYRK+iRChWopn
sQFHXNNmiZVzJmpZBTJ0lbOAUhP4aG4qndgCzfDNJKXa9pGsoECrvt7/ZupfT1Yt
9+fvIivmrMFZpr5NZrdofJv4V8LSAwc4afmwWAeSlbG86Ip1ibuc+8YkeiXikNb7
IvTLZcHpAgMBAAECggEAHNHOn/QPJ7rlGoQvbn26ayQinxe763Tyj9onNjk5LWui
0l7TxPDbqczwlCDFLX91xgW0PRltMEjGC3v7dZkJmYT6Bsys6oV++Ht9iOyqQwyX
0vZV9JHJsirI0HdOeK6f63azEV299g5/wCA8+1QEXmQLxJmttyKjjp3xCXQ5+LEZ
Xile4RMa9NRR0BhdQ7D5vGpXwfgnWGImHJMoOijbVigwx5MUEQhsfafXSZnNdB6f
9+ZF7ewDlrqq5FkE7IEbJ+TGHZfZlE7luNC3m7O/Eam4RtVJS+pfXSjqHmhXLmG/
Rx2WIA9ncuxzQlD1ZpwOGmliP6MOGTkyHsh8JcIBswKBgQDlyxkztfxo3AtAeAN0
EJo3Al0scPRZRMMMOJ9TuMUts/oYsrkPVObKkmfDFXoNdLB6I2IyjAOYGRe8C2Oz
EONi+EmowDyOx8+JLwkbEHi68R6ZZgKooYHnZnjcfmPfJzwkwhGxgWGAwsFNSSf4
glxPEVZeTgxBRtm17JKp+fb23wKBgQDd5ehz+R6E78+S293kPlg4iRevDv2T961A
sSJCb/0oDXj1vtoHTv7YdZMcv4gEoHfmdihVrggjeMayty74Vp6ERdFctaKW+TWt4
nFPaPEqwBuEv8QHNNK78fBaHyUH9UmPsJPqwpQ+ew05ED9tpsgVol2Bv7DUt4mY5
GS8lIxhINwKBgEgVM6yi87C5BdaNTxgDdTy4Qx4DuMKf7UdSI7iRh1jU0ikZNy/2
BAebcW0iuYyrBAjsPIt6nE4D4QwdzoKHU6ziEckbtGNdjl6MIKEaw6RwqpaYB1F6
iFNcM6GHDDEeD6HANuilmz5W2WgzAJTV37r1x1ABz5pSbUzCDye+v5elAoGAUpkB
BSJnLN7DaowzNYHLfwfw6/XtiEW6lQkakpZzKpSRQRCQwgWysUpav2nALNC6sOus
qexmkvu0pmDf2dmzB/dBeCKrB4J0DcpLIEIvHwUAj8Lrg8InnM5n6JWO3cfsb/t3
4YcfoF5c5NLuPpLIlp06hY7sYK8UlA5+0RkWMdMCgYBMJvh9w1SMBx6VrgNMGp++
3oycDmNQAtLydf9hv8Wku2nV3ugen/Q9rWp/Gd65KpSexDjmx6rZZzbTT8SDBZvh
/fYJkatql9YNhFlChcWl+nuQhop4JNzzRiit3YoYitWxOCI/qe8dkGVrTI+4gtV4
95VlwA4YlY5wk+UI6yvdHw==
-----END PRIVATE KEY-----';
    V_TOKEN_URI VARCHAR2(200) := 'https://oauth2.googleapis.com/token';
BEGIN
    GET_FCM_ACCESS_TOKEN(V_CLIENT_EMAIL, V_PRIVATE_KEY, V_TOKEN_URI, V_ACCESS_TOKEN, V_EXPIRES_IN);
    DBMS_OUTPUT.PUT_LINE('访问令牌: ' || V_ACCESS_TOKEN);
    DBMS_OUTPUT.PUT_LINE('有效期: ' || V_EXPIRES_IN || ' 秒');
END;
/

关键注意事项

令牌缓存：生成的令牌有效期为1小时，建议缓存令牌，避免重复请求Google API。
私钥格式：必须严格清理私钥的首尾标记和换行符，否则签名会失败。

网络访问：确保Oracle服务器能访问oauth2.googleapis.com，若有防火墙需开放对应端口，必要时配置网络ACL：

BEGIN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
        acl => 'google_api.xml',
        description => '允许访问Google API',
        principal => 'YOUR_USER',
        is_grant => TRUE,
        privilege => 'connect',
        start_date => SYSTIMESTAMP,
        end_date => NULL
    );
    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
        acl => 'google_api.xml',
        host => 'oauth2.googleapis.com'
    );
    COMMIT;
END;
/

Oracle版本兼容：若使用12c以下版本，需用字符串截取替代JSON_VALUE解析响应体。

内容的提问来源于stack exchange，提问作者Anil Kumar

如何在PL/SQL中用serviceAccountKey.json生成FCM访问令牌

完整PL/SQL存储过程 #

使用方法 #

关键注意事项 #

完整PL/SQL存储过程

使用方法

关键注意事项