SQL & PL/SQL :: Calling Pipelined Function In Merge Statement?
Dec 2, 2010
I am getting a (PL/SQL: ORA-00903: invalid table name) compile error in a procedure using a merge statement. I have seen many examples using this technique and am at a loss as to why I can't compile.
the pipelined function is:
FUNCTION f_crcli_pipe(pi_source_data IN sys_refcursor,
pi_limit_size IN PLS_INTEGER DEFAULT pkg_crcli_variables.c_cursor_limit_def)
RETURN CRCLI_AA
PIPELINED
PARALLEL_ENABLE(PARTITION pi_source_data BY ANY)
IS
[code].....
the error is pointing to the TABLE function in the USING clause of the merge statement.
View 7 Replies
ADVERTISEMENT
Nov 12, 2013
Orcl Ver: 11g R2. I am leveraging the Oracle's pipelined table function.It's working fine for static SQL.
create or replace package test13_pkg as type r_disc_req is record(disc_line_id number,
req_id number); type t_disc_req is table of r_disc_req; function F_GetDiscReq return t_disc_req pipelined; procedure P_ProcessDiscReq;end; CREATE OR REPLACE PACKAGE BODY test13_pkgAS FUNCTION F_GetDiscReq RETURN t_disc_req PIPELINED IS lo_disc_req r_disc_req; BEGIN FOR r_row IN (SELECT disc_line_id, req_id FROM edms_disc_lines_stg WHERE ROWNUM < 10) LOOP lo_disc_req.disc_line_id := r_row.disc_line_id; lo_disc_req.req_id := r_row.req_id; PIPE ROW (lo_disc_req);
[code]...
View 11 Replies
View Related
Sep 13, 2012
I've used PIPELINED FUNCTION and I've no issues in using this. Just wanted to know is there a way so that I don't need to pipe each row separately and I can pipe a set of rows at once.
Like we use BULK COLLECT INTO to fetch multiple rows at once instead of fetching one row using SELECT INTO.
Below is the test case:
CREATE TABLE TMP_EMP
(
EMP_ID NUMBER(10,0),
EMP_NAME VARCHAR2(100),
DEPT_ID NUMBER(10,0),
SALARY NUMBER(14,0),
[code]....
View 13 Replies
View Related
Sep 13, 2013
In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition
ON(source.DNO = target.DNO
AND source.BNO=target.BNO);
I thought that using UNIONALL for select statement of the schemas as below.
SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....
View 5 Replies
View Related
Feb 4, 2013
I have the following C code:
class Factorial {
public:
int getVal (int a);
};
[code]....
/When I am trying to execute this function always get the ORA-06521. I changed the data types - but nothing changed.
Just in case, listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
[code]....
View 6 Replies
View Related
Jun 22, 2012
how to update or insert another (third table ) table with merge statement
View 6 Replies
View Related
Jul 18, 2011
how to use the MERGE Statement. actually I've used oracle Merge Statement before and it works very well. However today I tried to use and perform a command like that:
Merge into myTable mt using ( select 'data' field1, 'data2' field2, ect from dual
union
select 'data' field1, 'data2' field2, ect from dual
union
[code]...
This has not worked.What am I doing wrong?What could I do to solve this problem and axecute this statement sucessfully?
View 3 Replies
View Related
May 9, 2011
in Toad I am using this Merge statement , working accordingly.I want to use this merge statement in my form where can I use it.
I created a Procedure in Form Named Proc_merge and call it when validate item of :bankcode
PROCEDURE proc_merge IS
BEGIN
declare
v_com varchar2(5000);
BEGIN
V_COM := '
[code]...
when matched then
update set
trg.v_chq = src.cheque_no, trg.v_bankcode = src.bank_code ,trg.v_debit=src.debit, trg.v_credit=src.credit, trg.v_narration=src.narration
when not matched then
insert (trg.v_type, trg.v_no, trg.v_date, trg.v_chq, trg.v_bankcode, trg.v_debit, trg.v_credit, trg.v_narration)
values (src.voucher_type, src.voucher_no, src.voucher_date, src.cheque_no, src.bank_code, src.debit, src.credit, src.narration)
';
FORMS_DDL(V_COM);
end;
END;
no any result.
View 2 Replies
View Related
Jul 10, 2013
Table Name: F_SCENARIO
System : Dataware house
Oracle version : 11g
Record Count : 2 Million records
Correct scenario records
F_Key F_Bridge_key Record_type
1 1 1
2 1 2
3 1 3
Wrong scenario records
F_Key F_Bridge_key Record_type
1 1 1
2 -5 2
3 -6 3
I want to write a Merge statement to update the negative values into 1.
View 6 Replies
View Related
Dec 12, 2012
I am using Merge statement to copy data from one table to other,
merge into tabA a
using tabB b
on a.id = b.id
when match
update (
)
when not matched
(insert )
This is working all fine, as SQL, but when there is large volume, it blows out as there are intermediate commits for this ?
View 3 Replies
View Related
Feb 7, 2013
I have two tables have almost the same columns, how can I use merge statement to update the target table only when there is difference between source and target table. Is there any easier way not compare each column one by one? I am using Oracle 11.2
Here is the MERGE statement:
Merge into tb_trgt trgt using tb_src src
on (src.id = trgt.id)
when not matched then insert (trgt.id, trgt.nm, trgt.addr) values (src.id, src.nm, src.nm)
when matched then update set trgt.nm = src.nm, trgt.addr = src.addr
where trgt.nm <> src.nm or trgt.addr <> src.addr
;
Is there any easier way to specify the where clause in the NOT MATCHED? I don't want compare each column of the tables. Since I may have many columns in the tables.
View 3 Replies
View Related
Oct 29, 2012
{code{
i want to apply merge stmt on single table.
CREATE TABLE TEST11(TNO NUMBER(5), TVAL VARCHAR2(100), TID VARCHAR2(10));
INSERT INTO TEST11 VALUES(1,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(2,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(3,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(4,'VIJAYA','');
INSERT INTO TEST11 VALUES(5,'VIJAYA','');
[Code]....
My requiremen is if record is exists then i wan to update some value, if record not existes the i wan to insert new record
View 3 Replies
View Related
Mar 14, 2013
I was reading about merge statement and tried some variations,
create table MERGE_TEST(
C1 number,
C2 varchar2(10 char),
c3 number);
insert into MERGE_TEST values(1, 'Name 3', 300);
insert into MERGE_TEST values(1, 'Name 2', 200);
insert into MERGE_TEST values(1, 'Name 1', 100);
commit;
[code]...
why is result different in this querys?
View 14 Replies
View Related
Apr 6, 2011
mbr has 60,000 rows and member has 60,000 rows approx. two tables have indexes on ssn, and citi_no on them.
PK of mbr : mbr_id
PK of member : mbr_id
other columns are not PK, and have no index on it.
I'm wondering why the statment doesn't use index while ssn and citi_no have index.
MERGE INTO mbr t
USING (SELECT mbr_id,citi_no
FROM member) a
ON (t.ssn = a.citi_no)
WHEN MATCHED THEN
UPDATE SET t.asis_mbr_id = a.mbr_id
where t.ssn not in(select ssn from mbr group by ssn having count(*) > 1)
View 19 Replies
View Related
Aug 10, 2010
Can I have all the rows in a table updated at once in the merge statement?
MERGE INTO providermaster a
using
(
SELECT * FROM PROVIDERMASTER@INGEST) b
ON (b.MASTERPROVIDERID=a.MASTERPROVIDERID)
WHEN MATCHED THEN
UPDATE ....... I want to update all the other rows at once..
View 24 Replies
View Related
May 19, 2010
I am using MERGE statement first time in my application but I am facing error.
begin
merge into store.comm_dept a
using factory_data.comm_dept b
on (a.cd_dcode = b.cd_dcode)
when matched then
[code]........
ERROR IS: encountered the symbol 'INTO' when expecting on of the following:
:=.(@%;
View 7 Replies
View Related
Jun 26, 2013
I am writing below MERGE statement. In this cardinality between table_a and table_b is 1:2. I.e. each record in table_b corresponds to 2 records in table_a based on columns in ON clause.
Well this query throws below error.
----Error---
ORA-12801: error signaled in parallel query server P011
ORA-30926: unable to get a stable set of rows in the source tables
However, the same statement executes successfully when PARALLEL hint is removed altogether. (There are no duplicates in table_b based on unit,group,loc columns.)
-----Query--------
MERGE /*+ PARALLEL(8) */
INTO table_a a
USING table_b b
ON (a.unit = b.unit AND a.group = b.group AND a.loc = b.loc)
[Code]....
View 1 Replies
View Related
Feb 9, 2011
I have a merge statement in one procedure , this job runs on daily . It is running successfully since Jan-19-2010 and giving the below error now.
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcbgcur_9], [4224596], [1],
[4294967250], [2], [], [], []
It is giving error in the beginning of the MERGE statement.
View 11 Replies
View Related
Jul 19, 2012
How to handle the exception on below MERGE statement?
MERGE INTO COMM_EXSTS_COMIT_AGGR TARGET
USING
(
select * from ABC ) SRC
ON
(
SRC.COMMITMENT_ID = TARGET.COMMITMENT_ID
)
WHEN MATCHED THEN
UPDATE
WHEN NOT MATCHED THEN
INSERT ;
View 4 Replies
View Related
Sep 15, 2013
When I am executing merge statement I am getting the below error.
ORA-30926: unable to get a stable set of rows in the source tables
MERGE INTO TAN_LIST t
USING (SELECT * FROM (SELECT row_number () over (partition by TANNO order by null) rn,
dno,
TANNO,
SOL,
[Code]....
The query is fectching below data.
SELECT dno,TANNO,SOL,DESC,class,ct_dt,tcost
FROM MAT_LIST;
DNOTANNOSOLDESC CLASS CT_DT TCOST
63007565ADclass A A12345 08/28/131
[Code]...
Intially thers is no records in the TAN_LIST table. When I run the merge statemnt I am able to insert all 15 records into that table.
When I run the same merge statemment second time I am getting the below error.
ORA-30926: unable to get a stable set of rows in the source tables
So that I have used partition by cluase in the slect statement and I am able to resolve the error.
But it's inserting only 14 records not all 15. How to process all 15 records without the error..
View 16 Replies
View Related
May 24, 2013
I am using the below code to update specific sub-partition data using oracle merge statements.
I am getting the sub-partition name and passing this as a string to the sub-partition clause.
The Merge statement is failing stating that the specified sub-partition does not exist. But the sub-partition do exists for the table.
We are using Oracle 11gr2 database.
Below is the code which I am using to populate the data.
declare
ln_min_batchkey PLS_INTEGER;
ln_max_batchkey PLS_INTEGER;
lv_partition_name VARCHAR2 (32767);
lv_subpartition_name VARCHAR2 (32767);
begin
[code]....
View 2 Replies
View Related
Apr 22, 2013
I am planing to write the web-application which use Oracle DB 11g.I would like to understand what are the differences (specially, performance issues, steps of execution, optimizer possibilities) between calling SQL statements and PL/SQL procedures/functions. Which approach is more appropriate, and why?
Examples:
a) WebApp->Call("select * from employees where department_id = ?", 10) ;
b) WebApp->Call("? := mypackage.get_emp(?)", refCursor, 10);
create package mypackage is
function get_emp(dep_id in number) return sys_refcursor is
begin
open cur for select * from employees where department_id = dep_id;
end;
end;==========================================
Requirements: High-concurrency, 100+ db sessions, DB will not be used for business-logic.
View 10 Replies
View Related
Feb 27, 2011
When I replace the params manualy with dates in format of:
'27-feb-2011 08:00:00'
I get the real output.
But when I call the function with the TEST button in the PLSQL I don't get any output (Empty table).
Here is the original function (Bad output) and attaching a file with manuly added dates (Good output):
FUNCTION WhatRoomsTaken(dStartTime date, dEndTime date, dEventDate date)
RETURN Genrefcursor IS
Retval Genrefcursor;
BEGIN
open Retval for
[code].....
Why do I need to change in order to get the good output by calling the function with params?
View 5 Replies
View Related
Aug 1, 2011
for my r&d purpose i create function which call procedure,
create table test_tab
(a NUMBER(10),
B NUMBER(10),
C NUMBER(10)
[Code]...
but it will gv me error ORA-06575:FUNCTION IS IN INVALID STATE that means function created with compilation error.
but when i complied fucntion it doesnt gv me any error.
View 15 Replies
View Related
Mar 9, 2010
I have this following pakg
CREATE OR REPLACE PACKAGE pkg_test AS
-- Create a table type of the table you want
TYPE tbl_test IS varray(100) of VARCHAR2(30);
-- Function that will return the table type
FUNCTION fnc_test RETURN tbl_test;
-- End package
END;
CREATE OR REPLACE PACKAGE BODY pkg_test AS
FUNCTION fnc_test RETURN tbl_test IS
-- type table_name_va is varray(100) of VARCHAR2(30);
-- Variable of the type tbl_test
[code]...
But i am having problem calling this to test it.
declare
TYPE tbl_test IS varray(100) of VARCHAR2(30);
var_tbl_test tbl_test;
begin
var_tbl_test:= pkg_test.fnc_test;
[code]...
View 2 Replies
View Related
May 8, 2010
I created a PROCEDURE in that i am calling function which calculate sum of salary...I just want Output in format for that which function i need to use...?
Actual Output:::
DEPt_Name SALARY
ACCOUNTING 8750
RESEARCH 10875
SALES 11600
I want Output in well alignment column...i WANT Output IN column format but my output in not geting in that format...Is there any function to align output...I want Output in well alignment column
View 4 Replies
View Related
Sep 6, 2012
11.2
For procedures, we need to grant EXECUTE privilege.
For example:
grant execute on scott.process_salary to john;Lets say I have a function SCOTT.GET_EMPIDS and I want another user john to be able to invoke this fuction. So, what is the privilege that needs to be granted to John.
Is it SELECT or EXECUTE ?
grant select on SCOTT.GET_EMPIDS to john;or
grant execution on SCOTT.GET_EMPIDS to john;
View 2 Replies
View Related
Apr 7, 2011
I tried to use external C procedure from the database and i did all required steps as below.
1. create a C program
2. compile and link the C program
3. copy it to the Oracle_home/bin directory
4. configure listener.ora and tnsnames.ora files
listener.ora
=======
callout_listener =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP) (HOST = ip_address)(PORT = 1521)
[code]...
View 4 Replies
View Related
Jun 2, 2011
I have created a function in form field(when validate item) this should be called in separate procedure. How to call this function in procedure?
View 4 Replies
View Related
Jul 23, 2010
I'm trying to execute a dynamic sql that calls a function. But that function has inserts and deletes inside and this way it can't be called through a select statement. And to be worst, it has an other problem, my function uses a record type as parameter.
My code (sample):
-----------------
DECLARE
type r_parameters is record
(cd_query cons_query_param.cd_query%type,
cd_usuario cons_query_user.cd_usuario%type,
nr_param cons_query_param.nr_param%type,
vl_param varchar2(2000),
[code].....
View 5 Replies
View Related
