Does anyone have resources/advice on how to connect to a third party WebDAV with PL/SQL? I will be placing a file onto their server and retrieving a log file. Is it as simple as using UTL_HTTP & ‘…
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…
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 r…
Oracle case inside where clause
This is a simple question, I’ve read some details about using CASE in WHERE clause, but couldn’t able to make a clear idea how to use it. The below is my sample query: 1 SELECT * FROM dual 2 …
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…
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 statemen…
ORA-00054 Resource busy when dropping table
Can somebody explain this error? ORA-00054: Resource busy and aquire with NOWAIT specified This error came in a DROP TABLE. Because of it, procedures and packages are not getting compiled.
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 readabilit…