PL/SQL :: How To Write Proc That Checks Remote DB
Dec 4, 2012
I have a one critical remote PROD database that I want to check every 10 mins for its connectivity. If connection fails then an email & SMS is sent to me. My question is what is the best way to check if the remote database is up and running?
Can I use sqlplus system/manager@PROD? But sometimes this took so long and hanging. I want the fastest response time? How do I write a pl/sql proc check connection? That do something like the ff:
I created a table for my tnsname.ora entries.
cursor is c1 select dbname from tnsnames_tbl;
begin
connect system/manager@c1.dbname;
print c1.dbname || 'DB Connection OK';
exception
when others;
print c1.dbname || 'DB Connection Not OK';
end;
end;
View 9 Replies
ADVERTISEMENT
May 12, 2011
Having:
3 databases: CYR1,CYR2,UTF
Database links was established CYR2 -> CYR1, UTF -> CYR1
Function GETALL is on the CYR1 site
SQL> desc GETALL
Parameter Type Mode Default?
--------- -------- ---- --------
(RESULT) VARCHAR2
FMT VARCHAR2 IN
[code]....
My questions:
1) What could be reason that oracle would look so deeply in source of REMOTE procedure, especially as because remote procedure is in VALID, compiled state?!!
2) Could we classify such restrictions as a bug?
This is how I call the remote function:
declare
v varchar(300);
begin
v:= getall @orcl8.oegc.tgk11.com@csk ('%slu%',129);
dbms_output.put_line(v);
end;
This is "offending" statement in getall (basic static SELECT), that I had to throw away for call to getall to succeed:
select .... from ПРАВА_ПРОГ_ДЕТАЛИ ...
ПРАВА_ПРОГ_ДЕТАЛИ would occupy exactly 32 byte in UTF8 (2*15_cyrillic_chars + 2 underscores), but in CYR1, this table name is absolutely valid, only 17 chars long, because Cyrillic characters are represented by one byte!
View 10 Replies
View Related
Jan 5, 2013
I am trying to apply 14275572 this patch on our local database
DB : ORC it's on 11g (11.2.0.3.0)
OS : Redhat 5 (x86_64).
14275605 applied succesfully but 14275572 applying this patch getting below errors.
[Jan 5, 2013 7:37:36 PM] --------------------- Prerequisite for apply ---------------------
[Jan 5, 2013 7:37:36 PM] Running prerequisite checks...
[Jan 5, 2013 7:37:36 PM] Patch "14275572" is ignored as it is not a "Fusion Applications patch".
[Jan 5, 2013 7:37:36 PM] Check if patch "14275572" is a no-op patch.
[Jan 5, 2013 7:37:36 PM] None of the selected patches are no-op. patches..
[code]...
This patch requires some components to be installed in the home. Either the Oracle Home doesn't have the components or this patch is not suitable for this Oracle Home.OPatch failed with error code 39.
View 1 Replies
View Related
Apr 18, 2010
in oracle instalation when Product-Specific Prerequisite Checks window appears it show me theres a warning with checking network configration as shown in pic.theres a sites says that its not necessary to make it succeeded and its ok to leave it. should i leave this warning or it should be succeeded?
View 9 Replies
View Related
Aug 6, 2013
, I'd like to create a constraint (when creating a table) that checks to ensure that the 'Year' entered is less than or equal to the current year (based off SYSDATE). Per the code below, I keep getting the same error, "missing right parenthesis". I've spent more than an hour trying different ways to get this to work, but I've been failing miserably. I am using Oracle 11g Express.
CREATE TABLE TEST (Name VARCHAR2(7) PRIMARY KEY, Year NUMBER(4), CONSTRAINT TEST_YEAR_CK CHECK (Year <= (TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')))FROM DUAL);
*ERROR at line 4:ORA-00907: missing right parenthesis
View 7 Replies
View Related
Jul 20, 2012
i'm writing a job to export data on a weekly basis and archive those data in-case needed to be re-imported in future, its important that we able to import successfully if ever needed.
can impdp perform checks to see if dumps are valid ?how about old imp?if not, will it be safe enough to rely on the export log file? in another word is it safe to assume its all safe if there is no error or warning in the exp log?
View 7 Replies
View Related
Mar 1, 2013
Oracle 11.2.0.x
Reference: Accessing PL/SQL Stored Procedures using a Web Service (<i>Oracle® XML DB Developer's Guide</i>).
The feature works fine. Have tested a number of custom written PL/SQL procs this way - using it as a web service, and SoapUI and UTL_HTTP procedures as clients, calling the web service.
Question.
Can one support WSS (Web Services Security) with this feature?
I've added a WSSE to the SOAP envelope header when making the call - using standard password (no digest) and default addressing. The orawsv XDB servlet accepts the call, parses the SOAP envelope, and successfully executes the relevant PL/SQL procedure.
It however uses Basic Authentication (schema name and password). Not WSSE.
As the WSSE authentication data is part of the SOAP header (and not envelope body), the relevant PL/SQL procedure of course does/can not see the WSSE details. (also would not make sense ito how XDB abstracts orawsv as a web service interface and allows standard vanilla PL/SQL procedures and functions to serve as web service endpoints).
So if WSSE is to be supported, it would likely mean it needs to be supported in XDB itself. And that is outside my little area of Oracle expertise.
Not much on the net (lots about UTL_DBWS), and just a couple of basic orawsv supports notes on Metalink.
View 2 Replies
View Related
Oct 16, 2013
I have a requirement to generate the XML file from a pl/sql procedure. The methods which I have searched are either generating the XML from a single query or they are explicitly adding the nodes and elements. I need the method where I can dynamically append child nodes to the file.
View 12 Replies
View Related
Mar 24, 2010
How to make a call to a proc and immediately return without waiting for the called proc to end.
Nothing I try seems to do the trick...The calling program always has to wait.
View 6 Replies
View Related
Mar 31, 2010
i need to compile a proc program, say prog.pc.have oracle 10g in my system. Since i am new to proc programming, me on the steps to compile the proc program in oracle proc compiler.
View 2 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
Oct 30, 2012
I have a table TABLE1 which has following columns
proc_name
table_name
insert
update
delete.
The table contains a proc name, all tables used in that proc,operation is done on the tables. like sample data.
proc_name tab_name insert update del select
----------------------------------------------------------------------------------
Proc1 Table1 Y Y N Y
Proc1 Table2 y Y Y Y
I want to update this table throu running a generic procedure.Can it be possible to craete a procedure which parses through all procs of database and get above info and update TABLE1??
View 1 Replies
View Related
May 20, 2013
In my below code the procedures "total_score_proc" and "CopyInternalScores" are calling "score_proc" procedure 50 times
for different variable values.
Instead of calling the "score_proc" procedure 50 times.I want to hold the values in to collection , defining it in package and call that procedure only once.
how to implement "score_proc" using bulk collect.
CREATE OR REPLACE PACKAGE total_score_pkg
IS
PROCEDURE total_score_proc(pBUID IN STAGING_ORDER_DATA.BUID%TYPE,
OrderNum IN STAGING_ORDER_DATA.ORDER_NUM%TYPE,
[code]....
View 7 Replies
View Related
Jul 7, 2011
I found this thread, and recently i want to embedded oci in pro*c for distributed transaction reason.
can pro*c deal with distributed transaction? i want to use mts in pro*c .
View 14 Replies
View Related
May 24, 2005
I am using dynamic Pl/SQL with ProC and having problems with cursor. I've to execute stored procedure dynamically and get the result of select list into the cursor. The EXECUTE command is working fine but the FETCH gets failed with error "ORA-01002: fetch out of sequence". I've read that cursor variable can not be used with dynamic SQL.
Stored Procedure:
------------------
ROCEDURE open_mod_cur (
curs IN OUT cur_type,
module_id IN varchar2)
[Code].....
View 4 Replies
View Related
Aug 18, 2010
developing a procedure to create a partition every month passing in parms for the name and values, and also need to determine the appropriate tablespace that has adequate space available. Working within the constraints of the environment. I would like to pass in the table name as well. This code gives the general idea..
CREATE OR REPLACE PROCEDURE FS.CREATE_PART_test
declare
v_monyy varchar2;
v_yyyymm varchar2;
v_tblspc dba_data_files.tablespace_name%type;
[code]...
View 2 Replies
View Related
Nov 11, 2011
I am running a pro*c its working fine but my connection gets disconnect to my db server so i want to continue my proc if connection between my machine & server reestablish again weather it starts from start or from where it was stooped because of connection break
View 9 Replies
View Related
Aug 17, 2012
I have to copy all the procedures in database to local folder and extension( or file type) of each procedure is ".proc".
I did tried with dbms_metadata but as there are 300 procedures it consuming time & I want separate file for each one.
select
dbms_metadata.GET_DDL('PROCEDURE',u.object_name), u.object_name
from
user_objects u
where
object_type = 'PROCEDURE'
AND object_name in( 'P1');
View 2 Replies
View Related
Aug 25, 2008
I Want to declare some host variables with global scope. Like this C variable:
MyFile.h
...
char str_var[20];
...
But i can't put SQL sentences in a include file. And if i do this:
MyFile.pc
...
EXEC SQL BEGIN DECLARE SECTION;
char str_var_h[20];
EXEC SQL END DECLARE SECTION;
...
... I can't see this variable from others sources, although I declare it like 'extern'.
View 1 Replies
View Related
Jun 1, 2011
we have an application currntly running on an HP UX system that uses Oracle 9i database. the pro*C codes work fine with Oracle 9i, on the older system. however now we are migrating it to LINUX system (ORACLE 10g). in the new system we are facing issues with fetch statement
here is how we have the
the cursor statement:
EXEC SQL DECLARE diff_cns_list CURSOR FOR
select PREV.CNS_CODE,
PREV.CNS_DESCRIPTION,
[code]....
the code runs fine in the old operating system (HP UX) where oracle 9i was used. but fails in Oracle 10g(OS-LINUX).
the probable reason that we found out is that: the fetch statement returns zero rows, that is the reason why this error is being displayed. but Oracle 9i seemed to work out well with this.
we tried reducing the cursor conditions, where it fetches 3 rows. this is when is the fetch statement works fine.
the functionality of the code is that it should work fine even with no rows selected. Is there a way we can modfy the code to work with zero rows as well.
the error we are getting is : ORA-01002: fetch out of sequence
==========================
as per what is given in Oracle sites:
1) Fetching from a cursor after the last row has been retrieved and the ORA-1403 error returned. 2) If the cursor has been opened with the FOR UPDATE clause, fetching after a COMMIT has been issued will return the error. 3) Rebinding any placeholders in the SQL statement, then issuing a fetch before reexecuting the statement.
========================
we have checked:
1>this is not the case and ORA-1403 not returned
2>No update statements involved , there is a insert statement which inserts data in this table
3>i am not clear with this one.
View 1 Replies
View Related
Sep 19, 2012
create or replace package body test_exp_pkg as
procedure l_test_exp (errcode out Varchar2, errm out varchar2)
as
l_rec zt574%rowtype;
begin
[code]]....
output:
---------
anonymous block completed
in package init
caught in l_test_exp-1422ORA-01422: exact fetch returns more than requested number of rows
caught in l_test_exp errcode and errm set to -1422sqcORA-01422: exact fetch returns more than requested number of rows
caught in test_exp1User-Defined Exception
caught in others errcode and errm
View 8 Replies
View Related
Oct 1, 2013
I have a main procedure in oracle which invokes many procedures inside it. These internal procedures also calls functions and procedures inside it.This continues to many levels.
For ex:
Proc A
call c
call d
end............
[code]...
This loop goes on and on . I want to find the names of all procedures invoked at run time when main api is executed. Is it possible to find all of them using toad ? Is there any tool for doing this ?
View 16 Replies
View Related
Feb 4, 2013
I am working on oracle 11g...I have one normal insert proc
CREATE OR REPLACE PROCEDURE test2
AS
BEGIN
INSERT INTO first_table
(citiversion, financialcollectionid,
dataitemid, dataitemvalue,
[code]....
I am processing 1 lakh rows.tell me the reason why bulk collect is taking more time. ? According to my knowledge it should take less time. do i need to check any parameter?
View 5 Replies
View Related
May 10, 2011
I had never done this before, basically I need to get the result of the SQL query which had three columns and pass into thesend _mail proc, should I build a variable to hold the 3 value and send mail from it?
below is my send_mail proc
Quote:
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
[code]...
and below is my sql query
Quote:
select a.id, b.hr_info, c.details
from contact a, employees b, grid c
where a.id=b.id
and b.hc=c.hc
View 1 Replies
View Related
Aug 13, 2010
I am trying to compile a pro c file. Rest of the pro c file is compiling fine. But one particular file is showing following error.
INTERNAL ERROR: Failed Assertion [Code=40706]
View 11 Replies
View Related
Feb 22, 2011
I am trying to execute a STORE PROCEDURE from SQL*PLUS with no success:
SQL> execute PACKAGE.PROC(201011,'144792');
BEGIN PACKAGE.PROC(201011,'144792'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'PROC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
In fact, when i do: desc PACKAGENAME . I see that the procedure is waiting for 3 parameters and one of them is a REF CURSOR type:
SQL> desc PACKAGENAME
PROCEDURE PROCEDURENAME
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PWEEK NUMBER IN
PCLIENT VARCHAR2 IN
CRESULTS REF CURSOR IN/OUT
After searching a bit, i try the following:
SQL> execute PACKAGE.NAME(201011,'144792','CRESULTS
'=>:C1);
SP2-0552: Bind variable "C1" not declared.
SQL>
This is a preview of the PACKAGE header:
CREATE OR REPLACE PACKAGE PACKAGENAME
AUTHID CURRENT_USER
AS
--
TYPE CurTyp_Supp IS REF CURSOR;
--
TYPE TabTyp_Supp IS TABLE OF VARCHAR2 (10 BYTE);
--
TYPE ObjTyp_Prmt IS OBJECT (p_schemaname VARCHAR, p_filename VARCHAR);
--
PROCEDURE PROC(pWEEK NUMBER,
pCLIENT VARCHAR,
cResults IN OUT CurTyp_Supp);
This what the PACKAGE BODY looks like:
CREATE OR REPLACE PACKAGE BODY PACKAGENAME
IS
PROCEDURE PROC (pWEEK NUMBER,
pCLIENT VARCHAR,
cResults IN OUT CurTyp_Supp)
QUESTION:
HOW DO I MANAGE TO EXECUTE THIS PROCEDURE FROM SQL*PLUS
View 6 Replies
View Related
Jan 24, 2011
I have to write a PL/SQL procedure, which is supposed to take an array of strings as input. This array will have simple strings as elements, like
'000887S','000780S'.
Now I have a query in the procedure, which will return a row, for each of the array elements. For example:
SELECT
su.EMPLOYEE_ID,su.FIRST_NAME,
su.LAST_NAME
FROM
USERS su,
[code]......
In the place of the '?' in the above query, the array elements have to be passed. So we will get one row from the above query for each array element.
Now we either have to loop through the array elements to fetch the result set for the above query for each array element, or we can use some other method too. Our objective is to collect all the rows of the above query for each array element as a table data and this procedure has to return this table set.
what will be the best way to pass such a set of data to the proc and best way for the proc to return this result set. Like we can use arrays, table type data,ref cursors, etc.
View 1 Replies
View Related
Nov 20, 2012
I want to update my table base on changes in a column of a table from remote database automatically. I just want to pick some updated column then update to my own. i have made a trigger but failed.
CREATE OR REPLACE
TRIGGER TG_UPD_ID
after update OF STUD_ID on STUDENT@SCHOOLDB
for each row
begin
update MARKS set ID_ST = :new.STUD_ID where STUDID_STUDENT = :old.IDS;
end;
Error report:
ORA-02021: DDL operations are not allowed on a remote database
02021. 00000 - "DDL operations are not allowed on a remote database"
*Cause: An attempt was made to use a DDL operation on a remote database.
For example, "CREATE TABLE tablename@remotedbname ...".
*Action: To alter the remote database structure, you must connect to the remote database with the appropriate privileges.
View 11 Replies
View Related
Nov 19, 2008
We have eight servers. One of them is for centeral management. The other seven is for development and test databases. Every night, we start a process which exports database schemas (without data) into the centeral server. An example of the process we run is just like below:
CODEcd /data1/backup
mknod exp_pipe_oracle1 p
compress<exp_pipe_oracle1>dev.dmp.Z&
exp user/****@dev file=exp_pipe_oracle1 owner=schema1,schema2,schema3 statistics=none rows=n log=dev.log
As you can see, we use @dev to connect remote database. But dump file is created in local (centeral) server. We are planning to use datapump instead of classic export in future. But I couldn't find a solution, that could take a database export in a remote server and create dump file in local. I have looked at NETWORK_LINK parameter; but it doesn't seem to work for our case.
Is it possible to backup a remote database with datapump and make it create dumpfile in local? (Of course we can use some solutions such as NFS, but we really not prefer this; if datapump has ability to remotly backup.)
View 5 Replies
View Related
Feb 23, 2012
We have access to a remote Oracle database in Germany and need to insert selected data to our local Oracle database. Our problem is that we have to do several joins (7 tables) on the remote database as well as using one where clause (always the same: P.T_LIEF_LFNT_1='12803193').
Unfortunately we do not have rights to create a view on the remote database. Is there another way to send the entire query to the remote database for processing? Also, the query returns approximately 34,000 rows.
Here is our current query:
INSERT INTO PRIMUS(PARTNO,
SORT_FORMAT,
TP_WORKSPACE,
[Code].....
View 3 Replies
View Related