I have a procedure like this: procedure foo( p_field_name in varchar2, p_record in table%rowtype ) is v_value varchar2( 100 ); begin execute immediate 'select p_record.' || p_field_name || ' from dual' into v_value; end Oracle complains that "p_record"."my_field_name" is an invalid identifier, which leads me to believe that th...
In MS SQL Server it is possible to create a foreign key with ON UPDATE CASCADE option, so whenever you update one of the columns in the primary key, the foreign keys in other tables will also be update by the DBMS. So, how to do it in Oracle? ...
Suppose I have a custom file format, which can be analogous to N tables. Let's pick 3. I could transform the file, writing a custom load wrapper to fill 3 database tables. But suppose for space and resource constraints, I can't store all of this in the tablespace. Can I use Oracle Preprocessor for External Tables to transform the custo...
My team maintains an Oracle database that is approx. 200GB in size. All of the data (tables, indexes, etc) lives inside a single 'USERS' tablespace. Is this a bad idea? What benefits are there to having multiple tablespaces, and under what circumstances would I want to add more to my database? Thanks! ...
I need to pass an XML file as an input parameter to a stored procedure. The procedure will read the XML file and insert the data into a table. How can I perform the XML file read inside the stored procedure? Example code or reference links appreciated. Thanks in advance ...
Well.. the question is descriptive enough I guess. What I am looking for is an exact equivalent of the below MySQL command in oracle- mysqldump --xml --no-data -u[username] -p[pass] [db_instance] > [someXMLfile] Where on a linux box do I have to run the oracle command? Straight inside the shell would do? ...
Why Oracle is not using Bankers rule (the rounding method)? ...
I am trying to put together a deployment package for a psolution which needs to configure an Oracle database schema. The schema contains most of the Oracle object types in one form or another. I want to run the deployment entirely from the deployment application, and have created a custom installation task to perform most of the action...
I am new to Oracle (though familiar with SQL) and have to write a fairly complex query where a value derived from the current date is used many times. Rather than calculate the value each time, it would seem obvious to declare a constant for the purpose. However, when I then try to use my DateIndex constant in the subsequent SELECT sta...
I have an oracle server which is dropping Sessions (Connections) frequently, with just about 5 minutes idle time it is dropping the connection. Resource limit is not set on this server, and the IDLE_TIME for the User profile is set to unlimited, the strange thing is this happens even when we create a new instance, and that too behaves in...
My understanding of SQL*Loader's conventional path is that it simply generates INSERT statements and sends them to the database. Are there any performance benefits to using this rather than just generating the SQL statements programmatically and executing them against the database? ...
I have a table with some denormalized precalculated columns that are maintained by a trigger. The data sometimes is corrupted and the process fails but in these cases I need just ignore the exceptions and continue because it is not important to catch the error. How can I write the exception clause to just go on without raising any error...
I'm embarking on a project which will likely cross several million rows in the short future, so I am researching the database I use as that is sure to prove an issue. From what I have read, SQL in all its incarnations has issues once you get to the 2,000,000 rows issue for a table. Is there a good database recommended for these big size ...
I'm using hibernate to persist an entity to my database. For the moment it's a derby DB, but I will be moving it over to an oracle DB soon. in my classname.hbm.xml I have the id defined as such: <id name="id" type="long"> <column name="ID"/> <generator class="increment"/> </id> How do I set the starting value for the id? ...
What are some tools (commercial or OSS) that provide a GUI-based mechanism for creating schema upgrade scripts? To be clear, here are the tool responsibilities: Obtain connection to recent schema version (called "source"). Obtain connection to previous schema version (called "target"). Compare all schema objects between source and targ...
I'm working on maintaining some Oracle sql statements written by someone else, and all over the place I keep seeing this same pattern repeated in lots of statements and pl/sql blocks: select DECODE(NVL(t1.some_column,'~'),'~',t2.some_column,t1.some_column) some_column from t1, t2 where ... Now, isn't this identical to this much simple...
Hello all. I'm using the Oracle 10g Database. i'm trying to figure out how to write a simple sql query to: find the missing numbers in a table between say 86002895 and 86005197 (inclusive), There are 1955 rows between 86002895 and 86005197. Ex: Current Scenario : table_1 : tracking_no | id_value 86002895 | 10 86002896 | 10 86002899 |...
Does anyone know how to get triggers created over JDBC. It appears that the problem is to do with the semicolon. Any feedback much appreciated. The following SQL works when run on the database, but not when run using the following Java code: Connection c=null; Statement s=null; try { c=dataSource.getConnection(); ...
i am running the following query SELECT ora_database_name AS SERVER_NAME,'CNTODSSTG' AS DB_NAME,p.owner,p.object_name,s.text FROM all_procedures p,all_source s WHERE p.owner<>'SYS' AND p.owner<>'SYSTEM' AND s.TYPE='PROCEDURE' AND p.object_name=s.name GROUP BY p.owner,p.object_name,s.text s.text is the coding inside the procedure. Bu...
Currently am using postgres with diff schemas for diff modules.Now i am planning to switch towards oracle.But i heard that in oracle schema is not there..Is it true..if it is like that how can i migrate? ...