oracle

Preventing the oracle connection being get lost

How to prevent the connection to the oracle server being gets lost if it is kept for some ideal time ...

Oracle Forms: Why can't I add a new Type Spec?

I wish to add a Type Spec to an Oracle Form. Unfortunately, this option is disabled in the "New Program Unit" dialog: Does anyone know how to add a Type Spec to an Oracle Form? Version of Oracle Forms Builder: Forms [32 Bit] Version 10.1.2.0.2 (Production) ...

Oracle 7 ProC++ Libraries Help

I am modifying an old DLL which uses Oracle 7 ProC++ precompiled code (SQLLIB18.LIB) and don't have any documentation for this release. No joy back from Oracle either. Does anyone know what the numbers in the following compilation unit data represent? static const short sqlcud0[] = {8,4130,2,0,0,1,189,0,6,49,0,11,11,0,2,7,68,66,95,78,6...

How do I debug Oracle dynamic sql in sqlplus?

I have a PL/SQL statement that uses EXECUTE IMMEDIATE to execute a query. However, I'm having difficulty figuring out how to even get the text of the query that's being executed. I can't use dbms_output as the query is greater than 255 characters. Is there any way to make sqlplus echo the string that's passed in to EXECUTE IMMEDIATE? ...

Why is PLSQL slower than SQL*Plus

I have several Oracle queries that perform well when run through SQL*PLUS. However when they are executed as a part of a PL/SQL package, they take MUCH longer. Our DBA has watched these queries take 10 minutes through PLSQL and 10 seconds through SQL*Plus. Does anybody have any pointers on where to look for the misconfiguration? Clie...

DBMS debugger causes TOAD to hang

I can start the dbms debugger in Toad and use it normally but if I hit the 'halt execution' button or just hit the 'Run' button to reach the end of the function it causes TOAD to hang. Windows reports it as not responding and I have to kill it through the task manager. I have had this problem in Toad 9.7.2.5 and Toad 10 on two differen...

VBA/ADODB Run-Time Error: 3704

The following VBA subroutine will run most queries just fine. (ie: SELECT * FROM DUAL) Sub DisplayQuery(QueryString As String) Dim rs As New ADODB.Recordset Dim connStr As String connStr = _ "Provider=MSDAORA.1;" _ & "User ID=abc;Password=123;" _ & "Data Source=xxx/xxx;" out QueryString rs.Open QueryString, connStr, ...

Oracle Analytics Date Range

While researching Oracle Analytics, I came across this query: select an_id, a_date, min(a_date) over ( partition by an_id, trunc(a_date) order by a_date range between (2.5/24) preceding and (2.5/24) following ) mn, max(a_date) over ( partition by an_id, trunc(a_date) order by a...

Connection timeout for DriverManager getConnection

I am trying to connect to DB using the standard JDBC way connection = DriverManager.getConnection(url, username, password); Is there a maximum value of timeout on the connection, how long does a connection live, can I increase the value. I want in cases the connection to be open forever , is it a good idea. ...

how to enable SQL trace for a closed source app running on jboss and using ojdbc14.jar

Hello Hackers! I have a java app (deployed on Jboss/ WebLogic) that I have to use, it uses ojdbc14.jar to talk to oracle, it's is a neat app but the documentation sucks, a lot of magic happens behind the scenes. I need to trace all SQL calls. I have complete control on DB, App servers, DBA rights, admin rights everything. How do I do ...

Oracle same table name on different schema?

Is it possible to have same table name on different schema with different data on the tables within the one database? I think rather than create multiple database (that hold same table name) i should create multiple schema instead. Something like: Schema 1: table A, table B Schema 2: table B, table X PS: table B hold different da...

Migrating data between 2 databases in Oracle 9i

Hi, I am new to Oracle. Since we have rewritten an earlier application , we have to migrate the data from the earlier database in Oracle 9i to a new database , also in 9i, with totally different structures. The column names and types would be totally different. We need to map the tables and columns , try to export as much data as possib...

in Oracle RLS, when using a predicate to filter a table, then in a second predicate on another table, if i reference the first table, is the first predicate in effect or not?

I use the Oracle VPD facility(aka RLS, aka FGAC). Suppose that i use the predicate '(WHERE) id = 'XXXX'' on TableA and then i use the predicate '(WHERE) col in (SELECT col From TableA)' on TableB. Will TableA be filtered or not? Filtering is done only outside of predicate definitions, when you query the tables?. Will i need to make pr...

ORA-1555: snapshot too old: rollback segment number

Any idea about ORA-1555: snapshot too old: rollback segment number I am getting this error and nothing seems to be wrong. Please state under what conditions in may occur and how it can be avoided? ...

Querying record by date (01 may 1916) on Oracle using Java/EJB 2.x

I have an EJB 2.1 entity bean that queries a "date of birth" column in an Oracle table. The column in the database is of type DATE. The query works without problems except for one specific date: 01 may 1916. This has something to do with daylight savings. Oracle stores the date as: 5/1/1916 1:00:00 AM, while the time of all other dates...

SQL Agent Job - Connection may not be configured correctly or you may not have the right permissions on this connection?

I'm getting this error when running an SSIS package through SQL Agent Failed to acquire connection "ORACLE ADO.NET". Connection may not be configured correctly or you may not have the right permissions on this connection. When I log on as the SQL Agent User and run the ssis package directly it is fine. When I then execute it through the...

Including not found criteria in the result

I have this query select BUnit, value from myTable where BUnit in (555,556,557,558) and this values for Bunit are constructed dynamicaly, ie. a program builds queries on demand, and not always this will be the selected numbers. A possible query result would be Bunit value 556 10.2 558 5.18 How...

Return a 'DESC package' in PHP using OCI

Hi I am trying to get information on an Oracle package directly from PHP using OCI8: $sql = 'DESC my_package'; $stmt = oci_parse($conn, $sql); oci_execute($stmt); this returns: Warning: oci_execute() [function.oci-execute]: ORA-00900: invalid SQL statement in /oci8_test.php on line 16 I know the command works as I tried it in SQL...

Data Modeling for EAV

How are others using relational modeling tools to map a logical model or one in third normal form to a database that uses EAV? ...

Open Source Equivalents to Erwin

I am aware of CA/Erwin community edition but it is constrained to 25 objects. I have also tried modeling in Visio which was painful. Curious to learn of any open source equivalents that not only pictorially allow for relational modeling but can also generate DDL. ...