I am working on a script that is reading from a numeric column converting it to a character string where the decimal is dropped (assumed) and the value is padded with leading zeros to fill the specified lengh. Using to_char(payment_amt,'FM0000000V00') is working fine for non-negative values. When the value of the field is a negative number the output includes a leading - (i.e., -000002458).
For the negative numbers I need to convert them to a 'Signed EBCDIC' value, where the last digit is converted to a specified character (in the above example it would be changing the 8 to a Q) and also replace the - with a zero, resulting in 00000245Q.
What I am doing is generating a fixed-length record with values in a specific location. What I'm getting now looks like this:
I have a table test with column containing dates, characters and numbers. I have to extract the number part and the three characters before the number . My data looks like :
TEST ID DATA 1 3/12/2007 2 0 3 3/8/2010 ABC 217 4 NONE 5 COLM XYZ 469 6/8/2011 6 LMN 209
I am currently working on a Data Dictionary project where we need to run a few rules against the give data sources to see if they all comply together.
One of the rule is to check if the no. is negative or not. So for that what I tried to do was to check if first the field is number or not and then check on if it is negative or not.
This is the code I am currently trying on and is not looking good.
SELECT 1 FROM DUAL WHERE decode(DECODE( TRANSLATE('-123.45','-0.123456789',''), NULL, 1,0), 1,substr('-123.45',1,1) ,' ' ) = '-'
When I use JDBC to retrieve the data and convert it to mill seconds, and it also shows as negative time. My question is the retrieved time doesn't show the negative date, but obvious it is a BC date and earlier than 0001-01-01.
1. How can we detect/show the date to show correctly for the BC date without checking c.time < to_date('0001-01-01','YYYY-MM-DD')? (I am wondering if there is format configuration to show the time like 17-JUN-08 04.06.22.893 PM BC, or something to show the time is negative )
S -- format mask display both sing + and - MI -- format mask display negative sign at end PR --Returns negative value in <angle brackets>. and Returns positive value with a leading and trailing blank.
I want the output should display only leading negative if the value is negative. I have used S99990.99 as format Mask. but need output should display only leading negative if the value is negative.
i am doing a data transformation using the following function
FLOOR(ONSET_TPQ-VAX_DATE)
Where ONSET_TPQ and VAX_DATE are both dates. Now in a situation where the ONSET_TPQ precedes VAX_DATE I want the result to be null for those records. As an example if ONSET_TPQ= 2000 and VAX_DATE=2010
I want the result to be NULL as I dont want negative values. Any exact SQl syntax on how to do that. DO i write a case statement?
There are at most 2 entries of a in b. Depending on the value of the type column in B, this determines whether the entry should be male or female. I want to have a select statement that will retrieve 2 rows into one row essentially like below, how is this done:
id male_name female_name 1 paul paula
the column names will appear as such, if its a 0 its a male name if its 1 its a female name, there will generally be 2 entries in B for 1 value of a.
SELECT CASE WHEN "PIVOT_ROW_GENERATOR"."ID" = 0 THEN "PIVOT_SOURCE"."ID_NUMBER" WHEN "PIVOT_ROW_GENERATOR"."ID" = 1 THEN "PIVOT_SOURCE"."ID_NUMBER" [code].........
There are millions of DISTINCT ID values in TABLE_1 and corresponding to each ID there are some values for all the days of a month. I need to insert these values in TABLE_2 in the above format.
select * from function('input') where the function dynamically returns different tables.
I have gotten to the point where I can return the cursor but I cannot format it as a table to use in the query. It return's it as XML. This is my function:
create or replace FUNCTION CAMS_FUN_GEN_REPORTS(PARAM1 IN VARCHAR2) RETURN SYS_refCURSOR AS BEGIN DECLARE p_recordset SYS_refCURSOR; begin OPEN p_recordset FOR 'SELECT * from STATS_FLAGGED_TOTALS_ME'; RETURN p_recordset; END; END CAMS_FUN_GEN_REPORTS;
This is my query:
select * from CAMS_FUN_GEN_REPORTS('')
So normally you would do this
select * from TABLE(CAMS_FUN_GEN_REPORTS('') as tabletype)
But I will never know the end result table as it will be dynamic so I have to find a way to cast the result of the function into a new table I can select on.
I have looked into pipe lining also but from what I can tell you still need to know the table definition. Which I can't know from the client side. The server will control the 'routing' and thus the end result of the select.
We are able to insert CLOB into database, Using oracle Text I'm able to search inside clob. [two questions solved ]. The question arise when we need to send this data to application either as file or as text(varchar2).
I'm able to generate file from CLOB using function,unfortunately it resides inside db and developer is not able to access it.
1)There is option to mount application partition inside db and export file over there but it is not viable option according to management.
2)I've Googled the solution to create JAVA API. Which will perform OS command like scp to send file from db to app(or any remote host). For security reason this option is also dropped.
3)I tried dbms_lob.substr but actual text inside clob is too long. File generated from CLOB sized around 5 MB.
So I guess it requires lot of effort if I wanted to convert into varchar2 as out parameter inside function.
So, to give CLOB data to developer as file or as varchar2. I'm not able to get any solution. Is there any other option using database to convert to string/varchar2 from CLOB ? Or do I need to drill down more into third option.
I am converting from 10g to 11g app server and having problem with the exec_sql.execute command. In my 10g version, the code is:
Declare lv_dbms_journal_cursor exec_sql.curstype; lv_status INTEGER; BEGIN IF (exec_sql.IS_OPEN (lv_dbms_journal_cursor)) THEN exec_sql.close_cursor(lv_dbms_journal_cursor); [code].....
After this code caused the 11g forms session to crash, I modified the 11g version to the below, and it still crashes.
I have the following set of numbers that i am passing in as one input into a stored procedure.
234,456,234,456,567
Now i want to take this list of numbers and use it in an IN statement:
select * from table where column_a in (P_INPUT);
however, when i try this, it give me an invalid error. I have tried inserting single quote around each value and get the same invalid error. I tried a To_char around my column, which solved the error, but it never finds a match!
I want to get 10 random numbers from existing 100 numbers. How can we get/generate random numbers ?
for example I have a table with customer ID, customer Name, having 100 record. We want 10 customers ID randomly from that 100 record not repeated any number. Have any command or procedure for that ?
I am creating a function to sum five numbers (less 1). Is it possible to have an array of numbers in an SQL function, and how would this be implemented?
Here is the screenshot of my output (I cannot embed links until 5 posts!): flic.kr/p/eaSHBP
CREATE OR REPLACE FUNCTION sumfivenumbers ( n1 NUMBER, n2 NUMBER, n3 NUMBER, n4 NUMBER, n5 NUMBER) RETURN NUMBER IS Sumnums NUMBER; BEGIN SELECT SUM(n1+n2+n3+n4+n5-1) INTO Sumnums FROM DUAL; DBMS_OUTPUT.PUT_LINE(Sumnums); RETURN 1; END sumfivenumbers; / SELECT sumfivenumbers(5,5,5,5,5) AS "Five Numbers less 1" FROM DUAL;
I have a requirement to display numbers as equivalent alphabets , like if the stored value is 1 then it should display as 'A' ,2 means 'B' ,is there a way to find out.
CREATE TABLE APS ( ITEM_NO NUMBER, ITEM_NAME VARCHAR2(12)) INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (1,'TEST1'); INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (2,'TEST2'); INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (3,'TEST3'); INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (4,'TEST4');
-- The output to be is.
item_no , item_name A TEST1 B TEST2 C TEST3 D TEST4
I tried to convert numbers to words, it shows the below error.
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 6 11:00:29 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT TO_CHAR (TO_DATE (2447834, 'j'), 'jsp') FROM DUAL;
TO_CHAR(TO_DATE(2447834,'J'),'JSP') --------------------------------------------------------------------------- two million four hundred forty-seven thousand eight hundred thirty-four
SQL> SELECT TO_CHAR (TO_DATE (244783400, 'j'), 'jsp') FROM DUAL; SELECT TO_CHAR (TO_DATE (244783400, 'j'), 'jsp') FROM DUAL * ERROR at line 1: ORA-01830: date format picture ends before converting entire input string
Here i face probelm that he numbers must be follw by DOT "." , this is not correct if the statment only conatines numbers without DOT that not extract. As the
SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj' , '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .) '|' || -- or '(.[0-9]+)' -- starts with decimal point ) AS result FROM dual ;
but mean i have to add . after numbers . as follow
SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj' , '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .) '|' || -- or '(.[0-9]+)' -- starts with decimal point ) AS result FROM dual ;
Is the 12c version of the Grid Control only for Oracle Cloud? Not completely sure of exactly what cloud computing is. Looks like a conglomeration of shared resources. Anyway we are converting all of our servers over to vmware virtual machines except the ones we manage in other states. I am upgrading each database to 11 r2 as we convert the servers. Anyway my Grid server is next to convert to a virtual machine. I am using Grid Control 10.2.0.5.0 now. I want to upgrade to the latest version of the Grid control. 12c seems to stress and boast about managing the "Oracle Cloud" to manage regular single node Oracle servers as well as vmware virtual machines?
I have seen lots of examples of using PIVOT to return an sql query where the rows have been converted to columns. All the examples I can find require you to have two columns with multiple rows, and also require you to know the data which is in one of columns to make titles for the new columns.
I need something a little different...I have a query using UNION which goes something along the lines of...
SELECT [get data from one place] UNION SELECT [get data from another place] UNION SELECT [get data from another place]
this returns the following... a_val ----- 100 200 300
query to return something like this... a_val1 a_val2 a_val3 ------ ------ ------ 100 200 300
The names of the columns don't really matter. And I dont want to create a new table in the database just for the result.
We have a queue in which the message is coming from external system. The payload of the queue table is a PL/SQL record type. Once we get the message in the queue, we de-queue the message and read through the PL/SQL type collection and process the message.
From the below query, we are able to convert the PL/SQL collection message to XML message and see the data.
SELECT dbms_xmlgen.getxml ('SELECT USER_DATA FROM <Queue_table> X WHERE X.USER_DATA.SALE_ORDER.P_HEADER_REC.ORIG_SYS_DOCUMENT_REF=800501298') FROM dual;
The new requirement is the message would come in a XML message in the queue. So my question is, is there any way through which the XML message can be converted to the PL/SQL record structure directly (it would be the opposite operation of the above query).
