You Asked

Hi Tom,

i create a function that i want it to do some jobs ,just for the test my function will be
FUNCTION test
RETURN varchar2 IS
val number(7);
BEGIN
select seq_ref_ext_grpe.nextval into val from dual;--recupere la sequence actuelle
dbms_outpu.put_line('val == '||val);
return val;
END GET_SEQ;

and i hade a procedure that do a merge and when not matched do an insert
this the the merege i made:

MERGE INTO BT_ADM.TIE_GRPE cible
USING (
SELECT smth
) sas
ON (smth)
WHEN MATCHED THEN
UPDATE
SET
......

WHEN NOT MATCHED THEN
INSERT (ID_TEST,VAL_TEST)
VALUES ( ID,test()
);

the probleme is in when not matched ,when inserting i call the function,i could see the dbms_output i made into the function getting the right values , but i see no insert after checking the table where the insert was made , i hade no error also.

THanks in advance for the help , i was working on that for a while , and i think the syntax is correct since the dbms output is displayed, am suspecting the merge and calling a function inside

Ali,

Best regards

and Connor said...

You'll need to give a complete test case, because I can't replicate your issue - see below


SQL> drop sequence seq_ref_ext_grpe;

Sequence dropped.

SQL> create sequence seq_ref_ext_grpe;

Sequence created.

SQL>
SQL> create or replace
  2  FUNCTION f1 RETURN number IS
  3  val number(7);
  4  BEGIN
  5  select seq_ref_ext_grpe.nextval into val from dual;--recupere la sequence actuelle
  6  dbms_output.put_line('val == '||val);
  7  return val;
  8  END;
  9  /

Function created.

SQL>
SQL> drop table t purge;

Table dropped.

SQL> create table T ( id int, p int );

Table created.

SQL>
SQL> set serverout on
SQL>
SQL> MERGE INTO T
  2  USING (
  3  select 10 id from dual
  4  ) d
  5  ON (t.id = d.id)
  6  WHEN MATCHED THEN
  7  UPDATE
  8  SET p = 10
  9  WHEN NOT MATCHED THEN
 10  INSERT (id,p)
 11  VALUES ( f1(),99 );
val == 1

1 row merged.

SQL>
SQL> select * from t;

        ID          P
---------- ----------
         1         99

SQL>
SQL> MERGE INTO T
  2  USING (
  3  select 10 id from dual
  4  ) d
  5  ON (t.id = d.id)
  6  WHEN MATCHED THEN
  7  UPDATE
  8  SET p = 10
  9  WHEN NOT MATCHED THEN
 10  INSERT (id,p)
 11  VALUES ( f1(),99 );
val == 2

1 row merged.

SQL>
SQL> select * from t;

        ID          P
---------- ----------
         1         99
         2         99

SQL>
SQL> MERGE INTO T
  2  USING (
  3  select 10 id from dual
  4  ) d
  5  ON (t.id = d.id)
  6  WHEN MATCHED THEN
  7  UPDATE
  8  SET p = 10
  9  WHEN NOT MATCHED THEN
 10  INSERT (id,p)
 11  VALUES ( f1(),99 );
val == 3

1 row merged.

SQL>
SQL> select * from t;

        ID          P
---------- ----------
         1         99
         2         99
         3         99

SQL>
SQL>

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ali chahib, January 31, 2016 - 10:21 am UTC

Thanks a lot , that was been helpful , that was my first wuestion here , so i didnt know the prcess ;)

anyway , it worked for me too :)

January 31, 2016 - 12:57 pm UTC

Glad we could help :-)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

callin a function into an insert of merge

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs