Hello, I need to create a table with the following structure: calendar week; week start date; week end date which contains all weeks beginning in 2007 until the current week. The special thing is, that when an end of month falls within a week, the week is cut in two slices - one record that has a start date that is the beginning of ...
When I try the following: declare var_type VARCHAR2(10); begin var_type := 'B'; select case var_type when 'B' then 'Beans' when 'L' then 'Legumes' end from tableOfBeans ; end; I get an error saying ORA-06550: line 4, column 1: PLS-00428: an INTO clause is expected in this SELECT sta...
In my project, I am using Oracle Database and SubSonic for DAL. I have a problem with SubSonic and Oracle Schema, that is: When developing, I used a schema DEV in Oracle Database and generate DAL using SubSonic. After that when release to customer, he used a new schema TEST in Oracle Database and changed the connection string in app.co...
Hello I am trying to display the constraints in one of my tables but for some reason I get the message no rows selected. Noted below is the table I have created. Create table Teams ( TeamID varCHAR2(4) constraint Teams_TeamID_PK Primary Key, TeamName VARCHAR2(40) ); This is the code I am using to show my constraints. SELECT c...
I am assigned the task by manager to create a DDL script. One of the fields that is required is specified as PIC S9 with length 16, but I don't know whether PIC S9 is supported by Oracle. The DDL script need to be compatible with oracle 10g. What is this PIC S9 data type? Is this supported by Oracle? If yes, can i use the following?...
Oracle's default date format is YYYY-MM-DD. Which means if I do: select some_date from some_table ...I lose the time portion of my date. Yes, I know you can "fix" this with: alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; But seriously, why isn't the above the default? Especially in a DBMS where the two primary time...
what is the equivaltent of PIC S9 with lenght 16 in oracle? I want it for storing timestamp ...
I encountered this question in an interview and had no clue how to answer: There is a table which has a index on a column, and you query: select * from table_name where column_having_index="some value"; The query takes too long, and you find out that the index is not being used. If you think the performance of the query will be bett...
Is it possible to redirect a TNS declaration to another one? For my current project, I require "flipping" from server to server in certain circumstances. To do this I use 3 TNS entries. One to hold the TNS name that my application will connect to: # application access to DB DB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST ...
I have a java web application which needs to upload files and we want to store these files on the filesystem rather than in the database. The database will store the document metadata only. The question is whether to just store the path as a string in Oracle, or as a BFILE locator? Can a BFILE locator point to a location which is on a d...
To optimize SELECT queries, I run them both with and without an index and measure the difference. I run a bunch of different similar queries and try to select different data to make sure that caching doesn't throw off the results. However, on very large tables, indexes take a really long time to create, and I have several different ide...
I've notice that in Oracle, the query SELECT COUNT(*) FROM sometable; is very slow for large tables. It seems like the database it actually going through every row and incrementing a counter one at a time. I would think that there would be a counter somewhere in the table how many rows that table has. So if I want to check the numb...
Hi, there, I just installed Oracle, and I am working with SQL*Plus because the GUI tool, SQL Developer, won't work. I need to run some scripts, and I found that after starting SQL*Plus I couldn't navigate from a session to another directory. The instructions that I did find after I searched on the internet all talk about a gui interfac...
I am a code developer, not a DBA, and I tend to get lost with loooong SQL queries, when I use many joins (10 joins is pretty common for me). I would like to find a Visual SQL Query Builder (free if possible) that could connect to Oracle and see all the tables there, so I would only select visually my keys that link the Joins, and selec...
How to compare groups of tuples in sql: consider the following example: TABLE T1 -------- GROUP VALUE ----- ----- A FOO A BAR X HHH X ZOO TABLE T2 -------- GROUP VALUE ----- ----- B ZOO C FOO C BAR I want to write an sql query which compares the groups of values...
I am using an oracle table and have created a unique constraint over four columns. Can these columns within the constraint have NULL in them? ...
Edit: Please answer one of the two answers I ask. I know there are other options that would be better in a different case. These other potential options (partitioning the table, running as one large delete statement w/o committing in batches, etc) are NOT options in my case due to things outside my control. I have several very large ...
I have pairs of tables in the format TABLE and TABLE_TWIN now TABLE is the main table with lots of data TABLE_TWIN is a table with the exact same fields with a little data (different data) Now I would like to copy all rows from TABLE_TWIN to TABLE using a stored procedure. I have many such tables and could like the stored procedure t...
I am trying to write to an Oracle clob field a value over 4000 characters. This seams to be a common issue but non of the solutions seem to work. So I pray for help from here. Down and dirty info: Using Oracle 9.2.0.8.0 Hibernate3 implementing pojo's with annotations Tomcat 6.0.16 Oracle 10.2.x drivers C3P0 connction pool provider In...
Lets state the conditions where sqlcxt() can cause segmentation fault, I am woking on unix, using ProC for database connections to Oracle database. My program crashes and the core file shows that the crash is due to the sqlcxt() function A loadobject was found with an unexpected checksum value. See `help core mismatch' for details, and...