Forum Stats

  • 3,768,299 Users
  • 2,252,772 Discussions
  • 7,874,520 Comments

Discussions

oauth.create_client using client_credentials grant fails with "ORA-00922: missing or invalid option"

Attempting to create some ORDS RESTful client credentials with this block executed in SQL Workshop (logged in with APEX Developer user):

BEGIN 

oauth.create_client(

   p_name => 'BILogging'

   ,p_grant_type => 'client_credentials'

   ,p_owner => 'BI'

   ,p_description => 'Ancillary automation actions including logging'

   ,p_origins_allowed => null

   ,p_redirect_uri => '' --'urn:ietf:wg:oauth:2.0:oob'

   ,p_support_email => '[email protected]'

      ,p_support_uri =>'https://support.corp.com'

   ,p_privilege_names => 'bi.dmutils.privilege.logging'

);

commit;

END;


and also tried lighter version:


oauth.create_client(

 p_name => 'abc',

 p_grant_type => 'client_credentials',

 p_privilege_names => null

);


But I consistently get this exception:

ORA-00922: missing or invalid option

ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_180100", line 560

ORA-06512: at "SYS.DBMS_SYS_SQL", line 1658

ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_180100", line 546

ORA-06512: at "APEX_180100.WWV_FLOW_DYNAMIC_EXEC", line 1919


Has anyone else had success (or similar trouble) with oauth.create_client?


I'm pretty sure I've had success with this method before, though in a newer edition of APEX, I believe.


Key content of "About" dialog in APEX build:

Application Express

Details

Product Build 18.1.0.00.45

Schema Compatibility 2018.04.04

Host Schema APEX_PUBLIC_USER

Application Owner APEX_180100

Workspace ID 1316500422741850


CGI Environment

APEX_LISTENER_VERSION 18.3.0.r2701456

PLSQL_GATEWAY WebDb

GATEWAY_IVERSION 3

SERVER_SOFTWARE Mod-Apex

GATEWAY_INTERFACE CGI/1.1

SERVER_PORT 443


Database Version

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

CORE 12.2.0.1.0 Production

NLSRTL Version 12.2.0.1.0 - Production

PL/SQL Release 12.2.0.1.0 - Production

TNS for Solaris: Version 12.2.0.1.0 - Production

Character Set

NLS_CHARACTERSET: AL32UTF8

DAD CHARACTERSET: UTF-8

Tagged:

Best Answer

Answers

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,059 Employee
    Accepted Answer

    those are APEX error messages, what happens if you run that block in a SQLcl or SQL Developer session, logged in as same database user?

  • Justin Warwick
    Justin Warwick Member Posts: 119 Bronze Badge

    You led me to an error on my side: I am embarrassed to say that I had a "set serveroutput on" pasted into the top of the PL/SQL block, so "ORA-00922: missing or invalid option" is what you see (in APEX SQL workshop) if you execute in SQL Workshop with that directive. It works totally correctly in both SQL Workshop and conventional SQL client without the "set serveroutput on".

    I am sorry to have wasted your time, and thanks for you help, Jeff.