MaxPrint PLSQL API Documentation
Procedures and Functions
- Print_Payload
- Dbms_Print_Payload
- Get_Apex_Directory_Content
- Maxprint_Generate_Report
- Maxprint_Generate_Template
Print_Payload
This is a supporting procedure helps to retrieve clob output in htp.print function.
Parameter:
* P_Text Clob input
Return: Clob input in htp.p function.
Dbms_Print_Payload
This is a supporting procedure helps to retrieve clob output in dbms_output.
Parameter:
* P_Text Clob input
Return: Clob input in dbms_output.
Get_Apex_Directory_Content
This function returns any content from Oracle APEX Application or Workspace directories.
Parameter:
* P_Directory Value A for APEX Static Files Directory and W for Workspace Files Directory.
* P_File_Name Template File Name in above mentioned directory.
* P_App Application Id if P_Directory is set to 'A'.
* P_Workspace Workspace Id if P_Directory is set to 'W'
Return: The MaxPrint API response as a CLOB
Get_Json_From_Sql
This function returns json from sql provided, there are there styles of sql supported in this function
1) Json Object Sql
SELECT json_arrayagg(json_object('department_name' value d.department_name,
'department_number' value d.department_id,
'employees' value (select json_arrayagg(
json_object('employee_number' value e.employee_id,
'employee_name' value e.first_name || ' ' || e.last_name,
'salary' value e.salary,
'email' value e.email,
'phone_number' value e.phone_number,
'hire_date' value e.hire_date) returning clob)
from hr.employees e
where e.department_id = d.department_id
order by e.employee_id) returning clob) returning clob
) result
from hr.departments d
order by d.department_name2) Cursor Sql
SELECT d.department_name AS "department_name",
d.department_id AS "department_number",
CURSOR(SELECT e.employee_id AS "employee_number",
e.first_name||' '||e.last_name AS "employee_name",
e.salary,
e.email,
e.phone_number,
e.hire_date
FROM hr.employees e
WHERE e.department_id = d.department_id
ORDER BY e.employee_id) AS "employees"
FROM hr.departments d
ORDER BY d.department_name3) Simple Sql
SELECT d.department_name AS "department_name",
d.department_id AS "department_number",
e.employee_id AS "employee_number",
e.first_name || ' ' || e.last_name AS "employee_name",
e.salary,
e.email,
e.phone_number,
e.hire_date
FROM hr.departments d, hr.employees e
WHERE e.department_id = d.department_id
ORDER BY d.department_name, e.employee_idmaster query columns
Parameter:
* P_sql Value A for APEX Static Files Directory and W for Workspace Files Directory.
* p_sql_type 'J' for JSON Object sql , 'C' for Cursor sql , 'S' for simple sql , examples of these options are provided above.
* p_query_master_cols This parameter is used in case of simple sql only.
Returns: The JSON data response as a CLOB
Maxprint_Generate_Report (Clob Output)
This function is a wrapper to maxprint_api_call function which invokes a rest request to MaxPrint API with following parameters and return the rendered report in response.
Parameter:
* p_url The url endpoint of the MaxPrint API i.e. https://app.maxprint.io/api/generateReport .
* p_api_key The API key provided.
* p_template The template in docx format (clob content) in base64 URI.
* p_data The data provided to render report with \"report\" key as root element.
* p_output_type Three types are supported docx, pdf, html.
* p_wallet_path The filesystem path to a wallet if request is https ex., /home/oracle/wallet
* p_wallet_pwd The password to access the wallet.
* p_timeout The amount of time in seconds to wait for a response.
Returns: The MaxPrint API response as a CLOB
Maxprint_Generate_Report_B (Blob Output)
This function is a wrapper to maxprint_api_call function which invokes a rest request to MaxPrint API with following parameters and return the rendered report in response.
Parameter:
* p_url The url endpoint of the MaxPrint API i.e.https://app.maxprint.io/api/generateReport
* p_api_key The API key provided.
* p_template The template in docx format (clob content) in base64 URI.
* p_data The data provided to render report with "report" key as root element.
* p_output_type Three types are supported docx, pdf, html.
* p_wallet_path The filesystem path to a wallet if request is https ex., /home/oracle/wallet
* p_wallet_pwd The password to access the wallet.
* p_timeout The amount of time in seconds to wait for a response.
Returns: The MaxPrint API response as a BLOB
Maxprint_Generate_Template (Clob Output)
This function is a wrapper to maxprint_api_call function which invokes a rest request to MaxPrint API with following parameters and return the rendered report in response.
Parameter:
* p_url The url endpoint of the MaxPrint API i.e.https://app.maxprint.io/api/generateTemplate
* p_api_key The API key provided.
* p_data The data provided to render report with "report" key as root element.
* p_wallet_path The filesystem path to a wallet if request is https ex., /home/oracle/wallet
* p_wallet_pwd The password to access the wallet.
* p_timeout The amount of time in seconds to wait for a response.
Returns: The MaxPrint API response as a CLOB
Maxprint_Generate_Template_B (Blob Output)
This function is a wrapper to maxprint_api_call function which invokes a rest request to MaxPrint API with following parameters and return the rendered report in response.
Parameter:
* p_url The url endpoint of the MaxPrint API i.e.https://app.maxprint.io/api/generateTemplate
* p_api_key The API key provided.
* p_data The data provided to render report with "report" key as root element.
* p_wallet_path The filesystem path to a wallet if request is https ex., /home/oracle/wallet
* p_wallet_pwd The password to access the wallet.
* p_timeout The amount of time in seconds to wait for a response.
Returns: The MaxPrint API response as a BLOB
Examples
1a. Generate Report (Clob Output)
DECLARE
v_sql CLOB := 'SELECT d.department_name AS "department_name",
d.department_id AS "department_number",
CURSOR(SELECT e.employee_id AS "employee_number",
e.first_name||chr(32)||e.last_name AS "employee_name",
e.salary,
e.email,
e.phone_number,
e.hire_date
FROM hr.employees e
WHERE e.department_id = d.department_id
ORDER BY e.employee_id) AS "employees"
FROM hr.departments d
ORDER BY d.department_name';
v_Directory VARCHAR2(1) := 'A';
v_Filename VARCHAR2(100) := 'xxxxxxxxxx.docx';
v_App VARCHAR2(100) := 'xxx';
v_api_key VARCHAR2(100) := 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
v_template CLOB;
v_data CLOB;
v_output_type VARCHAR2(3) := 'pdf';
v_wallet_path VARCHAR2(50) := 'xxxxxxxxxxxxxxxxxxx';
v_wallet_password VARCHAR2(15) := 'xxxxxxxxxxxxxx';
v_timeout NUMBER := 6000;
v_debug BOOLEAN := FALSE;
v_result CLOB;
BEGIN
dbms_output.enable(1000000);
v_template := Maxprint_Plsql_Api.Get_Apex_Directory_Content(P_Directory => v_Directory,
P_File_Name => v_Filename,
P_App => v_App);
v_data := maxprint_json_utils.get_json_from_sql(p_sql => v_sql, p_sql_type => 'C');
v_result := Maxprint_Plsql_Api.Maxprint_generate_report(p_api_key => v_api_key,
p_template => v_template,
p_data => v_data,
p_output_type => v_output_type,
p_wallet_path => v_wallet_path,
p_wallet_password => v_wallet_password
);
Maxprint_Plsql_Api.Dbms_Print_Payload(v_result);
END; 1b. Generate Report (Blob Output)
DECLARE
v_sql CLOB := 'SELECT d.department_name AS "department_name",
d.department_id AS "department_number",
CURSOR(SELECT e.employee_id AS "employee_number",
e.first_name||chr(32)||e.last_name AS "employee_name",
e.salary,
e.email,
e.phone_number,
e.hire_date
FROM hr.employees e
WHERE e.department_id = d.department_id
ORDER BY e.employee_id) AS "employees"
FROM hr.departments d
ORDER BY d.department_name';
v_Directory VARCHAR2(1) := 'A';
v_Filename VARCHAR2(100) := 'xxxxxxxxxx.docx';
v_App VARCHAR2(100) := 'xxx';
v_api_key VARCHAR2(100) := 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
v_template CLOB;
v_data CLOB;
v_output_type VARCHAR2(3) := 'pdf';
v_wallet_path VARCHAR2(50) := 'xxxxxxxxxxxxxxxxxxx';
v_wallet_password VARCHAR2(15) := 'xxxxxxxxxxxxxx';
v_timeout NUMBER := 6000;
v_debug BOOLEAN := FALSE;
v_result BLOB;
BEGIN
dbms_output.enable(1000000);
v_template := Maxprint_Plsql_Api.Get_Apex_Directory_Content(P_Directory => v_Directory,
P_File_Name => v_Filename,
P_App => v_App);
v_data := maxprint_json_utils.get_json_from_sql(p_sql => v_sql,p_sql_type => 'C');
v_result := Maxprint_Plsql_Api.Maxprint_generate_report_b(p_api_key => v_api_key,
p_template => v_template,
p_data => v_data,
p_output_type => v_output_type,
p_wallet_path => v_wallet_path,
p_wallet_password => v_wallet_password
);
dbms_output.put_line(dbms_lob.getlength(v_result));
END;
2a. Generate Template (Clob Output)
DECLARE
v_sql CLOB := 'SELECT d.department_name AS "department_name",
d.department_id AS "department_number",
CURSOR(SELECT e.employee_id AS "employee_number",
e.first_name||chr(32)||e.last_name AS "employee_name",
e.salary,
e.email,
e.phone_number,
e.hire_date
FROM hr.employees e
WHERE e.department_id = d.department_id
ORDER BY e.employee_id) AS "employees"
FROM hr.departments d
ORDER BY d.department_name';
v_Directory VARCHAR2(1) := 'A';
v_api_key VARCHAR2(100) := 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
v_data CLOB;
v_wallet_path VARCHAR2(50) := 'xxxxxxxxxxxxxxxxxx';
v_wallet_password VARCHAR2(15) := 'xxxxxxxxxxxx';
v_result CLOB;
BEGIN
dbms_output.enable(100000);
v_data := maxprint_json_utils.get_json_from_sql(p_sql => v_sql,p_sql_type => 'C');
v_result := Maxprint_Plsql_Api.Maxprint_generate_template(p_api_key => v_api_key,
p_data => v_data,
p_wallet_path => v_wallet_path,
p_wallet_password => v_wallet_password
);
Maxprint_Plsql_Api.Dbms_Print_Payload(v_result);
END;
2b. Generate Template (Blob Output)
DECLARE
v_sql CLOB := 'SELECT d.department_name AS "department_name",
d.department_id AS "department_number",
CURSOR(SELECT e.employee_id AS "employee_number",
e.first_name||chr(32)||e.last_name AS "employee_name",
e.salary,
e.email,
e.phone_number,
e.hire_date
FROM hr.employees e
WHERE e.department_id = d.department_id
ORDER BY e.employee_id) AS "employees"
FROM hr.departments d
ORDER BY d.department_name';
v_Directory VARCHAR2(1) := 'A';
v_api_key VARCHAR2(100) := 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
v_data CLOB;
v_wallet_path VARCHAR2(50) := 'xxxxxxxxxxxxxxxxxxxxx';
v_wallet_password VARCHAR2(15) := 'xxxxxxxxxxxx';
v_result BLOB;
BEGIN
dbms_output.enable(100000);
v_data := maxprint_json_utils.get_json_from_sql(p_sql => v_sql,p_sql_type => 'C');
v_result :=Maxprint_Plsql_Api.Maxprint_generate_template_b(p_api_key => v_api_key,
p_data => v_data,
p_wallet_path => v_wallet_path,
p_wallet_password => v_wallet_password
);
dbms_output.put_line(dbms_lob.getlength(v_result));
END;