Error in Unix shell script by using oracle cursor

Question

I keep on getting error as said below:

line 10: warning: here-document at line 2 delimited by end-of-file (wanted `')L_QUERY

My shell script code is as follows:

#!/bin/bash 
sqlplus -silent USERNAME/PASSWORD@DBS <<SQL_QUERY
begin
  for cur_r in (select Branch, Account_Type, Title, FirstName, LastName, Birthday, WorkPhone, HomePhone, Address, State, Zip, Email from accountrequest_temp)
  loop
    insert into accountrequest (Branch, Account_Type, Title, FirstName, LastName, Birthday, WorkPhone, HomePhone, Address, State, Zip, Email)
      values (cur_r.branch, cur_r.account_type, cur_r.title, cur_r.firstname, cur_r.lastname, to_date(cur_r.birthday, 'DD/MM/YYYY), cur_r.workphone, cur_r.homephone, cur_r.address, cur_r.state, cur_r.zip, cur_r.email);
  end loop;
end;
SQL_QUERY

Please help.

First, I should mention that this isn't a great use of a cursor. You should instead use INSERT INTO...SELECT. This will improve your performance by not making individual INSERT calls for each record and reducing the number of context switches. If you did that, you wouldn't even need the PL/SQL block. — Del
– Del, Commented Aug 17, 2021 at 13:15
Here is a link to an Oracle article explaining my cursor comment in more detail: Link — Del
– Del, Commented Aug 17, 2021 at 13:23
@Butter : From your error message, you see that your shell complains about an unterminated HERE-document. This means that sqlplus is not even invoked, which in turn means that, whatever error you may have in your SQL query, it has nothing to do with your problem. I can not reproduce the HERE-error, so either you have not pasted your command verbatim (I guess your username is not really USERNAME), or that you have some non-printable characters in your script. My feeling is that the latter is correct, so I would look at the script as a hex-dump to check for erroneous characters. — user1934428
– user1934428, Commented Aug 18, 2021 at 7:02

Roberto Hernandez · Accepted Answer · 2021-08-17 13:23:27Z

As far as I can see you have two errors:

When you use a BEGIN/END block, you need to finish with /. If not, it is interpreted as a comment and it won't be executed.
You are missing the end quote in the TO_DATE function

Besides the point that you don't need a cursor for this, it would be

#!/bin/bash 
sqlplus -silent USERNAME/PASSWORD@DBS <<SQL_QUERY
begin
  for cur_r in (select Branch, Account_Type, Title, FirstName, LastName, Birthday, WorkPhone, HomePhone, Address, State, Zip, Email from accountrequest_temp)
  loop
    insert into accountrequest (Branch, Account_Type, Title, FirstName, LastName, Birthday, WorkPhone, HomePhone, Address, State, Zip, Email)
      values (cur_r.branch, cur_r.account_type, cur_r.title, cur_r.firstname, cur_r.lastname, to_date(cur_r.birthday, 'DD/MM/YYYY'), cur_r.workphone, cur_r.homephone, cur_r.address, cur_r.state, cur_r.zip, cur_r.email);
  end loop;
commit;
end;
/
SQL_QUERY

Although I would do this instead

#!/bin/bash 
sqlplus -S USERNAME/PASSWORD@DBS <<SQL_QUERY
set echo off 
insert into accountrequest (Branch, Account_Type, Title, FirstName, LastName, Birthday, WorkPhone, HomePhone, Address, State, Zip, Email)
select Branch, Account_Type, Title, FirstName, LastName, to_date(birthday, 'DD/MM/YYYY'), WorkPhone, HomePhone, Address, State, Zip, Email from accountrequest_temp;
commit;
SQL_QUERY

Collectives™ on Stack Overflow

Error in Unix shell script by using oracle cursor

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related