-
How can we call a function from an anonyms block?
madhuk17
- Feb 9th, 2007
We can call a function from anonymous block as a part of sql statement written in that block.eg: create or replace function fn_emp(v_empno in number)return varchar2 isbeginselect ename into v_ename fr...
rajnish123
- Dec 30th, 2006
hi,suppose a function add two number and you desire to call it from anonyms block.create or replace function f_add(a in number,b in number) return number asc number;beginc:= a+b;return (c);end;t...
-
Hi,Can somebody please answer couple of my questions ASAP?1) How do you call a trigger on a table?2) What is the maximum number of data strings that can be returned through DBMS_OUTPUT?Much thanks!!
madhuk17
- Feb 8th, 2007
Hi,I think trigger is not called.The code in the trigger defines the action the database needs to perform whenever some database manipulation (INSERT, UPDATE, DELETE) takes place.I think maximum number of data strings that can be returned through DBMS_OUTPUT is 255 bytes.
gchandra_sekar
- Feb 7th, 2007
Hi!) Trigger cannot be called as it automatically gets activated once u touch the table.2) Prior 10g user SQL> set serveroutput on size 1000000 or if it is 10gORA10GR2> set serveroutput on size unlimited
-
How to display the contents of a current record fetched in a reference cursor?
Vishakha
- Feb 17th, 2007
declare cursor v_cursor is select empno, empnm, empdept from emp where empdept = 10;begin open v_cursor; ...
geetha24
- Feb 13th, 2007
To display the cursorOPEN v_cursor FOR SELECT ename, empno, deptno FROM emp WHERE deptno = p_deptno ORDER BY ename;LOOP FETCH v_cursor INTO v_ename, v_empno,...
-
What is a cluster and what is the real time use and business reasons to use Clustering
KSPRADEEP
- Oct 17th, 2012
Cluster are groups of one or more tables physically stores together to share common columns and are often used together
g_sidhu
- Feb 6th, 2008
Clustering is a method of storing tables that are intimately related and often joined together into the same area on disk. For example, instead of the BOOKSHELF table being in one section of the disk ...
-
-
How to reduce the the burden/main memory on database if i am using refcursor to hold large data to increase performance.
gauravy
- Jul 5th, 2017
Use pipeline functions
durgadas.menon
- Aug 13th, 2008
Unlike tables ref cursors do not store data. They act like mold to hold data during the execution of the piece of code so proper coding and use of bind variables can help decrease the burden of proces...
-
Function which returns previous business date of given input date
How can I write a function which returns previous business date of given input date1. skip holidays when you find previous day2. skip Sunday and saturday's when you find previous dayUS holidays are defined in HOLIDAYS tabledate, holiday_desc1-Jan-2007 New Year
ravgopal
- Oct 10th, 2007
If you need to get the previous business date, just change the in_dt variable in declaration to input_dt date := '&Dt';in_dt date := input_dt - 1;
ravgopal
- Oct 10th, 2007
Did you find the solution? if not then, here it is..try thisa) Create the sample table holiday : create table holiday (h_dt date, h_desc varchar2(100));b) let us feed some records;Inse...
-
When to use collection types in PL/SQL?
krishnaindia2007
- Jun 23rd, 2008
Collection is an ordered group of elements all are of same time. Each element in the collection has unique subscript to identify its position in the collection.Pl/sql offers three collection type...
ravgopal
- Oct 10th, 2007
The following bullet points can be referred to when deciding what collection best suits a particular solution.VarrayUse to preserve ordered list Use when working with a fixed set, with a known number ...
-
What is Oracle collections
kirangsv
- Jan 6th, 2008
A collection can be loosely defined as a group of ordered elements, all of the same type that allows programmatic access to its elements through an index. Commonly used collection types used in the pr...
dev5000
- Dec 26th, 2007
It is kind of datatype.Collection is nothing but?a convenient?way to manipulate many variables at once.There are 3 types of collection.1. INDEX BY TABLE2. NESTED TABLE3. VARRAYS
-
Oracle Cursor types
What are the types of cursors apart from explicit and implicit cursors ? Explain when and where they are used ?
krishnaindia2007
- Jun 1st, 2008
There are only two types of cursors in oracle Pl/sql.1. Implicit cursors2. Explicit cursors We can define an explicit cursor as static or dynamic (REF CURSOR). ...
Manoharan
- Oct 25th, 2007
1. Implicit Cursor2. Explicit Cursor3. Ref CursorImplicit Cursor like SQL%rowcount,SQL%rowtype...Explicit cursors are user defined cursorRef Cursor is object name of the cursor type. Its mainly used for dynamic purpose
-
Oracle extract records from temporary table
Assume that I am using a temporary table in a procedure and I am inserting records and updating another set of records through Merge statement. If I use cursor in that temporary table, How can I extract all of the records from temporary table at the end of the stored procedure. Can you please give coding for this.
johnjerry
- Oct 7th, 2009
Make sure you do not COMMIT* in the middle of the procedure. You can do any number of insertion into and deletion from temporary table and use "Select * from temp_table" to fetch a...
priyanka jolly
- Nov 24th, 2007
Create Global temporary tables and say on commit preserve rows. The global temporary tables have data available till the session is active. So you if you insert it at one procedure and want to access it somewhere else you can do that very easily till the session is activeThanks Priyanka
-
Oracle - Could not find the Program unit
While i compiled my code , it got compiled but while running my application i got the Oracle error "Could not find the Program unit ? "Can any one tell exactly why this happens.I had validated all the objects and even released all the Locked objects . But still this error persisted .
binurajnair
- Feb 20th, 2008
This error can occur in the following scenario also. The application has a constant connection with the database and a package which the application uses gets recompiled. And the recompiled package co...
raj_imr2000
- Jan 15th, 2008
Hi,ORA-06508:PL/SQL: could not find program unit being calledCause:An attempt was made to call a stored program that could not be found. The program may have been dropped or incompatibly modified, or ...
-
Passing the parameter to TOP keyword
Hi All,I want create a stored procedure by passing the parameter to top nth recordfor Excreate proc test1(@x integer)asbeginselect top @x empid from emp order by empid descendwhen iam executing iam getting error .Please let me know any other alternative way
shruti.potdar
- Dec 10th, 2008
Solution to your problem:Create Procedure TEST1( p_num number)isv_eid number;begin?? select empid into v_eid from EMPwhere empid=(select A.empid from (SELECT empid, rank() over (order by empid desc) RNK FROM emp) A where A.RNK=p_num);dbms_output.put_line('Top'|| p_num || 'emp is ::'|| v_eid);End;/
xambrose
- Jul 19th, 2008
create proc test1(@x integer)asbeginSELECT empid FROM (SELECT empid FROM emp ORDER BY empid ) WHERE ROWNUM = @x;end
-
ORA-01756: quoted string not properly terminated
Hi Can anyone explain what this mean? I have practise PL/SQL about REF obj_type, my codes are: CREATE or REPLACE TYPE home_type AS OBJECT (street VARCHAR2(50), city VARCHAR2(20), state VARCHAR2(20), zipcode VARCHAR2(6), owner VARCHAR2(10));/CREATE TABLE homes OF home_type;INSERT INTO homes VALUES ('300 Regina St', 'WATERLOO', 'ON', '010010', 'HFWANG');INSERT INTO homes VALUES ('301 Regina St', 'WATERLOO',...
priti
- Nov 11th, 2019
Kindly let me know what do you want in result using ref cursor? here you ref cursor as a table which is not exists in system. that is what it is giving error
-
Temporary Tables
How to create temporary table? Give some example
venkat82.k
- Mar 20th, 2008
Hi guys........Temporary tables means which tables are hold data temporarlyi mean these holds the data certain time mainly only that session.exp:-create global temporary table employ(emp_id number(10)...
-
Long Raw Columns
How many columns you can create in table from this type "long raw"?
madhupriya.nukala
- Jun 25th, 2008
Only single column is allowed per a table of data type LONG or LONG RAW
-
Referenced and Dependent objects
How oracle manages dependency between referenced and dependent objects ?
johnjerry
- Oct 7th, 2009
We can set REMOTE_DEPENDENCIES_MODE inside init.ora parameter file to TIMESTAMP or SIGNATURE.ALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE = [TIMESTAMP|SIGNATURE]
YugundharK
- Oct 13th, 2008
Oracle manages dependency between referenced and dependent objects by using 2 modes1. Timestamp mode2. Signature mode
-
Paging In Oracle
Hi All,I have requirement of sending the records based on the input received from web.I have a ref_cursor returning 30 rows..Here I am supposed to do paging on the set of records present in the above ref cursor.If web gives 1 , then I should send first 10 records in the ref cursor..If gives 2 , then I should send next 10 records in the ref cursor..similarly�..I will get some sort of indicator from...
muraligrandhi
- Jun 9th, 2010
With the help of Ref cursor you can get particular no of records(means 1=10 records,2=20 records like that), for that you can use rownum in the filter condition.If you add below query in Ref cursor yo...
pvidhya
- Sep 3rd, 2008
First you have to filter the no of records you need totally (i.e., if you receive input as 2 and if the no of records you need to return are 20, then you need to filter 20 rows) using where rownum
-
RollForward
What is the rollforward in pl/sql
goelumesh
- Jul 1st, 2008
Roll forward refers to the process Oracle goes through to apply changes contained in the redo log files (both online and archive). The database clock (as measured by the system change number) is moved...
-
Return statement and OUT Parameters
What are Return Statement and OUT Parameter in PLSQL Function?
Database Steve
- May 20th, 2009
Functions always return a value.Procedures do not.The use of IN, OUT, and IN_OUT parameters as scalar values or ref cursors is dependent on your need and overall performance not on a pseudo-standard as answered above.
KD09714
- Jun 28th, 2008
Function must have return statement by which it returns one value.Though we can use out parameter in function(function not getting called from select statement or DML), it is not good programming prac...
PL/SQL Interview Questions
Ans