Oracle New table creation

I was creating a new table in jdev. After filling all the column information i click ok. It was opened a pop up with name Offline Database ObjectManager
message like
"Registering all dependencies(e.g. foreign keys) in the offline database.

Processiong Schema : FUSION"

it is taking long time to process, So i tried to close the pop up, it is closing the entaire JDEV.

Could any one help me in this regard.

Smilar Topics

  • Oracle new table MICROSOFTDTPROPERTIES appeared

    I am using Oracle 9i database. A new table (MICROSOFTDTPROPERTIES) is appeared and also two new procedures(DT_SETPROPERTYBYID, DT_DROPUSEROBJECTBYID)
    . Could you please tell me about this table and procedures?

  • Oracle New Table size estimation

    I want to create a new table that will store (4 crore records)

    how to estimate the size allocation for creation new table

    DESC:

    CON_KEY NOT NULL VARCHAR2(30)
    TITLE VARCHAR2(15)
    FORENAME VARCHAR2(15)
    F_MIDDLE_NAME VARCHAR2(15)
    S_MIDDLE_NAME VARCHAR2(15)
    T_MIDDLE_NAME VARCHAR2(15)
    SURNAME VARCHAR2(30)
    GENERATION VARCHAR2(10)
    SUFFIX VARCHAR2(15)
    CREATED DATE
    LAST_UPD DATE
    DEL_FLG VARCHAR2(1)
    STATUS NUMBER(3)
    INDICATOR NUMBER(10)

    Any help

  • Oracle New table out of columns

    I am a starter in oracle.

    I have a table wit columns:

    amt1 date1 amt2 date2 amt3 date3
    10 10/9 20 11/09 30 12/09

    i want to create a new table or view from the above which looks like below

    amt date
    10 10/09
    20 11/09
    30 12/09

    Kash

  • Oracle new table

    below I have pasted the info for the new table that I have to create.
    It needs 2 columns ,one for the string values 'N' and 'Y'
    second column for the description.
    what are the best names could be given?
    Pls give your suggestions 

    National Highway System 
 
     N = No, not on National Highway System
     Y = Yes, on National Highway System

  • Oracle Slow table creation after upgrade from 10.2.0.3 to 11.2.0.1 using DBUA

    I've recently completed a database upgrade from 10.2.0.3 to 11.2.0.1 using the DBUA.

    I've since encountered a slowdown when running a script which drops and recreates a series of ~250 tables. The script normally runs in around 19 seconds. After the upgrade, the script requires ~2 minutes to run.

    By chance has anyone encountered something similar?

    The problem may be related to the behavior of an "after CREATE on schema" trigger which grants select privileges to a role through the use of a dbms_job call; between 10g and the database that was upgraded from 10G to 11g. Currently researching this angle.

    I will be using the following table creation DDL for this abbreviated test case:

    create table ALLIANCE  (
   ALLIANCEID           NUMBER(10)                      not null,
   NAME                 VARCHAR2(40)                    not null,
   CREATION_DATE        DATE,
   constraint PK_ALLIANCE primary key (ALLIANCEID)
           using index
       tablespace LIVE_INDEX
)
tablespace LIVE_DATA;


    When calling the above DDL, an "after CREATE on schema" trigger is fired which schedules a job to immediately run to grant select privilege to a role for the table which was just created:

    create or replace
trigger select_grant
after CREATE on schema
declare
    l_str varchar2(255);
    l_job number;
begin
    if ( ora_dict_obj_type = 'TABLE' ) then
        l_str := 'execute immediate "grant select on ' ||
                                     ora_dict_obj_name ||
                                    ' to select_role";';
        dbms_job.submit( l_job, replace(l_str,'"','''') );
    end if;
end;
/ 

    Below I've included data on two separate test runs. The first is on the upgraded database and includes optimizer parameters and an abbreviated TKPROF. I've also, included the offending sys generate SQL which is not issued when the same test is run on a 10g environment that has been set up with a similar test case. The 10g test run's TKPROF is also included below.

    The version of the database is 11.2.0.1.

    These are the parameters relevant to the optimizer for the test run on the upgraded 11g SID:

    SQL> show parameter optimizer
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      11.2.0.1
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
 
SQL> show parameter db_file_multi
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     8
 
SQL> show parameter db_block_size
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
 
SQL> show parameter cursor_sharing
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
 
SQL> column sname format a20
SQL> column pname format a20
SQL> column pval2 format a20
SQL> select sname, pname, pval1, pval2 from sys.aux_stats$;
 
SNAME                PNAME                     PVAL1 PVAL2
-------------------- -------------------- ---------- --------------------
SYSSTATS_INFO        STATUS                          COMPLETED
SYSSTATS_INFO        DSTART                          03-11-2010 16:33
SYSSTATS_INFO        DSTOP                           03-11-2010 17:03
SYSSTATS_INFO        FLAGS                         0
SYSSTATS_MAIN        CPUSPEEDNW           713.978495
SYSSTATS_MAIN        IOSEEKTIM                    10
SYSSTATS_MAIN        IOTFRSPEED                 4096
SYSSTATS_MAIN        SREADTIM               1565.746
SYSSTATS_MAIN        MREADTIM
SYSSTATS_MAIN        CPUSPEED                   2310
SYSSTATS_MAIN        MBRC
SYSSTATS_MAIN        MAXTHR
SYSSTATS_MAIN        SLAVETHR
 
13 rows selected.


    Output from TKPROF on the 11g SID:

    ********************************************************************************
 
create table ALLIANCE  (
   ALLIANCEID           NUMBER(10)                      not null,
   NAME                 VARCHAR2(40)                    not null,
   CREATION_DATE        DATE,
   constraint PK_ALLIANCE primary key (ALLIANCEID)
           using index
       tablespace LIVE_INDEX
)
tablespace LIVE_DATA
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          4           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          4           0
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 324
********************************************************************************


    ... large section omitted ...

    Here is the performance hit portion of the TKPROF on the 11g SID:

    SQL ID: fsbqktj5vw6n9
Plan Hash: 1443566277
select next_run_date, obj#, run_job, sch_job
from
 (select decode(bitand(a.flags, 16384), 0, a.next_run_date,
  a.last_enabled_time) next_run_date,       a.obj# obj#,
  decode(bitand(a.flags, 16384), 0, 0, 1) run_job, a.sch_job  sch_job  from
  (select p.obj# obj#, p.flags flags, p.next_run_date next_run_date,
  p.job_status job_status, p.class_oid class_oid,      p.last_enabled_time
  last_enabled_time, p.instance_id instance_id,      1 sch_job   from
  sys.scheduler$_job p   where bitand(p.job_status, 3) = 1    and
  ((bitand(p.flags, 134217728 + 268435456) = 0) or
  (bitand(p.job_status, 1024)  0))    and bitand(p.flags, 4096) = 0    and
  p.instance_id is NULL    and (p.class_oid is null      or (p.class_oid is
  not null      and p.class_oid in (select b.obj# from sys.scheduler$_class b
                           where b.affinity is null)))   UNION ALL   select
  q.obj#, q.flags, q.next_run_date, q.job_status, q.class_oid,
  q.last_enabled_time, q.instance_id, 1   from sys.scheduler$_lightweight_job
  q   where bitand(q.job_status, 3) = 1    and ((bitand(q.flags, 134217728 +
  268435456) = 0) or         (bitand(q.job_status, 1024)  0))    and
  bitand(q.flags, 4096) = 0    and q.instance_id is NULL    and (q.class_oid
  is null      or (q.class_oid is not null      and q.class_oid in (select
  c.obj# from sys.scheduler$_class c                          where
  c.affinity is null)))   UNION ALL   select j.job, 0,
  from_tz(cast(j.next_date as timestamp),      to_char(systimestamp,'TZH:TZM')
  ), 1, NULL,      from_tz(cast(j.next_date as timestamp),
  to_char(systimestamp,'TZH:TZM')),     NULL, 0   from sys.job$ j   where
  (j.field1 is null or j.field1 = 0)    and j.this_date is null) a   order by
  1)   where rownum = 1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.47       0.47          0       9384          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.48       0.48          0       9384          0           1
 
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  COUNT STOPKEY (cr=9384 pr=0 pw=0 time=0 us)
      1   VIEW  (cr=9384 pr=0 pw=0 time=0 us cost=5344 size=6615380 card=194570)
      1    SORT ORDER BY STOPKEY (cr=9384 pr=0 pw=0 time=0 us cost=5344 size=11479630 card=194570)
 194790     VIEW  (cr=9384 pr=0 pw=0 time=537269 us cost=2563 size=11479630 card=194570)
 194790      UNION-ALL  (cr=9384 pr=0 pw=0 time=439235 us)
    231       FILTER  (cr=68 pr=0 pw=0 time=920 us)
    231        TABLE ACCESS FULL SCHEDULER$_JOB (cr=66 pr=0 pw=0 time=690 us cost=19 size=13157 card=223)
      1        TABLE ACCESS BY INDEX ROWID SCHEDULER$_CLASS (cr=2 pr=0 pw=0 time=0 us cost=1 size=40 card=1)
      1         INDEX UNIQUE SCAN SCHEDULER$_CLASS_PK (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 5056)
      0       FILTER  (cr=3 pr=0 pw=0 time=0 us)
      0        TABLE ACCESS FULL SCHEDULER$_LIGHTWEIGHT_JOB (cr=3 pr=0 pw=0 time=0 us cost=2 size=95 card=1)
      0        TABLE ACCESS BY INDEX ROWID SCHEDULER$_CLASS (cr=0 pr=0 pw=0 time=0 us cost=1 size=40 card=1)
      0         INDEX UNIQUE SCAN SCHEDULER$_CLASS_PK (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 5056)
 194559       TABLE ACCESS FULL JOB$ (cr=9313 pr=0 pw=0 time=167294 us cost=2542 size=2529254 card=194558)


    and the totals at the end of the TKPROF on the 11g SID:

    ********************************************************************************
 
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          4           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          0          4           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
 
 
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       70      0.00       0.00          0          0          0           0
Execute     85      0.01       0.01          0         62        208          37
Fetch       49      0.48       0.49          0       9490          0          35
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      204      0.51       0.51          0       9552        208          72
 
Misses in library cache during parse: 5
Misses in library cache during execute: 3
 
   35  user  SQL statements in session.
   53  internal SQL statements in session.
   88  SQL statements in session.
********************************************************************************
Trace file: 11gSID_ora_17721.trc
Trace file compatibility: 11.1.0.7
Sort options: default
 
       1  session in tracefile.
      35  user  SQL statements in trace file.
      53  internal SQL statements in trace file.
      88  SQL statements in trace file.
      51  unique SQL statements in trace file.
    1590  lines in trace file.
      18  elapsed seconds in trace file.

    The version of the database is 10.2.0.3.0.

    These are the parameters relevant to the optimizer for the test run on the 10g SID:

    SQL> show parameter optimizer
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.2.0.3
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE
 
SQL> show parameter db_file_multi
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     8
 
SQL> show parameter db_block_size
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
 
SQL> show parameter cursor_sharing
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
 
SQL> column sname format a20
SQL> column pname format a20
SQL> column pval2 format a20
SQL> select sname, pname, pval1, pval2 from sys.aux_stats$;
 
SNAME                PNAME                     PVAL1 PVAL2
-------------------- -------------------- ---------- --------------------
SYSSTATS_INFO        STATUS                          COMPLETED
SYSSTATS_INFO        DSTART                          09-24-2007 11:09
SYSSTATS_INFO        DSTOP                           09-24-2007 11:09
SYSSTATS_INFO        FLAGS                         1
SYSSTATS_MAIN        CPUSPEEDNW           2110.16949
SYSSTATS_MAIN        IOSEEKTIM                    10
SYSSTATS_MAIN        IOTFRSPEED                 4096
SYSSTATS_MAIN        SREADTIM
SYSSTATS_MAIN        MREADTIM
SYSSTATS_MAIN        CPUSPEED
SYSSTATS_MAIN        MBRC
SYSSTATS_MAIN        MAXTHR
SYSSTATS_MAIN        SLAVETHR
 
13 rows selected.

    Now for the TKPROF of a mirrored test environment running on a 10G SID:

    ********************************************************************************
 
create table ALLIANCE  (
   ALLIANCEID           NUMBER(10)                      not null,
   NAME                 VARCHAR2(40)                    not null,
   CREATION_DATE        DATE,
   constraint PK_ALLIANCE primary key (ALLIANCEID)
           using index
       tablespace LIVE_INDEX
)
 
tablespace LIVE_DATA
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.01          0          2         16           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.01          0          2         16           0
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 113
********************************************************************************


    ... large section omitted ...

    Totals for the TKPROF on the 10g SID:

    ********************************************************************************
 
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          0          0           0
Execute      1      0.00       0.00          0          2         16           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.02          0          2         16           0
 
Misses in library cache during parse: 1
 
 
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       65      0.01       0.01          0          1         32           0
Execute     84      0.04       0.09         20         90        272          35
Fetch       88      0.00       0.10         30        281          0          64
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      237      0.07       0.21         50        372        304          99
 
Misses in library cache during parse: 38
Misses in library cache during execute: 32
 
   10  user  SQL statements in session.
   76  internal SQL statements in session.
   86  SQL statements in session.
********************************************************************************
Trace file: 10gSID_ora_32003.trc
Trace file compatibility: 10.01.00
Sort options: default
 
       1  session in tracefile.
      10  user  SQL statements in trace file.
      76  internal SQL statements in trace file.
      86  SQL statements in trace file.
      43  unique SQL statements in trace file.
     949  lines in trace file.
       0  elapsed seconds in trace file.


    Edited by: user8598842 on Mar 11, 2010 5:08 PM

  • Oracle Extracting Table creation DDLs without PCTFREE, PCTUSED,etc

    Version:11.1.0.7.0

    Using impdp (or any method) , can i extract the Table creation DDLs without the storage parameters like PCTFREE, PCTUSED but with tablespace information.
    A table creation DDL should look like

    CREATE TABLE DAILY_TASKS
(
    DAILY_TASK_ID number(3) not null,
    DAILY_TASK_NAME varchar2(35) not null
)
TABLESPACE CM_TBS;

  • Oracle New DB creation in a same server

    I have a database PRODCIM in a server(Sun Solaris 5.8) with characterset WE8ISO8859P1, I want to create another database TEMP with characterset UTF8 and export all schemas except default users created during database creation and import it into TEMP databse and then shutdown PRODCIM and rename TEMP database into PRODCIM by creating a control file using alter databse backup control file to trace and edit the file with new path.

    My question is:

    In PRODCIM the path of the data files are:
    /u01/app/oracle/prodcim
    /u02/app/oracle/prodcim

    When I create TEMP database, can I create another directory /u01/app/oracle/PRODCIM and /u01/app/oracle/PRODCIM and keep all the TEMP database related files under that directory? I just want to make sure this will not cause any problem to the oraiginal database PRODCIM.

    Also I have the following users:

    ANONYMOUS,CTXSYS,DBSNMP,DIP,DMSYS,EXFSYS,MDSYS,OLAPSYS,ORDSYS,SYS,SYSMAN,SYSTEM,TEMP,WKSYS,WMSYS,XDB

    Are all these users are created during database creation? or any of them are user created schema?

  • Oracle External Table Creation for .dat file

    I have created an External Table, below is the code 

    CREATE TABLE  "NFO_DATA_LOAD_STAGING_TEST1" 
   (	"DATA_DISCRAPANCY" VARCHAR2(100), 
	"APPLICATION_NUMBER" VARCHAR2(30), 
	"BATCH_NUMBER" VARCHAR2(30), 
	"AMC_ID" VARCHAR2(30), 
	"TA_BRANCH" VARCHAR2(30), 
	"SERVER_DATE_1" DATE, 
	"SERVER_TIME_1" DATE, 
	"ARN_NUMBER" VARCHAR2(30), 
	"BROKER_CODE" VARCHAR2(30), 
	"SUB_BROKER_CODE_1" VARCHAR2(30), 
	"SUB_BROKER_CODE_2" VARCHAR2(30), 
	"SUB_BROKER_CODE_3" VARCHAR2(30), 
	"BROKER_ADDRESS" VARCHAR2(100), 
	"FOLIO_NO" NUMBER, 
	"MODE_OF_HOLDING" VARCHAR2(30), 
	"STATUS" VARCHAR2(30), 
	"SALUATION" VARCHAR2(30), 
	"CONTACT_PERSON_NAME" VARCHAR2(30), 
	"INVESTOR_NAME" VARCHAR2(30), 
	"GUARDIAN_NAME" VARCHAR2(30), 
	"PAN_NO" VARCHAR2(30), 
	"POA_HOLDER_NAME" VARCHAR2(30), 
	"KYC" VARCHAR2(30), 
	"GUARDIAN_PAN_NO" VARCHAR2(30), 
	"DOB_DOC" DATE, 
	"GUARDIAN_RELATIONSHIP" VARCHAR2(30), 
	"SALUATION_2" VARCHAR2(30), 
	"DATE_OF_BIRTH" DATE, 
	"SECOND_HOLDER_NAME" VARCHAR2(30), 
	"GUARDIAN_NAME_2" VARCHAR2(30), 
	"PAN_NO_2" VARCHAR2(30), 
	"GUARDIAN_PAN_NO_2" VARCHAR2(30), 
	"KYC_2" VARCHAR2(30), 
	"GUARDIAN_RELATIONSHIP_2" VARCHAR2(30), 
	"SALUATION_3" VARCHAR2(30), 
	"DATE_OF_BIRTH_3" DATE, 
	"THIRD_HOLDER_NAME" VARCHAR2(30), 
	"GUARDIAN_NAME_3" VARCHAR2(30), 
	"PAN_NO_3" VARCHAR2(30), 
	"GUARDIAN_PAN_NO_3" VARCHAR2(30), 
	"KYC_3" VARCHAR2(30), 
	"GUARDIAN_RELATIONSHIP_3" VARCHAR2(30), 
	"ADDRESS_LINE_1" VARCHAR2(100), 
	"PINCODE" NUMBER, 
	"ADDRESS_LINE_2" VARCHAR2(100), 
	"CONTACT_NUMBER_O" VARCHAR2(15), 
	"CITY" VARCHAR2(30), 
	"CONTACT_NUMBER_R" VARCHAR2(15), 
	"STATE" VARCHAR2(30), 
	"MOBILE_NUMBER" VARCHAR2(15), 
	"COUNTRY" VARCHAR2(30), 
	"EMAIL_ID" VARCHAR2(30), 
	"ALT_EMAIL_ID" VARCHAR2(30), 
	"ADDRESS_LINE_1_A" VARCHAR2(100), 
	"PINCODE_A" NUMBER, 
	"ADDRESS_LINE_2_A" VARCHAR2(100), 
	"CONTACT_NUMBER_O_A" VARCHAR2(15), 
	"CITY_A" VARCHAR2(30), 
	"CONTACT_NUMBER_R_A" VARCHAR2(15), 
	"STATE_A" VARCHAR2(30), 
	"MOBILE_NUMBER_A" VARCHAR2(15), 
	"COUNTRY_A" VARCHAR2(30), 
	"EMAIL_ID_A" VARCHAR2(30), 
	"ALT_EMAIL_ID_A" VARCHAR2(30), 
	"DESPATCH_ACCOUNT" VARCHAR2(30), 
	"REDEMPTION_PAYOUT" VARCHAR2(30), 
	"I_PIN_ASSIGN" VARCHAR2(30), 
	"DIVIDEND_PAYOUT" VARCHAR2(30), 
	"T_PIN_ASSIGN" VARCHAR2(30), 
	"TO_FUND_NAME" VARCHAR2(30), 
	"TO_FUND_ID" VARCHAR2(30), 
	"OPTION_F_S" VARCHAR2(30), 
	"MCR_NO" NUMBER, 
	"BANK_NAME" VARCHAR2(30), 
	"ACCOUNT_NUMBER" NUMBER, 
	"BANK_BRANCH" VARCHAR2(30), 
	"ACCOUNT_TYPE" VARCHAR2(30), 
	"MCR_NO_P" NUMBER, 
	"CHEQUE_DD_NO" NUMBER, 
	"BANK_NAME_P" VARCHAR2(30), 
	"PAYMENT_DATE" DATE, 
	"BANK_BRANCH_P" VARCHAR2(30), 
	"ADVICE_NO" NUMBER, 
	"PAYMENT_TYPE_P" VARCHAR2(30), 
	"AMOUNT_P" NUMBER, 
	"MINIMUM_AMOUNT" NUMBER, 
	"AMOUNT_WORDS" VARCHAR2(1000), 
	"NOMINEE_NAME" VARCHAR2(30), 
	"SALUATION_STG" VARCHAR2(30), 
	"NOMINEE_DOB_STG" DATE, 
	"GUARDIAN_NAME_STG" VARCHAR2(30), 
	"RELATIONSHIP_STG" VARCHAR2(30), 
	"ADDRESS_LINE_1_STG" VARCHAR2(100), 
	"COUNTRY_STG" VARCHAR2(30), 
	"ADDRESS_LINE_2_STG" VARCHAR2(100), 
	"PINCODE_STG" NUMBER, 
	"CITY_STG" VARCHAR2(30), 
	"CONTACT_NUMBER_STG" VARCHAR2(15), 
	"STATE_STG" VARCHAR2(30), 
	"MOBILE_NUMBER_STG" VARCHAR2(15), 
	"REMARKS_STG" VARCHAR2(100), 
	"EXCEPTION_CHK_STG" CHAR(1), 
	"HOLD_CHK_STG" CHAR(1), 
	"PAN_STG" CHAR(1), 
	"BOARD_RESOLUTION_STG" CHAR(1), 
	"KYC_STG" CHAR(1), 
	"MOA_STG" CHAR(1), 
	"CHEQUE_STG" CHAR(1), 
	"ASL_STG" CHAR(1), 
	"TRUST_DEED_STG" CHAR(1), 
	"PARTNERSHIP_DEED_STG" CHAR(1), 
	"BYE_LAWS_STG" CHAR(1), 
	"AUTO_DEBIT_STG" CHAR(1), 
	"ENROLLMENT_FORM_STG" CHAR(1), 
	"APPROVED" VARCHAR2(10), 
	"CREATED_BY" NUMBER, 
	"CREATION_DATE" DATE, 
	"LAST_UPDATE_DATE" DATE, 
	"LAST_UPDATED_BY" NUMBER, 
	"FUND_TYPE" VARCHAR2(30)
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY DUMP_DIR
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE
	      BADFILE 'emp.bad'
                      LOGFILE 't.log_xt'			 
                      FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY ' " ' LDTRIM
                      REJECT ROWS WITH ALL NULL FIELDS
                      (
                       
			"DATA_DISCRAPANCY"  
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"APPLICATION_NUMBER" 
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"BATCH_NUMBER"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"AMC_ID" 
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"TA_BRANCH"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"SERVER_DATE_1"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"SERVER_TIME_1"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"ARN_NUMBER"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"BROKER_CODE"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"SUB_BROKER_CODE_1"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"SUB_BROKER_CODE_2"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"SUB_BROKER_CODE_3"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"BROKER_ADDRESS"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"FOLIO_NO"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"MODE_OF_HOLDING"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"STATUS"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"SALUATION"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"CONTACT_PERSON_NAME"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"INVESTOR_NAME"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"GUARDIAN_NAME"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"PAN_NO"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"POA_HOLDER_NAME"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"KYC"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"GUARDIAN_PAN_NO"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"DOB_DOC"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"GUARDIAN_RELATIONSHIP"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"SALUATION_2"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"DATE_OF_BIRTH"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"SECOND_HOLDER_NAME"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"GUARDIAN_NAME_2"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"PAN_NO_2"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"GUARDIAN_PAN_NO_2"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"KYC_2"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"GUARDIAN_RELATIONSHIP_2"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"SALUATION_3"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"DATE_OF_BIRTH_3"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"THIRD_HOLDER_NAME"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"GUARDIAN_NAME_3"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"PAN_NO_3"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"GUARDIAN_PAN_NO_3"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"KYC_3"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"GUARDIAN_RELATIONSHIP_3"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"ADDRESS_LINE_1"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"PINCODE"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"ADDRESS_LINE_2"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"CONTACT_NUMBER_O"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"CITY"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"CONTACT_NUMBER_R"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"STATE"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"MOBILE_NUMBER"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"COUNTRY"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"EMAIL_ID"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"ALT_EMAIL_ID"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"ADDRESS_LINE_1_A"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"PINCODE_A"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"ADDRESS_LINE_2_A"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"CONTACT_NUMBER_O_A"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"CITY_A"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"CONTACT_NUMBER_R_A"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"STATE_A"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"MOBILE_NUMBER_A"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"COUNTRY_A"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"EMAIL_ID_A"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"ALT_EMAIL_ID_A"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"DESPATCH_ACCOUNT"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"REDEMPTION_PAYOUT"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"I_PIN_ASSIGN"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"DIVIDEND_PAYOUT"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"T_PIN_ASSIGN"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"TO_FUND_NAME"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"TO_FUND_ID"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"OPTION_F_S"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"MCR_NO"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"BANK_NAME"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"ACCOUNT_NUMBER"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"BANK_BRANCH"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"ACCOUNT_TYPE"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"MCR_NO_P"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"CHEQUE_DD_NO"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"BANK_NAME_P"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"PAYMENT_DATE"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"BANK_BRANCH_P"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"ADVICE_NO"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"PAYMENT_TYPE_P"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"AMOUNT_P"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"MINIMUM_AMOUNT"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"AMOUNT_WORDS"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"NOMINEE_NAME"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"SALUATION_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"NOMINEE_DOB_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"GUARDIAN_NAME_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"RELATIONSHIP_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"ADDRESS_LINE_1_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"COUNTRY_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"ADDRESS_LINE_2_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"PINCODE_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"CITY_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"CONTACT_NUMBER_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"STATE_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"MOBILE_NUMBER_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"REMARKS_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"EXCEPTION_CHK_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"HOLD_CHK_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"PAN_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"BOARD_RESOLUTION_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"KYC_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"MOA_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"CHEQUE_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"ASL_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"TRUST_DEED_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"PARTNERSHIP_DEED_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"BYE_LAWS_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"AUTO_DEBIT_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"ENROLLMENT_FORM_STG"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"APPROVED"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"CREATED_BY"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"CREATION_DATE"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"LAST_UPDATE_DATE"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"LAST_UPDATED_BY"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' ,
			"FUND_TYPE"
                                                         TERMINATED BY ","  OPTIONALLY ENCLOSED BY ' " ' 
   
 
                      )
                                  )
      LOCATION
       ( 'Key_File_Report.dat'
       )
    )

    while trying to select the data from external table, am facing the below error.

    ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-00554: error encountered while parsing access parameters KUP-01005: syntax error: found "identifier": expecting one of: "and, column, exit, (, ltrim, lrtrim, ldrtrim, missing, notrim, rtrim, reject" KUP-01008: the bad identifier was: LDTRIM KUP-01007: at line 4 column 77


    can anyone suggest me how to resolve this.

  • Oracle user table creation date

    How do I query creation date of a user table

  • ORACLE NEWBIE - table creation

    I currently use MySQL, but I am starting work on an application which runs on Oracle 8i. I am in the process of learning 8i, but for now I would like to work on the application in MySQL (since the API is database independant). Is there an easy way to dump the SQL table create statments from the Oracle instance (ie. is the a function equivalent to mysqldump on MySQL)?

  • Oracle Temp table creation within PL/SQL

    I am new to PL/SQL would like to know how to create a table within a pl/sql procedure and read the data from a database table and insert the data into a txt file. At the end I would like to delete the table. Please help me out.

    Thanks in advance for your kind help.

    Ramesh

  • Oracle Temp table creation within PL/SQL

    I am new to PL/SQL would like to know how to create a table within a pl/sql procedure and read the data from a database table and insert the data into a txt file. At the end I would like to delete the table. Please help me out.

    Thanks in adavance for your kind help.

    Ramesh

  • Oracle new table

    i have a table in access anad i want to make a table in oracle,sql using all the data from that table in access. How can i do that , i tried exporting it but i can not because it can not export it for some reason. It gives ODBC error.
    Please suggest.
    Ashish

  • Oracle new database creation

    Does anyone know if it is possible to create a new database in a new location other than the one specified by {ORACLE_BASE} ?

  • Oracle Nested Table Creation

    I'm trying to register a schema such that the repeating elements (both leaf and non-leaf nodes) are stored as nested tables instead of LOB VARRAYS. I've included the xdb:storeVarrayAsTable="true" attribute in the schema, but the repeating elements are still stored in LOBs. I've also tried creating the table by manually specifying the repeating element that should be stored as tables.

    The schema i'm using is the FGDC metadata schema (http://www.fgdc.gov/csdgm). The elements which are in question are:

    <xs:element name="onlink" type="xs:string"/>
    <xs:element name="origin" type="xs:string"/>
    <xs:element name="themekey" type="xs:string"/>
    <xs:element name="themekt" type="xs:string"/>
    <xs:element name="tempkey" type="xs:string"/>
    <xs:element name="tempkt" type="xs:string"/>
    <xs:element name="stratkey" type="xs:string"/>
    <xs:element name="stratkt" type="xs:string"/>
    <xs:element name="placekey" type="xs:string"/>
    <xs:element name="placekt" type="xs:string"/>
    <xs:element name="citeinfo">
    <xs:complexType>
    <xs:sequence>
    <xs:element ref="origin" maxOccurs="unbounded"/>
    <xs:element ref="onlink" minOccurs="0" maxOccurs="unbounded"/>
    </xs:sequence>
    </xs:complexType>
    </xs:element>
    <xs:element name="citation">
    <xs:complexType>
    <xs:sequence>
    <xs:element ref="citeinfo"/>
    </xs:sequence>
    </xs:complexType>
    </xs:element>
    <xs:element name="crossref">
    <xs:complexType>
    <xs:sequence>
    <xs:element ref="citeinfo"/>
    </xs:sequence>
    </xs:complexType>
    </xs:element>
    <xs:element name="theme">
    <xs:complexType>
    <xs:sequence>
    <xs:element ref="themekt"/>
    <xs:element ref="themekey" maxOccurs="unbounded"/>
    </xs:sequence>
    </xs:complexType>
    </xs:element>
    <xs:element name="temporal">
    <xs:complexType>
    <xs:sequence>
    <xs:element ref="tempkt"/>
    <xs:element ref="tempkey" maxOccurs="unbounded"/>
    </xs:sequence>
    </xs:complexType>
    </xs:element>
    <xs:element name="stratum">
    <xs:complexType>
    <xs:sequence>
    <xs:element ref="stratkt"/>
    <xs:element ref="stratkey" maxOccurs="unbounded"/>
    </xs:sequence>
    </xs:complexType>
    </xs:element>
    <xs:element name="place">
    <xs:complexType>
    <xs:sequence>
    <xs:element ref="placekt"/>
    <xs:element ref="placekey" maxOccurs="unbounded"/>
    </xs:sequence>
    </xs:complexType>
    </xs:element>
    <xs:element name="keywords">
    <xs:complexType>
    <xs:sequence>
    <xs:element ref="theme" maxOccurs="unbounded"/>
    <xs:element ref="place" minOccurs="0" maxOccurs="unbounded"/>
    <xs:element ref="stratum" minOccurs="0" maxOccurs="unbounded"/>
    <xs:element ref="temporal" minOccurs="0" maxOccurs="unbounded"/>
    </xs:sequence>
    </xs:complexType>
    </xs:element>
    <xs:element name="idinfo">
    <xs:complexType>
    <xs:sequence>
    <xs:element ref="citation"/>
    <xs:element ref="keywords"/>
    <xs:element ref="crossref" minOccurs="0" maxOccurs="unbounded"/>
    </xs:sequence>
    </xs:complexType>
    </xs:element>


    I can do the following create statement for the keyword elements:

    SQL> create table fgdctab(recordID VARCHAR2(32) PRIMARY KEY, MD XMLTYPE)
    2 XMLTYPE COLUMN MD XMLSCHEMA "http://www.opengis.net/cat/csw" ELEMENT "metadata"
    3 VARRAY MD.xmldata."idinfo"."keywords"."theme" STORE AS TABLE theme_ntab
    4 (VARRAY "themekey" STORE AS TABLE themekey_ntab)
    5 VARRAY MD.xmldata."idinfo"."keywords"."place" STORE AS TABLE place_ntab
    6 (VARRAY "placekey" STORE AS TABLE placekey_ntab)
    7 VARRAY MD.xmldata."idinfo"."keywords"."stratum" STORE AS TABLE stratum_ntab
    8 (VARRAY "stratkey" STORE AS TABLE stratkey_ntab)
    9 VARRAY MD.xmldata."idinfo"."keywords"."temporal" STORE AS TABLE temporal_ntab
    10 (VARRAY "tempkey" STORE AS TABLE tempkey_ntab);

    Table created.

    Elapsed: 00:00:02.01
    SQL> desc fgdctab;
    Name Null? Type

    --------
    RECORDID NOT NULL VARCHAR2(32)
    MD SYS.XMLTYPE(XMLSchema "http:
    //www.opengis.net/cat/csw" E
    lement "metadata") STORAGE O
    bject-relational TYPE "metad
    ata2795_T"

    but the following statements won't work:

    SQL> create table fgdctab(recordID VARCHAR2(32) PRIMARY KEY, MD XMLTYPE)
    2 XMLTYPE COLUMN MD XMLSCHEMA "http://www.opengis.net/cat/csw" ELEMENT "metadata"
    3 VARRAY MD.xmldata."idinfo"."citation"."citeinfo"."origin" STORE AS TABLE origin_ntab
    4 VARRAY MD.xmldata."idinfo"."citation"."citeinfo"."onlink" STORE AS TABLE onlink_ntab
    5 VARRAY MD.xmldata."idinfo"."keywords"."theme" STORE AS TABLE theme_ntab
    6 (VARRAY "themekey" STORE AS TABLE themekey_ntab)
    7 VARRAY MD.xmldata."idinfo"."keywords"."place" STORE AS TABLE place_ntab
    8 (VARRAY "placekey" STORE AS TABLE placekey_ntab)
    9 VARRAY MD.xmldata."idinfo"."keywords"."stratum" STORE AS TABLE stratum_ntab
    10 (VARRAY "stratkey" STORE AS TABLE stratkey_ntab)
    11 VARRAY MD.xmldata."idinfo"."keywords"."temporal" STORE AS TABLE temporal_ntab
    12 (VARRAY "tempkey" STORE AS TABLE tempkey_ntab);
    VARRAY MD.xmldata."idinfo"."citation"."citeinfo"."onlink" STORE AS TABLE onlink_ntab
    *
    ERROR at line 4:
    ORA-22809: nonexistent attribute

    SQL> create table fgdctab(recordID VARCHAR2(32) PRIMARY KEY, MD XMLTYPE)
    2 XMLTYPE COLUMN MD XMLSCHEMA "http://www.opengis.net/cat/csw" ELEMENT "metadata"
    3 VARRAY MD.xmldata."idinfo"."crossref" STORE AS TABLE crossref_ntab
    4 (VARRAY "citeinfo"."onlink" STORE AS TABLE onlink_ntab);
    create table fgdctab(recordID VARCHAR2(32) PRIMARY KEY, MD XMLTYPE)
    *
    ERROR at line 1:
    ORA-02320: failure in creating storage table for nested table column
    "SYS_XMLTYPE_00003$"."idinfo"."crossref"
    ORA-22809: nonexistent attribute

    What am I missing with regards to how oracle breaks these elements into nested tables?

  • Oracle Dynamic Table Creation from views which are present

    I've a Oracle Package with the procedures to create a table from a view.While I run the main procedure in the Package, it has to drop the table which need to be created first and then it has to get created from the view specifed.
    While I execute, it drops the table fine and it is not able to create the table from the view.
    The errors are caught in a table and I can see insuffient priviledges.
    Do I need to give grant create privilege to gxpstg.util to create the tables from the views?
    I tried to truncate table and its not working either.

    Any suggestions or ideas welcome.

    create or replace package body pkg_fpxrpt_mart is

    c_t_parties constant varchar2(2999) := upper('t_fpxrpt_parties');
    c_v_parties constant varchar2(2999) := upper('v_fpxrpt_parties');

    c_st_pkg_name constant varchar2(2999) := upper('PKG_FPXRPT_MART');
    -- Private variable declarations
    l_v_call_stack varchar2(2999);
    v_control_id varchar2(2999);

    procedure clean_fpx_rpt_v_mart is
    c_proc_name constant varchar2(2999) := upper('clean_fpx_rpt_v_mart');
    begin
    -- parties
    v_control_id := 'drop table';
    l_v_call_stack := c_st_pkg_name||'.'||c_t_parties;
    begin
    execute immediate 'drop table ' || c_t_parties;
    EXCEPTION
    when others then
    gxpstg.AIG_UTIL.log_exception(v_control_id||' '||c_t_parties ||' '||c_st_pkg_name||'.'||c_proc_name||')');
    end;

    --
    end; -- end of clean_gxp_rpt_v_mart

    procedure create_fpx_rpt_v_mart is
    c_proc_name constant varchar2(2999) := upper('create_fpx_rpt_v_mart');
    begin
    -- parties
    v_control_id := 'create table';
    l_v_call_stack := c_st_pkg_name||'.'||c_proc_name;
    begin
    execute immediate 'create table '||c_t_parties ||' as select * from ' || c_v_parties;
    EXCEPTION
    when others then
    gxpstg.AIG_UTIL.log_exception(v_control_id||' '||c_t_parties||' '||c_st_pkg_name||'.'||c_proc_name||')');
    -- raise;
    end;

    end;

    procedure index_fpx_rpt_v_mart is
    c_proc_name constant varchar2(2999) := upper('index_fpx_rpt_v_mart');
    begin
    v_control_id := 'index ';

    begin
    execute immediate 'create index X_ATTR_FCT_PARTIES on T_FPXRPT_PARTIES (RUN_ID,DATA_DATE_ID,FPXRPT_PARTY_ID,TOP_PARENT_FPXRPT_PARTY_ID,PARTY_LEVEL_FROM_TOP)';
    EXCEPTION
    when others then
    gxpstg.AIG_UTIL.log_exception(v_control_id||' X_ATTR_FCT_PARTIES '||c_st_pkg_name||'.'||c_proc_name||')');
    -- raise;
    end;

    commit;
    end;
    ---
    procedure analyze_fpx_rpt_v_mart is
    begin
    commit;
    end;
    ---
    procedure main is
    begin
    clean_fpx_rpt_v_mart;
    create_fpx_rpt_v_mart;
    index_fpx_rpt_v_mart;
    analyze_fpx_rpt_v_mart;
    end;
    --
    begin
    -- Initialization: truncate tables
    commit;
    end pkg_fpxrpt_mart;

  • Oracle New table column not being updated or inserted

    I have a small application that has a report to display table contents and then 2 linked pages, 1 for updating and 1 for creating new entries.
    I've added a column to the table and created new text field items in both the form pages to allow for data entry and updates, but the data just isn't being inserted or updated.

    They're both using the same process to enter/update the data so I'm guessing it's something up with the process (which is just a straght Automatic Row Processing DML process), but I can't see any way to regenerate/recompile the process tso it picks up the new column.

    Dougie McGibbon

  • Oracle New table doesnt return any result in jsp

    i have created a table :draft_table , under a schema : myschema
    i want to select data from draft_table to my jsp page but i can select data from other existing tables but cant select the new one although granted select , update ,insert to public for it..

    i cant think any reason other than privileges .. but it i have already grant privileges, any help would be appreciated..

  • Oracle Unwanted Table Creation In Database

    This is Anandkumar From India

    Presently working as DBA right now we r running 10.2 g Oracle Database In RAC Environment On Windows 2003 R2 Standard Edition. Now the problem is that in database there are some unwanted tables created in the database which r not created by user and all these table names begin with BIN and with some other text in table name. when I try to describe the table structure it show that database object is not available. But when I run the query select * from tab;
    Query shows these unwanted tables in the query result/

    Really I don't know what these table are.

    Pls if somebody helps me out to sort out this problem it will be a great help