Or are there any oracle data dictionary to tell me which tables are being referenced in (materalised) views? I wish to find out what tables references are used in a DML. I prefer to use an oracle package as it can be self contained in the database. But other suggestions welcome. Open source very welcome. Here is the link to the synt...
hi! i have 3 tables A,B and C. table A has column employee_name,id table B is the main table and has columns id,os version. table c has the columns id,package id and p_version. I want to query the count of employee_name where the id of table a and c are matched with id of table b(which is the main table). I should also get the names...
I have an Oracle function returning record defined in the package, so one can do: select a,b,c FROM my_function(...); Calling this oracle function from .NET is as simple as executing normal sql query. Unfortunately the function has to do updates now and when it is called like this Oracle complains that updates are not allowed within ...
i using oracle 10g database, i installed two instance in my machine(dev and dev10g), i using query "select instance_name,status from v$instance" this query return one instance information but i need two (dev and dev10g) instance information, how i write a query. it is possible without using dblink. pls help me. ...
I have a few sql scripts that I need to run via SQL*Plus. These scripts connect several times as different users with a connect user_01/pass_01@db_01. Now, each time the script does such a connect, it confirms the successful connection with a connected. This is distracting and I want to turn it off. I can achieve what I want with a set...
Hi, I'm using Workshop for Weblogic and I'm testing an XQuery Transformation. Both MFL and XSD are valid. But the XQuery doesn't seem to work... it gives me this error: Error occurred while executing XQuery: loader constraint violation: when resolving method "javax.xml.stream.XMLInputFactory.createXMLStreamReader(Ljava/io/Reader;)Ljava...
How can I manage multiple versions of a database easily? I have some data (as views as selects for data originating in tables from other schemas), which other database may reference using various means including database synonyms & links. I wish to provide a sort of interface/guarantee in-case future for applications/databases which u...
I have a hierarchical table on Oracle pl/sql. something like: create table hierarchical ( id integer primary key, parent_id references hierarchical , name varchar(100)); I need to create a procedure to alter that table so I get a new field that tells, for each node, if it has any children or not...
I am currently developing a function that is meant to execute dynamically created SQL statements. This is done by concatenating the columns and fetching them via cursors. The problem is that when there is a function with a comma between its arguments, the concat concatenates the contents of the functions inclusive. Is it possible to ski...
I would like to get the last date of records modified. Here is a sample simple SELECT: SELECT t01.name, t01.last_upd date1, t02.last_upd date2, t03.last_upd date3, 'maxof123' maxdate FROM s_org_ext t01, s_org_ext_x t02, s_addr_org t03 WHERE t02.par_row_id(+)= t01.row_id and t03.row_id(+)= t01.pr_addr_id and ...
hey, i wrote a java app that communicates and stores data in oracle; my question is how do i clear entered information from individual columns in oracle? for example, say i have a following table: create table example (id integer, name varchar(50), surname varchar(50)); and it contains current information, how do i then clear in...
I am trying to export the source for a java object from an oracle database using the following code. DECLARE blob1 BLOB; BEGIN DBMS_LOB.CREATETEMPORARY(blob1, FALSE); DBMS_JAVA.EXPORT_SOURCE('OBJECTNAME', 'SCHEMANAME', blob1); END; Whenever I try to run it I get an oracle.aurora.rdbms.ModifyPermissionException exception even t...
I have an oracle .dmp file, and no access to a local oracle install.. Is there any way I can read the data or open it in another program to see what data is in this file? ...
Hi, I'm planing to build some database project. One of the tables have a lot of attributes. My question is: What is better, to divide the the class into 2 separate tables or put all of them into one table. below is an example create table User { id, name, surname,... show_name, show_photos, ...) or create table User { id, name, sur...
What would be equivalent of Number(4) of Oracle database to MySQL Datatype ? ...
Hi, I'm trying to do an Insert operation using iBatis. INSERT INTO SCHEMA.TABLE (FIELD1, FIELD2, FIELD3) VALUES (#field1#, (SELECT ANOTHER_FIELD FROM SCHEMA.TABLE WHERE FIELD4= #field2#), #field2#) ...
The documentation states: "Precision can range from 1 to 38. Scale can range from -84 to 127". How can the scale be larger than the precision? Shouldn't the Scale range from -38 to 38? ...
Hi, all. I have a series of extremely similar queries that I run against a table of 1.4 billion records (with indexes), the only problem is that at least 10% of those queries take > 100x more time to execute than others. I ran an explain plan and noticed that the for the fast queries (roughly 90%) Oracle is using an index range scan (on...
Does anyone have experience with database links between 10g and 11g? Is this a supported setup/operation? Is it possible? Are there any problems? Caveats? ...
How do I calculate number of years since product was made (rounded to 1 decimal point) for products that were made less than five years ago? Thank you. ...