topic Re: Creating Oracle Table by connection to oracle in SAS Programming

Creating Oracle Table by connection to oracle

Virga — Wed, 07 Aug 2019 09:23:03 GMT

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

Re: Creating Oracle Table by connection to oracle

JBailey — Wed, 07 Aug 2019 12:20:23 GMT

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

Re: Creating Oracle Table by connection to oracle

Virga — Wed, 07 Aug 2019 12:39:43 GMT

@JBailey,
Amazing !! It worked ! )))
I understood about semicolons, thank you very much, Jeff !
Good luck you too! )

Re: Creating Oracle Table by connection to oracle

JBailey — Wed, 07 Aug 2019 13:24:17 GMT

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