天道酬勤,学无止境

oracle

The mystic getClobVal()

I have a table (AKADMIN) with an XMLTYPE column which name is XML. I would like to use the getClobVal() with this column. select t.xml.getClobVal() /**/ , t.xml.getClobVal() -- , t.xml.getClobVal() as clobval , t.xml.getClobVal() from akadmin t where ROWID = 'AAAQc6AAIAAAADDAAA' ; In the resultset the first 4 column give CLOB type, but the fifth column XMLTYPE. I have to type any comment or alias after getClobVal() to correct (CLOB) type of the result. Why? Another issue, when I leave the alias of tablename: select xml.getClobVal() from akadmin t where ROWID = 'AAAQc6AAIAAAADDAAA' ; It throws

2022-01-19 01:33:26    分类:问答    oracle   comments   clob   xmltype

Set language for Oracle JDBC thin client

I want to specify language for the JDBC connection before it is actually created. For example if I specify wrong L/P credentials in DriverManager.getConnection(url, user, password) I need to get ORA error localized to the language I selected. I use Oracle thin client and setting NLS_LANG environmental variable did not work

2022-01-19 00:43:55    分类:问答    java   oracle   jdbc

UPDATE with JOIN syntax for Oracle Database

First, I execute the following SQL statements. drop table names; drop table ages; create table names (id number, name varchar2(20)); insert into names values (1, 'Harry'); insert into names values (2, 'Sally'); insert into names values (3, 'Barry'); create table ages (id number, age number); insert into ages values (1, 25); insert into ages values (2, 30); insert into ages values (3, 35); select * from names; select * from ages; As a result, the following tables are created. ID NAME ---------- ---------- 1 Harry 2 Sally 3 Barry ID AGE ---------- ---------- 1 25 2 30 3 35 Now, I want to update

2022-01-18 21:06:38    分类:问答    oracle   join   sql-update   oracle12c

Weird Oracle behaviour - cross join in recursive CTE works with comma but not with cross join clause

Suppose following simple recursive query r to list several numbers. When recursive part of query is cross joined with unnecessary 1-row table using old way with comma separation, everything works fine (dbfiddle): with r (x) as ( select 1 as x from dual union all select x + 1 from r,dual where x < 5 ) select * from r If I change comma to cross join clause, I get ORA-32044: cycle detected while executing recursive WITH query error (dbfiddle): with r (x) as ( select 1 as x from dual union all select x + 1 from r cross join dual where x < 5 ) select * from r; Reproduced on Oracle 11g (my project

2022-01-18 20:53:25    分类:问答    oracle   cycle   recursive-query   cross-join

ORA-01722: invalid number on Entity Framework

I'm executing a stored procedure from Oracle DB that is: PROCEDURE GET_TIM_USER_CUSTO(P_ANOMES IN VARCHAR, USER_CUSTO OUT SYS_REFCURSOR) IS BEGIN OPEN USER_CUSTO FOR SELECT ID, COD_UTILIZADOR,ANO_MES, TO_NUMBER(DESENCRIPTAR_DADO(CUSTO, (SELECT VALOR FROM TIM_CONFIG WHERE PARAMETRO='CRIPT_KEY'))) CUSTO, TO_NUMBER(DESENCRIPTAR_DADO(CUSTO_EXTRA, (SELECT VALOR FROM TIM_CONFIG WHERE PARAMETRO='CRIPT_KEY'))) CUSTO_EXTRA FROM TIM_USER_CUSTO WHERE SUBSTR(P_ANOMES, 1, 4)=SUBSTR(ANO_MES, 1, 4); END GET_TIM_USER_CUSTO; If I execute it on the Oracle directly it returns a result set well. But If I call

2022-01-18 19:49:30    分类:问答    oracle   entity-framework   stored-procedures

Reset sum when condition is met in Oracle

My data is structured as follows: Timestamp | Hour | Count -------------------------- 20190801 01 | 1 | 10 20190801 02 | 2 | 20 20190801 03 | 3 | 10 20190801 04 | 4 | 5 20190801 05 | 5 | 15 20190801 06 | 6 | 10 20190802 01 | 1 | 5 20190802 02 | 2 | 20 20190802 03 | 3 | 5 20190802 04 | 4 | 15 20190802 05 | 5 | 20 20190802 06 | 6 | 5 20190803 01 | 1 | 30 I'm trying to make an SQL query that will calculate a running SUM but resets when the hour is 3. The result should look like this: Hour | Count | SUM ------------------ 1 | 10 | 10 2 | 20 | 30 3 | 10 | 10 /* RESET */ 4 | 5 | 15 5 | 15 | 30 6 |

2022-01-18 18:49:16    分类:问答    sql   oracle

Using 'LIKE' operator with a subquery that returns multiple results

Newbie to SQL. Kindly help. I need to count number of records which have a pattern in one of the fields, for multiple patterns. I know how to do it for one pattern, but how do I get count of each pattern when there are multiple patterns coming from a subquery. I am using Oracle. I will try to explain with an example. SELECT count(*) FROM TableA WHERE TableA.comment LIKE '%world%'; Now this code will return the number of records which have 'world' anywhere in the TableA.comment field. My situation is, I have a 2nd query which has returns a list of patterns like 'world'.How do I get the count of

2022-01-18 18:47:01    分类:问答    oracle   subquery   sql-like

Connecting to a database in Pro C using Oracle Wallet

I have an Oracle Wallet configured and tested. I have an application written with Pro C which needs to connect using the wallet. I have been unable to find documentation on this. Looking for any information I can get on how to connect with Pro C using an Oracle Wallet. Thanks in advance. Scott

2022-01-18 18:22:00    分类:问答    c++   oracle   oracle-pro-c   oracle-wallet

In SQL, how to aggregate on a field inside the table

I'm sorry the TITLE is not so specific. I'll try to explain: I'm new to SQL. I'm at work and wrote a query which has 9 columns that takes information out of many tables. on the 9th column are names of types of machines, on the 3rd are value representing the time a machine worked in a month. I need to add a 10th column which will have for each type of machine, the maximum of 3rd columns for this type. lets say there are 5 machines of type XR (5 rows in the table) with times (3rd column) of 1,2,3,4,5 (in hours). I need that on the 10th column, all rows where type of machine is XR will have the

2022-01-18 17:17:43    分类:问答    sql   oracle   aggregate   max

cx_Oracle and the data source paradigm

There is a Java paradigm for database access implemented in the Java DataSource. This object create a useful abstraction around the creation of database connections. The DataSource object keeps database configuration, but will only create database connections on request. This is allows you to keep all database configuration and initialization code in one place, and makes it easy to change database implementation, or use a mock database for testing. I currently working on a Python project which uses cx_Oracle. In cx_Oracle, one gets a connection directly from the module: import cx_Oracle as

2022-01-18 15:48:32    分类:问答    python   database   oracle   cx-oracle