?
Solved

Create table or external table using Procedure

Posted on 2005-04-05
8
Medium Priority
?
8,096 Views
Last Modified: 2013-12-12
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
Comment
Question by:senico
  • 3
  • 3
  • 2
8 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 13714328
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
 
LVL 8

Accepted Solution

by:
Pierrick LOUBIER earned 800 total points
ID: 13714336
DDL statements must be executed with dynamic SQL in procedures.

CREATE PROCEDURE TEST AS
BEGIN
     EXECUTE IMMEDIATE 'CREATE TABLE ...';
     COMMIT;
END;
0
 
LVL 1

Author Comment

by:senico
ID: 13714588
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 22

Expert Comment

by:Helena Marková
ID: 13714618
I am sorry I don't use TOAD. But there must be somewhere icon for executing a procedure there.
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 13714684
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
 
LVL 1

Author Comment

by:senico
ID: 13714713
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
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 13714718
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
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 13714725
You can also write a PL/SQL block in SQL Editor window or in SQL*Plus :

begin
execute test;
end;
/
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question