Coding tutorials - Sql and Oracle database handling

Sql and Oracle database handling:

create table test

(

 c0 varchar2(10),

 c1 number default '100'

)

;

test.ctl:

load data

infile *

truncate

into table test

fields terminated by ','

trailing nullcols

(

 c0 char

)

begindata

1,

===============

BEGIN

 DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(

 credential_name => 'OCI$RESOURCE_PRINCIPAL',

 table_name => 'sales',

 file_uri_list => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/

c4u04/b/moviestream_gold/o/custsales/*.parquet',

 format => '{"type":"parquet","partition_columns":

[{name:"month","type":"varchar2(20)"}]}'

 );

END;

/

mgubar: Finally, here is the generated ddl:

CREATE TABLE sales

 ( "DAY_ID" TIMESTAMP (6),

 "GENRE_ID" NUMBER(19,0),

 "MOVIE_ID" NUMBER(19,0),

 "CUST_ID" NUMBER(19,0),

 "APP" VARCHAR2(4000 BYTE),

 "DEVICE" VARCHAR2(4000 BYTE),

 "OS" VARCHAR2(4000 BYTE),

 "PAYMENT_METHOD" VARCHAR2(4000 BYTE),

 "LIST_PRICE" BINARY_DOUBLE,

 "DISCOUNT_TYPE" VARCHAR2(4000 BYTE),

 "DISCOUNT_PERCENT" BINARY_DOUBLE,

 "ACTUAL_PRICE" BINARY_DOUBLE,

 "MONTH" VARCHAR2(20 BYTE)

)

 ORGANIZATION EXTERNAL

 ( TYPE ORACLE_BIGDATA

 DEFAULT DIRECTORY "DATA_PUMP_DIR"

 ACCESS PARAMETERS

 ( com.oracle.bigdata.fileformat=parquet

com.oracle.bigdata.filename.columns=["month"]

com.oracle.bigdata.file_uri_list="https://objectstorage.usashburn-1.oraclecloud.com/n/c4u04/b/moviestream_gold/o/custsales/*.parquet"

com.oracle.bigdata.credential.schema="ADMIN"

com.oracle.bigdata.credential.name="OCI$RESOURCE_PRINCIPAL"

com.oracle.bigdata.trimspaces=notrim

 )

 )

 REJECT LIMIT 0

 PARTITION BY LIST ("MONTH")

 (PARTITION "P1" VALUES (('2019-01'))

 LOCATION

 ( 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/c4u04/b/

moviestream_gold/o/custsales/month=2019-01/*.parquet'

 ),

 PARTITION "P2" VALUES (('2019-02'))

 LOCATION

( 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/c4u04/b/

moviestream_gold/o/custsales/month=2019-02/*.parquet'

 ),

 PARTITION "P3" VALUES (('2019-03'))

 LOCATION

 ( 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/c4u04/b/

moviestream_gold/o/custsales/month=2019-03/*.parquet'

 ),

PARTITION "P4" VALUES (('2019-04'))

 LOCATION

 ( 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/c4u04/b/

moviestream_gold/o/custsales/month=2019-04/*.parquet'

 ),

 ...

 PARTITION "P24" VALUES (('2020-12'))

 LOCATION

 ( 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/c4u04/b/

moviestream_gold/o/custsales/month=2020-12/*.parquet'

 ))

 PARALLEL ;

=====================

PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
   old_balance REAL;
   new_balance REAL;
   overdrawn   EXCEPTION;
BEGIN
   SELECT bal INTO old_balance FROM accts WHERE acctno = acct_id;
   new_balance := old_balance - amount;
   IF new_balance < 0 THEN
      RAISE overdrawn;
   ELSE
      UPDATE accts SET bal = new_balance WHERE acctno = acct_id;
   END IF;
EXCEPTION
   WHEN overdrawn THEN
      ...
END debit_account;