Creating Oracle Table by connection to oracle

[Virga]

Hello!

I need to create table in oracle database by using connect to oracle from SAS EG.

Proc sql;

connect to oracle as ora(PATH=ORAPATH USER=ORAUSER PASSWORD="ORAPASS");

  execute(

        CREATE TABLE SMS_INFO_CONTACT_HIST

       (App_id DECIMAL(16,0),

       Reg_id NUMBER(3),

       MSISDN VARCHAR(11),

       UCP_CONTACT_ID NUMBER(22,0),

       COALESCE_CONTACT_DTTM TIMESTAMP(0),

       camp_name VARCHAR(100),

       sender_name VARCHAR(50),

       contact_text VARCHAR(1024));

  create unique index UCP_CONTACT_ID on SMS_INFO_CONTACT_HIST (UCP_CONTACT_ID);

) by ora;

disconnect from ora;

quit;

But an error occurs.

SAS EG log:

ERROR: ORACLE execute error: ORA-00922: missing or invalid option.

Code works in PL SQL: 

    CREATE  TABLE SMS_INFO_CONTACT_HIST
         (App_id   DECIMAL(16,0),
          Reg_id    NUMBER(3),
          MSISDN VARCHAR(11),
          UCP_CONTACT_ID NUMBER(22,0),
          COALESCE_CONTACT_DTTM TIMESTAMP(0),
          camp_name VARCHAR(100),
          sender_name VARCHAR(50),
          contact_text VARCHAR(1024));

    create unique index UCP_CONTACT_ID on SMS_INFO_CONTACT_HIST (UCP_CONTACT_ID);

Can't understand, whats wrong. Maybe someone came across and can explain? I would really appreciate your help.

Thank You

[JBailey · Accepted solution]

Hi @Virga ,

You have a couple of issues here. There is a ";" where there should not be and you need to add a second EXECUTE statement. 

I have tested this code and it works. 

Proc sql;
connect to oracle as ora(PATH=orapath USER=orauser PASSWORD="ORAPASS");
  execute(
        CREATE TABLE SMS_INFO_CONTACT_HIST
          (App_id DECIMAL(16,0),
           Reg_id NUMBER(3),
           MSISDN VARCHAR(11),
           UCP_CONTACT_ID NUMBER(22,0),
           COALESCE_CONTACT_DTTM TIMESTAMP(0),
           camp_name VARCHAR(100),
           sender_name VARCHAR(50),
           contact_text VARCHAR(1024)) -- removed a semicolon 

) by ora;
   execute (create unique index UCP_CONTACT_ID on SMS_INFO_CONTACT_HIST (UCP_CONTACT_ID)
) by ora;
disconnect from ora;
quit;

Good luck, if you need anything else, holler!

Best wishes,
Jeff

[Virga]

---

@JBailey,

Amazing !! It worked ! )))

I understood about semicolons, thank you very much, Jeff ! 

Good luck you too! )

---

[JBailey]

Hi @Virga 

I am happy it solved your problem. When you have more questions about using SAS with databases, feel free to include my username ( @JBailey ) in the post. The system will notify me.

If you don't mind, mark my previous post as the solution because it helps when others are searching for answers.

Best wishes,

Jeff