Skip to Main Content

Infrastructure Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

App Development with Oracle Cloud Infrastructure & Oracle Autonomous Database: Get Started (DEV5345)

Sergio-OracleSep 14 2019 — edited Sep 30 2019

Steps Used During Demonstrations for OracleCode 2019 Session DEV5345

Preparation before Demos

1. Launch Cloud Developer Image

Make sure you add your public ssh key. If you don’t have an ssh key pair, see the documentation on how to generate one.

2. ssh to your instance running Cloud Developer Image, set up tunnel

ssh -i <path to ssh private key> -L 5901:localhost:5901 opc@<instance IP address>

3. Set VNC password and start VNC server

$ vncpasswd

Then:

$ vncserver -geometry 1600x1200 -geometry 800x600 -geometry 1920x1200

4. Configure OCI Client and add API Key to Profile

Run

$ oci setup config

and follow the directions, providing the necessary OCIDs as described in the documentation on Required Keys and OCIDs.

Remember to upload your API key by following the instructions in the same documentation. If you accepted all the defaults during the oci client setup, the public key to upload is the output of this:

$ cat /home/opc/.oci/oci_api_key_public.pem

Create Database

5. Create Autonomous Transaction Processing Database using the OCI CLI

First, use the oci-metadata utility to assign your tenancy’s OCID (aka compartment id) to an environment variable. As of 9/16/2019, there is an issue with the Cloud Developer Image that requires the following command to be run before you begin:

$ sudo rm /var/tmp/oci-utils.log

After that, you can invoke the commands to

$ export C=`oci-metadata -g compartmentid --value-only`

$ oci db autonomous-database create --compartment-id $C --db-name <DATABASE NAME> --cpu-core-count 1 --data-storage-size-in-tbs 1 --admin-password “<YOUR PASSWORD>”

You should see output similar to:

    {

      "data": {

        "autonomous-container-database-id": null,

        "compartment-id": "ocid1.tenancy.oc1..aaaaaaaa5eldpsknwduzu4m533r3h5oxthv6y ydmyeqok3lunzc6ri3sjw7q",

        "connection-strings": null,

        "connection-urls": null,

        "cpu-core-count": 1,

        "data-storage-size-in-tbs": 1,

        "db-name": "owadb1",

        "db-version": null,

        "db-workload": "OLTP",

        "defined-tags": {},

        "display-name": "autonomousdatabase20190915163711",

        "freeform-tags": {},

        "id": "ocid1.autonomousdatabase.oc1.uk-london-1.abwgiljrahdoyzxqemcriad7lka dd22ofc3trsvojg4y53dfs4xlmnoomdhq",

        "is-auto-scaling-enabled": false,

        "is-dedicated": false,

        "is-preview": false,

        "license-model": "BRING_YOUR_OWN_LICENSE",

        "lifecycle-details": null,

        "lifecycle-state": "PROVISIONING",

        "service-console-url": null,

        "time-created": "2019-09-15T16:37:11.073000+00:00",

        "used-data-storage-size-in-tbs": null,

        "whitelisted-ips": null

      },

      "etag": "5ed81868",

      "opc-work-request-id": "ocid1.coreservicesworkrequest.oc1.uk-london-1.abwgilj rviyijwgzwqucpirxco2632wd2exz7th52elxvi4t72yqussyzyka"

    }

5.1 Check on the Database Provisioning Progress

Next, export the the ID of the database being provision to an environment variable, DB_ID so you can use it later.

$ export DB_ID=`oci db autonomous-database list --compartment-id $C | jq -r '.data[] | select( ."db-name" == “<DATABASE NAME>" ).id'`

You can check the database state with the following command. Initially, this command will return PROVISIONING

$ oci db autonomous-database get --autonomous-database-id $DB_ID | jq -r '.data["lifecycle-state"]'

AVAILABLE

Get Connected using SQLcl

6.Download and extract Credentials (wallet)

$ oci db autonomous-database generate-wallet --autonomous-database-id $DB_ID --password <YOUR PASSWORD> --file wallet.zip

7.Set TNS_ADMIN environment variable, add it to .bash_profile, and extract wallet.zip

$ export TNS_ADMIN="`cat /etc/ld.so.conf.d/oracle-instantclient.conf`/network/admin"

$ sudo -E unzip ~/wallet.zip -d $TNS_ADMIN

8. Start and configure SQLcl

$ sql /nolog

SQL> set cloudconfig /home/opc/wallet.zip

9. Connect to admin user

`SQL> connect admin/<YOUR PASSWORD>@<YOUR DATABASE NAME>_high

Get connected with SQL Developer

10. Start VNC client and initialize the Oracle Linux desktop

Start a VNC client, connect to vnc://localhost:5901. Follow the instructions to initialize the Oracle Linux desktop.

11. Start SQL Developer and Create a Connection

From the main Desktop menu, choose Applications > Programming > SQL Developer

To create a database connection (See figure 4.):

In the connections panel, click the icon to create a New Database Connection…

  • Name your connection
  • For Connection Type, choose Cloud Wallet
  • Browse for the wallet.zip you downloaded earlier

You can leave the default Service unless you have other Autonomous Databases in this tenancy

Connecting Python To Autonomous Database

12 Interactive Python

Using system python version 2.7, you can import the already installed cx_Oracle module and connect to Autonomous Database like so:

$ python

>>> import cx_Oracle

>>> db=cx_Oracle.connect("admin/<YOUR PASSWORD>@<YOUR DATABASE NAME>_high")

>>> db.version

>>> '18.4.0.0.0'

For Python 3.6, we can install the cx_Oracle module via pip:

$ sudo python3 -m pip install cx_Oracle

WARNING: Running pip install with root privileges is generally not a good idea. Try `__main__.py install --user` instead.

Collecting cx_Oracle

  Downloading https://files.pythonhosted.org/packages/6a/6f/0ab8c62992bb694c851f9cd6d19974d27e9fca8970fc27794b9653fd174f/cx_Oracle-7.2.2-cp36-cp36m-manylinux1_x86_64.whl (731kB)

    100% |████████████████████████████████| 737kB 1.5MB/s

Installing collected packages: cx-Oracle

Successfully installed cx-Oracle-7.2.2

Then, again, to connect:

$ python3

Python 3.6.8 (default, Aug  7 2019, 08:02:28)

[GCC 4.8.5 20150623 (Red Hat 4.8.5-39.0.1)] on linux

Type "help", "copyright", "credits" or "license" for more information.

>>> import cx_Oracle

>>> db=cx_Oracle.connect("<YOUR PASSWORD>@<YOUR DATABASE NAME>_high")

>>> db.version

'18.4.0.0.0'

Connecting Node.js to Autonomous Database using node-oracledb

13. Based on sample code from from node-oracledb repo on github.com/oracle

Clone the node-oracledb repo from the Oracle organization on github.com

$ git clone https://github.com/oracle/node-oracledb

Edit dbconfig.js using VSCode

  • Open Visual Studio Code via Desktop menu Applications > Programming > VsCode - OSS
  • Open dbconfig.js in /home/opc/node-oracledb/examples
  • Change the username, password and connection string to match your details and
  • Save the file

Run connect.js

The Node.js 10.x as well as the node-oracledb module version 4 are already installed, so you are ready to run connect.js

$ node connect.js

Comments

BluShadow
The majority of those commands are SQL*Plus commands. That means that they are specific to SQL*Plus and are not part of SQL or PL/SQL which is what you write your procedures/packages with.

Tell us more about what you are trying to achieve and we can advise the best way, because this may be that you put everything in a procedure and code the requirements to generate output or you put everything in a script and execute it via a job etc.
221158
You can include the select statement in a cursor within a database procedure and use dynamic SQL (execute immediate to execute the DDL).

Christopher Soza
Oracle BI DBA
Orix Consultancy Services Ltd
b: http://sozaman.blogspot.com
519811
The system setup is that from Oracle 10g database, data will be copied to locally deployed Oracle XE databases. In order to do that, tables in Oracle XE should be truncated. But before truncating the table, constraints should be disabled first.Then after inserting records from Oracle 10g, constraints shall be enabled again.

I hope you could help me with this one. Thanks in advance.
Marwim
Hello sidkaterin,

here is a package I use for development. Never give access to this package to a normal user!

Regards
Marcus
CREATE OR REPLACE PACKAGE  my_trunc
IS
PROCEDURE disable_triggers;
PROCEDURE enable_triggers;
PROCEDURE disable_constraints;
PROCEDURE enable_constraints;
PROCEDURE truncate_table(p_tabelle IN VARCHAR2);

END my_trunc;
/

CREATE OR REPLACE PACKAGE BODY my_trunc
IS

PROCEDURE disable_triggers
IS
    cur_name                INTEGER;
    rows_processed          INTEGER;
BEGIN
    cur_name := dbms_sql.open_cursor;
    FOR c_trg IN(
        SELECT  'ALTER TRIGGER ' || trigger_name || ' DISABLE ' stmnt
        FROM    user_triggers
        )
    LOOP
        dbms_sql.parse (
                 cur_name
                ,c_trg.stmnt
                ,dbms_sql.v7
                );
        rows_processed := dbms_sql.execute (cur_name);
    END LOOP;
    dbms_sql.close_cursor (cur_name);
EXCEPTION
    WHEN OTHERS THEN
        dbms_sql.close_cursor (cur_name);
        RAISE_APPLICATION_ERROR (
                 -20999
                ,'Disable Trigger: '||SUBSTR(SQLERRM, 1, 100));
END disable_triggers;

PROCEDURE enable_triggers
IS
    cur_name                INTEGER;
    rows_processed          INTEGER;
BEGIN
    cur_name := dbms_sql.open_cursor;
    FOR c_trg IN (
        SELECT  'ALTER TRIGGER ' || trigger_name || ' ENABLE ' stmnt
        FROM    user_triggers
        )
    LOOP
        dbms_sql.parse (
                 cur_name
                ,c_trg.stmnt
                ,dbms_sql.v7
                );
        rows_processed := dbms_sql.execute (cur_name);
    END LOOP;
    dbms_sql.close_cursor (cur_name);
EXCEPTION
    WHEN OTHERS THEN
        dbms_sql.close_cursor (cur_name);
        RAISE_APPLICATION_ERROR (
                 -20999
                ,'Enable Trigger: '||SUBSTR(SQLERRM, 1, 100)
                );
END enable_triggers;

PROCEDURE disable_constraints
IS
    cur_name                INTEGER;
    rows_processed          INTEGER;
BEGIN

    cur_name := dbms_sql.open_cursor;
    FOR c_con IN (
        SELECT  'ALTER TABLE ' || table_name || ' DISABLE CONSTRAINT ' || constraint_name || ' ' stmnt
        FROM    user_constraints
        WHERE   constraint_type = 'R'
        )
    LOOP
        dbms_sql.parse (
                 cur_name
                ,c_con.stmnt
                ,dbms_sql.v7
                );
        rows_processed := dbms_sql.execute (cur_name);
    END LOOP;
    dbms_sql.close_cursor (cur_name);

    cur_name := dbms_sql.open_cursor;
    FOR c_con IN (
        SELECT  'ALTER TABLE ' || table_name || ' DISABLE CONSTRAINT ' || constraint_name || ' ' stmnt
        FROM    user_constraints
        WHERE   constraint_type != 'R'
        )
    LOOP
        dbms_sql.parse (
                 cur_name
                ,c_con.stmnt
                ,dbms_sql.v7
                );
        rows_processed := dbms_sql.execute (cur_name);
    END LOOP;
    dbms_sql.close_cursor (cur_name);

EXCEPTION
    WHEN OTHERS THEN
        dbms_sql.close_cursor (cur_name);
        RAISE_APPLICATION_ERROR (
                 -20999
                ,'Disable Constraints: '||
                 SUBSTR(SQLERRM, 1, 100)
                );
END disable_constraints;

PROCEDURE enable_constraints
IS
    cur_name                INTEGER;
    rows_processed          INTEGER;
BEGIN

    cur_name := dbms_sql.open_cursor;
    FOR c_con IN (
        SELECT  'ALTER TABLE ' || table_name || ' ENABLE CONSTRAINT ' || constraint_name || ' ' stmnt
        FROM    user_constraints
        WHERE   constraint_type != 'R'
        )
    LOOP
        dbms_sql.parse (
                 cur_name
                ,c_con.stmnt
                ,dbms_sql.v7
                );
        rows_processed := dbms_sql.execute (cur_name);
    END LOOP;
    dbms_sql.close_cursor (cur_name);

    cur_name := dbms_sql.open_cursor;
    FOR c_con IN (
        SELECT  'ALTER TABLE ' || table_name || ' ENABLE CONSTRAINT ' || constraint_name || ' ' stmnt
        FROM    user_constraints
        WHERE   constraint_type = 'R'
        )
    LOOP
        dbms_sql.parse (
                 cur_name
                ,c_con.stmnt
                ,dbms_sql.v7
                );
        rows_processed := dbms_sql.execute (cur_name);
    END LOOP;
    dbms_sql.close_cursor (cur_name);

EXCEPTION
    WHEN OTHERS THEN
        dbms_sql.close_cursor (cur_name);
        RAISE_APPLICATION_ERROR (
                 -20999
                ,'Enable Constraints: '||
                 SUBSTR(SQLERRM, 1, 100)
                );
END enable_constraints;

PROCEDURE truncate_table
    (
     p_tabelle              IN VARCHAR2
    )
IS
    cur_name                INTEGER;
    v_statement             VARCHAR2(500);
    rows_processed          INTEGER;
BEGIN
    v_statement     := 'TRUNCATE TABLE ' || p_tabelle || ' ';
    cur_name        := dbms_sql.open_cursor;
    dbms_sql.parse (
             cur_name
           ,v_statement
            ,dbms_sql.v7
            );
    rows_processed  := dbms_sql.execute (cur_name);
    dbms_sql.close_cursor (cur_name);
EXCEPTION
    WHEN OTHERS THEN
        dbms_sql.close_cursor (cur_name);
        RAISE_APPLICATION_ERROR (
                 -20999
                ,'Truncate Table ' || p_tabelle || ': ' ||
                SUBSTR(SQLERRM, 1, 100)
                );
END truncate_table;

END my_trunc;
/
SHO ERR
Edited by: Marwim on 18.09.2008 07:33
Replaced lessThan greaterThan with != because using code-markup does not hinder the forum software from changing the characters.
519811
Thank you. I can review this and maybe would revise a few statements to fit my requirements.
247514
sidkaterin wrote:
set feedback off
set verify off
set echo off
prompt Finding constraints to disable...
set termout off
set pages 80
set heading off
set linesize 120
spool tmp_disable.sql;
select 'spool igen_disable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||
' DISABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c, user_tables u
where c.table_name = u.table_name;
--and c.constraint_type IN ('P','R','U');
select 'exit;' from dual;
set termout on
prompt Disabling constraints now...
set termout off
@tmp_disable.sql;
Or run your procedure here.
exec myprocedure ;
exit
/

however, there are other procedures that shall be done after disabling the constraints. I want to put them in one package. How do I call this from a procedure? When I tried to simply enclose it in a PROCEDURE format, the spool file command is not recognized. It returned error message. Hope anyone can help me with this. Thanks in advance.
1 - 6

Post Details

Added on Sep 14 2019
0 comments
196 views