Problem with "merge" in oracle package

Hello,
Can you please clarify what do you mean by:

package body was cut near the merge instruction. See an example below.

Can you attach a screenshots showing the issue?

For me posted package body sql is not compiled, giving syntax errors.

Thanks.

Hello.

I sent you a wrong package before, sorry. There is a correct one:

create or replace package test_merge_pkg is

  procedure test_proc;

end test_merge_pkg;
/
create or replace package body test_merge_pkg is

  procedure test_proc
  is
  begin
    merge into test_tab d
    using (select 1 id , '123' NM from dual t) src
    on (d.id = src.id)
    when not matched then
      insert (d.id,d.val)
      values (src.id,src.nm)
    when matched then
      update set d.val = src.nm;
  end;
end test_merge_pkg;
/

First step, I prepared a package in plsql developer.

After I opened the same package with DataGrip:


Package was compiled with DataGrip, got a error:

If you reopen package with plsql developer, you will see the corrupt package.

Thank you!

Created two issues, please follow:

https://youtrack.jetbrains.com/issue/DBE-2165 

https://youtrack.jetbrains.com/issue/DBE-2166 

Hi,

I can not reproduce the problem in DataGrip 2016.3.1.

Could you try?

Thank you.

Hi

Now it's okay.

But there is another strange error about merge:

Thanks

Hi,

Could you give full script to reproduce the problem?
Thank you.

Something wrong with the length of line

DataGrip

PL/SQL Developer

As you see this line much longer.

If you try to recompile package you will receive corrupted package again - dangerous IDE :)

https://drive.google.com/open?id=0B85_mXilf1tUX21WMDl0TWFtNU0 

Hi,

Could you specify DataGrip version and Oracle version? Since I can not reproduce the problem with Oracle 12 and DG 2016.3.1 (after copy/paste it was formatted as it shown on screenshot)

It's not right part of the package, a little bit lower.

"

     p_job_inst_id,
     bti_klut1_Data_s.Nextval)
when matched then
  update set d.UL_OLD = src.UL_OLD,d.NM = src.NM,d.NMDOC1" <- this line

DataGrip 2016.3.1
Build #DB-163.9735.6, built on December 13, 2016
Licensed to DataGrip Evaluator
Expiration date: January 20, 2017
JRE: 1.8.0_112-release-408-b2 x86
JVM: OpenJDK Server VM by JetBrains s.r.o

Orcale 11.2.0.4.0

Something strange is happening...

How you open the package?

I see this line as 273, in your screenshot it's 284.

I opened package there:

I've found a problem. Thank you. It seems to be incorrect sources generation.
Thank you.

I'm glad.

Good luck!

YouTrack issue to track progress https://youtrack.jetbrains.com/issue/DBE-3856

Thank you.

There is another issue that relates to your problem https://youtrack.jetbrains.com/issue/DBE-3863

FYI

It's not about "merge" command issue but about the similar problem.

Could someone explain WHY DataGrip performs fails when it was asked simply to run SQL file (package body) on a certain database schema?

A few minutes ago I found out that DataGrip FAILS to execute package body in case a SELECT statement contains CASE command with alias END... And the package body contains TWO similar functions with the same oddity. I understand that developer made a misprinting though alias END is not a forbidden alias name and SQLPlus and PL/SQL Developer normally works with the code meantime DataGrip - fails.

Please check a dumb example of such function from the package. N.B. Two similar functions with similar code in a row should be. If only one - no issue.

function f_test(p_a in number,
                p_b in varchar2)
  return varchar2
is
  v_res table_lang.field%type;
begin
  begin
    select case when pl.field is not null then pl.field
                else p.field
           end end
      into v_res
      from table p
 left join table_lang pl on pl.table_id = p.id
                        and pl.lang = p_b
     where p.id = p_a;
  exception
    when no_data_found then
      v_res := null;
  end;
  return v_res;
end f_test;

An example of error output from DataGrip

[2017-01-21 17:48:04] [65000][6550] ORA-06550: line 14, column 22:
[2017-01-21 17:48:04] PL/SQL: ORA-00904: "P_B": invalid identifier
[2017-01-21 17:48:04] ORA-06550: line 4, column 5:
[2017-01-21 17:48:04] PL/SQL: SQL Statement ignored
[2017-01-21 17:48:04] ORA-06550: line 18, column 7:
[2017-01-21 17:48:04] PLS-00201: identifier 'V_RES' must be declared
[2017-01-21 17:48:04] ORA-06550: line 18, column 7:
[2017-01-21 17:48:04] PL/SQL: Statement ignored
[2017-01-21 17:48:04] ORA-06550: line 21, column 3:
[2017-01-21 17:48:04] PLS-00372: In a procedure, RETURN statement cannot contain an expression
[2017-01-21 17:48:04] ORA-06550: line 21, column 3:
[2017-01-21 17:48:04] PL/SQL: Statement ignored

DataGrip 2016.3.2
Build #DB-163.11103.2, built on December 29, 2016
Licensed to Kostyantyn Krakovych
Subscription is active until September 24, 2017
JRE: 1.8.0_112-release-408-b6 x86_64
JVM: OpenJDK 64-Bit Server VM by JetBrains s.r.o

Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Hi,

We're planning to include fix with "wide" string in packages in next EAP and DataGrip 3.3.

@Kostyantyn Krakovych could you start new forum thread with your problem?

Thank you.

@Tatyana Aleksandrova
We've solved the issue. Fix is available in DataGrip 2016.3.3.
Thank you