I need to have a stored procedure in PL/SQL that return a string containing a json object. This object must be a list of object representing rows a table. For example I have the table MY_TABLE(ID, TB_VALUE) The output must be : My stored procedure looks like that : What’s the best way to achieve this ? Should I use
Tag: oracle
Oracle database – update table with random value from literal table, but change data per row
I want to randomly pick a data tuple from a literal table to fill in another table, and randomize the row to pick each time. With the below query, I fill all rows with the same data, so I think the random row is picked once and used everywhere. So how can I get a random row for each row
SQL Create a new calculated column based on values of multi rows and cols
I have a data about airline’s booking, using Oracle db, sample is structured as below: Recordlocator is booking code Sequencenmbr: whenever there is a change in booking, it records new status of a booking with higher Sequencenmbr. So the highest Sequencenmbr in the database shows the latest/current status of bookings Sequenceair: is the sequence of flights in bookings, it may
Pivot Issue ORA-00918 column ambiguously defined
I am running the below query and need to output the rows as columns. When I am adding the second MAX statement, I am getting an error ORA-00918 column ambiguously defined. Not sure what I am doing wrong. Any help will be appreciated. Answer You probably need something like this I.e. 1) add alias to you pivot labels do not
Rewrite NVL and select statement according to string
I have a column, called parametre, which contains our parameters: So i want to get a output FA or DL, according to string PRIJEM_CISTY or PRIJEM_VRATKA: It works. But is there any “better” solution for this ? For example, decode() or RegExp() ? Answer You can put the two together to query
Calculate total units sold and total sales value
Can you help me check my answers whether is this the right way? Im really new to database This is the question This is my answer The reason i use with read only constraint was to enhance the security! Answer The way I see it, query would look like this: If compared to yours: don’t select columns you don’t need
Conditional debit from credits and running balance using SQL
I’ve data like below in a table cryptotransactionledger id transaction_typeid transaction_type amount totalcoins 1 1 bitcoin-credit 30 30 2 2 ethereum-credit 20 50 If I spend bitcoin, I’m putting a new entry like below in the same table with transaction_typeid 3 and similarly for ethereum with transaction_typeid 4 id transaction_typeid transaction_type amount totalcoins 1 1 bitcoin-credit 30 30 2 2
SQL command not properly ended in Python OracleSQL
I’m having some trouble with SQL code that works when I run it in DBeaver, but when I execute it with cx_Oracle it comes up with the error of: The python code is good, but I’m not much of a SQL programmer, so maybe someone can look to see if there is any obvious coding errors. It’s just weird that
How to debug the issue of index monitoring wherein index entry is not found in table v$object_usage?
I am facing an issue while enabling monitoring on indexes , I executed following command to enable index monitoring and then checked the entry in v$object_usage view but couldn’t find any record in it : O/p is Index REPORT.DY_SUM_DLY_SCH_TRN_DIV altered. Checking the entry in v$object_usage : In output no records are coming . How to debug this issue ? Answer
Zero lead digit for a number removed when concatenating in SQL
I have written a couple of PL SQL Functions to return a currency and amount from a compressed field. These work perfectly individually. e.g. returns GBP returns 0.1 Concatenating the 2… returns GBP.1 I want it to be GBP0.1, any idea what is going wrong? The amount function returns a NUMBER the currency a VARCHAR2. It is Oracle SQL. Thanks