Skip to content
Advertisement

Tag: plsql

PL/SQL fill variable with if-condition value

i am trying to fill a variable in PL/SQL with an if-condition value like this: v_variable := if(4 > 3) THEN ‘A’ ELSE ‘B’ END; but this doesnt work. Are there any options to do this? Answer Instead of doing this with an if, I’d go with case:

Oracle Regular Expression (REGEXP_LIKE) Too Long Error – ORA-12733

I have to validate an IPv6 address in PL/SQL. I came up with the regular expression from here: Regular Expression (RegEx) for IPv6 Separate from IPv4 I am getting an ORA-12733: regular expression too long error. Is there any way around this? The limit is 512 (https://stackoverflow.com/a/2694119/3112803), I’m at 657. I cannot think of any way to split this up.

How to know if a user has a privilege on Object?

I would like to know if a user has a privilege on an object or not. I’m working on SQL Developer. When I query manually the table DBA_TAB_PRIVS, I get all the information needed. However, I need this information to be used in some triggers and functions. So, I’m writing PL/SQL function that will return 1 if a role has

Flattening a hierarchial data set in Oracle

I have a set of data as shown below I need to flatten this data as below: And so on. I can have up to 30 parent child relationship I used the connect by and sys_connect_by_path as follows The result set is too large. There are other trees in this table and I don’t think it just looks at the

How to check any missing number from a series of numbers?

I am doing a project creating an admission system for a college; the technologies are Java and Oracle. In one of the tables, pre-generated serial numbers are stored. Later, against those serial numbers, the applicant’s form data will be entered. My requirement is that when the entry process is completed I will have to generate a Lot wise report. If

Oracle cascade delete

Is cascade delete on a table more efficient than individual delete statements (executed in a single plsql block) ? Answer What cascade delete does is issue individual delete statements. Examine the following test case: In the trace file, you will find a line like this: That is Oracle issuing a delete statement against CHILD for each record it’s deleting in

Is there a combination of “LIKE” and “IN” in SQL?

In SQL I (sadly) often have to use “LIKE” conditions due to databases that violate nearly every rule of normalization. I can’t change that right now. But that’s irrelevant to the question. Further, I often use conditions like WHERE something in (1,1,2,3,5,8,13,21) for better readability and flexibility of my SQL statements. Is there any possible way to combine these two

Advertisement