PHP Blog
-
4 min readIn Oracle, the SELECT statement can be used in conjunction with the CASE WHEN ELSE statement to specify conditions and return values based on those conditions.The basic syntax for using the CASE WHEN ELSE statement with the SELECT statement is as follows:SELECT CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ... ELSE default_value END FROM table_name;In this syntax:The CASE statement is followed by one or more WHEN clauses that specify the conditions to be evaluated.
-
4 min readIn Oracle APEX, you can determine the hostname by utilizing the ORA_APEX_UTIL.GET_HOST_NAME function. This function returns the hostname of the server where your APEX application is running. You can use this function in a SQL query, PLSQL block, or an application process to retrieve the hostname dynamically. This can be useful for displaying the hostname in your APEX application or for any other functional requirements that involve the hostname.
-
6 min readTo get the primary key of a system table in Oracle, you can query the ALL_CONS_COLUMNS and ALL_CONSTRAINTS views. By joining these views based on the table name and constraint type, you can retrieve the primary key column names for a specific system table. Additionally, you can also query the USER_CONS_COLUMNS and USER_CONSTRAINTS views if you only want to retrieve information about tables owned by the current user.
-
3 min readTo use reverse like in Oracle, you can use the LIKE operator along with the reverse function. The reverse function in Oracle is used to reverse a string.For example, if you want to find all the rows in a table where a certain column ends with a specific string, you can use the reverse function in conjunction with the LIKE operator.
-
4 min readTo select 10 rows from a column in Oracle, you can write a SQL query using the "ROWNUM" function. First, you need to specify the column you want to retrieve data from in the SELECT statement. Then, use the ROWNUM function in the WHERE clause to limit the result set to the first 10 rows. Make sure to order the results if you want a specific order.
-
5 min readTo check active connections in Oracle, you can query the V$SESSION view in the Oracle database. This view provides information about the active sessions in the database, including details such as the username, program name, and status of each session. By querying this view, you can see which users are currently connected to the database and what they are doing.
-
5 min readTo delete duplicate rows in Oracle, you can use a combination of the ROWID pseudocolumn and the DELETE statement. First, you can identify the duplicate rows using a subquery with the ROW_NUMBER() function partitioned by the columns that should be unique. Then, you can delete the duplicates by using the DELETE statement with a subquery that selects the ROWIDs of the duplicate rows. Make sure to back up your data before executing any delete operations to prevent accidental data loss.
-
6 min readTo split a column value in Oracle SQL, you can use the SUBSTR function to extract the desired substring from the original column value. You can specify the starting position and the length of the substring to be extracted. Alternatively, you can use the INSTR function to locate a specific character or pattern within the column value and then use the SUBSTR function to extract the substring before or after that character.
-
4 min readTo display an image in an Oracle form, you can use a graphical image item. First, you need to create a graphical image item in the form. Then, set the image item's properties such as Filename and Image Format to specify the image you want to display. You can also use PL/SQL code to dynamically set the image item's image source. Finally, run the form to see the image displayed in the graphical image item.
-
4 min readRounding up in Oracle can be done using the CEIL function. This function rounds a number up to the nearest integer. For example, if you have a number like 4.3, using the CEIL function will round it up to 5.To use the CEIL function in Oracle, simply provide the number you want to round up as an argument. For example: CEIL(4.3)This will return the value 5.You can also use the CEIL function with negative numbers. For example: CEIL(-4.
-
4 min readTo find the max value of an alphanumeric field in Oracle, you can use the MAX() function in a SQL query. By selecting the alphanumeric field and applying the MAX() function to it, you can retrieve the maximum value stored in that field. It's important to note that alphanumeric fields are typically stored as strings in Oracle, so the comparison for the maximum value will be based on the alphabetical order of the characters.