I have a field with a date/time value like this: 2009-11-17 18:40:05 It's in UTC. In the query how can I convert this to EST? I'm trying something like this but it throws an error. // datetime is the field name SELECT FROM_TZ(TIMESTAMP TO_DATE(datetime, 'yyyy-mm-dd hh24miss'), 'EST') AS DT FROM db_name ...
I have a select query in MySQL shown below: This query executes and produces results in the form of a table Current | Past1 | Past2 | Past 3 | Past4 200 600 800 000 88 I would like to transpose these results to get information in the form: Therefore I would like the reults to be (transposed) Current 2...
I have 3 tables, one which represents a "supertype", with an ID column. Two other tables are each subtypes, with an ID column that is a foreign key to the supertype table, plus a subtype-specific column. I want a query that returns all the data, as well as a column I can use as a discriminator that tells me what table the row came from...
I have a select query in MySQL shown below: This query executes and produces results in the form of a table Current | Past1 | Past2 | Past 3 | Past4 200 600 800 000 88 ----------------------------------------------- I would like to transpose these results to get information in the form: Therefore I would l...
Each time I run my application it creates a connection to the Oracle database (pretty typical). The problem I'm running into is that, when I kill the debugger to stop the application the code to close the database connection never gets called. Oracle doesn't seem to realize that those connections are now unimportant and it tries to kee...
I want to select *, and not have to type out all individual columns, but I also want to include a custom column with a case statement. I tried the following: select *, (case when PRI_VAL = 1 then 'High' when PRI_VAL = 2 then 'Med' when PRI_VAL = 3 then 'Low' end) as PRIORITY from MYTABLE; But...
Is this possible? ...
I have a table T with columns X, Y and Z. I need to retrieve non-group by column X value of the first row of every group, group by column Y value, and MIN of column Z value in a SQL single query. Please could you help me out. ...
I am trying to set up a synchronization model to sync my consolidated Oracle database with a remote SQL Anywhere database. I have a couple Views on the consolidated Oracle database that I want to pull into the Sql Anywhere database as tables. I want to pull them over as they touch 4-5 tables (gotta love good normalization) and only ha...
I have an Oracle script that looks like the following: variable L_kSite number; variable L_kPage number; exec SomeStoredProcedureThatReturnsASite( :L_kSite ); exec SomeStoredProcedureThatAddsAPageToTheSite( :L_kSite, :L_kPage ); update SiteToPageLinkingTable set HomePage = 1 where kSite = :L_kSite and kPage = :L_kPage; Supposedly th...
I´m a little bit frustrated with my Java environment that didn´t allow me to call the method OraclePreparedStatement#setPlsqlIndexTable ... but i think i should write the code before... String plSqlBody = "some pl/sql procedure call" /* * The PL/SQL procedure parameter is here of type * TYPE t_date_table IS TABLE OF DATE INDEX BY PLS_...
hi all. the following text is an excerpt of oracle documentation Oracle® Database PL/SQL Language Reference 11g Release 1 (11.1) : Unhandled exceptions can also affect subprograms. If you exit a subprogram successfully, PL/SQL assigns values to OUT parameters. However, if you exit with an unhandled exception, PL/SQL does n...
Does impdp always truncate the table before it loads the data even if I don't recreate the meta data? If it does, is there an option to tell it to append the data instead? ...
hi all. I use TOAD to do my PL/SQL development. In TOAD when i type a procedure name and press f4, I can see this procedure's source code. I think TOAD get the source code from v$sqltext view. To confirm my thought, I wrote a query: select * from v$sqltext but when I execute the upper query, Oracle give me an error: ORA-00942: tab...
Using Oracle 10g, I need to rename a bunch of FK constraints which all end in LITE to include an FK prefix. My thinking was (I've ensured all names are short enough to accommodate the prefix): DECLARE v_name VARCHAR2(30 BYTE); v_new_name VARCHAR2(30 BYTE); CURSOR c1 is select CONSTRAINT name from user_constraints where constraint...
Hi, I have a query with several aggregate functions and then a few grouped columns. I want to take one of the grouped columns out of the group and perform some sort of aggregate "concatenating" of all the VARCHAR values it has. (Ideally in a new carriage separated list). Here is my query and I note where I'd like to do this: SELECT r...
I have dmp file that was created by EXP utility. The source database has table compression enabled. How can I disable compression while importing dmp file. The destination database does not have this future enabled. I can not find any switches on IMP utility for this purpose. imp u/p@sid file=test.dmp LOG=test.log IGNORE=Y TABLES...
How can i generate a unique key of length at most 12 character with these attributes: Name, Father's name, Mother's name, Date of birth, Place of birth. Thanks in advance :) ...
I have an Oracle database where I'm trying to select a user field from the earliest row (based on a time field) where certain conditions are met, and I don't know how to do it. Here's the gist of my query: SELECT id, CASE WHEN value = 'xyz' THEN 'Pending' ELSE 'Not Pending' END AS status, ti...
I want to convert the follow string to date: 2004-09-30 23:53:48,140000000 I tried: to_date('#', 'YYYY-MM-DD HH24:MI:SS,FF9') But PL/SQL keep throwing this error: ORA-01821: date format not recognized. FF9 is incorrect for Oracle, any suggestion? ...