Executing Stored Procedure / Function From Database Link?
Feb 24, 2011
I have 2 databases:
Database A
Database B
In Database A I have a series of config tables and stored procedures/functions In Database B I have a lot of tables.
I would like to execute my stored procedures and all associated functions in database A on my data in database B.
Now I've figured out that creating a database link enables me to do SQL selects on data in both A and B...but how do I run SP/Funcs ? I've read something about packages but not sure if I'm heading in the right direction.
Do I need to create a simple synonym ? Can I use the existing DB link ? or is there a special way of calling them, or...
I like the A/B database set up since I can keep battle tested code in one location and have it work across multiple dbs...saves me having to create everything in every database.
View 2 Replies
ADVERTISEMENT
Apr 9, 2013
There are 2 Oracle databases with pseudo names Remote and Local. I have a function in Remote called FUS.F_Return_10 which simply returns 10 for testing purposes, where FUS is a schema name. In Local I want to create a procedure that will call the above function. Here's the PL/SQL:
CREATE OR REPLACE PROCEDURE TEST
(
V_COUNT OUT NUMBER
)
AS
V_FOO NUMBER(2,0);
BEGIN
[Code]...
There's a Public Database Link called PER_ACC in Local. When I try to create this procedure I get: Encountered symbol "@" when expecting one of the following: .(*%&................
where my mistake is?
View 7 Replies
View Related
Nov 18, 2010
Procedure: CREATE OR REPLACE procedure test (a number, b varchar2) is
begin
dbms_output.put_line(a ||'->'||b);
end;
Anyonymous Block:
begin
exec test(1,'m');
end;
/
When i run this i am getting this error
ORA-06550: line 2, column 7:
PLS-00103: Encountered the symbol "TEST" when expecting one of the following
[code]...
View 5 Replies
View Related
Aug 28, 2012
I am trying to execute the SQLPLUS command (CONNECT) from the stored procedure.It is throwing below error message.
Stored Procedure:
SQL> select user from dual
2 ;
USER
------------------------------
SYS
SQL> create or replace
2 PROCEDURE PROCEDURE1 AS
3 BEGIN
4 sqlplus sys/sys@D as sysdba;
5 --execute immediate 'create user 'kkk' identified by 'kkk';
6 END PROCEDURE1;
7 /
Warning: Procedure created with compilation errors.
SQL> show err
Errors for PROCEDURE PROCEDURE1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/9 PLS-00103: Encountered the symbol "SYS" when expecting one of the
following:
:= . ( @ % ;
The symbol ":=" was substituted for "SYS" to continue.
[code]....
View 29 Replies
View Related
Jun 9, 2010
Interviewer asked me "Tell me Diff. between Stored procedure vs. Function ".....I given technical answer which is mentioned in my Faq..But he asked me , dont gv me answer in technical manner..He was interested in which case u use Stored procedure and Function....
View 3 Replies
View Related
Jan 12, 2010
I have to call oracle stored procedure or a function to update user id of one or several rows depending on what user selects.
The oracle person will be writing the stored procedure or function. I am a java developer but was never involved in writing oracle functions. So..
I want to know what is the difference betn stored procedure and function?
From java point of view are there any guidelines when to prefer one over the other(betn stored procedure and function).
View 2 Replies
View Related
Feb 13, 2012
I am familiar with WRAP command.It is used in command prompt.
C:> wrap iname=filename.sql
and this thing will convert filename.plb file.
but now I want to wrap my stored Procedure or function.how can i do this thing?it is saved in my disk?
View 10 Replies
View Related
Apr 16, 2010
I have wrote a Stored Procedure Function that get all the rows from a Staging Table and assigns it to a CLOB and returns the CLOB. The issue is I'm getting the dreaded
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "F_CLOB_TEST", line 21
ORA-06512: at line 7
The error does not occur when I remove the 'PRC_ID', 'S_FIL_NAME' & 'exportDt' from the query. It works fine. The PRC_ID has data such as "700$702$7 05$706$707$708$709$710$711$712$713$714$715$294404$294405$294407$294408$294409$294410"
and S_FIL_NAM is the same for all columns - "SBENE_FILID810-2010-04-07-10.59.17"
The query returns 829 rows. Also I have to include a few more columns in the query which have data larger than the 'PRC_ID', but I have not included them here in the sample code, as this code by itself returns the ORA-6502 error.
create or replace
FUNCTION "F_CLOB_TEST" (job_id in Number)
return clob
is
c_clob clob;
[code]....
View 26 Replies
View Related
Apr 30, 2013
I need to call the VB function below from a Procedure's PL/SQL code and capture the returned variable into a varchar2 variable.I looked at the several means and nothing seems to work.
View 5 Replies
View Related
Jun 19, 2012
in a 9.2.0 db I create a db link but does not function :
CREATE DATABASE LINK "ONEDB"
CONNECT TO "user1" IDENTIFIED BY "****" USING 'ONEDB';
SQL> select * from dual@"ONEDB";
select * from dual@"ONEDB"
*
ERROR : ORA-12154: TNS:could not resolve service nameBut on the same server I can connect to 11g in sqlplus :
Z:>sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Mar. Juin 19 10:13:45 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> connect user1@ONEDB
password :
Connected.Of cours the other DB links toward 9.2.0 databases work well.
View 4 Replies
View Related
Feb 17, 2010
I want Oracle stored function/procedure to calculate number of working days between two dates. We need to exclude Firdays and Saturdays as there are weekend holidays and also exclude official holidasy that lie between two dates.
View 7 Replies
View Related
Jun 7, 2011
Is it possible create dynamically database link within procedure.
It give me the following error
ORA-01031: insufficient privileges
But i can drop database link within procrdure.
Is there any way to do this?
View 4 Replies
View Related
Jun 17, 2009
the procedure to link oracle 9i database server withi 10i.
View 1 Replies
View Related
Feb 15, 2011
How to call a procedure by passing the db link dynamically.
View 1 Replies
View Related
Oct 5, 2010
I am trying to insert data from 9i to 11g using db link with the below query but while doing so the select statement is going for a full table scan even though rowid is used. But when we execute the same select statement without this insert command it is using proper index. Similar issues I am facing for updates.
Query:
INSERT INTO /*+ APPEND */ emp@db_link
select *
from emp where rowid ='AAC2SmAIIAAAHQgAAZ'
View 6 Replies
View Related
May 22, 2013
I have created a function for knowing the status of link which connect me with remote database.function is as follows
---------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION get_status_db_link (p_db_link_in VARCHAR2)
RETURN NUMBER AS
rows NUMBER;
v_code NUMBER;
v_errm VARCHAR2(64);
[code]....
Now functin should returns 1 when link is up and it should return 0 when link down but sometime when link is down it continues showing executing it hang my pc.
View 1 Replies
View Related
Oct 18, 2012
The Oracle DB in question is 11.2.0.1, x64, Server 2008.I also have a SQL Server 2005 database that runs a third party product, "PaperVision", which we use to manage documents of various kinds. This SQL Server server is also Win 2008, x64.Now, on the server that runs SQL Server, I have a simple view which is defined as such :
select DOCID,
DOCINDEX1,
DOCINDEX2,
DOCINDEX3,
DOCINDEX4,
DOCINDEX5,
DOCINDEX6,
DOCINDEX7,
DOCINDEX8,
[code]....
This view works great from the SQL Server side. I also created a database link from Oracle to the SQL Server machine, and it also works great.It is defined as such :
CREATE PUBLIC DATABASE LINK PVE_SQLSERVER
CONNECT TO EVP_PVE_USER
IDENTIFIED BY <PWD>
USING 'PVE_SQLSERVER';
Where EVP_PVE_USER is a user created on the SQL Server machine with rights to select from this view.I know it works because I get results with a sql command like :
select * from VW_PVE_DOCS_1_1@PVE_SQLSERVER;
I also created a view on the Oracle server that refines this information. It is defined as such :
CREATE OR REPLACE FORCE VIEW EVPDBA.VW_PVE_CONTRACTS_INALERT
(
DOCID,
EFFECTIVE_DATE,
EXPIRATION_TYPE,
ALERT_PERIOD_START,
ALERT_PERIOD_END,
ACRONYM,
[code]....
This view also works fine, i.e., I can select * from it from the sql command line.Now, the problem comes in when I need to run a procedure that processes this view every night and/or week.I have stripped everything out of this procedure that is not relevant, and it is defined as such for this forum :
CREATE OR REPLACE PROCEDURE EVPDBA.TESTME
is
tnum number := 0;
begin
select count(*) into tnum from VW_PVE_CONTRACTS_INALERT;
end;
/
If I execute this procedure from the sql command line, all is well.When I run it from a scheduler job, I get
ORA-01010: invalid OCI operation
ORA-02063: preceding line from PVE_SQLSERVER
ORA-06512: at "EVPDBA.TESTME", line 5
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1
I am aware that DBMS_SCHEDULER performs a commit when scheduling a job, however, this is not scheduled from a trigger.I scoured the forums and have found a few things that seemed relevant, but not much. One had to do with the version of the JDBC driver between two Oracle databases, but I wonder if the age difference between Oracle 11 and SQL Server 2005 (Express) might be an issue. The fact that all command line select statements and running the procedure work fine implies to me that there is an additional issue raised due to the scheduler.
The other posts I found talked about performing a commit just before any select that ultimately pulls across a db link. I did this, and still no luck.One other useful fact - the job appeared to run succesfully at 5am, yet trying again at 8am threw the error, so it may be sporadic. (Although during regular daytime hours it is a very repeatable error).
I am looking into reformatting things to use the older DBMS_JOB, however, I really like the log history of job details and other functionality available with SCHEDULER.
View 5 Replies
View Related
Jun 20, 2011
There is one DBMSJOB i created schedule to run every day at 22:00 hrs. The jobname is IT_TO_DUM_LOADING and the job type is Stored procedure.
I queried all_source and found stored procedure does not exist in database.
SQL> select text from all_source
2 where type = 'PROCEDURE'
3 and name ='IT_TO_DUM_LOADING';
no rows selected
But i could see the job details in dba_scheduler_jobs view. I cannot use WHERE clause in dba_scheduler_jobs to restrict and find only the job row as i receive time zone error. How it is possible to view the job in dba_scheduler job view whereas i could not find the stored procedure of its in all_source?
View 7 Replies
View Related
Aug 9, 2011
I'm trying to run an sp to debug the issue. SQL Developer generated this script for me..
DECLARE
P_MMRRRR VARCHAR2(200);
P_DATA1 xxx_package.xxxtype;
P_DATA2 xxx_package.xxxtype;
BEGIN
P_MMRRRR := 04/2011;
[code]........
The package definition for xxxtype is
TYPE xxxtype IS REF CURSOR;
The error I keep getting is
Bind Variable "P_DATA1" is NOT DECLARED
anonymous block completed
View 5 Replies
View Related
Mar 25, 2013
I want to insert multiple records on a database using a stored procedure.
View 11 Replies
View Related
Mar 5, 2009
I don't know how to control if a call to a PL function return errors.
I have this ksh:
executeFunction () {
echo "[executeFunction ]-> Phoebus DB started"
sqlplus -s /nolog <<!EOF!
CONNECT $USER/$PWD@$DB
@/sql/ejecutaFuncion.sql
EXIT;
!EOF!
}
executeFunction
And the code of "ejecutaFuncion.sql" is:
executeusers.startProcessing();
EXIT
How i can controle if the "startProcessing" function has any problems from the ksh?
View 1 Replies
View Related
Mar 24, 2008
I have created one function in the package.
function : Tier_wh
package : Order_DESC
Function defined as
function TIER_WH( message in out Xorder_desc)
...
...
end;
Xorder_desc is defined as
create or replace type Xorder_desc type
(order number(10),
location number(10),
wh varchar2(20)
);
how to execute this function which is defined in the package .
View 1 Replies
View Related
Dec 24, 2012
Execute sp1 param1...param6
Execute sp2 param1...param8
Execute sp3 param1...param4
All these stored procedures deals with insert/updated transactions . i need to create a new stored procedure to execute all this in a single stored procedure which will be something like
create procedure sp4(param1...param8)
as
begin
Execute sp1 param1...param6
rollback if any error
Execute sp2 param1...param8
rollback if any error
Execute sp3 param1...param4
rollback if any error
end;
View 6 Replies
View Related
Oct 2, 2013
I'm trying to find a simple way of getting around this. I have a PL/SQL procedure which loops through a list of values in a table. These values are the actual names of the functions I want it to run in sequence. The problem is I can get the string value each time but I need to store the return value of each function into a number variable e.g.
BEGIN
open all_tests;
fetch all_tests BULK COLLECT INTO test_tabl;
close all_tests;
for myindex IN 1..test_tabl.count LOOP
time_taken := test_source1 ==> this will work but how do I avoid hardcoding the name of the function to be executed and use the test_name value instead?? time_taken is declared as a number (ie the return value of each function is a number)
test_detail := test_tabl(myindex).test_name; ==> test_detail now contains the string name of the function
dbms_output.put_line('Test detail is ' || test_detail);
end loop;
View 2 Replies
View Related
Jul 6, 2012
This error is returned when executing a Function with the RETURN X%ROWTYPE is used.Here is the code.
CREATE OR REPLACE function FE_GET_addr (
p_PIDM IN addr.addr_PIDM%TYPE,
p_atyp1 IN VARCHAR2,
p_atyp2 IN VARCHAR2,
p_atyp3 IN VARCHAR2,
p_atyp4 IN VARCHAR2
[code]....
View 5 Replies
View Related
Feb 6, 2006
I have created a Package Body and Package Spec for a function to select a username and a password from a table and return the username.
The code i have created is this:-
CREATE OR REPLACE PACKAGE BODY USER_LOGIN
AS
FUNCTION user_select (USERNAME_IN VARCHAR2,
PASSWORD_IN VARCHAR2)
RETURN VARCHAR2 IS
USERNAME_OUT VARCHAR2(12);
BEGIN
[code]........
The package body and spec compiles successfully but i am having trouble when i execute this function. I am trying to do this :-
VARIABLE RETVAL VARCHAR2(12)
EXEC User_login.user_select('HELLO','HELLO',:RETVAL );
but i am getting the following error
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'USER_SELECT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
View 5 Replies
View Related
Oct 4, 2010
I am getting errors while executing the following block.
create TYPE c_Rec as object(a VARCHAR2(1), b NUMBER);
DECLARE
-- TYPE c_Rec as object(a VARCHAR2(1), b NUMBER);
TYPE c_collection IS TABLE OF c_Rec;
l_coll c_collection := c_collection();
BEGIN
[code]........
error
06530. 00000 - "Reference to uninitialized composite"
View 1 Replies
View Related
Feb 6, 2013
I'm trying to execute a procedure within a Declare/Begin/End statement and using variables as input parameters in my procedure but I keep on getting an Invalid SQL Statement Error. My code is below:
declare
START_dt VARCHAR2(30);
END_DT VARCHAR2(30);
begin
SELECT '01-APR-2011'
INTO END_DT
FROM DUAL;
[code]....
The table the procedure is pulling data from doesn't have proper date/time stamps but my procedure takes the varchar dates above and turns them into dates in the procedure so the input date parameters are left as just string characters.
View 6 Replies
View Related
Sep 29, 2010
How to export the procedure,views,function and packages in a database, by using Export commmand.
View 2 Replies
View Related
Jul 31, 2013
How to execute Sql Server procedure from Oracle.I have created a dblink and when calling directly to that procedure it is throwing error.
--creationg sqlserver procedure
create proc getRecords
as
print 'testing';
--creating db link
create database link remote connect by ss identified by ss using 'remote';
[code]....
I have found DBMS_HS_PASSTHROUGH package is capable of doing it but not sure how to do.
Nathan
View 11 Replies
View Related
