What are the mandatory fields in the following tables that I need to populate in order that they can pass validation and can be imported in the respective base tables PO_HEADERS_ALL (Purchasing documents open interface) PO_LINES_ALL (Purchasing documents open interface) PO_LINE_LOCATIONS_ALL (Purchasing documents open interface) PO_...
In the answer to a previous post (Tuning Rows-to-Cols Query), I learned how to more efficiently construct a row-to-cols query which allows for filtering by date. However, I now need to take this one level further. The schema for the query below is as follows: SAMPLE (1-to-many) TEST (1-to-many) RESULT (1-to-MANY) Each sample has one or...
Hi, I have inherited a function in an ASP.NET (C#) application where the author used the Microsoft.Practices.EnterpriseLibrary.Data library, but I have been asked to change it so that it uses System.Data.OracleClient. This function uses a stored procedure form a database. itemName, and openDate are string parameters that the function ta...
I have a SQL*Loader control file that has a line something like this: FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '#' Normally, I'd use a quotation mark, but that seems to destroy emacs's python syntax highlighting if used inside a multi-line string. The problem is that we are loading an ADDRESS_LINE_2 column where only 7,000 out...
What if I want to connect to Oracle, MySQL or even MS Access ? ...
I execute the command and get following message at console: ---------------------------------------------------------------------- SQLLDR APPS@VIS/APPS CONTROL= SQL*Loader: Release 9.2.0.1.0 - Production on Tue Sep 2 10:49:27 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Commit point reached - logical recor...
Gday, I have a table that shows a series of scores and datetimes those scores occurred. I'd like to select the maximum of these scores for each day, but display the datetime that the score occurred. I am using an Oracle database (10g) and the table is structured like so: scoredatetime score (integer) ------------...
Hi all. I need to debug in pl/sql to figure times of procedures, I want to use: SELECT systimestamp FROM dual INTO time_db; DBMS_OUTPUT.PUT_LINE('time before procedure ' || time_db); but I don't understand where the output goes to and how can I redirect it to a log file that will contain all the data I want to collect? I use Oracle ...
Is there any way to select the numbers (integers) that are included between two numbers with SQL in Oracle; I don't want to create PL/SQL procedure or function. For example I need to get the numbers between 3 and 10. The result will be the values 3,4,5,6,7,8,9,10. Thx. ...
Hi, I have a table: CREATE TABLE MY_TABLE ( MY_ID NUMBER NOT NULL, COLUMN_1 NUMBER, COLUMN_2 NUMBER ); ALTER TABLE MY_TABLE ADD CONSTRAINT PK_FOO PRIMARY KEY (MY_ID); at a later point, when executing the following sql, I get an error: ALTER TABLE MY_TABLE DROP PRIMARY KEY DROP INDEX; ALTER TABLE MY_TABLE ADD CONSTRAINT PK_FOO P...
This question is about a general technique in SQL, that I can't quite work out how to best achieve with my current understanding of the language. Note that this will be running against Oracle, in case any vendor-specific functionality would be particularly useful, though I expect there is a nice way of doing this with "pure" SQL. Short...
What is the Oracle equivalent to sql server management studio? A client where you can connect to a server and browse the schema and data. ...
SQL Server Syntax: UPDATE #RELATIONSHIP SET SEGMENT_START_DT = #SEGMENTS_LANE.SEGMENT_START_DT, SEGMENT_END_DT = #SEGMENTS_LANE.SEGMENT_END_DT, SEGMENT_ID = 'XCSLANE' +'-'+ #SEGMENTS_LANE.LANE_ID +'-'+ CONVERT(VARCHAR,#SEGMENTS_LANE.SEGMENT_START_DT,113) FROM #SEGMENTS_LANE WHERE #RELATIONSHIP.LANE_ID = #SEGMENTS_LANE.LANE_ID AND #RELA...
Hi everybody, I'm trying to deploy a couple of jar files in a code module for an event action in FileNet P8 4.0 (the FileNet server runs on WebSphere 6.1). One of these jars is my custom code, and the other jar is the thin driver for Oracle called ojdbc14.jar (I also tried with ojdbc15.jar), the custom code uses the oracle jar in order ...
Suppose I have schemas A and B. In schema A I would like to call package X in schema B. However, there exists a package B in schema A. A: package B B: package X When I call from schema A: begin b.x.foo(); end it looks for procedure X in package B, i.e. A.B.X(), and gets an error. How can I fully qualify the call to force...
This occurs when fetching values which have a max length of 50, to a buffer which could only hold 30 chars. I've already looked up the error and found a possible solution, which is to expand the size of the buffer which the value is bound to. The problem is, this occurs only in some of our systems and not in others. Does the Oracle ver...
Hi! I have the following SQL problem. Scenario: I have two tables: Change and ChangeTicket. There is a 1:n relationship. One Change has cero or many changeTickets. No change means no changeTickets. A changeTicket has a status (open/closed/...) A changeTicket has a field representing how many minutes took this change. A Change has a co...
I'd like to automatically update a database column with an aggregate of another column. There are three tables involved: T_RIDER RIDER_ID TMP_PONYLIST ... T_RIDER_PONY RIDER_ID PONY_ID T_PONY PONY_ID PONY_NAME ... T_RIDER and T_PONY have an n:m relationship via T_RIDER_PONY. T_RIDER and T_PONY have some more columns ...
I have problem when joining tables (left join) table1: id1 amt1 1 100 2 200 3 300 table2: id2 amt2 1 150 2 250 2 350 my Query: select id1,amt1,id2,amt2 from table1 left join table2 on table2.id1=table1.id2 My supposed o/p is: id1 amt1 id2 amt2 row1: 1 100 1 150 row2: 2 200 2 250 row3: 2...
In a stored procedure I have used to; raise_application_error (-20010, 'My Message'); to raise a custom error in a certain situation. What I am trying to do is when I make my JDBC call from java, to be able to identify this error as not just being a SQLException so that I can handle it differently. I though I could identify it by the ...