Databases
StackOverflow2013
Postsdbo
POError Converting an External Table Creation Instruction to a PLSQL Procedure
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POError Converting an External Table Creation Instruction to a PLSQL Procedure
    primarykey
    data
    text
    <p>I'm having some problem converting a single External table instruction into a PLSQL Procedure. In particular, this is the External Table create instruction that works perfectly.</p> <pre><code>create table TMP_TBL ( id VARCHAR2(10) ) organization external ( TYPE ORACLE_LOADER default directory DATA_DIR access parameters ( RECORDS DELIMITED BY NEWLINE fields terminated by '|' missing field values are null ) location ('test.txt') ) reject limit unlimited; </code></pre> <p>I'm trying to make out of this a PLSQL procedure that creates the table given the external data file. This is what i've done since now but i'm still having some problem when i'm calling it.</p> <pre><code>PROCEDURE CREATE_TMP_TBL(FILENAME VARCHAR2) IS BEGIN EXECUTE IMMEDIATE 'create table TMP_TBL ( id VARCHAR2(10) ) organization external ( type oracle_loader default directory DATA_DIR access parameters ( records delimited by newline fields terminated by ''|'' missing field values are null ) location ('''||FILENAME||''') ) reject limit unlimited;'; END CREATE_TMP_TBL; </code></pre> <p>Executing the procedure with the following command:</p> <pre><code>exec pkg_load.CREATE_TMP_TBL('test.txt'); </code></pre> <p>It gives me this error:</p> <pre><code>Error report: ORA-00911: invalid character ORA-06512: at "PKG_LOAD", line 43 ORA-06512: at line 1 00911. 00000 - "invalid character" *Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual. *Action: </code></pre> <p>line 43 is the "BEGIN" number line of the procedure CREATE_TMP_TBL.</p> <p>Info:</p> <pre><code>Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production PL/SQL Release 11.2.0.2.0 - Production "CORE 11.2.0.2.0 Production" TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production </code></pre> <p>Any suggestion to fix this problems are welcome. Thanks in Advice,</p> <p>Carlo</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USaraknoid
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POError Converting an External Table Creation Instruction to a PLSQL Procedure
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIf all you want to potentially do is to change the file that is referenced, You can do that with the ALTER TABLE statement - see here for all changes you can make to an external table (LOCATION attribute). http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables013.htm#i1007591
      singulars
      1. POError Converting an External Table Creation Instruction to a PLSQL Procedure
      1. USOldProgrammer
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload