1

I am developing an APEX application on Oracle Cloud. Since I'm creating some functions and procedures I want to use utPLSQL to test these functions or procedures. If I follow the instructions of the installation for utPLSQL it says u need to download a zip file, and then unzip that file on the database server. There is a shell to acces in the Oracle Cloud environment, however it is unclear to me how I would use that shell to install utPLSQL in this environment. Unfortunately my Linux knowledge is limited, so I have a few questions:

  • How would I get/upload or maybe download the zip file of utPLSQL in/to this environment?
  • At what folder should this file be located?
  • How would the file be unzipped?

Thanks in advance!

HotFix
  • 157
  • 1
  • 11
  • where does it say it needs to be ran from the database server? you should be able to run it from sqlplus/sqlcl from any machine so long as you have SYSDBA access. – thatjeffsmith Mar 18 '21 at 16:50
  • @thatjeffsmith I think I misinterpreted that. I thought the files needed to be located on that server. – HotFix Mar 18 '21 at 17:08
  • I would be super interested in an answer for this question. It seems that you can't grant SYSDBA access to users on oracle cloud (I am using v19c, when trying to alter roles through the web "Database Actions" interface, the only roles I see are; CDB_DBA DBA LBAC_DBA PDB_DBA) – Ryan Apr 23 '21 at 15:24

2 Answers2

2

Here are the instructions for installing utPLSQL on the Oracle Autonomous Cloud DB, running 19c (At the time of writing utPLSQL supported max Oracle v19c).

  1. Download the package: https://github.com/utPLSQL/utPLSQL/releases/download/v3.1.10/utPLSQL.zip

  2. Unzip it - it really does not matter where you unzip it. You can use some program like winzip to unzip it. On Mac there is a default program installed.

  3. Open the file utPLSQL/source/install.sql in sqlDeveloper. The full path doesn't matter, on my computer it is /Users/connect/oracle/utPLSQL/source/install.sql (Mac)

  4. Click the run script button. Installing utPLSQL

  5. When it asks write admin as the user - this is the trick. Dont try to install it as ut3 user. Just use the admin user** Selecting schema

** It is possible to install it as a different user (ut3) however you neeed to grant roles and permissions. I couldn't get this to work properly - I could grant the roles using the script create_synonyms_and_grants_for_public.sql but it was complaining that I have insufficient privileges. You might have to play around with user roles in the web interface https://dba.stackexchange.com/questions/290401/how-to-grant-sysdba-access-to-users-on-oracle-cloud-v19c

** You can't create tablespaces on autonomous cloud databases, just for info. There is another script that creates the ut3 user - it asks for the tablespace. Basically its just alot eaisier to install it as admin... And it works. To see it in action see the documention examples: http://utplsql.org/utPLSQL/latest/userguide/getting-started.html

Ryan
  • 782
  • 1
  • 10
  • 25
1

Could you review this blog Apex-utPLSQL, this might be useful

Anupam Sinha
  • 334
  • 1
  • 5
  • Thanks for your answer, this is an apex application using and doing statistics on the utPLSQL framework. It still requires that you have installed utPLSQL in your database. And that is what I am trying to do. – HotFix Mar 18 '21 at 16:44
  • I managed to install this using sqlDeveloper. I set some access first in the console, im not sure if this really is nessessary though. https://dba.stackexchange.com/questions/290401/how-to-grant-sysdba-access-to-users-on-oracle-cloud-v19c Then I ran source/create_utplsql_owner.sql with SYSTEM as the tablespace and UT3 as the username. Then source/install.sql. Worked fine, but I still need to fix the permissions, there are some grant scripts. If I get it working I will let you know – Ryan Apr 26 '21 at 16:05