SQL & PL/SQL :: Invoke Procedure With DML And COMMIT Through DBLINK?

May 29, 2012

A simple procedure is created as follows:

Create procedure p1(a varhar2, b varchar2, c_err out varchar2) is
begin
insert into t1 values(a,b);
commit;
exception
when others then
c_err:= substr(sqlerrm,1,250);
end;

It is executed fine.But if it is executed from a dblink as

declare
c varchar2(200);
begin
t1@remote_link('testa','testb',c);
dbms_output.put_line(c);
end;

It is giving error "ORA-02064: distributed operation not supported". How can I pass DML through this procedure and commit from a dblink?

View 5 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Create Procedure By Using Dblink

Jun 26, 2011

iN MY DB SERVER (CALLED A),THERE IS A DBLINK TO ANOTHER DB SERVER (CALLED B). DBLINK'S IS LINKB.
----------
BY USING LINKB,I CAN CREATE,DROP TABLE;DELETE,UPDATE,INSERT DATAS IN SERVER B.

Now,i want to create procedure on server b(but i can only deal with b using dblink linkb).

View 4 Replies View Related

SQL & PL/SQL :: Calling Packaged Procedure With Refcursor As Out Parameter Over DBlink?

Oct 7, 2011

I have a requirement in which I have to call a packaged procedure created in one database DB1 to other database DB2 through a DBLink. The packaged procedure to be called has refcursor as OUT parameter.

When I run the procedure in DB1 , it works fine:
Declare
v_ref_data SYS_REFCURSOR;
a1 Number;
b1 varchar2(100);
c1 varchar2(100);

Begin
apps.XXCU_DB1_PKG.get_DB1('101062','9138', v_ref_data);

FETCH v_ref_data into a1,b1,c1;
dbms_output.put_line(a1|| ' '|| b1|| ' '|| c1);
End;

When I run the packaged procedure from DB2 using DBlink, it gives error:

Declare
v_ref_data SYS_REFCURSOR;
a1 Number;
b1 varchar2(100);
c1 varchar2(100);

Begin
apps.XXCU_DB1_PKG.get_DB1@dblink('101062','9138', v_ref_data);
FETCH v_ref_data into a1,b1,c1;
dbms_output.put_line(a1|| ' '|| b1|| ' '|| c1);

[code]...

I read somewhere on other forums that refcursors can not be passed through the DB links, is it true??

View 1 Replies View Related

Calling Procedure With Commit From Trigger

Dec 8, 2006

I searched, found this one hit and according to mateoc15, you cannot commit within a procedure that is called from a trigger. He must be right, because mine is not committing either, nor are any errors given.

Trigger

Create or replace trigger owner_name.table1_trg2
after update on table1
for each row
call owner_name.procedure1;

procedure Code (psuedo):

Create or replace procedure1 as
begin
update table1 set col1 = 'whatever';
commit;
exception when others then
rollback;

Executing the procedure as owner_name on SQLPlus works fine, but when I update a column of the table via the PL/SQL package (on the web form), the column does not update, telling me that the procedure never fired from the trigger.

View 5 Replies View Related

Forms :: How To Invoke Dll From Oracle 10g

Feb 9, 2010

I want to know how we can invoke a .NET dll from Oracle forms? I did some research and found that WebUtil or ORA_FFI package can work in this process.

Being new to this concept, I am looking for documentation specific to my concern.

View 4 Replies View Related

ODI Invoke Is Not Generating XML Properly

Feb 5, 2013

In weblogic server Axis2 is deployed and it is showing Active.And we have uploaded ODI invoke service also in AXIS2 succesfully and it is showing Active status also...By using the soap its generating the XML with below mentioned error..

some info:- We rae using ODI 10g.
Weblogic 10.3.5.0

XML GENERATING:--_

<xs:element name="inPhasesUptoAndIncludingPostDispatch" nillable="true" type="xs:anyType" />
<xs:element name="key" nillable="true" type="xs:anyType" />
<xs:element name="localPolicyAssertions" nillable="true" type="xs:anyType" />

[code]...

The XML page cannot be displayed Cannot view XML input using XSL style sheet. correct the error and then click the Refresh button, or try again later.

The operation completed successfully. Error processing resource [URL]

</xs:schema></wsdl:types><wsdl:message name="invokeScenarioMessage"><wsdl:part name="part1" element="ns...

View 2 Replies View Related

Forms :: How To Invoke And Display Scanned Image

May 26, 2010

How to invoke and display a scanned image side by side with an Oracle Form without minimizing one of the two. I used the code below to call my document, but when i navigate to it so as to be able to see the detail , the oracle form will be dismissed and vice-versa.

DECLARE
v_claim varchar2(400):=:global.photo_path||:b4.doc_no||'.JPG';
BEGIN
client_Host('cmd /c START "" "'||v_claim||'"');
[code]....

Should there be another better way,

View 10 Replies View Related

Forms :: Using Java Importer Tool To Invoke Web Services From Oracle?

Feb 1, 2011

I am trying to use the Java Importer tool to invoke web services from Oracle forms.

During my research on Java Importer tool, I found that Quote:"The class browser lists all of the Java classes found on the CLASSPATH. The classes are ordered in the same way that they are represented in the CLASSPATH."

The above quote is from Oracle Documentation.

I want to know where I can find the CLASSPATH so that I can add new Java Classes to it. Our forms builder runs on Unix environment.

View 5 Replies View Related

PL/SQL :: Calling Dbms_Scheduler From Forms Trigger To Invoke SqlPlus Script In Linux

Mar 25, 2013

I would like to use Dbms_Schedule to call SqlPlus script in Linux .

BEGIN     
sys.Dbms_Scheduler.Create_Job (
job_name => 'TEST_SQL_JOB',

[Code]....

This is giving me the error: ORA-27369: job of type EXECUTABLE failed with exit code: No such file or directory

BUT when I issue the command: /usr/local/bin/sqlplus -s s/ibs @/usr2/dms/run/lkp_sql_test.sql
it works fine.

View 4 Replies View Related

SQL & PL/SQL :: Execute Immediate With DBLink

Feb 23, 2011

I need to write several variations of this statement below to query a remote database, but am having trouble with the syntax.

execute immediate 'select count(*) from ' || v_tablename || '@dblink ' || into v_rowcount;
PLS-00103: Encountered the symbol "INTO"....

What's the correct way to format this kind of dynamic query?

View 4 Replies View Related

PL/SQL :: ORA 02070 And DBLink?

Jul 20, 2012

i am using 11g and pl sqlk developper, here is the script snippet

MERGE INTO GDIEXP.OEM_SCHEMAS@OX10CC30_GDI.LOTO_QUEBEC.COM O
USING (
SELECT T.GRANTEE, I.INSTANCE_NAME
FROM DBA_ROLE_PRIVS T, V$INSTANCE I
WHERE T.GRANTED_ROLE = 'PRIVPROPRIETAIRES') DRP

[code]...

An oracle 02070 is popping up, i don't what's the real reason behind it, i only know it works when i don't use the db link. What can i do ?

View 3 Replies View Related

Getting Data Over SQL Server DBLink

Apr 17, 2012

I just created a dblink from our Oracle 9i database to a SQL Server 2008 R2 Server using oracle heterogeneous services. The dblink seems to be OK and I can query the remote SQL Server database but I only get results for numeric columns.

Say the remote SQL Server table was created like:

CREATE TABLE [MITLOC] (
[MLCONO] [numeric](3, 0) NOT NULL,
[MLWHLO] [nchar](3) NOT NULL,
[MLITNO] [nchar](15) NOT NULL,
[MLWHSL] [nchar](10) NOT NULL,
[MLBANO] [nvarchar](20) NOT NULL,
[MLCAMU] [nvarchar](20) NOT NULL,
[MLFACI] [nchar](3) NOT NULL,
[MLWHLT] [nchar](2) NOT NULL,
[MLSTQT] [numeric](15, 6) NOT NULL)

The remote dblink is named after M3PREP.

Then if I run:

CODEselect MLCONO, MLSTQT from MITLOC@M3PREP => this works OK and I get the two columns.

If I run:

CODEselect * from MITLOC@M3PREP => I get the same results as in the previous query and all nchar, nvarchar type columns are missing, I only get the same two columns.

If I run:

CODEselect MLCONO, MLWHLO from MITLOC@M3PREP=> I get ORA-00904 - the column name entered is either missing or invalid.

I set up my .ora init file like this, I suspect there is something missing here, is it related to the NLS_LANG or so?

CODE#
# HS init parameters
#
HS_FDS_CONNECT_INFO = M3PREP
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME = M3PREP.LOG

#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

View 1 Replies View Related

Way To Reduce Dblink Timeout

Dec 28, 2011

is there any way to reduce the dblink timeout, i need to test if the remote server is up by testing the dblink throw 'select 1 from dual@link_name' but it take too long time 5-6 min., i need to make it 5 sec after that the exception tns timeout appear. I try with some sqlnet.ora parameters like INBOUND_ TIMEOUT but not work for me, also i try using tnsping but it also take long time when the remote server is down.

View 2 Replies View Related

Nested Tables By DBLINK

Feb 1, 2011

There's a table T with a nested table within it on the master database. I need to mantain a materialized view of the table T on a remote database.

I get this error: QUOTE ORA-22804: remote operations not permitted on object tables or user-defined type columnsIs there any recommended workaround of this problem? The remote data must have the same structure as the master one.

The data should be refreshed every day, the data changes moderately, there are more or less 500 records.

View 2 Replies View Related

SQL & PL/SQL :: Selecting Clob Value Over DBLink?

Sep 10, 2012

any way to select the clob value from dblink.?

View 7 Replies View Related

SQL & PL/SQL :: Inserting Data Using DBLINK

Feb 21, 2013

I am inserting XMLTYPE data using DBLINK I am getting the following error.

INSERT INTO APSP.SALES_HISTORY@APSP_LINK
SELECT * FROM KMBS.SALES_HISTORY

ORA-22804: remote operations not permitted on object tables or user-defined type columns

Source table structure

Name Null? Type
----------------------------------------- -------- ----------------------------
SC_NO NOT NULL NUMBER(25)
LT_DATE TIMESTAMP(6)
METHOD XMLTYPE

Target table structure(another DB)

Name Null? Type
----------------------------------------- -------- ----------------------------
SC_NO NOT NULL NUMBER(25)
LT_DATE TIMESTAMP(6)
METHOD XMLTYPE

how to insert XMLTYPE data using DBLINK.

View 16 Replies View Related

How To Create A Private DBLINK In 10g

Oct 20, 2012

I want to create a private DBLINK in 10g and grant access to use this to limited (another 2/3) users. Is that possible ?I don't want to create PUBLIC DBLINK because the users to use it are limited.

View 4 Replies View Related

Bulk Collect With DBLink?

Feb 23, 2012

create or replace PROCEDURE CDR_PROC_ARCHIVE_ORDER_EXTRACT
IS
/*
Criteria to be followed to Order Archival

* Order Status should be 'Cancelled' or 'Complete'
* Order Closed date should be 6 months before
*
-- main Cursor to spool the Orders to be archived based on criteria

[code]...

View 1 Replies View Related

SQL & PL/SQL :: Materialized View Over DBLink

May 7, 2010

I have got a materialized view which is created over a dblink as below:

CREATE MATERIALIZED VIEW CRIMEREPORTODSV
TABLESPACE ODS_DATA
BUILD IMMEDIATE REFRESH FAST AS
(SELECT * FROM CRIME_INT@CRISP);

This is all fine and works apart from any changes to the source table (CRIME_INT) isn't reflected unless I perform a refresh. Whereas I want any changes to be reflected straight away. I have had to use a Materialized view in this case as one of the columns in the source table is a CLOB and Oracle won't allow creation of a view with a CLOB field over DBLINK.

View 4 Replies View Related

Control Timeout Before Connecting Via DBLink?

Feb 28, 2012

Is there any way to control the timeout before connecting via dblink. the idea is to test the dblink before calling functions over it, the problem it when i try 'select 1 from dual@dblink' in case if the remote server is down it takes too long time 5-7 min. which causing hang up the session,

So is there any parameter to reduce the waiting time say after 3 sec stop and return the timeout exception.I try the SQLNET.INBOUND_CONNECT_TIMEOUT parameter but not working, also try the tnsping but it alse take long time when the server down.

the database version is 10gr2.

View 5 Replies View Related

Materialized View Log On DBLink Table?

Aug 6, 2010

DB1: Sql Server
DB2: Oracle 11g

Is it possible to create a MV log against table@DB1? If not, then am I limited to refresh complete if I need to create MVs against @DB1?

View 1 Replies View Related

SQL & PL/SQL :: Accessing Data From Server By Using DBLINK

Oct 22, 2010

We are accessing data from the server ADM.WORLD by using DBLINK.We got the following error.

PL/SQL: ORA-04052: error occurred when looking up remote object
sysadm.PS_HP_INC_ELIG_VW@ADM.WORLD
ORA-00604: error occurred at recursive SQL level 1
ORA-28000: the account is locked
ORA-02063: preceding line from ADM

For that we checked in the server ADM.WORLD for the account the account is showing locked .After that we successfully accessed the object sysadm.PS_HP_INC_ELIG_VW@ADM.WORLD.

For next day also the account is locked.Why the account is frequently locking.

View 15 Replies View Related

Windows :: Move Data From One DB To Other Without DBlink And Using Dot Net

Oct 30, 2012

I have database A and B , we are strictly restricted to not to create any DB links between these database. In database A based on complex logic we in a stored procedure we populate the data to table type..now dot net will take this table type data and insert the same in database B.

Question here :

1.When we try to bulk collect all the data into table type , i am getting error operand exceeds limit, but it is mandate to put all data in one short to table type as dot net will take that and insert into B database.
2.Is there any good logical method to achieve this in a simple way.

View 3 Replies View Related

SQL & PL/SQL :: Difference Between Materialized View And DBLink

Jun 24, 2010

What is the difference between Materialized view and dblink. When we use materialized view and when we use dblink. In my knowledge mviwes and dblinks are used to retrieve data from remote database.But I am unable to understand when should we materialized views and when should we use dblinks.

View 1 Replies View Related

PL/SQL :: Generate Stats For DBLink Table (SQL)

Sep 7, 2012

I am trying to generate some statistics on tables connected by a dblink. I know with oracle you have table_columns which you can reference and pull some stats from.

Trying to get the column count and record counts for each table connected by a dblink. I have tried these queries below to see if I could see any db properties: (some just to try something different)

select * from "table_owner".table_column@dblink ;
select * from "status"@dblink;
select /*DRIVING_SITE(a) */ count(*) from @dblink a;

What is the best method to finding this out without spending a lot of time? I have over 30 tables which are with large record sets and would love to learn a faster approach then pulling a sample table and doing a manual count and query for each table to count the rows.

View 3 Replies View Related

Possible To Specify Login / Password For DBLINK When Calling It?

Apr 3, 2013

I have a Oracle 91 database running on Windows 2003 with a DBLINK setup to connect to an external client. I'm looking at how i can setup a quick job that, say every 5 mins, runs a query over the DBLINK and if it returns data, then success. If it returns an error, then the DBLINK is down, moreso the external server, and then fire off an email to someone.

However, this DBLINK i think was created to be used by a specific username/password. When i try to run a query against it using SYS, i get invalid username/password error.

So is there a way i can use a DBLINK and specify the username/pwd to be used at runtime? I'm new to Oracle database..Something like 'select * from global_name@DBLINK1 as username/pwd'.

View 10 Replies View Related

DBLink Not Querying Remote Table

Apr 16, 2013

I successfully created the following DBLink in my DEV environment:

CREATE PUBLIC DATABASE LINK PROD
CONNECT TO USER1 IDENTIFIED BY pwd98
USING 'PRD';

I have a table in both schemas. In PROD it has 0 recs, in DEV it has 134 recs.

select count(*) from hold@PROD.

The above query gives me 134 recs instead of 0!

Here is my TNSNAMES entry for PRD:

PRD=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=localhost)
(PORT=1529)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=PRD_USR)
))

View 14 Replies View Related

SQL & PL/SQL :: Unable To Recreate DBLink In Another DB / Schema?

Mar 25, 2010

I'm unable to recreate the db link (private) in another schema/database. Even its a schema object, its come without schema name while extracting from database using metadata api.

code is below (just for extracting)

CREATE OR REPLACE FUNCTION DDI.DBLINK_DDL
RETURN CLOB
AUTHID CURRENT_USER
AS
v_meta_handle NUMBER;
v_meta_handle_trans NUMBER;
V_DOC CLOB;

[code].....

View 8 Replies View Related

Wrong Number Format Display Using DBLink

Jan 7, 2013

I have an issue trying to execute some queries using a dblink. When i run any query with numeric fields only display 4 digit and int the source database the fields have 5 digit. The dblink work between ans MSSQL database to an Oracle Database

Example:
MSSQL
select cardnumber from card
cardnumber
19121
19122

Oracle (with dblink)
select cardnumber from card@dblink1
cardnumber
1912
1912

View 1 Replies View Related

Server Utilities :: Connect To Databases With DBLINK

Jul 17, 2013

I'm try to connect to databases with DBLINK. It works, but I drop the dblink to do it again in the final server. The problem that I now is that there is not any DBLINK in my database but I can see the data of the other database (with select 1 from DATABASE.TABLE)

Is my database saving the data of the other database without de DBLINK?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved