SAS program stuck forever while executing proc sql insert

[leen]

Hi, my sas is stuck everytime I try to run this code:

proc sql;
    insert into myora.Customer_t
    set customerid = 100,
        customername = "Cool Future",
        customeraddress = "123 Lake Side Drive",
        customercity = "Ithaca",
        customerstate = "NY",
        customerpostalcode = "14850";
quit;

But I can connect to myora and select as normal (as shown below:)

1    libname myora oracle user="xxx" password=XXXXXXXXXX path="xe";
NOTE: Libref MYORA was successfully assigned as follows:
      Engine:        ORACLE
      Physical Name: xe
1  !                                                                  run;
2    libname orasas "D:\hw5";
NOTE: Libref ORASAS was successfully assigned as follows:
      Engine:        V9
      Physical Name: D:\hw5

3    title2 "Output2";
4    proc sql;
5    select * from
6    (select * from myora.customer_t where customerstate="NY" or customerstate="PA")
7    order by customerstate, customername;
NOTE: Writing HTML Body file: sashtml.htm
8    quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.25 seconds
      cpu time            0.09 seconds

This is what it looks like when I submit those code:

I really don't know why it is stuck every time just for this insert statement. I tried to wait for it. But it just won't finish executing.

There is no log output, I can't even scroll the log window, or anywhere else.

I can insert this row as normal in SQL developer:

INSERT INTO Customer_t (customerid, customername, customeraddress, customercity, customerstate, customerpostalcode)
VALUES (100, 'Cool Future', '123 Lake Side Drive', 'Ithaca', 'NY', '14850');

(Log: "1 row inserted.")

[ballardw]

Are you sure that the credentials you log into Oracle with have permission to write to that particular table?

Also, what constraints are on the customer_t table? Perhaps what you attempt to insert violates one or more constraints such as missing a variable.

And in the possibly lower probability of causing problems I see you reference "Customer_t" in the first SQL and "customer_t" in the second. I don't use Oracle so don't know how case sensitive names for table may be. So you may want to ask the Oracle administrators.

[leen]

Oracle sql plus is not case sensitive. The credentials are the same as those in sql developer. But I can insert as normal there, which means there shouldn’t be any permission/modifying rule issue. even though there is one i think I should get an error instead of running forever.

[Tom]

Why are you using two totally different syntaxes for the INSERT statement?

What happens if you use the second syntax in SAS?

libname myora oracle ...... ;
proc sql;
INSERT INTO myora.Customer_t (customerid, customername, customeraddress, customercity, customerstate, customerpostalcode)
VALUES (100, 'Cool Future', '123 Lake Side Drive', 'Ithaca', 'NY', '14850')
;
run;

[Patrick]

Just as a thought: In the screenshot you share you're issuing a DELETE and an INSERT within the same Proc SQL. It could be - just a theory - that the DELETE doesn't get committed and though the INSERT is waiting.

To test the theory: Issue the DELETE and INSERT using two Proc SQL to ensure that the DELETE gets commited prior to the INSERT. 

[leen]

Thank you but No that’s what I added after I kept having this problem.

[SASKiwi · Accepted solution]

Try SQL Passthru. That's exactly like using SQL Developer:

proc sql;
  connect using myora;
  execute (
INSERT INTO Customer_t (customerid, customername, customeraddress, customercity, customerstate, customerpostalcode)
VALUES (100, 'Cool Future', '123 Lake Side Drive', 'Ithaca', 'NY', '14850')
) by myora;
run;

[Sajid01]

Hello @leen .
Reading this post along with your other post (https://communities.sas.com/t5/SAS-Programming/SAS-takes-so-long-to-initiate-print-even-very-small-d... ) and the fact that you have used path="XE",  gives me the impression that you have PC SAS and Oracle express installed on your PC and that your SAS initialization and often running small tasks is slow.
Under these circumstances creating a libname statement implies bring your entire Oracle table into SAS at the backend. That may cause the execution of the process to be slow.
Rather than dwell on the mechanics of the process, I would recommend that you try SQL pass through as suggested by @SASKiwi 

[leen]

I can use sql pass through but it's a homework assignment so i am required to use libref. Anyway I mis-clicked on the accept button and I might find solution by myself. Thank you.

[SASKiwi]

To be pedantic, you are using the LIBREF as the definition for the Passthru connection...🙂