Skip to Main Content

SQL Developer

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.

How does SQL Developer select SDO_GEOMETRY properties as text?

User_1871May 4 2022 — edited May 4 2022

I have a Oracle 18c query that outputs an SDO_GEOMETRY object:

select
  sdo_geometry('LINESTRING (1 2,3 4)') as sdo_geom
from
  dual

That works as expected in SQL Developer:

CTRL+F5 Output: [MDSYS.SDO_GEOMETRY]

F5 Output: SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(1, 2, 3, 4))

Question: How does SQL Developer convert the SDO_GEOMETRY object properties to text? What code or functions does it use?
The reason I ask:
I have a use case where I want to output the SDO_GEOMETRY properties...the same way that SQL Developer does (when I hit F5).
But I haven't found a clean way to do that using SQL. There doesn't seem to be an OOTB function for it.
I cobbled together a query that more-or-less works:

select
    'MDSYS.SDO_GEOMETRY('                         ||
    a.shape.sdo_gtype                       || ', ' ||
    nvl(to_char(a.shape.sdo_srid), 'NULL')  || ', ' ||
  
    NVL2(
        a.shape.sdo_point,
        'MDSYS.SDO_POINT_TYPE ('
        || COALESCE(TO_CHAR(a.shape.sdo_point.X), 'NULL') || ', '
        || COALESCE(TO_CHAR(a.shape.sdo_point.Y), 'NULL') || ', '
        || COALESCE(TO_CHAR(a.shape.sdo_point.Z), 'NULL')
        || ')',
        'NULL'
    ) || ', ' ||  
  
     'MDSYS.SDO_ELEM_INFO_ARRAY(' || (select listagg(column_value,', ') from table(a.shape.sdo_elem_info)) || '), ' ||
     'MDSYS.SDO_ORDINATE_ARRAY(' || (select listagg(column_value,', ') from table(a.shape.sdo_ordinates)) || '))'
    as sdo_geom_properties
from
    (
    select
        SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(1, 2, 3, 4)) as shape
    from
        dual
    union all
    select
        SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(-79, 37, NULL), NULL, NULL) as shape
    from
        dual
  ) a  

image.pngBut that's not as simple or robust as I'd like it to be.
How does SQL Developer do it? Could I do something similar to what SQL Developer does, but in a query?
I'm aware that we can convert SDO_GEOEMTRY to other formats like WKT or JSON. But in this case, I would prefer to output the raw SDO_GEOMETRY properties, if possible.

Comments

EdStevens

orasiya wrote:

Dear Friends,

I need bash example (good & effective rman bash example) of rman backup for my production database , simple but effective that

1- it does level 0 backup of database including the control file , spfile every week on saturdays

2- it does incremental level 1 backup everyday

3- it does archivelog backup everyday

for review

----------------------------- level 0

run

{

allocate channel ch01 device type disk format '/ZFS/DB_PRO/disk1/FULL_%U';

allocate channel ch01 device type disk format '/ZFS/DB_PRO/disk2/FULL_%U';

allocate channel ch01 device type disk format '/ZFS/DB_PRO/disk1/FULL_%U';

allocate channel ch01 device type disk format '/ZFS/DB_PRO/disk2/FULL_%U';

.....

backup as backupset incremental level 0 section size 32g database tag 'RMAN/FULL_L0' plus archivelog not backed up 2 times;

#control file backup

allocate channel ch_cntl device type disk format '/ZFS/DB_PRO/disk1/cf_%U';

backup as backupset CURRENT CONTROLFILE channel ch_cntl;

#spfile backup

allocate channel ch_sp device type disk format '/ZFS/DB_PRO/disk1/sp_%U';

backup as backupset SPFILE channel ch_sp;

DELETE NOPROMPT OBSOLETE;

}

----------------------------- level 1

run

{

allocate channel ch01 device type disk format '/ZFS/DB_PRO/disk1/INC_%U';

allocate channel ch01 device type disk format '/ZFS/DB_PRO/disk2/INC_%U';

allocate channel ch01 device type disk format '/ZFS/DB_PRO/disk1/INC_%U';

allocate channel ch01 device type disk format '/ZFS/DB_PRO/disk2/INC_%U';

.....

backup as backupset incremental level 1 section size 32g database tag 'RMAN/LEVEL_L1' plus archivelog not backed up 2 times;

DELETE NOPROMPT OBSOLETE;

}

------------------------------

-------------------- archivelog backup

run

{

allocate channel ch01 device type disk format '/ZFS/DB_PRO/disk1/ARC_%U';

allocate channel ch01 device type disk format '/ZFS/DB_PRO/disk2/ARC_%U';

allocate channel ch01 device type disk format '/ZFS/DB_PRO/disk1/ARC_%U';

allocate channel ch01 device type disk format '/ZFS/DB_PRO/disk2/ARC_%U';

backup archivelog all not backed up 2 times;

delete noprompt archivelog until time '(sysdate-30)';

}

--------some rman configuration setting

RMAN> show all;

RMAN configuration parameters for database with db_unique_name DB_PRO are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 5;

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/ZFS/DB_PRO/disk1/auto/%F';

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+REC1/DB_PRO/snapcf_DB_PRO.f';

Why do you allocate different directories for the level 0 backup than for the level 1 backups? This is needless complexity.

YOu don't need separate run blocks for archivelogs and/or controlfile/spfile.  This is needless complexity

I accomplish everything you want with a single shell script.  Here's the core of it:

#!/bin/sh

ORACLE_SID=$1

inclvl=$2

logfile=$3
ORAENV_ASK=NO

source oraenv

rman target /  <<EOF >> $logfile

set echo on

delete force noprompt obsolete;

backup as compressed backupset incremental level $inclvl database tag ${ORACLE_SID}_db_bkup plus archivelog delete all input /

tag ${ORACLE_SID}_arch_bkup;

list backup;

EOF

1 - 1

Post Details

Added on May 4 2022
7 comments
998 views