Hai guys, I've a query in which i need to interchange the values of two fields. The query is as follows: SELECT TO_DATE(A.G_LEDGER_DATE,'dd/mm/YYY')as G_LEDGER_DATE,C.ACC_MASTER_NAME, A.G_LEDGER_REF_NO , NVL(CASE WHEN B.G_LEDGER_SECTION = 1 THEN CASE WHEN (SELECT COUNT(*)FROM SOSTRANS.ACC_GEN_LEDGER WHERE G_LEDGER_SECTION = ...
Let say that I have 2 tables with the same structure : STOCK and NEW_STOCK. These tables have a primary key composed of (ID_DATE, ID_SELLER, ID_INVOICE, ID_DOC). Now, I need to get for every (ID_DATE, ID_SELLER, ID_INVOICE, ID_DOC), the value of the amount (field AMOUNT) regarding this requirement: If a record is present in NEW_STOCK, ...
I need to find the rows where a certain column contains line feed. This does not work: select * from [table] where [column] like '%\n%' In SO, I found the solution for SQL Server: http://stackoverflow.com/questions/1085662/new-line-in-sql-query But this does not work in Oracle. Is there any ANSI SQL solution? This should be a standard...
In our Stored procedures we have the following code towards the very end. <<SQL_ERROR>> V_SYS_ERROR_MSG := SUBSTR(SQLERRM, 1, 252); DBMS_OUTPUT.PUT_LINE('ERROR IN EXECUTION IN PROCEDURE'); DBMS_OUTPUT.PUT_LINE('THE ERROR CODE IS ' || V_SYS_ERROR || '- ' || V_SYS_ERROR_MSG); we have statements like fol...
Hi, I am new to Oracle PL/SQL and am having some difficulty conceptualizing collections with regard to records. I have the following problem: I want to compare various fields of a record from the client table to various fields of a record from the person table. e.g., LName, FName, Soc. (unfortunately, there is no identifier to easily l...
I really should know this, but would someone tell me how to change the default database on Linux? For example: I have a database test1 on server1 with ORACLE_SID=test1. So, to connect to test1 I can use: sqlplus myuser/password Connects to the default database, test1 I would now like the default sqlplus connection to go to database...
I'm stuck with a SQL Query. I have this table: [Reserve] ID NUMBER START_DATE, DATE END_DATE, DATE .......... (more cols) I need to look if the next count is more than 0 for any of the dates between START_DATE and END_DATE (including both). If the count is more than 0, the query must stop inmediately and return "There is no loca...
Is it true that better concurrency can be achieved in Oracle databases than in MS SQL Server databases? In particular in an OLTP scenario, such as an ERP system? I've overheard an SAP consultant making this claim, referring to Oracle locking techniques like row locking and multi-version read consistency and the redo log. ...
Hi All, Can anyone please let me know, How to take script for schema of the tables, stored procedures of Oracle through SQL Developer..? Thanks in Advance. Mahesh ...
In Oracle's regexp_replace function, there is a parameter replace_string in which you can specify backreferences \1 to \9. Is there a way to refer to backreferences after the 9th one? Oracle treats \10 as \1 followed by a literal 0. ...
Am looking and testing all the source control solutions to use with Oracle Forms/Reports builders. but, none of the existing solutions works well with Oracle products. Any one out there did a similar practice? and how do you control the source codes of Oracle Forms/Reports? ...
Hi, I'm using SQL Plus 11.1.0.6.0 to run a script that performs batch inserts into an Oracle 10g db. The problem i noticed is when inserting some code string into a clob field that has some lines with trailing spaces such as: ....public void myMethod().... --trailing space here ....{ ........int myVar = 1; ........ -- empty line with t...
Oracle's database link allows user to query on multiple physical databases. Is there any MySQL equivalent ? Workaround ? I want to run a join query on two tables , which are in two physical databases. Is it possible in MySQL ? ...
I am using Hiberbnate 3.1.3. I have a mapping as below and when I try to insert a record into the TEST_TABLE, I get an Exception: 'Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not fetch initial value for increment generator' <class name="com.test.app.to.TestTable" table="TEST_TABLE" schema="TEST"> ...
I have to populate the result of a query into an array in my pl/sql proc. For example, I have a employeeId empArr (TYPE empArr IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;) and i want to populate this array with the result of a sql statement : select empId where dept = 'accounts'. Is there a way to do this ? Or would you suggest...
hi I have A anb B in String format A = 14/01/2007 B = 22:10:39 I try to insert date and time: SQL = "insert into MyTbl(Tdate,Ttime) value ('" + Convert.ToDateTime(A) + "','" + Convert.ToDateTime(B) + "')"; i got ORA-01843 error, what I can do ? thank's in advance ...
Hi, We plan to configure a stored procedure to run as a batch job daily using Oracle DBMS scheduler package. We would like to know what would be the best way to log an error message when there is an error occured. Is logging to a temporary table an option? or is there a better option. Thanks in advance. ...
How to avoid "characterset conversion buffer overflow" error in sql*loader? error # 00626. I am not able to find this on internet please suggest me the solution for this. ...
I'm trying to make a Crystal Reports 11 report off an Oracle database that's grouped by user. I've got over one thousand users. I want to create a parameter field that prompts the person to select which users they would like to view the results for. However my parameter selection field is only showing 221 of the possible users. The u...
I have table that has date field. When I run a query, I see this: 01/10/2009 22:10:39 How can I retrieve only the time (IE: 22:10:39) ...