Solved

Custom menu type "PL/SQL Block" with window transfer

  • 26 October 2019
  • 15 replies
  • 3574 views

I would like to perform an action in one custom menu and then move to another window.

A simple but not user-friendly solution is to create two custom menus:

  • First: action type: “PL / SQL Block” - execute code (create a new row in the table),
  • Second: action type: “Create window with transfer” - go to window.

Is it possible to switch to another window in the “PL / SQL block” (run procedure / function)?

icon

Best answer by Mathias Dahl 10 November 2019, 21:28

View original

15 replies

Userlevel 7
Badge +21

I asked exactly the same on https://open.ifsworld.com/forum/Lists/TechnologyandInfrastructure/Flat.aspx?RootFolder=https%3a%2f%2fopen%2eifsworld%2ecom%2fforum%2fLists%2fTechnologyandInfrastructure%2fTransfer%20user%20to%20new%20page%20based%20upon%20SQL%20based%20RMB&FolderCTID=0x012002008DE2C81BB3ACBA4E86F2E1FE6C858CEE

Matt Wilson’s answer was:
-----

I have not tried this since IFS 7.5 so I do not know how it will react with current versions. 

 

You start by getting the address to the screen.  So, open the screen you want to go to.  The press and release the Alt key.  Select the view menu and select address bar or you can simply Ctrl+Shift+A.  This is the address to the screen.  Parameters are added like url parameters.  Goto the customer order screen and you will see that there is a Company parameter there already. 

Next you need the base address of the application server.  This can be found on the system parameters screen or fnd_setting_tab table.  You want the URL_EXT_SERVER parameter.  Concat your url with the address you acquired above with a "/" in between.

Last you need to use the oracle package UTL_HTTP to make the call to this URL.  Best of luck!

Matt
-----

Hope it helps you.

Userlevel 4
Badge +9

If you get it to work please share :)

Thank you very much for information. After simple tests,I got message:

Forbidden PLSQL code rejected: "UTL_HTTP"

FNDSQLFORBIDDEN: Forbidden PLSQL code rejected: "UTL_HTTP"

 

I think, in the case of Custom Menu (PL/SQL Block), the “UTL_HTTP” package is prohibited.

Maybe I'm doing something wrong, I will try again.

Userlevel 6
Badge +18

I wonder whether this is because the UTL_HTTP package doesn’t immediately work with https:// addresses, and you are perhaps using https in your IFS URL?

Here’s an article that seems to do a good job (i.e. I haven’t used it myself) of explaining some of the challenges and how to get UTL_HTTP to access https addresses:

https://oracle-base.com/articles/misc/utl_http-and-ssl

 

Nick

It seems to me that this is not a http:// or https:// problem. In PL / SQL Developer, the same query works well. :(

Userlevel 6
Badge +18

It could still work on one and not the other based on Oracle version (client for PLSQL Developer vs server for the DB) or a different in wallet/installed certificates

Userlevel 4
Badge +9

It seems to me that this is not a http:// or https:// problem. In PL / SQL Developer, the same query works well. :(

Any luck ? 
have tried myself to get it to work but no luck so far. 

No.

http:// also doesn't work. This is not a protocol problem.
The use of UTL_HTTP is blocked in the SQLRecognizer.class.

 

Userlevel 7
Badge +30

You will not be able to switch to another screen from a block of PL/SQL code. You need to keep two custom menus for this, to be executed in order, manually.

Userlevel 6
Badge +18

You will not be able to switch to another screen from a block of PL/SQL code. You need to keep two custom menus for this, to be executed in order, manually.

Thanks for confirming @Mathias Dahl .  This is the approach we ended up using here to workaround the same issue, but I had hoped that something cleaner might be possible.

Nick

Userlevel 7
Badge +30

There is one more option, which is a bit of a hack, but might work depending on your needs and restrictions. You would use a custom menu of the type that can run a local program. The program can be a simple cmd file. It can, using SQL*Plus or similar tool, login to the database and do what needs to be done (you can either hardcode the username and password, or ask for it). Then the script can open a URL that would open IFS, with the screen you want to go to. For the last step to work you need to start IEE with ClickOnce and not a direct exe call. Should not be hard to put together if you know your tools...

 

You will not be able to switch to another screen from a block of PL/SQL code. You need to keep two custom menus for this, to be executed in order, manually.

Thank you for your answer.

Alternative solution - add a new custom menu type through "modification".

Userlevel 7
Badge +18

Does anyone know how to call a package function inside of a URL?

Like....

ifsapf:[&c_custom_api.get_url(&order_no)]

 

If so, you could stuff the DML inside of a function with an autonomous transaction, then return from the function the UTF16LE+BOM base64 encoded data transfer. I've figured out everything but the URL syntax.

Userlevel 7
Badge +30

Does anyone know how to call a package function inside of a URL?

Like....

ifsapf:[&c_custom_api.get_url(&order_no)]

 

If so, you could stuff the DML inside of a function with an autonomous transaction, then return from the function the UTF16LE+BOM base64 encoded data transfer. I've figured out everything but the URL syntax.

Allowing that would be a possibly quite big security whole, so I think it will never happen, at least not as a generic feature. You could certainly program/customize your form to look for parameters in the URL that would make it do any kind of database call (or any kind of call/code), but that’s a special case. In Aurena, you can kind of do this via our REST APIs, but it’s a POST, not a GET and a different thing.

 

Userlevel 7
Badge +18

Edit: Disregard this comment. I'll edit this when I'm in front of a PC.

Reply