I am new to oracle and I am learning cursors.My table City has two columns city_id,city_name.So,this is what I tried: i am trying to assign the data of cursor to the newly declared value v_list SYS_REFCURSOR;.But the output is coming as error at v_list := city_list;.How can I assign all the values of cursors to the another cursor directly or
Tag: plsql
How to generate the dynamic where condition in pl/sql cursor
Generate the dynamic where condition in pl/sql cursor based on input values For Example : Input Values : a b c values Query Format : CREATE OR REPLACE Procedure abcprocedure ( a IN Number,b …
Is it possible to use GRANT inside a trigger in Oracle 18c?
I try to create a trigger which automatically grants select on all new tables for a specific schema, whenever a new table in this schema is created. Background for this is IBM InfoSphere Information …
Adding output to function PL/SQL
I am trying to get my function to include the date and current user, but I keep getting an error that my function is in an invalid state. If I comment out or remove the two dbms_output… lines it works just fine. Any ideas of how to return that output? Using Oracle SQL Developer Answer You have to declare the
Way to alter one table if another was altered
For example, I am having table T1 and archive table T1_A Create table T1(n number); Create table T1_A(archieve_dt date,n number); To make activation working correctly if I am adding a column to T1 …
How to extract the package name using regexp_substr
I’d like to extract the package name using regexp_substr. For example: create or replace package body something.pkg_test is I want that: pkg_test I tried to use lookbehind just to ignore “…
A function that returns different column values based on input
Here is a table FRUITS – FILTER A B C Apple 0 3 6 Orange 1 4 7 Apricot 2 5 8 Is it possible to create a function that returns column A for apple (0), B for Orange(4) and C for …
Oracle Compound Trigger : PLS-00103 Encountered when Attempting to get Max of Date Column
I am trying to construct a trigger that will update Table B with Max value end_date column when the corresponding end_date of table A is updated. This is so I can calculated and save the date …
Writing plsql exception errors to DMBS_OUTPUT and FND_FILE job log
I have a MERGE/UPDATE statement. I want to add exception handling such that if the update fails for any reason, write to DBMS_output and job log. I’ve come up with something – it compiles OK, but …
How to create a collection and insert values in a table with the collection?
Hey I am creating a database to store some information about a sport club, where I have trainers and athletes, but when I am trying to insert trainers(entrenadors) to the table that errors appears: …