SQL & PL/SQL :: Create Trigger Which Will Test Database Link?

Aug 29, 2012

I want to create a database trigger which will test the database link, if it is ok then it will use dblink and do its work.

If it fails then it will send the data into its own server logfile.

I Wrote:

CREATE OR REPLACE TRIGGER PERMIT.TESTTRG
AFTER INSERT OR UPDATE
ON PERMIT.TR_LP_M_H_COMPANY_25072012
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
Declare
l number; nIgn PLS_INTEGER; nRows PLS_INTEGER := 0;

[code]....

When I execute it it is giving error:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/11 PLS-00201: identifier 'EXEC_SQL.CONNTYPE' must be declared
4/11 PL/SQL: Item ignored
8/2 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

[code]....

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Create Test Table - Instead Of Trigger

Dec 30, 2010

table
CREATE TABLE "TEST"
("CONTRACT_REF_NO" VARCHAR2(20 BYTE) primary key,
"CHG_GL" VARCHAR2(9 BYTE),
"CHG_GL_1" VARCHAR2(9 BYTE),
"CHG_GL_2" VARCHAR2(9 BYTE),

[code]...

10 rows selected.

create or replace view test_view
as
select 1 id,CONTRACT_REF_NO,chg_gl
from test
union all

[code]...

11 rows selected.

I have done only for one id.

so the problem is the front end is showing this test_view that people will modify.but they have to actually modify the table ,so I can think of an instead of trigger(instead of insert and instead of update ones).

View 16 Replies View Related

SQL & PL/SQL :: Create Test Table - Trigger Throw Error Message

Mar 4, 2010

create table test123 as (unit varchar2(5),qty varchar2(25));
insert into test123('ABC','10,40,50');
insert into test123('PQR','20,30,40,10');
insert into test123('XYZ','20,10,70');

I have a table called test123 which qty field. if the sum of qty is entered more than 100 or less than 100, it should throw error.

I wrote this trigger..but it is not working.

create or replace restrict_sum
after insert or update of qty on test123
for each row
declare
v_sum number;
[code].........

View 5 Replies View Related

Forms :: Where To Create Database Link

Jun 18, 2013

Usually where should we create the database link? In the production or development? create public database link dblinkname connect to user_in_db2 identified by password_in_db2 using 'tnsentry name in db2'; In production or development?

View 2 Replies View Related

Networking And Gateways :: Create A Database Link?

Mar 11, 2010

I am trying to create a database link using this syntax:


CREATE PUBLIC DATABASE LINK remote
USING 'remote';

where 'remote' is the service name. It creates it successfully giving the message "External database Created"

Note: I am doing using TOAD

But when i am trying to query something like

select * from scott.employee@remote

it gives an error:
ORA-12154: TNS:could not resolve the connect identifier specified

But the 'remote' db entry exists in the tnsnames.

View 14 Replies View Related

Create Database Link With Oracle Client?

Oct 11, 2013

on my Windows 7 machine with Oracle Client 12.0.1.0 installed I'm trying to create a database link to a database server, that runs under Oracle Standard Edition1 11.2.0.2. When i login to sqlplus on the client with "sqlplus /nolog" and type the command : SQL> create database link SID  connect to user identified by passwd  using 'tnsnames.ora connection'; I'm getting an SP2-0640: not logged in.

Ok, thats clear, because i use sqlplus with /nolog option. But on client side normally I have no database, where I can login. So I think, there will be no user, or? The database to which I want to connect is set in tnsnames.ora. Generally: Is it possible to use the Oracle Client to create a database link?

View 8 Replies View Related

SQL & PL/SQL :: Create A Trigger With After Logon Database?

Apr 28, 2011

i would like to trace some connexions on database, once compile the code , i have the issus following:

10/2 PL/SQL: SQL Statement ignored
11/20 PL/SQL: ORA-00942: table or view does not exist

this is the

DROP TABLE connect_user;
CREATE TABLE connect_user
(
nom_ora varchar2(15),
naom_os varchar2(15),
programme varchar2(20),
poste varchar2(20),

[code]...

View 3 Replies View Related

SQL & PL/SQL :: Which Privileges Required To Create A Trigger Like After Logon On Database

Mar 17, 2011

Which Privileges required to create a trigger like after logon on database. I have grant Create any trigger privilege to user but still not able to create and give me an error insufficient Privilege.

View 5 Replies View Related

Application Express :: 4.1 Create Webservice Reference / Test

Aug 29, 2012

I created an report and made it accessible as Restfull server. When I checked the url, I got an XML back. So the service is working.

As test I wanted to create a webservice reference to that same page, but receive ORA-06502 PL/SQL: numeric or value error: hex to raw conversion error whenever I test the service. I tried all kind of options.

Settings:
URL ...........
HTTP MEthod : GET
Parameters
app
page
reportid
(and even parmvalues, lang, output)
outputformat : XML

When I test this service I receive above error. I use Apex 4.1

View 0 Replies View Related

Performance Tuning :: Export SQL Plan From Test Database To Prod Database?

Jul 16, 2013

An SQL query is taking a lot of time than usual and not completing even left after hours! The query joins a table with a quite complex view.

The same query in a test database completes in less than 2 mins.

I would like to export the sql plan from test database to prod database.

how to export/import in 10.2.0.4 version for a particular sql statement's execution plan.

View 2 Replies View Related

Client Tools :: Connecting Directly To Required Database (not Test Database)

Feb 17, 2012

we have a production database 'X'. Now i have created a test database 'T' and did'nt configured another listener to it! The issue is when i cam connecting to oracle through sqlplus i am directly connecting to Test database 'T' but not the production database 'X'----ofcourse i can login to production DB afterwards. but initially i want to access the production database 'X'.

View 15 Replies View Related

Export/Import/SQL Loader :: Refresh A PROD Database Into TEST Database

Jun 17, 2012

I need to refresh a PROD database into TEST database. The PROD and TEST runs on 10g. I need a full refresh. Is there any pre req's which i should keep in mind ?.

View 1 Replies View Related

PL/SQL :: Create Trigger To Insert Data From One User To Another User In Same Database?

Apr 16, 2013

I Created One Trigger as Follows

CREATE OR REPLACE TRIGGER TRIGGER1
BEFORE INSERT
ON table1
FOR EACH ROW

[code]......

Here , I Want To Insert The Data From My User To Test User . In This Situation When I Execute The Above Trigger It Shows The Error PL/SQL: ORA-00942: table or view does not exist

View 3 Replies View Related

SQL & PL/SQL :: Encrypt / Hide Password In Create DB Link Script

Sep 14, 2010

I have a script that creates all db links for a schema. The script currently has hardcoded password use to create db link. We do not want to hardcoded password in the script.

I am planning to manually ask to input the password using accept command in sqlplus but this may be time consuming because there are many db links. So, I wanted to know if there are any better options ? Is there any way to use encrypted password in the sql file?

View 6 Replies View Related

Copy Database From Production To Test?

Feb 22, 2011

Would like to know if I can replicate Production database (10.2.0.2) to Test (10.2.0.4).

tell me the process to do that on windows environment.

I wonder if I can do it with two different oracle versions?

View 4 Replies View Related

Windows :: Test If Database Is Running?

Sep 15, 2010

I have a unix script that will check if the database is up and running. I need a similar script but this time in windows batch file.

this is the snippet of the code from the unix script:

#! /bin/sh
sqlplus -s /nolog > /dev/null 2>&1 <<EOF
whenever sqlerror exit failure

[Code].....

View 2 Replies View Related

Stress Test Of A Database In 2 Configuration

Sep 13, 2013

i've perform a stress test of a database in 2 configuration : 

1) Single instance 11.2 with 8gb Sga e 4gb Pga on Linux, 6core with instance caging =4, resource_manager=defaut_plan

2) 2 Nodes Rac 11.2 with 8gb Sga e 4gb Pga in both nodes on Linux, 32core with instance caging =4, resource_manager=defaut_plan.

I've used Swingbench and the transaction per minute are equal, Avg transaction per s in rac are Greater then single instance (4ms vs 40ms).I know that rac is not for high performance.My question is: is rac config best than single instance and if yes what is the %?

View 0 Replies View Related

Reports & Discoverer :: How To Create Link File Dynamically In Oracle 6i

Oct 27, 2012

how to create link file dynamically in oracle reports 6i

View 3 Replies View Related

Reports & Discoverer :: How To Create Link For Uploading File From Report

Feb 4, 2012

how to create link for uploading file from report

View 2 Replies View Related

Migrate Current Production Database To Test 10.2.0.4 On Windows

Feb 1, 2011

I have to migrate the current production database to test 10.2.0.4 on windows. Any non-export way to upgrade 9i to 10 g?

i have following steps

1) ALTER DATABASE BACKUP CONTROLFILE TO TRACE
2) shutdown oracle 9i database on server A
3) copy database file, controlfile, redolog, and other files to new server B
4) alter the controlfile backup with new location of bdump, udump, and log file and data file locations
5) user oradim ORADIM -NEW -SID SID [-INTPWD PASSWORD ]-MAXUSERS USERS
-STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA
6) start dabase in upgrade mode
7) run catpat.sql and util102.sql
8) take backup
9) open database for users

View 3 Replies View Related

Application Express :: How To Create A Dynamic Action From Link Column In Classic Report

Feb 23, 2013

I Have an apex page that display a modal window utilizing jquery. In the modal window I have a classic report with a link column that I want to capture its click event.

I was thinking I could create a dynamic action with selection type=jquery selector. Not for sure if I need to do anything on link column and do not know the syntax jquery selector.

View 5 Replies View Related

Server Utilities :: Import Table From Another Database By Using Database Link?

Mar 17, 2010

i would like to import a table from another database by using database link.

impdp muba/muba tables=FUNCTION_NO directory=testdump NETWORK_LINK=DBLINK1

i created the public database link as system user

it gave error like

Import: Release 10.2.0.1.0 - Production on Wednesday, 17 March, 2010 11:07:02
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production
Starting "MUBA"."SYS_IMPORT_TABLE_01": muba/******** tables=FUNCTION_NO directory=testdump NETWORK_LINK=DBLINK1
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

[code]....

after that i try to import as system user like

impdp system/passwd tables=FUNCTION_NO schemas=muba directory=testdump NETWORK_LINK=DBLINK1

but error like

Import: Release 10.2.0.1.0 - Production on Wednesday, 17 March, 2010 11:27:43
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production
UDI-00010: multiple job modes requested, schema and tables.

when i use IMP utility

imp system/passwd file=dump.dmp fromuser=userA touser=userB log=dmplog.log but when i use IMPDP what is the parameter equal to fromuser & touser

View 3 Replies View Related

Application Express :: Database Authentication Via Database Link?

Jun 26, 2013

Is it possible to use Database authentication in APEX through database link, and how?Also is it posible to read roles from users through database link?

View 3 Replies View Related

Server Utilities :: System Crashed / Dump Database Into PC To Test And Enhance It?

Feb 5, 2013

My system crashed .

I have an application on the server .

I have installed oracle 10g .

how I can dump the database into my PC to test it , and enhance it.

View 9 Replies View Related

PL/SQL :: Database Link From 11g To 10g?

Jul 5, 2012

I am trying to create a database link from the 11g database to the 10g database using:
create database link ORCL10R2 connect to <username10g> identified by <password10g> using <db10g>;

It Returns
Database link created.

select sysdate from dual@ORCL10R2 returns error:
ERROR at line 1:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

what changes I need to make to tnsnames and listener at both servers.

View 1 Replies View Related

Database Link Not Active

May 15, 2013

I am newbie to oracle database.

I am using oracle 9i version and in EMCA satndalone application . I have created database links.

The database links have been created, and three databases are local.

When i am pressing the test button in emca . It gives notification database link not active.

What should i do?

View 3 Replies View Related

SQL & PL/SQL :: Using A Database Link In A Scheduled Job?

May 12, 2010

A procedure in a package uses a database link. The database link is defined without a username and a password. The user that uses the database link is supposed to be present in the opposite database as wel.

When the procedure is started in SQL*Plus or TOAD it runs perfect. But the procedure has to run in a scheduled job. And that doesn't work. The procedure fails because of: ORA-01017 invalid username/password. The user that is used to run the scheduled job is the right one.

View 5 Replies View Related

SQL & PL/SQL :: Database Link Creation

Jan 28, 2013

I have installed oracle database 11gr2 in laptop1 and installed oracle database 11gr2 in laptop2.Both the laptop's are connected to same internet connection through wifi. So my question is can i create a database link between these two databases using this wifi? give some steps in creation of a database link.

View 13 Replies View Related

DB LINK To External Database?

Jul 10, 2013

i need access to a view running on another database server. i configured my db link and tried to compile the view. but the following message occurs: ora-12154: TNS: Connection Identifier..

View 16 Replies View Related

10g Materialized View Using A Database Link

May 20, 2009

In Oracle 10gR2.If a materialized view uses a database link for the query in order to create a snapshot of data on a remote instance, does the name of the database link have to be an entry in the tnsnames.ora file?

The following link suggests not, but is not version specific: Materialized View - Oracle Wiki FAQ

However, the following 11g documentation suggests that the database link name must be the same as the global name of the target database.URL..
I can't any info specific to 10gR2.

We have three instances. Our application metadata is stored in a schema in B.METADATA. There is a shell schema (B.METADATASHLL) that provides access to the tables to remote applications.

On instance A, we have a shell schema (A.METADATASHLL) that creates materialized views as follows:
CREATE DATABASE LINK METADATA_PRIME
CONNECT TO metadatashll IDENTIFIED BY password
USING 'B';

Our materialized view is created using the query SELECT * FROM METADATA.APPLICATIONS@METADATA_PRIME WHERE Application = 'A';

The query works, but the materialized view does not.I'm being told that the database link has to be named B as follows

CREATE DATABASE LINK B
CONNECT TO metadatashll IDENTIFIED BY password
USING 'B';

Which means that I can only have one public link to instance B, and I'm in a pickle if I create a second application (which I have) using the same model).

View 6 Replies View Related







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