I have 3 tables , same no of columns ,i want to insert whole the data from all these table into a single table,which also having same no of column and the same data type.

sateeshchandra

• Oct 14th, 2014

"sql INSERT ALL INTO suppliers (supplier_id, supplier_name) VALUES (1000, IBM) INTO suppliers (supplier_id, supplier_name) VALUES (2000, Microsoft) INTO customers (customer_i...

J. Ali (PL/Sql)

• Nov 29th, 2013

INSERT INTO DYMMY (VAL)
SELECT COLUMN_VALUE val FROM table (tab_contents_nt (1, 2, 3))
UNION ALL
SELECT COLUMN_VALUE val FROM table (tab_contents_nt (4, 5, 6))
UNION ALL
SELECT COLUMN_VALUE val FROM table (tab_contents_nt (7, 8, 9))

Answer Question  Select Best Answer

Procedure also written the values using out parameter than why we need functions?

Rakesh057

 Profile Answers by Rakesh057 Questions by Rakesh057

• Jul 1st, 2014

Because procedure is pre compiler and function is not pre compiler so that the reason we go for an calculation part in function

Madhav

• Jul 1st, 2014

Procedure also written in out parameter but we can not use procedure in Sql statement.

But if we wrote function and ita cant contain DML operation then we can access this function in SQL queries.

Answer Question  Select Best Answer

Delete duplicate records without using sub query? Is That possible

Aak

• Sep 15th, 2016

Use a temp table, store distinct rows. Either delete the old table and rename this new table with the old table name or truncate old table and insert rows from temp table to the old table.

kadari ashok

• Jul 14th, 2016

We can do through co-related sub query

Answer Question  Select Best Answer

Eltonclardy

 Profile Answers by Eltonclardy Questions by Eltonclardy

• Jan 9th, 2015

Yes PL/SQL have different data types compared to C and C++. These two datatypes in SQL i.e scalar and composite are very mush useful in SQL.

Hema nagaraju

• Dec 20th, 2014

Scallar,composite datatypes.
scllar datatypes are varchar2,number
composite datatypes are records,tables,collections which can store multiple values at a time

Answer Question  Select Best Answer

If i insert record in table A and these record should update in table B by using Trigger.How to achieve this.
Trigger - insert into one table update into another table

Nagendra

• Sep 10th, 2015

"sql CREATE TRIGGER update_trigger ON CUSTOMER_info FOR update AS BEGIN IF UPDATE (cust_name) OR UPDATE (contact) BEGIN INSERT INTO update_log ( cus...

Avinash

• Aug 10th, 2015

We can use instead of trigger in this case

Answer Question  Select Best Answer

Ram Devineni

• Nov 3rd, 2015

Alter session set nls_date_format=mon-dd-yyyy hh:mi:ss
/

Purvesh Khanna

• May 5th, 2015

If you are using sql developer then go to Tools ->> Preference ->> Database ->> NLS -->> Date Format.
Send the format as you want.

Answer Question  Select Best Answer

If I create N number of small procedures with one PL/SQL block (or) procedure why we need package?

Prasad Bagaregari

• May 11th, 2018

An embedded object where all the required units can be in one place.

Tarika Bhutani

• May 25th, 2017

Whenever we run any subprogram of a package the whole package get loaded in SGA. Which actually reduces the number of context switching and hence increases speed and better performance. Whereas in procedures we the context switching is more. Procedures are useful for writing small modules.

Answer Question  Select Best Answer

 Raise_application_error  is  a  procedure  of  package  DBMS_STANDARD which allows  to  issue an user_defined error messages from stored sub-program or database trigger.

Ashutosh

• Oct 8th, 2012

Their classes of errors and diff. errors are divided among these classes simply because of ease in classification, For defining errors and error message with raise_application_error procedure by users...

SARANYA

• Jul 25th, 2011

I know that the error no in raise_application_error are non standard but tell me on what basis we are giving those numbers? is that simply a number or it some implicit meaning ?

Answer Question  Select Best Answer

g_sidhu

 Profile Answers by g_sidhu Questions by g_sidhu

• Jan 31st, 2008

In addition to modularizing application development, stored procedures and functions have thefollowing benefits:1) Improved performanceAvoid reparsing for multiple users by exploiting the shared SQL a...

deviji2000

 Profile Answers by deviji2000 Questions by deviji2000

• Oct 16th, 2007

procedures and function hv many benefits in addition to modularizing application development.1)          easy maintenance  :  sub programs are lo...

Answer Question  Select Best Answer

     a. Stored procedure or anonymous block     b. an application program such a PRC *C, PRO* COBOL     c. SQL *PLUS      a. PACKAGE NAME.PROCEDURE NAME (parameters);        variable := PACKAGE NAME.FUNCTION NAME (arguments);        EXEC SQL EXECUTE    ...

shi_fu

 Profile Answers by shi_fu Questions by shi_fu

• Nov 30th, 2010

a. procedure is called by its name:pack_name.proc_name;function is called from a expression:a=b*(function(param));b.(same as above)procedure is called by its name:pack_name.proc_name;function is calle...

g_sidhu

 Profile Answers by g_sidhu Questions by g_sidhu

• Feb 17th, 2008

a. PACKAGE NAME.PROCEDURE NAME (parameters);     variable := PACKAGE NAME.FUNCTION NAME (arguments);        b.     ...

Answer Question  Select Best Answer

Baji Shaik

 Profile Answers by Baji Shaik Questions by Baji Shaik

• Jun 5th, 2011

Strong ref cursor return value, but seek ref cursor it can't return any value

askvenki

 Profile Answers by askvenki Questions by askvenki

• Jul 20th, 2007

           A strong REF CURSOR type definition specifies a return type, but a weak definition does not.DECLARE   TYPE EmpCurTyp IS REF ...

Answer Question  Select Best Answer

Art11

 Profile Answers by Art11 Questions by Art11

• Sep 27th, 2011

A REF Cursor is a datatype that holds a cursor value in the same way that a VARCHAR2 variable will hold a string value. It is not a cursor, but a variable that points to a cursor. It is not limited t...

sunil

• Jul 26th, 2011

CURSOR A cursor is a variable that runs through the tuples of some relation. This relation can be a stored table, or it can be the answer to some query. A cursor can be used in realtime when u nee...

Answer Question  Select Best Answer

Guest

• Jan 16th, 2007

A forward declaration looks like the package definition part but is inside the bode.e.g.procedure A isbegin  B;end A;procedure B isbegin  null;end B;will not work, because during call to B B...

Donald H. Kirschman

• Dec 27th, 2006

Scalar data types store one single element of data, as opposed to composite data types.  This is not a concept that is unique to Oracle PL/SQL; rather, it is common to any programming language.&n...

Answer Question  Select Best Answer

g_sidhu

 Profile Answers by g_sidhu Questions by g_sidhu

• Feb 1st, 2008

Cursor variables are like C or Pascal pointers, which hold the memory location (address) of some item instead of the item itself. Thus, declaring a cursor variable creates a pointer, not an item. In P...

nisha

• Aug 24th, 2006

ref cursor is a simple cursor but it is a datatype that allow developers to declare cursor variableref cursor has 2 types:-strong ref cursor where we mention the return type with rowtype.weak cursor w...

Answer Question  Select Best Answer

lieni

• Jul 30th, 2007

Yes you can pass the whole select statement of the cursor dynamicaly, see ref_cursors.There is also a system package sys.dbms_sql which allows everything to make dynamically.

CHINMAY

• Jun 28th, 2007

Yes,You are right we can only pass the value for where clause in paramterised cursor

Answer Question  Select Best Answer

Pixie

• Feb 18th, 2014

PL/SQL tables are PL/SQLs way of providing arrays. They are temporary tables in memory and thus are processed very quickly. They are not database tables, and DML statements cannot be issued against...

sbagai2001

 Profile Answers by sbagai2001 Questions by sbagai2001

• May 30th, 2006

DECLARE   TYPE EmpCurTyp IS REF CURSOR;   // Reference Cursor - weak    TYPE NumList IS TABLE OF NUMBER;  // Table of Number   TYPE NameList IS TABLE OF VA...

Answer Question  Select Best Answer

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• May 3rd, 2008

You can use either of them . Both are same.

amahanit

 Profile Answers by amahanit Questions by amahanit

• Oct 13th, 2007

Both are same

Answer Question  Select Best Answer

Ashwini

• Dec 23rd, 2006

Cursor Variable RestrictionsCursor variables are subject to the following restrictions; Oracle may remove some of these in future releases. Cursor variables cannot be declared in a package since they ...

rashid

• Sep 20th, 2006

hi friend cursor variable can't use with dynamic sql.cursor variable can't use with in package because it is not having persistent state.nulls can't assinged to cursor variable.remote procedure can't accept cursor variable

Answer Question  Select Best Answer

gkbiswal

 Profile Answers by gkbiswal Questions by gkbiswal

• Oct 21st, 2007

It(autonomous transaction) will work but it'll not satisfy your business requirement. Mutating error is not actually an error but Oracle has provided this feauture to do away with any si...

trupti

• Feb 13th, 2007

Hi, you can use pragma autonomous_transaction for mutating trigger ie after declare, the first statement is pragma autonomous_transaction.
try it,
it really works!!

Answer Question  Select Best Answer

g_sidhu

 Profile Answers by g_sidhu Questions by g_sidhu

• Feb 19th, 2008

Triggers on system events can be defined at the database or schema level. A trigger defined at the database level fires for all users, and A trigger defined at the schema or table level fires only when the triggering event involves that schema or table.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 5th, 2006

In database trigger:  Trigger for a table /view  is a database triggerlike ( before , insert  * update, delete, insert * row level, statement level)2 * 3 * 2 = 12  + instead off tr...

Answer Question  Select Best Answer