-
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...
-
Writing Pl/SQL function
Write a PL/SQL fuction named say TRUN, which does the same work as the oracle predefined function "TRUNCATE".
ajaz_11i
- Jul 9th, 2008
It will truncate/delete all the rows in the table emp very fast as compared of delete command, but the table must have no Foriegn keyMr. Ajaz Ahmad Kumar
sgbang
- Jul 7th, 2008
Hi,Please check out this function:create or replace function trun(p_table_name varchar2) return number asbeginexecute immediate ' truncate table '|| p_table_name;return 0 ;end; /How to ex...
-
Is the order in which the conditions are given in the 'WHERE' clause are important?
Mean I heard from Oracle 8i onwards the order of where clause condition does not matter which oracle creates the explain plan...and execute..Is it true?
manoj_behera82
- Aug 7th, 2008
Yes the order is important. But it is important for the performance of the query?and not for the final result. The output of the query will be the same irrespective of the order in which the join con...
gtomar
- Aug 7th, 2008
Hi, Where Clause conditions execution are independent of order in explain plan but it reads top to bottom.Thanks,Gunjan
-
How to take DATE as user input in PL/SQL block?
raghuprasad
- Aug 25th, 2008
Inputting the date in PL/SQL is same as in SQL. Simply pass the date as a string in the format DD-MON-YYYY.Eg: SELECT * FROM TABLE WHERE HIRE_DATE='dd-mon-yyyy'.OrYou can also use Date functions TO_DATE('25-aug-2008','......date formate......');Hope this is the answer, you are looking for.Raghu.
rajivgupta780184
- Aug 22nd, 2008
Create or replace function get_period (pDate varchar2) return varchar2is pday varchar(20);begin SELECT TO_CHAR(to_date('&DATE','MM/DD/YYYY'),'DAY') into pday FROM DUAL;return pday;end get_period;to call the function select get_period(08/03/2008) from dual;
-
Oracle PRAGMA'S
Outline the List of PRAGMA'S used in Oracle.
amrapali
- Apr 21st, 2012
PRAGMA The instruction is a statement that provides some instructions to the compiler. Pragmas are defined in the declarative section in PL/SQL. The following pragmas are available: AUTONOMOUS_TRA...
bobbyiiit
- Apr 9th, 2010
PRAGMAPragma is a keyword in Oracle PL/SQL that is used to provide an instruction to the compile.Types Of Pragmas :-1 - AUTONOMOUS_TRANSACTION2 - EXCEPTION_INIT3 - RESTRICT_REFERENCES4 - SERIALLY_REUSABLE
-
Autonomous Transcation
What are advantages of Autonomous Transcation?
raghunadhn
- Mar 3rd, 2010
Autonomous transactions are independent of the main transactions.You can commit the changes in the other tables using these transactions, even though the main transaction is not a success. Depending on one transaction, you can commit/rollback the other transactions.
namita04
- Dec 23rd, 2009
A autonomous transaction starts with the first SQL statement of the PL/SQL block and ends with a commit.
-
Handle Multiple Rows Without Loops
How can we handle multiple rows without using loops in PL SQL?
sen_sam86
- Aug 18th, 2009
This can be done by a function called TABLE FUNCTION by means of this you can return more number of rows and columns this is more effective than using ref cursorregards sen-prakash
rajivgupta780184
- Oct 31st, 2008
If we want to return multiple row from a function then we can use ref cursor function .Example is given below: create or replace function f1 return sys_refcursor as r1 sys_refcursor;begin open r1 fo...
-
Infinite Loop
How will you stop an infinite loop without closing the program?
muraligrandhi
- Jun 7th, 2010
For terminating the INFINITE LOOP we can use EXIT statement.Example: LOOP monthly_sales:=daily_sales*31; EXIT when monthly_sales > 3000; END LOOP;
joe0082
- Apr 13th, 2009
Use the?EXIT statementThis will stop the looping and proceed to the next executable statement.or EXIT WHEN condition
-
Recreating and Rebuilding Indexes
What is recreating and rebuiding of indexes?
raghunadhn
- Mar 3rd, 2010
Rebuilding IndexSyntax: Alter index index_name rebuild.If you rebuild the index, the existing index will be dropped and created immediately, and any overheads existing on the index wil be removed. The...
-
SQL Optimization
How to optimize SQL Statements?
Sudhir Kumar
- Jan 20th, 2016
Try to make use of mqts or temporary tables if possible ..try to transfer the conditions from non key columns to key columns
ace1234567
- Mar 4th, 2015
update the statistics of all the tables used in the query. Check the explain plan. Check whether query is using the existing indexes. If required add/modify new indexes. Check for the sequence of cond...
-
Types of Lock
Explain share and exclusive type of lock.
hmounir
- Jan 25th, 2010
Oracle uses two modes of locking in a multi-user database:Exclusive lock mode (X) prevents the associates resource from being shared. This lock mode is obtained to modify data. The first transaction t...
rahulmca
- Jan 24th, 2010
The two basic types of locks are DML (table) locks, and DDL (dictionary) locks. DML locks are designed to guarantee data integrity in a multi-user environment. DML locks prevent conflicting DML and DD...
-
Execute Immediately
What is the meaning of execute immediately?
hmounir
- Jan 25th, 2010
EXECUTE IMMEDIATE is the replacement for DBMS_SQL package from Oracle 8i onwards. It parses and immediately executes a dynamic SQL statement or a PL/SQL block created on the fly. Dynamically created a...
Brajesh Singh Bhadoriya
- Jan 25th, 2010
Execute immediate is used to prepare dynamic SQL. There might be scenario where one has to prepare the SQL statement at run time in a PL/SQL object. Execute immediate requires one SQL statement which ...
-
Rectify Deadlock
If a deadlock has occured and if you have given with the dump, how will you identify that where the deadlock has occured, how the deadlock occured and how to rectify the same.
kari
- Oct 16th, 2011
the steps necessary to identify and rectify code causing deadlocks are: * Locate the error messages in the alert log. * Locate the relevant trace file(s). * Identify the SQL statements i...
skumar.t15
- Aug 5th, 2010
Each user session might have one or more tasks running on its behalf where each task might acquire or wait to acquire a variety of resources. The following types of resources can cause blocking that c...
-
Unconstrained Loop
What is unconstrained loop?suggest some example.
vikz.pal
- Jan 23rd, 2011
Unconstrained loop means loop that can't be handled as per our will.Basic loop is a simple example of unconstrained loop - if we ignore proper exit statement in basic loop then it becomes an infin...
PL/SQL Interview Questions
Ans