I am trying to run the below script. My script works fine without ‘ WITH ADMIN OPTION’ in EXECUTE IMMEDIATE. But when using ‘ WITH ADMIN OPTION’ i get below error. “Error report – ORA-00900: …
Tag: plsql
Select field based on other column max value in oracle pl/sql
I am calculating a field called “Degree Level” in a view. This is a field in the table “Degrees”, and the table shows degrees for each faculty member. A faculty member can have more than one degree. The field “degree level” is also in the table “Crosswalk_Table”…
ORA-06553: PLS-801:Internal Error [55018]
I want to make a function call like SELECT URUN_GETIR(‘test1’) FROM DUAL; but i got ORA-06553: PLS-801: Internal Error [55018]. I tried ORA-06553: PLS-801: internal error [55018] when testing function returning ROWTYPE this like for ex URUN_GETIR(‘test1’).KULUSERNAME but getting same e…
Define a CLOB input parameter for Oracle function (OR Define a function with CLOB input parameter)
I am going to run a code block like below: In which its purpose is to create a function that one of its input argument is CLOB data type. But by executing my code, I get following error: ORA-01704: string literal too long I searched on the internet and also among stackoverflow questions but it didn’t ge…
Getting error while assigning value to collection variable
procedure ins_note (pi_data in note_detail_arr) is begin FOR i IN pi_data.first..pi_data.last loop IF pi_data(i).template_id IN (022, 019, 015, 017, 021, 001, 010, 016, 018, 020, 023) …
cast VARCHAR2 to LONG,RAW,CLOB and find HASH md5
There is a table A with fields (str1 VARCHAR2(4000 CHAR),str2 VARCHAR2(4000 CHAR)) There is a table B with fields (str1 VARCHAR2(4000 CHAR),str2 VARCHAR2(4000 CHAR),hash_code NUMBER) As a result there is an exception when length(str1||str2) is longer than 4000 (varchar2 is too small). How can I cast this to o…
Can I combine these two queries into a single query?
Note:AUT_GROUP_ID column in Table CBS_OWNER.AUT_GROUP_MAP is the only factor differentiating both queries. AUT_GROUP_ID IN (20,4,7) —>ONNET AUT_GROUP_ID IN (3,6) —> OFFNET The first query …
Removing backslashes when using Oracle’s JSON_ARRAY function?
How do you get rid of the back slashes, but still keep the double quotes when using json_array? TABLE A: Wrong code: Wrong output: Desired output: And yes, I know you can just concatenate like so “select ‘[‘|| etc. without using the json_array, but I need to have that in the code. Is there a…
How can I resolve “success with compilation error” error while creating trigger in Oracle PL/SQL?
I am trying to create a trigger that updates “event” table rather than updating the “v_event” view. When I run this script I get “success with compilation error” error. What am I doing wrong? Answer Pay attention to what you do. In order to find out what went wrong, either …
Force timeout SQL Oracle on users response
I have a PL/SQL procedure which has a couple prompts which require the user to enter some information for processing. For example, item numbers, business units & so on. This as well prompts confirmation at the end of the script. This allows several users to rely on the same script to massively setup data …