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

What is the difference between a package and a module?

tariq_oracle

 Profile Answers by tariq_oracle Questions by tariq_oracle

• Apr 13th, 2008

Module is a collection of Package

Answer Question  Select Best Answer

What is the difference between Nested Table and Normal Table?

garvi.1983

 Profile Answers by garvi.1983 Questions by garvi.1983

• Mar 5th, 2008

Normal Table : It contain rows and columns.Each Row-Column represente only one value i.e intersection of Row-Columncreate table t1(col1 number(10), col2 varchar2(9));------>Creates normal tabl...

Answer Question  Select Best Answer

What are analytical functions and how are they used?

singh.neerajin

 Profile Answers by singh.neerajin Questions by singh.neerajin

• Jul 22nd, 2008

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and ...

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

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

How can i schedule a procedure every day at 10 pm

rajivgupta780184

 Profile Answers by rajivgupta780184 Questions by rajivgupta780184

• Aug 13th, 2008

This is the sample code you have to adjust time according to your requirementSET AUTOPRINT ONVAR jobno NUMBER;BEGIN DBMS_JOB.SUBMIT(:jobno, 'JOBNAME;', TRUNC(SYSDATE) + 6/24, 'TRUNC(SYSDATE + 1) + 6.183/24'); COMMIT;END; ThanksRajiv
GuptaYogik Technologies Pvt. Ltd.

Answer Question  Select Best Answer

How can i choose which query is best?i.e On which factors i can choose the best query?

madanuprashanth

 Profile Answers by madanuprashanth Questions by madanuprashanth

• Jul 18th, 2008

By forcing the SQL engine to use indexes.If query is too much nested then break it into two or three queries using temp tables and then write one query by joining these temp table to perform the task.

Answer Question  Select Best Answer

How to fill a refcursor with dataset in C#.i want to pass a dataset as parameter and fill a refcursor with the same dataset.

viv_mca

 Profile Answers by viv_mca Questions by viv_mca

• Jul 31st, 2008

you need to use ODP.Net connector.In OracleCommand object add OracleParameter object with following propertiesOracleDbType = OracleDbType.RefCursor;Direction= ParameterDirection.Output;give the name of your stored procedures's refcursor parameter name

Answer Question  Select Best Answer

How to load whitespace delimited data using SQL*Loader?

psingla

 Profile Answers by psingla Questions by psingla

• Apr 21st, 2010

1. Create a ontrol file with extn (.ctrl). This file will map the flat file data with the Oracle table columns.2. Pass control file as one of the parameter of SQL Loader.

Answer Question  Select Best Answer

Define External Table

lanka_satya

 Profile Answers by lanka_satya Questions by lanka_satya

• Sep 12th, 2008

it is same as oracle table but act as view to the data available in an external file of a physical drive,so here we can query the data and retrieve and can do anything but cannot do manupulations on the data

Answer Question  Select Best Answer

How to call a PL/SQL function in another PL/SQL function? Is it possible? Give example?

Jirange

 Profile Answers by Jirange Questions by Jirange

• Nov 7th, 2008

Eg,create or replace fun_1 return varchar2asddmmyy varchar2;beginselect sysdate into ddmmyy from dual;end fun1;in above example function fun_1 calling another function (i.e. sysdate) thro' select statement. this way you can call any function in pl/sql function

Answer Question  Select Best Answer

How to send Email via PL/SQL Procedure?

rajivgupta780184

 Profile Answers by rajivgupta780184 Questions by rajivgupta780184

• Nov 24th, 2008

rem -----------------------------------------------------------------------rem Filename:   smtp.sqlrem Purpose:    Send e-mail messages from PL/SQLrem Notes:   ...

Answer Question  Select Best Answer

While tuning SQL query what should be taken into consideration?

edarasatish

 Profile Answers by edarasatish Questions by edarasatish

• Jun 23rd, 2009

During the SQL tuning, there couple of things need to be considered1. If it is below Oracle 9i we need make sure about the driving table concept (Since it uses RBO - Rule Based Optimization), which im...

Answer Question  Select Best Answer

What Is BulkBind? When it is used?

saishradha

 Profile Answers by saishradha Questions by saishradha

• Nov 27th, 2008

Bulk Bind is the facilty through which we can INSERT, UPDATE, DELETE or SELECT from records from a table in bulk rather than multiple individual statments..Suppose you have 10 inserts to be made so yo...

Answer Question  Select Best Answer

When we insert new values into the table using loops. Is it going to be auto commit or do we need to commit manually?

chaitra murthy

 Profile Answers by chaitra murthy Questions by chaitra murthy

• Mar 19th, 2009

All the DML statement requires manual commit where as DDL statements are autocommitted.

Answer Question  Select Best Answer

How to call a Procedure in Trigger and if any exception occurs in trigger then How do we rollback it?

Ayaz Uddin

• Nov 20th, 2014

Create or replace trigger trg
after insert on emp
for each row
begin
myproc(:new.empno, :new.ename);
end;

Answer Question  Select Best Answer

How to access cursor output from web application and present the record set in tabular form?

ace1234567

 Profile Answers by ace1234567 Questions by ace1234567

• Mar 4th, 2015

Using ref cursor and returning output in the plsql table we can return the result in tabular format.

Answer Question  Select Best Answer

How to select multiple data from different tables which do not have related constraints?

bhagavatula

 Profile Answers by bhagavatula Questions by bhagavatula

• Dec 10th, 2009

It is not necessary to have 'related constraints' between tables to retrieve data from multiple tables.  However, there should be a logic to associate data between the tables (related data) based on which you can select information from multiple tables

Answer Question  Select Best Answer

If you have to call same piece of code at many places in a package, How do you achieve this without calling the function many times?

kanchanG

 Profile Answers by kanchanG Questions by kanchanG

• Jul 7th, 2010

We can use 'Go To' statement in this case.

Answer Question  Select Best Answer