How to create temporary table? Give some example

ravi.8311

 Profile Answers by ravi.8311 Questions by ravi.8311

• Jun 28th, 2008

Create table temp as select*from base table where 1=1;

venkat82.k

 Profile Answers by venkat82.k Questions by 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)...

Answer Question  Select Best Answer

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Oct 20th, 2006

You should put your question here.  This forum is not a library.  Please take care.

Answer Question  Select Best Answer

How to skip columns while loading data with SQLLDR

lokeshRao

 Profile Answers by lokeshRao Questions by lokeshRao

• Jun 23rd, 2008

Specify Filler Load datatruncate into table empfields treminated by'|'(field1,field2 filler,field 3)

Answer Question  Select Best Answer

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by 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

 Profile Answers by ravgopal Questions by 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 ...

Answer Question  Select Best Answer

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• Jun 23rd, 2008

Pragma is a compiler directive. It passes information to compiler. These are processed at compile time not at runtime.             &nbs...

rajakumar_na

• Nov 2nd, 2007

Pragma is a compiler directive. Using SQLCODE & SQLERRM we know that which ERROR code is associate to what error.

Answer Question  Select Best Answer

Hi,There is problem with ult_file.fopen. When I run from the server side, there is no issue with utl_file and it is working fine.If I run the same procedure from Client machine, I am facing problem. It throws an exception Invalid_operation.I am using Oracle 8.2 and Unix server.Can you please let me know what change needs to be done? Is this related to permission issue.Thanks & Regards,Ananth P

stshekhar

 Profile Answers by stshekhar Questions by stshekhar

• Jun 22nd, 2008

the parameter set in configuration file init.oraand UTL_FILE_DIR belongs to the directory structure in oracle server machine. And the directory structure should be on same machine as oracle server. Th...

Answer Question  Select Best Answer

Database has 100 tables and some of the tables dont have any data. I want to pullout those table names from database in Oracle.

sr_2407

 Profile Answers by sr_2407 Questions by sr_2407

• Jun 4th, 2008

Select  table_name ,num_rows from user_tableswhere num_rows=0

ravgopal

 Profile Answers by ravgopal Questions by ravgopal

• Oct 10th, 2007

Analyze and perform statistics on whole schema.  This will populate the statistics details about each table.  You can pull NUM_ROWS column from all_tables to check whether it ...

Answer Question  Select Best Answer

What are the types of cursors apart from explicit and implicit cursors ? Explain when and where they are used ?

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by 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

Answer Question  Select Best Answer

What exactly the difference between 'TABLE OF' and 'REF CURSOR' in PL/SQL?For what purposes these both might be used?

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• Jun 1st, 2008

A ref cursor is a dynamic cursor in which the contents of cursor can be changed dynamically at run time depending upon our requirement.A ref cursor is basically a data type. A variable declared based ...

Answer Question  Select Best Answer

Can we issue rollback, commit in the trigger body. if we issue what is the result

Read Best Answer

Editorial / Best Answer

binurajnair  

 Profile Answers by binurajnair Questions by binurajnair

• Member Since Feb-2008 | Feb 28th, 2008

---

      An autonomous transaction is an independent transaction which can be committed independent of other transactions. An autonomous transaction will be committed with out committing the other non-autonomous transactions are committed.

The following exercise will help you to understand the difference between autonomous and normal transactions.

Create a table for the exercise.

create table test_table (a varchar2(50));

Create a non-autonomous procedure which will insert one row to this table.

CREATE OR REPLACE PROCEDURE non_autonomous_proc
IS
BEGIN
   INSERT INTO test_table
   VALUES      ('Non Autnomous Insert');

   COMMIT;
END;

Now execute the following code

DECLARE
BEGIN
   INSERT INTO test_table
   VALUES      ('Before Non Autnomous Insert');

   non_autonomous_proc;

   INSERT INTO test_table
   VALUES      ('After Non Autnomous Insert');

   ROLLBACK;
END;

Now query the table

select * from test_table;

As expected 2 rows will be retrieved

A
--------------------------------------------------
Before Non Autnomous Insert
Non Autnomous Insert

Now we will see the case of autonomous transaction.

Clear the test_table

delete from test_table;
commit;

Create an autonomous procedure which will insert one row to this table.

CREATE OR REPLACE PROCEDURE autonomous_proc
IS
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO test_table
   VALUES      ('Autnomous Insert');

   COMMIT;
END;

Now Execute the following code

DECLARE
BEGIN
   INSERT INTO test_table
   VALUES      ('Before Autnomous Insert');

   autonomous_proc;

   INSERT INTO test_table
   VALUES      ('After Autnomous Insert');

   ROLLBACK;
END;

And query the table

select * from test_table;

This will show only one row

A
--------------------------------------------------
Autnomous Insert

Conclusion

1) The commit statement in the autonomous procedure will commit the DML operations in the autonomous procedure without commiting the transactions before that.

2) The rollback statement caused both the inserts before and after the autonomous transaction to be rolled back, but not the autonomous transaction

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• Jun 1st, 2008

We can use commit and rollback in trigers using autonomous transactions.- An atonomous transaction is an independent transaction initiated by another transaction.- When an atonomous transaction is cal...

arbind1982

 Profile Answers by arbind1982 Questions by arbind1982

• Mar 12th, 2008

we can use commit or rolback in trigger without error by using "PRAGMA AUTONOMOUS_TRANSACTION;" on declaration sectionCREATE TRIGGER anniversary_triggerBEFORE INSERT ON employees FOR EACH RO...

Improve Answer

ashishdixit

 Profile Answers by ashishdixit Questions by ashishdixit

• May 29th, 2008

desc has two meanings and depends on the usage.when used in sql plus as in the below stt.desc <tablename>;it is taken as a sqlplus keyword and describes the table, dispalying column, constraints...

Sebastian Thomas

• Aug 17th, 2007

DESC will display the values in DESCending order when used in an ORDER BY clause.For example:SELECT * FROM empORDER BY empno DESC;

Answer Question  Select Best Answer

How will implement the PL/SQL tables?

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• May 29th, 2008

- Pl/sql is in memory representation of a table.- It is just a data type. ( Collection type)- It may store any number of rows from table.Here is  a simple exampleCREATE OR REPLACE PROCEDURE TEST_...

tonyrobert

 Profile Answers by tonyrobert Questions by tonyrobert

• Mar 27th, 2008

INDEX By Tables composed of two components1.? Primary key of data type BINARY_INTEGER2.? Column of scalar or record data type.Since this table is unconstrained, it can increase its size dynamically....

Answer Question  Select Best Answer

Give an example for calling procedure with user and system exception

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• May 29th, 2008

Simple example for predefined exception CREATE OR REPLACE PROCEDURE TEST_PROC AS A NUMBER ; BEGIN A := 'TEST'; DBMS_OUTPUT.PUT_LINE('THE VALUE OF A = '||A); EXCEPTION WHEN VALUE_ERROR ...

ravenkumar

 Profile Answers by ravenkumar Questions by ravenkumar

• Mar 17th, 2008

set serveroutput on;create or replace procedure proc1 (emp_id in emp.employee_id%type, emp_na out emp.last_name%type, emp_sal out emp.salary%type, emp_job out emp.job_id%type) is &...

Answer Question  Select Best Answer

begin For i in 1..5 loop insert into A values(i); savepoint 1; end loop; rollback to savepoint 1; commitend;--initially there are no data in table A. So my question is after execution of this block what should be the data present in table A?

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• May 29th, 2008

The first mistake in this code is savepoint 1;You can't give 1 as identifier. Identifier name must begin with a letter.insert into A values(i);savepoint x;Here you are defining save...

satyam_Ora

 Profile Answers by satyam_Ora Questions by satyam_Ora

• May 6th, 2008

The flow of execution will be as below:step1: loop will execute 5 times and the variable value(i) will store in the table a in series of values from 1 to 5.step2: all 5 values in series from 1..5 w...

Answer Question  Select Best Answer

ashishdixit

 Profile Answers by ashishdixit Questions by ashishdixit

• May 28th, 2008

Exception is equivalent of an error in the code.This may be due to incorrect data or wrong coding techniques. What ever the reason may be,when an error occurs in the plsql code, the command terminates...

praveen.pinisetti

 Profile Answers by praveen.pinisetti Questions by praveen.pinisetti

• Apr 6th, 2006

There is a lot to explain the execution of exceptions.Whenever error is occured,the corresponding exception will be raised and handled in Exception block.But in the case of nested blocks,The exception...

Answer Question  Select Best Answer

          I.  done using Database triggers.          ii. done using Integarity Constraints. I & ii. Exception :

Srinatha Reddy

 Profile Answers by Srinatha Reddy Questions by Srinatha Reddy

• May 21st, 2008

create table a ( b number, c number check (c >100) );create or replace trigger t1 before insert on a begindbms_output.put_line('this is before insert trigger'); end;create or replace trigge...

prashant

• Jul 6th, 2006

 ii. done using Integarity Constraints

Answer Question  Select Best Answer

mosam

 Profile Answers by mosam Questions by mosam

• May 21st, 2008

U can not create thr private packages in oracle becoze packages can not be nasted

KiranKW

 Profile Answers by KiranKW Questions by KiranKW

• May 3rd, 2007

hi ,  i dont think there is something called as public package / private package. But yes the methods defined in a package can either be public or private.Below is a simple expln.Variables and co...

Answer Question  Select Best Answer

 Declarative part, Executable part and Exception part. Datatypes PL/SQL

satyam_Ora

 Profile Answers by satyam_Ora Questions by satyam_Ora

• May 14th, 2008

All has given the right answer and I am repeating the same.There are the three component of PL/SQL program block1) Declarative( Where you can decalre the variable which would be used as a part of exce...

g_sidhu

 Profile Answers by g_sidhu Questions by g_sidhu

• Feb 16th, 2008

A PL/SQL block consists of up to three sections: declarative (optional), executable (required), and exception handling (optional).

Answer Question  Select Best Answer

satyam_Ora

 Profile Answers by satyam_Ora Questions by satyam_Ora

• May 8th, 2008

Please remove the commit from the begin statement and the it will execute properly.Thanks,

satyam_Ora

 Profile Answers by satyam_Ora Questions by satyam_Ora

• May 8th, 2008

DeclareCursor c1 is select * from emp FORUPDATE;Z c1%rowtype;BeginOpen C1;Fetch c1 into Z;Commit;Fetch c1 in to Z;end; SQL> start e1 Declare * ERROR at line 1: ORA-01002: fetch out of sequence...

Answer Question  Select Best Answer

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• May 13th, 2008

Using wrap utility we can encrypt source code of a function  to byte code (Not a readable format).  Becareful there is no  decrypt utility to convert  source co...

nitingalande

 Profile Answers by nitingalande Questions by nitingalande

• Jan 13th, 2008

Function can be encrypted using Oracle Wrap utility. this will prevent accessing from users without permission

Answer Question  Select Best Answer