JSON_ELEMENT_T:
the supertype for the JSON_OBJECT_T, JSON_ARRAY_T, JSON_SCALAR_T and JSON_KEY_LIST object types.
Each of them extends JSON_ELEMENT_T as a subtype.
An instance of type JSON_ELEMENT_T, which is an in-memory representation of the JSON data, can be constructed only by parsing JSON text.
An instance of JSON_ELEMENT_T can be casted to a subtype instance using PL/SQL function - treat. DanBrother 發表在 痞客邦 留言(0) 人氣(71)
CREATE TABLE json_test_tab (
id RAW(16) NOT NULL,
data CLOB,
CONSTRAINT json_test_tab_pk PRIMARY KEY (id),
CONSTRAINT json_test_tab_json_chk CHECK (data IS JSON)
);INSERT INTO json_test_tab (id, data)
VALUES(SYS_GUID(),
'{
"name": "Irene Palmer",
"gender": "female",
"company": "SPEEDBOLT",
"email": "irenepalmer@speedbolt.com",
"phone": "+1 (845) 436-2413",
"address": "983 Roosevelt Place, Leyner, Illinois, 5767",
"registered": "2017-02-14T09:51:25 -08:00",
"latitude": 52.001456,
"longitude": 161.045489,
"friends": [
{
"id": 0,
"name": "Chelsea Waller"
},
{
"id": 1,
"name": "Thomas Mccall"
},
{
"id": 2,
"name": "Booth Crawford"
}
],
"favoriteFruit": "apple"
}');
INSERT INTO json_test_tab (id, data)
VALUES(SYS_GUID(),
'{
"name": "Emma Mcdonald",
"gender": "female",
"company": "BYTREX",
"email": "emmamcdonald@bytrex.com",
"phone": "+1 (951) 413-2195",
"address": "143 Arkansas Drive, Leola, Maryland, 5138",
"registered": "2017-02-25T04:04:50 -08:00",
"latitude": -28.083351,
"longitude": 30.897873,
"friends": [
{
"id": 0,
"name": "Powers Turner"
},
{
"id": 1,
"name": "Hinton Delgado"
},
{
"id": 2,
"name": "Barrera Hutchinson"
}
],
"favoriteFruit": "banana"
}');
INSERT INTO json_test_tab (id, data)
VALUES(SYS_GUID(),
'{
"name": "Solis Rollins",
"gender": "male",
"company": "GROK",
"email": "solisrollins@grok.com",
"phone": "+1 (911) 439-3918",
"address": "910 Bragg Court, Trail, Virginia, 3065",
"registered": "2014-07-15T02:58:32 -08:00",
"latitude": 37.970592,
"longitude": -79.152205,
"friends": [
{
"id": 0,
"name": "Deborah Parrish"
},
{
"id": 1,
"name": "House Rich"
},
{
"id": 2,
"name": "Bennett Aguirre"
}
],
"favoriteFruit": "apple"
}');
INSERT INTO json_test_tab (id, data)
VALUES(SYS_GUID(),
'{
"name": "Dorthy Hoffman",
"gender": "female",
"company": "ARTIQ",
"email": "dorthyhoffman@artiq.com",
"phone": "+1 (806) 592-2352",
"address": "257 Clarkson Avenue, Vowinckel, Virgin Islands, 1478",
"registered": "2016-07-18T10:20:27 -08:00",
"latitude": 34.322025,
"longitude": 13.665359,
"friends": [
{
"id": 0,
"name": "Kristine Herrera"
},
{
"id": 1,
"name": "Branch Phillips"
},
{
"id": 2,
"name": "Wall Myers"
}
],
"favoriteFruit": "strawberry"
}');
INSERT INTO json_test_tab (id, data)
VALUES(SYS_GUID(),
'{
"name": "Medina Mcbride",
"gender": "male",
"company": "AVENETRO",
"email": "medinamcbride@avenetro.com",
"phone": "+1 (875) 470-2147",
"address": "863 Ridgecrest Terrace, Rockbridge, Vermont, 366",
"registered": "2014-11-03T08:32:50 -08:00",
"latitude": 81.576249,
"longitude": -41.531397,
"friends": [
{
"id": 0,
"name": "Angeline Powers"
},
{
"id": 1,
"name": "Mcdowell Perry"
},
{
"id": 2,
"name": "Luz Moody"
}
],
"favoriteFruit": "strawberry"
}');
commit;DanBrother 發表在 痞客邦 留言(0) 人氣(57)
Oracle Password Policy Checking PL/SQL Function Using REGEXP_COUNT
create or replace function pwd_policy_check_fnc(pwd varchar2)
return pls_integer
is
v_ct smallint default null;
begin
v_ct:=REGEXP_COUNT(pwd, '^[a-zA-Z][a-zA-Z0-9]{7,}$'); -- Password with more than 7 Alphanumeric characters without special ones and start with alphabet
return(nvl(v_ct,0));
end;
/
DanBrother 發表在 痞客邦 留言(0) 人氣(20)
Reading Loaded Files from XML DB through Oracle Embedded PL/SQL Gateway
type myfilelist.bat
DanBrother 發表在 痞客邦 留言(0) 人氣(65)
Building a simple webpage using Embedded PL/SQL Gateway
-- Create a demo user
create user demo_user identified by demo_user default tablespace users temporary tablespace temp;
grant connect,resource to demo_user;
DanBrother 發表在 痞客邦 留言(0) 人氣(24)
Using Oracle REGEXP_SUBSTR function & CONNECT BY LEVEL clause to Decompose String
DanBrother 發表在 痞客邦 留言(0) 人氣(554)
Change public synonyms to private synonyms in Oracle
The following anonymous PL/SQL block will change the public synonyms granted by SCOTT to private synonyms:
. oraenv
sqlplus / as sysdba
grant create any synonym to scott;
grant drop public synonym to scott;
DanBrother 發表在 痞客邦 留言(0) 人氣(15)
Pragma Serially_Reusable Practice:
According to Oracle® Database PL/SQL Language Reference 11g Release 1 (11.1),
the pragma SERIALLY_REUSABLE indicates that the package state is needed only for the duration of one call to the server. After this call, the storage for the package variables can be reused, reducing the memory overhead for long-running sessions.
DanBrother 發表在 痞客邦 留言(0) 人氣(24)
Upload File using PL/SQL Web ToolkitStep 1: Create a document storage table called “
UPLOAD_FILES”
DanBrother 發表在 痞客邦 留言(0) 人氣(64)
Use of Quote operator "q" in Oracle 11g
The following SQL statements will produce the same results.
select q'<Welcome to DanBrother's Blog. It's fantastic!>' as text from dual;
select q'#Welcome to DanBrother's Blog. It's fantastic!#' as text from dual;
select q'+Welcome to DanBrother's Blog. It's fantastic!+' as text from dual;
select q'@Welcome to DanBrother's Blog. It's fantastic!@' as text from dual;
select q'*Welcome to DanBrother's Blog. It's fantastic!*' as text from dual;
select q'(Welcome to DanBrother's Blog. It's fantastic!)' as text from dual;
select Q'cWelcome to DanBrother's Blog. It's fantastic!c' as text from dual;
DanBrother 發表在 痞客邦 留言(0) 人氣(45)
