Update on rows selected by a Cursor in SQL
In oracle if I define a cursor and during the same time if there is an update on the Table then what happens to my Cursor? Does it become invalid or Updates does not matter? ...
oracle
How do NULL values affect performance in a database search?
Hi In our product we have a generic search engine, and trying to optimze the search performance. A lot of the tables used in the queries allow null values. Should we redesign our table to disallow null values for optimization or not? Our product runs on both Oracle and MS SQL Server. ...
Can I used encrypted connections with Oracle Instant Client and ODP.NET?
We're currently using a fully installed oracle client and ado.net via odbc to establish an sql*net encrypted connection to an oracle database. My question is if we can achieve encrypted connections with the instant client and odp.net instead of full client and odbc, too? Thanks in advance! ...
How to determine ORACLE_HOME from PL/SQL?
Is it possible to determine where Oracle is installed using pure PL/SQL? Use case: In order to call an external C library, I need to know its exact path (for the create library call). My DLL will reside in Oracle's BIN directory, but I can't hard-code the path of the DB installation in my scripts... ...
Help me understand the difference between CLOBs and BLOBs in Oracle
This is mainly just a "check my understanding" type of question. Here's my understanding of CLOBs and BLOBs as they work in Oracle: CLOBs are for text like XML, JSON, etc. You should not assume what encoding the database will store it as (at least in an application) as it will be converted to whatever encoding the database was config...
Delphi TOracleQuery Bind Variable
I am trying to call a query from Delphi 6 with bind variables, using the DOA component set's TOracleQuery object. I have 6 variable, 1 of which is a string (VarChar2 in Oracle lingo). I have tried calling query.DeclareVariable with the otVarchar2 enumeration, which intuitively makes sense, the otString enumeration (as the documentation r...
tomcat oracle (9.2i) oci pool connection server.xml and usage syntax
I've been searching for a good example of how to configure a Oracle OCI pool connection in tomcat's server.xml (defining a JNDI resource) and subsequently how to use it. Here's what I have now in conf\server.xml: <Context docBase="ips" path="/ips" reloadable="true"> <Resource name="jdbc/IPS_DB" auth="Container" ...
Display contents of Oracle AQ SYS.AQ$_JMS_TEXT_MESSAGE
I have an application that uses JMS op top of Oracle advanced queuing. I would like to do a query on the queue table that shows the content of the message (which in my case is XML). So when I do a 'select user_data from [queue_table]' I get 'AQ SYS.AQ$_JMS_TEXT_MESSAGE' as a response. Is there a function so that the contents of this me...
Oracle: Select From Record Datatype
I have a function that returns a record datatype (2 fields: ID and Name). How can I get at the data from a select statement? Specifically, I am trying using an OracleCommand object attempting to get the object into my C# code. I initially tried ... CALL FUNCTION_NAME() INTO :loRetVal ... but I get a data type error for whatever type ...
Is it possible to use a database link between an oracle database and a postgresql database on different physical servers
I've trying to streamline an intranet application where some of the data is stored locally on the postgresql database of the intranet server and other related data is stored in our enterprise oracle 10g2r2 database. What I would like to do is create a view on either database box wouldn't really matter that combines for simplicity sake...
Oracle unique constraint with where clause
Hi All, I have an Oracle database table that I want to apply a unique constraint on. The issue is that I only want to apply the constraint if a column in that table is null, ie. if a row does not have a deleted_date column, then apply the constraint, otherwise ignore it. This will allow for soft deleting records and ignoring constrain...
Unit testing mfc application backed with oracle?
App:MFC backed by oracle Unit testing framework:googletest I am going to unit testing the code that will be added here on to the project.Unit testing framework is googletest. I am new to unit testing. Is googletest is ok for my application how can I get started? ...
how will you take only stored procedure backup in oracle 10g?
how will you take only stored procedure backup in oracle 10g? ...
Can you please help me in Oracle for writing the table contents in to a file using UTL functions
While writing the SQL procedure for creating a CSV file from the contents of the table, I am stuck at the error below: ERROR at line 1: ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 488 ORA-29283: invalid file operation ORA-06512: at "DSI_DPIPE.BUSSHOURS", line 28 ORA-06512: at "DSI_DPIPE.BUSSHOURS", line 55 ORA-0...
Can you help me in Oracle to insert only time (hh:mi) sent as VARCHAR2 parameter to a procedure into a DATE field?
While writing a procedure, I am stuck at how to insert a VARCHAR2 (which holds only hours and minutes) in a DATE column (which has to store my time for future reference). ...
XAConnection performance in Oracle (10g)
In our project, we use the Oracle XA Connection pool. Only a small subset of the queries(transactions) are distributed. Rest are quite straightforward single database modification. I would like to know if there is a performance difference in using XAConnections Vs the normal ones. We use websphere v6.1 as the server. If XAConn, is ...
Extracting rows from a DB including depedent rows
I'd like to generate insert-strings for a row in my Oracle database including all its dependent rows in other tables (and their dependent rows). Example: CREATE TABLE a ( a_id number PRIMARY KEY, name varchar2(100) ); CREATE TABLE b ( b_id number PRIMARY KEY, a_id number REFERENCES a(a_id) ); When I extract the row from a wit...
at which section set commands in oracle procedures can be used (declaration or executable)
at which section we can write the set cmds(i.e like set pagesize 250) in oracle procedures ...
Can you help me write a procedure in Oracle to spool data from a table to a CSV file?
I am writing a procedure to create a CSV file with the data in an Oracle table. I used "spool filename;" but an error is coming. Can I use spool in PL/SQL? ...
MODIFY COLUMN in oracle - How to check if a column is nullable before setting to nullable?
Hello all, I'm trying to fill in for a colleague in doing some Oracle work, and ran into a snag. In attempting to write a script to modify a column to nullable, I ran into the lovely ORA-01451 error: ORA-01451: column to be modified to NULL cannot be modified to NULL This is happening because the column is already NULL. We have sev...