I have year in YYYY format and I want to convert it to FYYY format. So, for instance, 2014 will be FY14, 2009 will be FY09, so on and so forth. I’m creating a View that contains a SUM aggregate …
Tag: oracle
How do I convert Months from Numeral to String Form in SQL Query Containing GROUP BY?
So, I’m creating a View that pulls a bunch of data (with a SUM aggregate on one field). The query contains GROUP BY clause and when I do the month conversion in the query as follows, I get an %invalid …
dbms_metadata.get_ddl not working
I want to get the DDL of Table CARD_TABLE in XT schema But my select Query works I queried dba_objects it still got the table: Answer From the dbms_metadata documentation: If nonprivileged users are granted some form of access to an object in someone else’s schema, they will be able to retrieve the gran…
SQL query to find greatest in columns and rows
How does one in oracle find the greatest of three columns in say 10 rows? The requirement is I have three dates column and I have need to find greatest of three columns in 10 rows. I know greatest will find in one row. How? Answer How about
Connect to External WebDAV with PL/SQL
Does anyone have resources/advice on how to connect to a third party WebDAV with PL/SQL? I will be placing a file onto their server and retrieving a log file. Is it as simple as using UTL_HTTP & ‘…
How to select sub string in oracle?
I have a scenario where my data is something like below: Chapter 18 Unit 10 Sect 16 Case 1 : I want to select Chapter 18 from the above string. Case 2 : I want to select Unit 10 from the above …
Convert oracle.sql.timestamptz to postgresql timestamp with timezone
Sorry, but i`m noob and i need your advices. I have some result set from oracle with timestamptz, i get string value of this timestamptz which like 2014-1-10 13.47.56.0 7:0 and then I need put it in postgresql-request in some function which takes timestamp with timezone. How can I convert this this string to …
oracle 12c – select string after last occurrence of a character
I have below string: So I want to select Sentence since it is the string after the last period. How can I do this? Answer You can probably do this with complicated regular expressions. I like the following method: Nothing like testing to see that this doesn’t work when the string is at the end. Somethin…
ORA-01219: database not open: queries allowed on fixed tables/views only
I Am using oracle 11g and SQL developer tool. When i tried to retrieve rows from db i am getting error message as :- I looked for sgadef.dbf file and it was missing in home directory.. Can someone help me to make it working. Answer First of all check the status of the instance you work with (this may need
Time zone conversion in SQL query
I am using a query to get some application Received Date from Oracle DB which is stored as GMT. Now I have to convert this to Eastern standard/daylight savings time while retrieving. I am using the below query for this: It works fine for Standard time. But for daylight savings time we need to convert it to &#…