• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8145
  • Last Modified:

Create table or external table using Procedure

Hai,

     I want to create table or create external table using procedure. But, i am not able to do it. Here the code :

CREATE PROCEDURE TEST AS

BEGIN      
      CREATE TABLE CLS_TEST
      (
       DATE_EXTRACT CHAR(8),
       APPL_ID CHAR(3),
       ENTITY_NAME CHAR(3),
       BORRO_NAME CHAR(35),
       ROC CHAR(15),
       BORRO_TYPE_CODE CHAR(2),
       BORRO_TYPE_DESC CHAR(30),
       CUST_NUM CHAR(6)
      )
       ORGANIZATION EXTERNAL
        (
         TYPE ORACLE_LOADER
         DEFAULT DIRECTORY Source_dir
         ACCESS PARAMETERS
         (
               RECORDS DELIMITED BY NEWLINE
            BADFILE bad_dir:'CLS_TEST_BADFILE.txt'
            LOGFILE log_dir:'CLS_TEST_LOGFILE.txt'
            FIELDS
            (
             DATE_EXTRACT     POSITION(01:08),
              APPL_ID               POSITION(09:11),
              ENTITY_NAME         POSITION(12:14),
              BORRO_NAME         POSITION(15:49),
              ROC                     POSITION(50:64),
              BORRO_TYPE_CODE  POSITION(65:66),
              BORRO_TYPE_DESC  POSITION(67:96),
              CUST_NUM               POSITION(97:102)))
             LOCATION('CLSCUSTOMER.txt'));
      
       COMMIT;
END;


I try to compile the script using TOAD (Tools For Oracle Developer). But,
I having this error message :

PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

   begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>

Can anybody help me to solve this problem.. I hope somebody can help me. ok




 
0
senico
Asked:
senico
  • 3
  • 3
  • 2
1 Solution
 
Helena Markováprogrammer-analystCommented:
You have to use EXECUTE IMMEDIATE for creating a table in a procedure:

CREATE PROCEDURE TEST AS
BEGIN
execute immediate 'CREATE TABLE CLS_TEST
     (
      DATE_EXTRACT CHAR(8),
      APPL_ID CHAR(3),
      ENTITY_NAME CHAR(3),
      BORRO_NAME CHAR(35),
      ROC CHAR(15),
      BORRO_TYPE_CODE CHAR(2),
      BORRO_TYPE_DESC CHAR(30),
      CUST_NUM CHAR(6)
     )
      ORGANIZATION EXTERNAL
       (
        TYPE ORACLE_LOADER
        DEFAULT DIRECTORY Source_dir
        ACCESS PARAMETERS
        (
             RECORDS DELIMITED BY NEWLINE
          BADFILE bad_dir:''CLS_TEST_BADFILE.txt''
          LOGFILE log_dir:''CLS_TEST_LOGFILE.txt''
          FIELDS
          (
           DATE_EXTRACT     POSITION(01:08),
            APPL_ID             POSITION(09:11),
            ENTITY_NAME        POSITION(12:14),
            BORRO_NAME        POSITION(15:49),
            ROC                  POSITION(50:64),
            BORRO_TYPE_CODE  POSITION(65:66),
            BORRO_TYPE_DESC  POSITION(67:96),
            CUST_NUM             POSITION(97:102)))
           LOCATION(''CLSCUSTOMER.txt''))';
     
      COMMIT;
END TEST;

I hope this will help you.
0
 
Pierrick LOUBIERIS Operational Excellence ManagerCommented:
DDL statements must be executed with dynamic SQL in procedures.

CREATE PROCEDURE TEST AS
BEGIN
     EXECUTE IMMEDIATE 'CREATE TABLE ...';
     COMMIT;
END;
0
 
senicoAuthor Commented:
Hai Henka,

I have tried to use your code. I able to compile the code succesfully. But now, i not able to run or to execute the procedure. Can you help  me to run or execute this procedure...ok, thank you
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Helena Markováprogrammer-analystCommented:
I am sorry I don't use TOAD. But there must be somewhere icon for executing a procedure there.
0
 
Helena Markováprogrammer-analystCommented:
I use PL/SQL Developer.  Here is an option "Test Window" - I can write

test;

and that is all.

Maybe you can find something similar in TOAD.
0
 
senicoAuthor Commented:
Hai Henka,

If i want to run or execute the procedure using sql plus, what command should i used? I tried this command:

execute test;

But, i got this error message:

ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at "RMDUSER.TEST", line 4
ORA-06512: at line 1

Can you help me to solve this problem...OK, thank
0
 
Pierrick LOUBIERIS Operational Excellence ManagerCommented:
Select the procedure in Procs tab, then click on the thunder icon above to execute (or right click on the procedure, then execute procedure).
0
 
Pierrick LOUBIERIS Operational Excellence ManagerCommented:
You can also write a PL/SQL block in SQL Editor window or in SQL*Plus :

begin
execute test;
end;
/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now