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;
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!
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.
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?
, 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
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?
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.
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.
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.
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??
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,
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)
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..
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
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'.
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
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
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 ?
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?
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?
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
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.
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.
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:
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.)
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,
