mortenbra / alexandria-plsql-utils Goto Github PK

Dear mortenbra!

After I have installed the whole package to my schema, I have tried to run the demo script of ews pkg but unfortunately I am facing with errors after errors. Please help me with these:

Instance info:
18.4.0.0.0
XE

1. getfolder, getitem, finditem etc... I've got the following error:
   Error report -
   ORA-06502: PL/SQL: numeric- or value error ()
   ORA-06512: at "SYS.UTL_RAW", line 380.
   ORA-06512: at "R1.NTLM_UTIL_PKG", line 145.
   ORA-06512: at "R1.NTLM_UTIL_PKG", line 293.
   ORA-06512: at "R1.NTLM_HTTP_PKG", line 466.
   ORA-06512: at "R1.MS_EWS_UTIL_PKG", line 300.
   ORA-06512: at "R1.MS_EWS_UTIL_PKG", line 677.
   ORA-06512: at line 5.

6502. 00000 - "PL/SQL: numeric or value error%s"
      *Cause: An arithmetic, numeric, string, conversion, or constraint error
      occurred. For example, this error occurs if an attempt is made to
      assign the value NULL to a variable declared NOT NULL, or if an
      attempt is made to assign an integer larger than 99 to a variable
      declared NUMBER(2).
      *Action: Change the data, how it is manipulated, or how it is declared so
      that values do not violate constraints.

What I have called:
`
declare
l_folder ms_ews_util_pkg.t_folder;
begin
debug_pkg.debug_on;
ms_ews_util_pkg.init('https://xxx/EWS/Exchange.asmx', someone, 'secret', 'file://home/orbit/wallets', 'secret2');

l_folder := ms_ews_util_pkg.get_folder (ms_ews_util_pkg.g_folder_id_inbox);
debug_pkg.printf('folder id = %1, display name = %2', l_folder.folder_id, l_folder.display_name);
debug_pkg.printf('total count = %1', l_folder.total_count);
debug_pkg.printf('child folder count = %1', l_folder.child_folder_count);
debug_pkg.printf('unread count = %1', l_folder.unread_count);
end;
`

After I have inserted some dbms putline code into the pkg i have the following values may causing this error:
p_number=1 p_length=4
p_number=-1576488441 p_length=4
l_domain_str=
p_number=5 p_length=2
p_number=5 p_length=2
p_number=40 p_length=4
p_number= p_length=2

This is where the error message points:
l_returnvalue := utl_raw.substr(utl_raw.cast_from_binary_integer(p_number, utl_raw.little_endian), 1, p_length);

--

2. After I have installed the package I have found these types created, can you tell me what these are for?
   SYS_PLSQL_9E442D16_45_1
   SYS_PLSQL_9E442D16_73_1
   SYS_PLSQL_9E442D16_DUMMY_1
   SYS_PLSQL_EFCB9540_104_1
   SYS_PLSQL_EFCB9540_150_1
   SYS_PLSQL_EFCB9540_158_1
   SYS_PLSQL_EFCB9540_340_1
   SYS_PLSQL_EFCB9540_348_1
   SYS_PLSQL_EFCB9540_410_1
   SYS_PLSQL_EFCB9540_47_1
   SYS_PLSQL_EFCB9540_9_1
   SYS_PLSQL_EFCB9540_96_1
   SYS_PLSQL_EFCB9540_DUMMY_1

Thank you for your help and time!

Regards, Andras

If flex_ws_api.make_request experiences an exception, the exception block returns null if it's because the response could not be converted to XML (I'm not sure this is a good idea) and returns without a value for other exceptions. This causes "ORA-06503: PL/SQL: Function returned without value" to be raised.

It seems to me this block should re-raise the exception for other errors.

Is returning null on a "cannot convert to XML" error the best idea? There's no longer any way to know what the server response actually was, then.

Hi there,
I would like to confirm which license is used in this project, is it MIT?
Can you update the repository with the license file?
Thanks

A colleague discovered that ooxml_util_pkg.get_xlsx_cell_value returns NULL if some the contents of the cell have formatting applied to them. This does not happen if the cell itself has formatting applied. For example, a cell containing "foo_bar_" (with only "bar" in italics) will return NULL.

I think that's because line 404 of ooxml_util_pkg uses this XPath expression to extract the text node from sharedStrings.xml:

'/sst/si[' || (l_string_index + 1) || ']/t/text()'

So it's looking for a <t> element that is a direct descendant of an <si> element.

The relevant part of the sharedStrings.xml file for the above example lists this string as:

<si>
    <r>
        <t>foo</t>
    </r>
    <r>
        <rPr>
            <i/>
            <sz val="11"/>
            <color theme="1"/>
            <rFont val="Calibri"/>
            <family val="2"/>
            <scheme val="minor"/>
        </rPr>
        <t>bar</t>
    </r>
</si>

I'm not sure what the correct behavior would be in this case. It seems to be a user would most likely expect the concatenated text inside all descendant <t> elements inside the <si>, even if there was a intervening <r> element.

Hello Morten,
We use "alexandria-plsql-utils", and Tokyo Region plans to abolish the AWS signature version 2 on June 24, 2019.
I have two questions
①Is "alexandria-plsql-utils" compatible with AWS signature version 4?

②If AWS Signature Version 4 is not supported, is there a workaround?
　Additional patch etc

If an Excel file contains special characters, they are returned with XML-encoding intact. For example, if my cell contains Special Characters: 'apos &amp <lt >gt "quot, then get_xlsx_cell_value returns Special Characters: 'apos &amp <lt >gt "quot. I believe this is because internally it uses .getstringval().

Hello,

I am attempting to use the ZIP package to unzip a set of files. Whenever I call get_file I normally error out on line 250. This is because line 244 always returns 0 for tl_fl_ind.

By getting to that point in the if's I am fairly confident in the blob and file_name inputs.

I tried a variety of different zip methods including a zip file that was created via this package. All of them returned tl_fl_ind of 0 and thus errored out in either the .copy at 250 or created corrupted blobs.

Is t_hd_ind + 42 the correct spot to find the tl_fl_ind?

This is not my strong suit so I apologize if I am missing something obvious or misinterpreted code.

Thanks,

Justin

In a XLSX workbook large enough that one of the XML files is over the 32767 byte limit, adding a comment will cause ORA-06502, PL/SQL: numeric or value error to be raised when finish() is called.

To replicate:

declare
    b blob;
begin
    xlsx_builder_pkg.clear_workbook;
    xlsx_builder_pkg.new_sheet( 'Sheet1' );
    for i in 1..10000 loop
        xlsx_builder_pkg.cell( 1, i, 'foo' );
    end loop;
    xlsx_builder_pkg.comment( p_row => 1, p_col => 1, 
        p_text => 'This is my note', p_author => 'me' );
    b := xlsx_builder_pkg.finish;
end;

When using slack_util_pkg on 12.2 I get the following error when trying to SEND_MESSAGE
ORA-24263: Certificate of the remote server does not match the target address.

Did some research to find: https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9536564700346663150

SOLUTION 

As of Oracle 12.2 the procedures of the utl_http package used to access remote webservices have a new parameter called https_host. One has to use this new parameter to avoid the error: 

For 12.2 the following can be added to slack_util_pkg as a fix/workaround
g_https_host string_util_pkg.t_max_db_varchar2 := 'slack.com';
...
, p_https_host => g_https_host
...

utPLSQL testing framework is released and no longer in beta. Could you please update the label in the list.

In readme.md URL for pl-sql-library-for-JSON needs to be updated to:

https://www.erasme.org/pl-sql-library-for-JSON

I would like to add a clickable mail to link and clickable hyper text links to the pdf documents I generate in pdf_builder_pkg, Could this be added to the code please or is there a way of passing this information in to the current code apis?

Thanks

Mark

It would be awesome if the ConvertID function was included in package.

I would like to convert EWS ID into OWA ID - so I can create a OWA link so users can be directed directly to web email.

In xlsx_builder_pkg.finish function
any usage of implicit type conversion should be avoided and changed with to_char(), otherwise error ORA-06502 appears when
pls_integer variable is appended to clob t_xxx together with varchar2 variable.
The error is raised when clob t_xxx length is greater than 32767, e.g. when exporting large sets of data to Excel. For instance,

        t_xxx := t_xxx || '<autoFilter ref="' ||
            alfan_col( nvl( workbook.sheets( s ).autofilters( a ).column_start, t_col_min ) ) ||
            nvl( workbook.sheets( s ).autofilters( a ).row_start, workbook.sheets( s ).rows.first() ) || ':' ||
            alfan_col( coalesce( workbook.sheets( s ).autofilters( a ).column_end, workbook.sheets( s ).autofilters( a ).column_start, t_col_max ) ) ||
            nvl( workbook.sheets( s ).autofilters( a ).row_end, workbook.sheets( s ).rows.last() ) || '"/>';

should be changed with

         t_xxx := t_xxx || '<autoFilter ref="' ||
            alfan_col( nvl( workbook.sheets( s ).autofilters( a ).column_start, t_col_min ) ) ||
            to_char(nvl( workbook.sheets( s ).autofilters( a ).row_start, workbook.sheets( s ).rows.first() ) ) || ':' ||
            alfan_col( coalesce( workbook.sheets( s ).autofilters( a ).column_end, workbook.sheets( s ).autofilters( a ).column_start, t_col_max ) ) ||
            to_char(nvl( workbook.sheets( s ).autofilters( a ).row_end, workbook.sheets( s ).rows.last() ) ) || '"/>';       

To_char is already used somewhere inside the code, I think Anton Sheffer forgot about it in newer added features, such as autoFilter.
Unfortunately, I don't know whether he contributes to this code or not anymore and it's impossible to comment his original page , so I'm adressing to you.

In ms_ews_util_pkg, the get_item function returns a record with the item_id and change_key elements always NULL, even when it successfully retrieves an email. The find_items_as_list function correctly populates these in its array, though.

It has to do with how the XML result is being parsed. I should have a fix shortly.

What steps will reproduce the problem?
1. @install_amazon.sql in a database that does not have APEX installed
2. select * from dba_errors;
3.

What is the expected output? What do you see instead?
"amazon S3" only packages will not install unless APEX is present.

What version of the product are you using? On what operating system?
ORACLE 11.2.0.4 on RedHat linux

Please provide any additional information below.

There are a couple of references to APEX functions, which can likely be 
replaced with generic ones, so that S3 modules can be installed independently.

amazon_aws_s3_pkg.pkb:
  '&Signature=' || wwv_flow_utilities.url_encode2 (l_signature);

debug_pkg.pkb:
  if (apex_application.g_debug) then
    apex_application.debug (p_msg);
  ...
  if (m_debugging or apex_application.g_debug) then 

I just ran into the same problem that I found here: pellegrinimarcos/plsql-utils#26

Oracle 12cR2 introduced a new parameter to utl_http.begin_request, called https_host, for when the URL and the SSL certificate do not match. Using the packages as they are now raises ORA-24263: Certificate of the remote server does not match the target address. There's currently no way to specify https_host for these packages.

I'm guessing this would need some sort of conditional compilation, since the function declaration in utl_http is different.

See also Doc ID 2275666.1 on Oracle Support.

I don't really want to fork and create a full pull request just to fix your link to PL/JSON. The project has moved to https://github.com/pljson/pljson

I'm not sure what's going on here, but I'm guessing it has something to do with intermediate XML. It seems that a query with dbms_xmlgen.convert() in it can cause invalid JSON to be produced.

declare
  c clob;
begin
  c := alexandria.json_util_pkg.sql_to_json( 'select dbms_xmlgen.convert(''abc "def" hij'') as foo from dual' );
  dbms_output.put_line(c);
end;
/

Produces:

{"ROWSET":{"ROW":{"FOO":"abc "def" hij"}}}

Instead of:

{"ROWSET":{"ROW":{"FOO":"abc \"def\" hij"}}}

Hello.

Is this still available to publc with BSD license?

I want to use it in an internal APEX project (i work in a distribution retail organization).

Can i use it freely ?

Is there a way to use NTLMv2 with this package?

Thanks

Hello,

Im a plsql developer. I want to contribute this project.

Best Regards,
Kemal

I was just using xlsx_builder_pkg for a project, and I think it should have invoker rights, mostly because of the query2sheet function. Without that setting, that function runs as whatever schema the package is installed in (in my case "alexandria"), so to access the tables used by the calling function, you have to grant select access to that schema. That seems like a security issue. I could install the package in whatever schema needs to use it, but I'm too lazy to figure out its dependencies and duplicating it all over the database seems inelegant.

The json_util_pkg will generate invalid JSON with the value getting converted is a number between 0 and 1.

BEGIN
  dbms_output.put_line( json_util_pkg.sql_to_json( 'select 0.5 as x from dual' ));
END;
/

Generates:

{"ROWSET":{"ROW":{"X":.5}}}

Neither IE 11 nor Firefox 35 will parse this result. The JSON specification says that numbers must begin with a digit. Numbers beginning with the decimal point are invalid.

I'm trying to format an Excel column as the Comma or Accounting format. I was able to use '#,##0.00_);(#,##0.00);"- "' (directly in Excel). The get_NumFmt does not seem to accept format names, and when I tried this custom format string, Excel did not like the .xslx file ("We found a problem with some content..."). I also looked at using OraFmt2Excel, but Oracle has no format model for showing negative numbers in parenthesis.
1- Is there a way to format a column in the Comma or Accounting format?
2- Even when I pass a valid get_NumFmt call as a parameter to set_column, it seems this must be done before the column's cells are populated. Why is it ignored when called after?

Does function clob_to_csv ( orc/csv_util_pkg.pkb ) support quoted newline characters? (LF|CRLF, \n|\r\n)

https://datatracker.ietf.org/doc/html/rfc4180#page-2

"escaped = DQUOTE *(TEXTDATA / COMMA / CR / LF / 2DQUOTE) DQUOTE"

The ooxml_util_pkg.get_worksheets_list returns the workbook's spreadsheets ordered by sheetid. That sheeted parameter seems to be assigned by a sequence as sheets are created, so the sheets come back in the order in which they are created, not as they appear in the workbook. That seems unintuitive.

@datRedHeadedGuy and I agree that it would be more intuitive for these sheets to be returned in document order. I think that can be done just by removing the ORDER BY clause.

Hi, I recently started to use the xlsx package and im getting some weird errors on large excel files that i will call them EXCELSAMPLE.xlsx, the errors printed by excel are:

EXCELSAMPLE.xlsx - Replaced Part /xl/worksheets/sheet1.xml part with XML error. '>' expeted. Line 7, column 130352.
EXCELSAMPLE.xlsx - Replaced Part /xl/worksheets/sheet1.xml part with XML error. The name in the end tag of the element must match the element type in the start tag. Line 7 column 130527.
EXCELSAMPLE.xlsx - Replaced Part /xl/worksheets/sheet1.xml part with XML error. Quote expected. Line 7, column 130353.
EXCELSAMPLE.xlsx - Replaced Part /xl/worksheets/sheet1.xml part with XML error. Equal expected. Line 7, column 130353.
EXCELSAMPLE.xlsx - Replaced Part: /xl/worksheets/sheet1.xml part with XML error. An attribute value must not contain '<'. Line 7, column 130353.
EXCELSAMPLE.xlsx - Replaced Part: /xl/worksheets/sheet1.xml part with XML error. Illegal qualified name character. Line 7, column 130353.

I'm getting this error on more than one excel and in different columns, so i want to know how to solve it

this can be because im trying to export to excel very large files? (Large files aka: 11,500 Kb)

i appreciate any kind of help with this, thank you, i'll be reading this thread so i'll be glad to answer any question you have

I have used this in oracle can you tell me for postgres .

l_ntlm_auth_str := plsql_utils.ntlm_http_pkg.begin_request(a_url, a_username, a_password);

I want to use same functionality in postgres.

Hello Morten,
This regarding Amazon S3. I can upload files to a bucket in US, but when I try to upload files to a bucket in Frankfurt, I get this error.

ORA-20000: The authorization mechanism you have provided is not supported. Please use AWS4-HMAC-SHA256.

I noticed a problem with the csv_util_pkg package (function clob_to_csv). When the size of the CSV-file is large (in my case more than 7,000,000 records) an error occurs "ORA-01426: numeric overflow". The reason for the error is that the variables used inside the function are of the PLS_INTEGER type has a range of up to 2147483647.

function clob_to_csv (p_csv_clob in clob,
                      p_separator in varchar2 := g_default_separator,
                      p_skip_rows in number := 0,
                      p_rows_count in number default null) return t_csv_tab pipelined
as
  l_csv_clob               clob;
  l_line_separator         varchar2(2) := chr(13) || chr(10);
  l_last                   pls_integer; -- <<< these variables
  l_current                pls_integer; -- <<< these variables
  l_line                   varchar2(32000);
  l_line_number            pls_integer := 0; -- <<< these variables
  l_from_line              pls_integer := p_skip_rows + 1; -- <<< these variables

Replacing PLS_INTEGER with INTEGER solves this problem. Is this valid? Or is the use of PLS_INTEGER the only correct one?

In xlsx_builder_pkg, multiple calls to the string version of cell() produce incorrect output. The contents of later cells are being put into the cell that should be empty. I believe this is due to the fact that strings are stored in a table of pls_integer index by varchar2(32767) and the string value is being used as the index. In fact, I think the fact that "ORA-06502: PL/SQL: numeric or value error: NULL index table key value" is not being raised is an Oracle bug.

Demo:

declare
    x blob;
    n varchar2(1);
begin
    xlsx_builder_pkg.clear_workbook();
    xlsx_builder_pkg.new_sheet();
    xlsx_builder_pkg.cell( 1, 1, 1 );
    xlsx_builder_pkg.cell( 2, 1, 12 );
    xlsx_builder_pkg.cell( 3, 1, 123 );
    xlsx_builder_pkg.cell( 4, 1, 1234 );
    xlsx_builder_pkg.cell( 1, 2, 'ABC' );
    xlsx_builder_pkg.cell( 2, 2, n );
    xlsx_builder_pkg.cell( 3, 2, n );
    xlsx_builder_pkg.cell( 4, 2, 'XYZ' );
    x := xlsx_builder_pkg.finish;
end;

The result xlsx file has "XYZ" in cells C2 and D2, when it should only be D2.

Hi!

This is a very great implementation, well done!

Did you planned implementing OAUTH2 authentication in the MS_EWS_UTIL_PKG?

Thank you,
regards ravaia

Compiling ooxml_util_pkg.pkb raise ORA-00913:

Package Body OOXML_UTIL_PKG compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
83/5      PL/SQL: SQL Statement ignored
85/7      PL/SQL: ORA-00913: too many values
Errors: check compiler log

This is pretty simple. A change added "state" to t_xlsx_sheet_properties, but that did not made it into the select list on line 83.

I have a APEX 5.0 App with an Page and place the initialization code in a before-header process.
And I have a Classic Report with Query:

SELECT * table(app_aputils.ms_ews_util_pkg.find_folders())

which returns error:

"PL/SQL: Function returned without value"

I tried the following code in SQL Workshop:
`
DECLARE
test INTEGER;
BEGIN
app_aputils.debug_pkg.debug_on;
app_aputils.ms_ews_util_pkg.init("xxx", "xxx", "xxx", "xxx", "xxx");

SELECT Count(*) INTO test from table(app_aputils.ms_ews_util_pkg.find_folders()) ;
DBMS_OUTPUT.PUT_LINE('Total = ' || TO_CHAR(test));
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SUBSTR(SQLERRM, 1, 200));
END;
`

This Code works fine, when I switch of the autocommit.
With the auotcommit Option it raise the same error:

ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "APP_APUTILS.FLEX_WS_API", line 399
ORA-06512: at "APP_APUTILS.MS_EWS_UTIL_PKG", line 305
ORA-06512: at "APP_APUTILS.MS_EWS_UTIL_PKG",

A colleague wanted to change the color of some text in an Excel file and I found that the xlsx_builder_pkg in this repo is out of date from the original author's (Anton Scheffer) version.

Hi Morten,

Now that you're on Github I don't need my repository anymore https://github.com/eggplantpasta/plsql-utils. I'm about to delete it but since it has a shorter name than yours I thought I'd offer to transfer it to you first. If you want it (I imagine it's too late now). Thanks for all your awesome code.

Cheers, Brett.

Hello!

The call to owa_util.get_cgi_env('SOAP_BODY') in your example ora/soap_server_pkg.pkb is returning null.

I'm running modowa version 2.11.17.

Could you tell if it is necessary to install or customize something to have this cgi variable filled?

This is the request:

curl --location 'https://{my-server}/servidor_soap_cdc_cobranca.handle_request'
--header 'Accept-Encoding: gzip,deflate'
--header 'Content-Type: text/xml;charset=UTF-8'
--header 'SOAPAction: "http://hst4.omni.com.br/ws_cob/ws_cdc_cobranca/get_token"'
--data '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ws="http://hst4.omni.com.br/ws_cob">
soapenv:Header/
soapenv:Body
ws:GetToken
12345678901011
1
1.0
</ws:GetToken>
</soapenv:Body>
</soapenv:Envelope>'

Can you add a function to dynamically add multiple rows to a table?

Thanks

Recently my code started failing with an error "Duplicate Headers" being returned from AWS, e.g. when uploading an object using AMAZON_AWS_S3_PKG.

Note: I was able to resolve this by editing the package to remove the bits that set the "Host" header. I don't know why that fixed it but it seems to work fine without this.

  --l_header_names(1) := 'Host';
  --l_header_values.extend;
  --l_header_values(1) := g_aws_host_s3;`

(this edit required adjusting the array indices for the other headers)

The pdf_string function in pdf_builder_pkg appears to 'hang' the session when the input text contains a character to be escaped.

The offending line appears to be:

dbms_lob.copy( t_rv, t_rv, dbms_lob.lobmaxsize, t_ind + 1, t_ind );

It appears to be the combination of having the same lob as source and destination and the use of dbms_lob.maxsize that causes the issue: if you calculate the size of the required copy e.g.

dbms_lob.copy( t_rv, t_rv, dbms_lob.getlength(t_rv) - t_ind + 1, t_ind + 1, t_ind );

the issue is resolved. Similarly defining a destination lob and assigning it the value of t_rv before the copies and assigning the resulting lob back to t_rv after the copies also addresses the issue.

Maybe this is a result of the Oracle 18c lob-related performance improvements?

There is no GOOGLE_CALENDAR_PKG in the source code.