I am trying to get old value from variable in PL/SQL for loop. For instance: I have l_sequnce variable with increment of 10. I hope I described the problem well 🙂 Answer Use ORDER BY in the SELECT statement and then you only need to check if the value has changed between the previous and current values: Or, don’t use
Tag: plsql
ORACLE json_object_t – error when call get_string() method on object got by chain of methods .get_array() .get()
I am trying to get a value from an array contained in JSON-object by chain of methods of JSON_OBJECT_T type – .get_array(…).get(0).get_string(…) But getting an exception component ‘GET_STRING’ must be declared Here is my code, could someone explain why the chain does not work? Answer The problem is that calling .get(0) returns the type JSON_ELEMENT_T, not a JSON_OBJECT_T type. JSON_ELEMENT_T
Merge two row in one from different table
Below is simple example, I need in one row display value from different table. eg. need resaults as: Answer With sample data you posted, that’s join with coalesce.
Why i am getting error while executing pl/sql program?
Answer In your code you are missing the semicolon in insert statement & radius value assignment statement – Demo.
PLS00215: String length constraints must be in range (1..32767)
I am new to pl/sql. I want to create a procedure that has three parameters called ‘startMonth’, ‘endMonth’, ‘thirdMonth’. In the procedure, I am executing a sql query which is in ‘run_sql’ column in table_query. Values for ‘startMonth’, ‘endMonth’, ‘thirdMonth’ are needed to this query. This is how I wrote the procedure. My plan is to put all the sql
HOW TO CHECK CONDITIONS FOR PREVIOUS MONTH
My table is like I am trying to get customer_ids where they didn’t do any transactions previous month and made transactions with at least 2 transaction_type at this_month.While doing this I am converting rundate to MM-YYYY format. What I’ve done for getting previous month and this month on MM-YYY format is : and this is what i got: But the
Oracle trigger multiple conditions in when clause
I’m trying to create a trigger that updates a column in a table when other columns are updated. but getting the following error while saving the trigger ORA-25000: invalid use of bind variable in trigger WHEN clause My trigger is as follows, I’m not sure what is wrong with the code. Answer Although IF is an alternative to WHEN, I’d
Display group having certain value only on max date and not in other entries of group
I am looking to display the group which is grouped on id column where trans_cd=’Audit’ only on max trans_proc_dt of that group. There should not be any other trans_cd=’Audit’ execpt for max trans_proc_dt. My output should be I can’t think how to go about getting my result set. Answer You can use analytic functions to calculate absolute maximum date and
How to distribute stock quantity from a single cell to different aging brackets based on the the aging brackets quantities?
We have a table with item codes and current stock quantity and quantities in aging brackets whenever they were received (1-90, 91-120, etc) as follows: I need to distribute the STOCK_AS_ON_DATE quantity in the age brackets until no balance quantity is left. Example: Item A’s STOCK_AS_ON_DATE is 40377, in A1TO90 column we can see that 4000 quantities were received, in
Oracle sql query to group two sets of dates if sequencials
I have the next table: EMPLOYEE CODE START_DATE END_DATE 02097368 F7H3 09/07/2018 20/10/2018 02097368 F7H3 21/10/2018 05/01/2019 02097368 F7H3 06/01/2019 12/01/2019 02097368 F7H3 13/01/2019 02/02/…