Skip to content

Instantly share code, notes, and snippets.

@tschf

tschf/google_drive.pkb

Last active November 30, 2021 00:36
Show Gist options
  • Star 3 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save tschf/7526011 to your computer and use it in GitHub Desktop.
Save tschf/7526011 to your computer and use it in GitHub Desktop.
Download ZIP
google_drive in plsql
Raw
google_drive.pkb
create or replace
PACKAGE GOOGLE_DRIVE
as
type t_varchar2 is table of varchar2(50);
-- Set default folder ID to upload to (got from last portion of URL when opening folder)
g_upload_folder_id varchar2(400) := '';
procedure get_authorization_url(
p_state in varchar2);
procedure authorization_code_callback(
code in varchar2 default NULL,
error in varchar2 default NULL,
state in varchar2 default NULL);
procedure set_access_token(
p_item_name in varchar2 default 'GOOGLE_ACCESS_TOKEN');
/*function create_folder(
p_folder_name in varchar2
) return varchar2;*/
function create_file(
p_data in BLOB
, p_mime_type in varchar2
, p_filename in varchar2
, p_folder_id in varchar2 default g_upload_folder_id) return varchar2;
end GOOGLE_DRIVE;
/
create or replace
PACKAGE BODY GOOGLE_DRIVE
as
--Don't change.
g_auth_url varchar2(400) := 'https://accounts.google.com/o/oauth2/auth';
g_token_url varchar2(400) := 'https://accounts.google.com/o/oauth2/token';
g_token_grant_type varchar2(20) := 'authorization_code';
--Update for different environment
g_wallet_path varchar2(400) := 'file:/etc/ORACLE/WALLETS/oracle1';
g_wallet_password varchar2(400) := '';
g_redirect_uri varchar2(400) := 'https://example.com/pls/apex/owner.google_drive.authorization_code_callback';
g_client_id varchar2(400) := '';
g_client_secret varchar2(400) := '';
function authorized_request(
p_access_token in varchar2
, p_url in varchar2
, p_method in varchar2 default 'GET'
, p_content_type in varchar2 default 'application/json'
, p_payload in varchar2 default NULL
, p_wallet_path in varchar2 default NULL
, p_wallet_password in varchar2 default ''
)
return CLOB
AS
l_req utl_http.req;
l_res utl_http.resp;
l_return CLOB;
l_response_tmp varchar2(1024);
BEGIN
if p_wallet_path IS NULL THEN
utl_http.set_Wallet(
path => g_wallet_path
, password => g_wallet_password);
else
utl_http.set_Wallet(
path => p_wallet_path
, password => p_wallet_password);
END IF;
l_req := utl_http.begin_request(
url => p_url
, method => p_method);
utl_http.set_header(
r => l_req
, name => 'Content-Type'
, value => p_content_type);
utl_http.set_header(
r => l_req
, name => 'Authorization'
, value => 'Bearer ' || p_access_token);
if p_payload is not null then
utl_http.set_header(
r => l_req
, name => 'Content-Length'
, value => length(p_payload));
utl_http.write_text(
r => l_req
, data => p_payload);
end if;
l_res := utl_http.get_response(
r => l_req);
BEGIN
LOOP
utl_http.read_line(
r => l_res
, data => l_response_tmp
, remove_crlf => FALSE);
l_return := l_return || l_response_tmp;
END LOOP;
EXCEPTION
WHEN
UTL_HTTP.END_OF_BODY
THEN
utl_http.end_response(
r => l_res);
END;
return l_return;
END authorized_request;
function authorized_request(
p_access_token in varchar2
, p_url in varchar2
, p_method in varchar2 default 'POST'
, p_content_type in varchar2
, p_payload in BLOB
, p_wallet_path in varchar2 default NULL
, p_wallet_password in varchar2 default ''
)
return CLOB
AS
l_req utl_http.req;
l_res utl_http.resp;
l_return CLOB;
l_response_tmp varchar2(1024);
l_amount_read INTEGER := 32767;
l_offset_read INTEGER := 1;
l_buffer_read RAW(32767);
l_offset INTEGER := 1;
BEGIN
if p_wallet_path IS NULL THEN
utl_http.set_Wallet(
path => g_wallet_path
, password => g_wallet_password);
else
utl_http.set_Wallet(
path => p_wallet_path
, password => p_wallet_password);
END IF;
l_req := utl_http.begin_request(
url => p_url
, method => p_method);
utl_http.set_header(
r => l_req
, name => 'Content-Type'
, value => p_content_type);
utl_http.set_header(
r => l_req
, name => 'Authorization'
, value => 'Bearer ' || p_access_token);
if p_payload is not null then
utl_http.set_header(
r => l_req
, name => 'Content-Length'
, value => length(p_payload));
while l_offset <= dbms_lob.getlength(p_payload) LOOP
dbms_lob.read(
lob_loc => p_payload
, amount => l_amount_read
, offset => l_offset
, buffer => l_buffer_read );
utl_http.write_raw(
r => l_req
, data => l_buffer_read);
l_offset := l_offset + l_amount_read;
END LOOP;
end if;
l_res := utl_http.get_response(
r => l_req);
BEGIN
LOOP
utl_http.read_line(
r => l_res
, data => l_response_tmp
, remove_crlf => FALSE);
l_return := l_return || l_response_tmp;
END LOOP;
EXCEPTION
WHEN
UTL_HTTP.END_OF_BODY
THEN
utl_http.end_response(
r => l_res);
END;
return l_return;
END authorized_request;
--Refer to docs: https://developers.google.com/accounts/docs/OAuth2WebServer
procedure get_authorization_url(
p_state in varchar2)
as
l_url_params varchar2(400);
BEGIN
l_url_params :=
'response_type=#RESPONSE_TYPE#&client_id=#CLIENT_ID#&redirect_uri=#REDIRECT_URI#&scope=#SCOPE#&state=#STATE#&access_type=offline&approval_prompt=force';
l_url_params := replace(l_url_params, '#RESPONSE_TYPE#', 'code');
l_url_params := replace(l_url_params, '#CLIENT_ID#', g_client_id);
l_url_params := replace(l_url_params, '#REDIRECT_URI#', g_redirect_uri);
l_url_params := replace(l_url_params, '#SCOPE#', 'https://www.googleapis.com/auth/drive.file');
l_url_params := replace(l_url_params, '#STATE#', p_state);
htp.p(g_auth_url || '?' || l_url_params);
END get_authorization_url;
procedure authorization_code_callback(
code in varchar2 default NULL,
error in varchar2 default NULL,
state in varchar2)
as
l_token_req utl_http.req;
l_token_res utl_http.resp;
l_token_req_payload varchar2(4000) :=
'code=#CODE#&client_id=#CLIENT_ID#&client_secret=#CLIENT_SECRET#&redirect_uri=#REDIRECT_URI#&grant_type=#GRANT_TYPE#';
l_response CLOB;
l_response_tmp varchar2(1024);
l_response_json JSON;
l_unescaped_state varchar2(200);
l_endpoint_url varchar2(200);
l_app_user app_users.username%type;
begin
l_unescaped_state := utl_url.unescape(state);
select apex_user into l_app_user
from (
select apex_user, to_char(view_date, 'hh:mi.ss') view_date, rank() over (order by view_date desc) rank
from apex_workspace_Activity_log
where apex_session_id = l_unescaped_state)
where rank = 1;
l_endpoint_url := 'http://example.com/pls/apex/f?p=249:143:' || l_unescaped_state;
--code was returned, get the token
if code is not null then
l_token_req_payload := replace(l_token_req_payload, '#CODE#', code);
l_token_req_payload := replace(l_token_req_payload, '#CLIENT_ID#', g_client_id);
l_token_req_payload := replace(l_token_req_payload, '#CLIENT_SECRET#', g_client_secret);
l_token_req_payload := replace(l_token_req_payload, '#REDIRECT_URI#', g_redirect_uri);
l_token_req_payload := replace(l_token_req_payload, '#GRANT_TYPE#', g_token_grant_type);
utl_http.set_wallet(
path => g_wallet_path
, password => g_wallet_password);
l_token_req := utl_http.begin_request(
url => g_token_url
, method => 'POST');
utl_http.set_header(
r => l_token_req
, name => 'Content-length'
, value => length(l_token_req_payload));
utl_http.set_header(
r => l_token_req
, name => 'Content-Type'
, value => 'application/x-www-form-urlencoded');
utl_http.write_text(
r => l_token_req
, data => utl_url.escape(l_token_req_payload));
l_token_res := utl_http.get_response(
r => l_token_req);
BEGIN
LOOP
utl_http.read_line(
r => l_token_res
, data => l_response_tmp
, remove_crlf => FALSE);
l_response := l_response || l_response_tmp;
END LOOP;
EXCEPTION
WHEN
UTL_HTTP.END_OF_BODY
THEN
utl_http.end_response(
r => l_token_res);
END;
l_response_json := JSON(l_response);
update app_users
set google_refresh_token = json_ext.get_string(
l_response_json
, 'refresh_token')
where upper(username) = l_app_user;
end if;
owa_util.redirect_url(
curl => l_endpoint_url);
exception
when others
then
dbms_output.put_line(utl_http.get_detailed_sqlerrm);
raise;
end authorization_code_callback;
procedure set_access_token(
p_item_name in varchar2 default 'GOOGLE_ACCESS_TOKEN')
as
l_token_req_payload varchar2(4000) :=
'refresh_token=#REFRESH_TOKEN#&client_id=#CLIENT_ID#&client_secret=#CLIENT_SECRET#&grant_type=#GRANT_TYPE#';
l_response CLOB;
l_response_tmp varchar2(1024);
l_response_json JSON;
l_token_req utl_http.req;
l_token_res utl_http.resp;
l_google_refresh_token app_users.google_refresh_token%type;
begin
select google_refresh_token into l_google_refresh_token
from app_users
where upper(username) = apex_util.get_session_state('APP_USER');
l_token_req_payload := replace(l_token_req_payload, '#REFRESH_TOKEN#', l_google_refresh_token);
l_token_req_payload := replace(l_token_req_payload, '#CLIENT_ID#', g_client_id);
l_token_req_payload := replace(l_token_req_payload, '#CLIENT_SECRET#', g_client_secret);
l_token_req_payload := replace(l_token_req_payload, '#GRANT_TYPE#', 'refresh_token');
utl_http.set_wallet(
path => g_wallet_path
, password => g_wallet_password);
l_token_req := utl_http.begin_request(
url => g_token_url
, method => 'POST');
utl_http.set_header(
r => l_token_req
, name => 'Content-length'
, value => length(l_token_req_payload));
utl_http.set_header(
r => l_token_req
, name => 'Content-Type'
, value => 'application/x-www-form-urlencoded');
utl_http.write_text(
r => l_token_req
, data => utl_url.escape(l_token_req_payload));
l_token_res := utl_http.get_response(
r => l_token_req);
BEGIN
LOOP
utl_http.read_line(
r => l_token_res
, data => l_response_tmp
, remove_crlf => FALSE);
l_response := l_response || l_response_tmp;
END LOOP;
EXCEPTION
WHEN
UTL_HTTP.END_OF_BODY
THEN
utl_http.end_response(
r => l_token_res);
END;
l_response_json := JSON(l_response);
apex_util.set_session_state(p_item_name, json_ext.get_string(
l_response_json
, 'access_token'));
end set_access_token;
/* function create_folder(
p_folder_name in varchar2
) return varchar2
AS
l_token app_user.access_token%type;
l_request_url varchar2(200) := 'https://www.googleapis.com/drive/v2/files';
l_payload JSON;
l_response CLOB;
l_response_JSON JSON;
BEGIN
l_payload := JSON;
l_payload.put('mimeType', 'application/vnd.google-apps.folder');
l_payload.put('title', p_folder_name);
l_response := authorized_request(
p_access_token => get_access_token(v('APP_USER'))
, p_url => l_request_url
, p_method => 'POST'
, p_payload => l_payload.to_char
);
l_response_JSON := JSON(l_response);
return JSON_EXT.GET_STRING(l_response_JSON, 'id');
END create_folder; */
function create_file(
p_data in BLOB
, p_mime_type in varchar2
, p_filename in varchar2
, p_folder_id in varchar2 default g_upload_folder_id) return varchar2
AS
l_insert_url varchar2(200) := 'https://www.googleapis.com/upload/drive/v2/files?uploadType=media';
l_update_url varchar2(200) := 'https://www.googleapis.com/drive/v2/files/#ID#';
--l_access_token app_user.access_token%type;
l_response CLOB;
l_response_json JSON;
l_update_json JSON;
l_parent JSON_LIST;
l_parent_id JSON;
l_file_id varchar2(200);
l_access_token varchar2(200);
BEGIN
set_access_token;
l_access_token := apex_util.get_session_state('GOOGLE_ACCESS_TOKEN');
l_response := authorized_request(
p_access_token => apex_util.get_session_state('GOOGLE_ACCESS_TOKEN')
, p_url => l_insert_url
, p_payload => p_data
, p_content_type => p_mime_type);
l_response_json := JSON(l_response);
l_file_id := json_ext.get_string(l_response_json, 'id');
l_update_url := replace(l_update_url, '#ID#', l_file_id);
l_update_json := JSON;
l_update_json.put('title', p_filename);
l_parent_id := JSON;
JSON.put(l_parent_id, 'id', p_folder_id);
l_parent := JSON_LIST;
l_parent.append(l_parent_id.to_json_value);
JSON.put(l_update_json, 'parents', l_parent);
l_response := authorized_request(
p_access_token => l_access_token
, p_url => l_update_url
, p_payload => l_update_json.to_char
, p_method => 'PUT');
l_response_json := JSON(l_response);
return json_ext.get_string(
l_response_json
, 'id');
END create_file;
end google_drive;
/
@jjaviersuarez
Copy link

How use?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment