Problem with "merge" in oracle package

Hi!

I tried to compile a package and had some problems:
- can't understand status of package (valid or invalid) - there is not indication in database window
- package body was cut near the merge instraction. See an example below.

create table test_tab(id number, val nvarchar2(255));

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 bti_kl1 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;
/


Under row "when matched then" I lost the rest of the package body.
19 comments
Comment actions Permalink

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.

0
Comment actions Permalink

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.
1.png
After I opened the same package with DataGrip:
1-1.png

Package was compiled with DataGrip, got a error:
2-1.png
If you reopen package with plsql developer, you will see the corrupt package.
2-2.png

0
Comment actions Permalink

Hi,

I can not reproduce the problem in DataGrip 2016.3.1.

Could you try?

Thank you.

0
Comment actions Permalink

Hi

Now it's okay.

But there is another strange error about merge:

Thanks

0
Comment actions Permalink

Hi,

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

0
Comment actions Permalink

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 

 

0
Comment actions Permalink

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)

0
Comment actions Permalink

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

0
Comment actions Permalink

Something strange is happening...

0
Comment actions Permalink

How you open the package?

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

I opened package there:

0
Comment actions Permalink

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

0
Comment actions Permalink

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

0
Comment actions Permalink

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

0
Comment actions Permalink

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.

0
Comment actions Permalink

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

0

Please sign in to leave a comment.