I have a following query for DB2 database: BEGIN DECLARE NUMBER_OF_ROWS INT; DECLARE ITER_INDX_1 INT; DECLARE ITER_INDX_2 INT; SET ITER_INDX_1 = 0; SET …
Tag: plsql
Work Around for PL/SQL to do column validation
I need to create a procedure to validate number of digits between 2 columns. I have some reason that this handling won’t do in Java so it needs to be a stored procedure. It first will get the template(result_format) from one of my table and itself contain data like then the argument P_RESULT will have input like then I need
How to use Replace/Substitution function in PL SQL
I’ve a doubt regarding replacing / substituting values in PLSQL. I’ve used listagg to segregate n number of values with each and every value being splitted by comma delimiter. For instance when executing the above query it returns 7digit alpha numeric values. For example ABCD123,EFGH456,IJKL789 After storing the above values in a variable. When trying to replace the comma (,)
How do I use both a SELECT query and RETURNING inside the same SQLPLUS INSERT INTO statement
I’m working on this project I’m using sqlplus and I’m making this procedure in PL/SQL: I just get the error: SQL command not ended properly. I can’t find any information online about how to properly do this. I tried to have t_order_no as a table. I tried having it RETURNING BULK COLLECT INTO. I tried putting the RETURNING before the
This procedure is inserting null values into my custom table – why?
Table output is like this when I pass acad_prog ‘BTRCS’ and admit_term ‘1701’ as input in procedure: Answer You need to use cursor values in if statement and in insert statement like this: This can also be done using insert into .. select .. (without cursor) as following: Cheers!!
Transform table to smoothen records in one column
the situation (a real life situation) is: I have a table with “log” records, for simplicity let’s assume there are only columns NAME, TIME, STATE (in reality there are few more). There are cca 1 Milion rows in which there are 100 Names (computers) and cca 10 states (productive, idle, automated, maintenance…) This can be interpreted as: *At t0 computer1
How do i use a cursor correctly
With the code below I am trying to search for certain words (SEED WORDS) inside reviews. Then depending on the number of Seed words inside each review I come up with a rating number. There are …
Is there an equivalent to TRY_CONVERT() in Oracle?
TRY_CONVERT() in T-SQL would return a value cast to the specified data type if the cast succeeded; otherwise it would return NULL. Is there a similar function in ORACLE or a way to reproduce this …
Shapiro-Wilk test
In PL/SQL, I want to calculate the Shapiro-Wilk value of my selected data. Apparently, the function DBMS_STAT_FUNCS.normal_dist_fit is able to do it, but it takes a table name as a parameter, and not the result of a select. The values I am working with are numbers, selected after many inner joins. Here is a sample of my values for a
problem with script creating table via if clause
We are trying to build a script which automatically checks if a table is already created and if so the script should end. If the table is not created the script should create it. case if …