Here is the code: and the following function doesn’t compile: It says: PL/SQL: ORA-00947: not enough values Why is that? Answer Why is that? You are trying to put 3 values (and multiple rows) into a single variable. Use BULK COLLECT INTO and wrap the values in the object type: Given the sample data: The…
Tag: oracle
Find value that is not a number or a predefined string
I have to test a column of a sql table for invalid values and for NULL. Valid values are: Any number and the string ‘n.v.’ (with and without the dots and in every possible combination as listed in my sql command) So far, I’ve tried this: The regular expression also matches the single charact…
i am trying to combine the PL/SQL and the SQL commands in manipulating the database. am i doing it correctly? any help and corrections?
enter image description hereuse PL/SQL with SQL commands in manipulating the database (PL/SQL structure, Data types, Variable, DBMS Output and Conditions) and this is my codes and what i have understandenter image description here Answer The sample code for me does not run. It gives The reason is that you hav…
How can I evaluate the size for when it returns the value whatever I want?
I have this query in which I check with the NVL function if a field is null that it returns the other one and so on respectively, but I want to make sure that regardless of the data that it returns it is not greater than 10 and if it is, then that it cuts the string in that limit
Find Stored Procedure By Table Name (Oracle)
Need help. Every morning at 4 o’clock a table is created in the database, I need to understand what sources are used to create it, so I tried to find a stored procedure that creates this table using all_source or dba_source (select * from all_source where upper(text) like ‘%TABLE_NAME%’, but…
Query monitoring changes in the field
I need to program a query where I can see the changes that certain fields have undergone in a certain date period. Example: From the CAM_CONCEN table bring those records where the ACCOUNT_NUMBER undergoes a modification in the CONCTACT field in a period of 6 months before the date. I would be grateful if you …
Oracle Apex conditional icons on values in classic report
I have a follow up question based on this answer which is string based: https://stackoverflow.com/a/41556608/221270 How to achieve the same conditional color and icon formatting but based on a value: If I use the code above I got this error: ORA-20999: Failed to parse SQL query! ORA-06550: line 10, column 1: …
How to increase performance when inserting more than 40k+ rows
My attempt: Currently, I am inserting a few records and it is giving exact results but in actuality, there are 40k+ records so I believe it will give performance issues also. Is there any way to insert the records faster because I will write a procedure for this insertion? Is there any other in which I can wr…
Adding time to a table in SQLplus without date
I am trying to create a table regarding telecommunication and I want to add time for 2 rows call_start_time and call_end_time and with that, I want to calculate the call_duration. This is what I used to enter the value If I use this format while displaying the data only the date is displayed not the time. Can…
Query to find the count of total IDs, and IDs having null in any column and the percentage of the two (i.e. Count of ID having null /total ID count)
There are two tables. Tables A has following structure ID Flag Name 1X 1 Y 2Y 0 Null 3Z 1 Null 4A 1 Y Table B has the following structure B_ID City State 1X Y Null 2Y Null Null 3Z Null Y 4A Y Y I want to get the count of all the IDs and the count of IDs