I have table with values in columns like colname TMC_MCH,OTA_MCH,CONSOL_MCH,RETAIL_MCH,TOUROP_MCH,SPEC_MCH,QRACTO_MCH RETAIL_MCH RETAIL_MCH,CONSOL_MCH CONSOL_MCH OTA_MCH I need to run query to fetch …
Tag: oracle
how to pass in array in procedure call in oracle
I have a procedure that takes in an array in postgres, this syntax works: SELECT * from myMethod(array[‘test’,’test’], array[”], 554, 73430, 322234, ‘shazam’); the array keyword is what I am …
Oracle SQL where clause does not work for some dates
Below query works fine with the recent dates (for e.g. if the date is 20-JUN-16, 30-SEP-19). But if I query with date value set to 29-MAR-80, 01-JAN-94 it does not return any result. Wondering what could be the reason. Am not allowed to change the existing year format to YYYY. We are on Oracle 19c. Answer Her…
I am dropping the with check option constraint from view but it is not being enforced
So I am stuck in a problem. I defined a view with the WITH CHECK OPTION constraint, now i wish to delete it, but even after dropping the constraint i get the error :=ora-01402: view WITH CHECK OPTION where- clause violation. this is the sequence of SQL commands i am executing – I cant figure why this is…
Error(8,1): PLS-00103: Encountered the symbol “SET”
I’m trying to create a function using oracle, it should be working fine but i keep getting the following error: Error(8,1): PLS-00103: Encountered the symbol “SET” here is my Function: Answer The problem is where do you put this command: SET SERVEROUT ON; cause its inside the function declar…
LOOP/FOR statement on oracle script
I’m running an ansible playbook that runs an sqlplus script to an Oracle DB. Basically the script creates a CSV file with some server info. The query is pretty much autogenerated, so it will be difficult change it. The problem is that this query brings all the 5000 server and I need only 200. I want to …
Oracle SQL join and expand [closed]
Closed. This question needs details or clarity. It is not currently accepting answers. Want to improve this question? Add details and clarify the problem by editing this post. Closed 2 years ago. Improve this question I have 2 tables – sales and ratio. sales table has 2 columns – state, sales. Thi…
Query to find the tables size occupied in GB in Oracle SQL for various SCHEMAS
I am trying to find the space occupied by list of tables in Oracle DB (for various SCHEMAS ) ,currently i am using this query QUERY 1 – DBA_SEGMENTS QUERY 2 – DBA_OBJECTS when i compare the LIST OF TABLES counts it’s not matching , what could be the reason ? can you please suggest is this co…
Oracle : replace string of options based on data set – is this possible?
I have column in table looking like this: Every option has a number of value. There is a table – let’s call it option_set and records look like I want to replace option name to number in 1st table, if exists of course, if not exists then =0. I have done this in PLSQL (get the pattern, go through e…
Create Children and Parent of a Person
I have the following Type Address and PhoneNumber: CREATE TYPE Adress AS Object (Street varchar2(50), PostalC number, Ville varchar2(50)); CREATE TYPE PhoneNumber AS Object (Ind varchar2(3), PhNumber …