I am currently facing a bit of a wall, I am attempting to take information from two different tables, have them displayed side by side with the information recording by count. One being the total …
Tag: oracle
How to call DBMS_CRYPTO.HASH when inserting a CLOB into Oracle with Python?
I’m storing CLOBs in Oracle. To speed up finding identical CLOBs, I’d like to introduce a hash value of the CLOBs. What I tried so far is INSERT the CLOB UPDATE the hash value based on the stored clob. How can this be done in one operation instead of two? This doesn’t work: (Oracle 12.2 usin…
JDE how to convert F983051.VRPODATA (long / blob) to a string (varchar2)?
JDE how to convert F983051.VRPODATA (long / blob) to a string (varchar2)? I’m not sure if I can do something like: Where FUNCTION is something that converts the BLOB file into a varchar2. P.d. I cannot use any kind of DDL. Please advise, Answer I actually found what I wanted: DBMS_LOB.SUBSTR({BLOB field…
ORACLE SQL: Combining 2 rows of 2 separate select queries
Hello I am very new to SQL and I have an issue. I have 3 tables job, revenue and cost. job.unid = cost.job_unid and revenue.job_unid I want to find the total revenue and cost for each job and then find the profit for each job all on one row: The headers looking something like this: Job No, Total Revenue, Tota…
TO_CHAR failing to set specific format in update
I’m trying to set a specific field in a record where the legacy code has used a DDMM format DATETIME as a char. I want to be able to find one specific value that matches something and basically change that one field. Super simple stuff, right? I know for a fact that if I query using to_char(SOME_DATETIM…
Recursive Subquerying with sorting
I looked at Tim Hall’s excellent article here, that allows you to work with self-referenced entities and show hierarchical data (starting with top level nodes and joining back recursively), using CTE like syntax in Oracle. So I have code that looks like this: For the the anchor rows (the top level hiera…
MERGE table, do nothing when matched
I have a table DOMAINS in 2 different schemas with columns ID, NAME,CODE,DESCRIPTION. For any NAME exist in new schema, it should use existing ID without any merge; for those new NAME records, it should insert with ID from old schema. How can i intepret the portion of do nothing in above query? Answer For you…
Oracle parameterized update query c#
It should work but it doesn’t. I have referred others but couldn’t find the reason. Thanks in advance. Joon Answer I found why it didn’t update table. To make it work I added parameters in the order of the query parameter and found it works. But I still do not understand why the order of add…
SQL Query Confused
Display: account no. customer id initial amount in terms of lakhs (rounded off to 2 decimals) [change the column heading to ‘INITIALAMTIN_LAKHS’] amount category(amt < 50000 show as 'Low', amt > …
Oracle PL/SQL Functions: “Invalid Datatype”
The following function checks to see if a zip code is present in a table called ZIPCODE: The schema for the ZIPCODE table is as follows: To test this function, I issued the following statement which should return false as the zip code passed as an argument is present in the ZIPCODE table: However, I receive t…