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

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

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

Answer Question  Select Best Answer

ravgopal

 Profile Answers by ravgopal Questions by ravgopal

• Oct 10th, 2007

We can do either DDL or DML inside a function.1) Let us try keeping a DML inside a function  1  create or replace function test (a number) return number as  2  begin  3  ...

vaibhav Tyagi

• Sep 26th, 2007

The main difference is in Function you can perform only DDLbut in Stored
procedure you can perform DDL, DML both

Answer Question  Select Best Answer

amahanit

 Profile Answers by amahanit Questions by amahanit

• Oct 13th, 2007

1) make the trigger as a statement level trigger.2) if u want to use :old and :new qualifer then create 2 trigger ,1st before insert trigger and use PL/SQL table to select the values,2nd after insert trigger and insert the values from PL/SQL tables.

be17be

 Profile Answers by be17be Questions by be17be

• Dec 1st, 2006

Ashish, can you please give an example?

Answer Question  Select Best Answer

 A  cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.A  cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.

divyesh

• Oct 13th, 2007

Ref cursors are used when you want cursor to hold any no. of columns in the row.If you are not sure about row type at complile time you use ref cursors.Sys_ref_cursors used when you want cursors to be passed from one procedures to another procedures or functions.

ddkdhar

 Profile Answers by ddkdhar Questions by ddkdhar

• Feb 13th, 2007

If we declare in package body then what is difference b/w cusrsor..........?how many types of refcursors..............?whata re the advantages of ref cursors....................?

Answer Question  Select Best Answer

manish dudhe

• Oct 15th, 2007

Hi,User Ref cursor as out parameter in procand execute as belowsql>var s refcursorsql>exec demo_proc('manish',:s);sql>print s

Rama Krishna,TCS

• Aug 9th, 2006

Hi Mukund!!       You have to return the selected cursor as OUT parameter and again you have to fetch the data from that OUT cursor..Thanks&RegdsRamki

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

Sumeet Lalvani

• Oct 30th, 2007

Simple statement....beginselect sysdate from into? A dual;dbms_ouput.put_line(A);end;

vasu

• Jun 28th, 2006

Declare x timestamp;begins select localtimestamp into x from dual;dbms_output.put_line(x);end;

Answer Question  Select Best Answer

prabhatushar

 Profile Answers by prabhatushar Questions by prabhatushar

• Oct 31st, 2007

ORA - 01426 is associated with Numeric  overflow Cause : Evalution of an value expression causes an overflow or underflow.Action : Reduce the operand

Answer Question  Select Best Answer

sankar

• Nov 14th, 2007

We have prdefined table 'col'. from this table we can find the column names dynamically. For ex. select cname from col where col=1 and tname='emp' then you will get first clomun name of emp table...

Naresh Dantu

• Nov 13th, 2006

You Can. Below is the example.DECLARECURSOR cr_dataISSELECT ROWID, a.*FROM fnd_user a where rownum < 10;l_table_name VARCHAR2(2000) := 'FND_USER';-- IMP--This table name should be same as your from...

Answer Question  Select Best Answer

santosh_pokiri

 Profile Answers by santosh_pokiri Questions by santosh_pokiri

• Nov 29th, 2007

Function: Whenever a function executes it returns value.ex:create or replace function sum(x number)asy number;begin    y:=x+2;    return y;    -- this ...

amahanit

 Profile Answers by amahanit Questions by amahanit

• Oct 16th, 2007

hi ravgopal i got u'r reply,thanks. but there is still one query ,in procedures OUT and INOUT parameters are copied from actual parameters to formal parameters(it takes extra time),so to...

Answer Question  Select Best Answer

amahanit

 Profile Answers by amahanit Questions by amahanit

• Dec 1st, 2007

There are hundreds of build-ins avialable few ones areDBMS_OUTPUT.PUT_LINE, DBMS_SQL, DBMS_DDL, DBMS_PIPE, DBMS_JOB

Answer Question  Select Best Answer

we have a PRIMARYKEY field with auto generation in sequence of a table of records. If we delete any one of records from the table, how can we update the table implicitly using Trigger?

amahanit

 Profile Answers by amahanit Questions by amahanit

• Dec 1st, 2007

Which table u want to update ,in case if it is the same table then write after delete  trigger on the table and select values from destination table.

Answer Question  Select Best Answer

deepakonroll

 Profile Answers by deepakonroll Questions by deepakonroll

• Dec 7th, 2007

DECLARECURSOR CUR_TABLES ISSELECT table_name from user_tables;V_TABLE NUMBER(5);v_table_name varchar2(100);BEGINFOR I IN CUR_TABLESLOOPSELECT COUNT(1)INTO V_TABLEFROM (SELECT table_name from user_tabl...

rajakumar_na

 Profile Answers by rajakumar_na Questions by rajakumar_na

• Nov 21st, 2007

Hi all plz understand the question.........Hi Ramki try this!SELECT COUNT (COLUMN_NAME) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'TABLENAME';FOR example;SQL> SELECT COUNT(COLUMN_NAME) FROM ...

Answer Question  Select Best Answer

joybanerjee

 Profile Answers by joybanerjee Questions by joybanerjee

• Dec 10th, 2007

The  purity level defines  what structure the  function reads  or  modifies.the  types  of  purity level:1)wnds--write  no database  stage  I.e&n...

malik.aman

 Profile Answers by malik.aman Questions by malik.aman

• Dec 4th, 2007

To be callable from SQL statements, a stored function (and any subprograms called by that function) must obey certain "purity" rules, which are meant to control side effects:When called from a SELECT ...

Answer Question  Select Best Answer

soorajsk_84

 Profile Answers by soorajsk_84 Questions by soorajsk_84

• Dec 19th, 2007

autonomous transaction  are independent transaction exCREATE OR REPLACE PROCEDURE a1ASPRAGMA AUTONOMOUS_TRANSACTION; BEGININSERT INTO table1 VALUES('autonomous transaction');COMMIT; END;D...

Answer Question  Select Best Answer

SELECT APE.DAT_INSERT_DATE as "Payment Entry Date", Case when APE.TXT_INTERMEDIARY_CD is null or APE.TXT_PAYER_CUSTOMER_ID APE.TXT_INTERMEDIARY_CD then (Select TXT_CUSTOMER_NAME from GENMST_CUSTOMER where TXT_CUSTOMER_CD = APE.TXT_PAYER_CUSTOMER_ID) else(Select TXT_INTERMEDIARY_NAME from GENMST_INTERMEDIARY where TXT_INTERMEDIARY_CD = APE.TXT_PAYER_CUSTOMER_ID) End as "Payer Name" , APE.TXT_PAYER_CUSTOMER_ID...

joybanerjee

 Profile Answers by joybanerjee Questions by joybanerjee

• Dec 20th, 2007

create or replace procedure sql_to_procedure as declare cursor c1 is SELECT APE.DAT_INSERT_DATE as "Payment Entry Date",APE.TXT_INTERMEDIARY_CD AS "Intermediary ID",APE.TXT_PAYER_C...

Answer Question  Select Best Answer

dinu78

 Profile Answers by dinu78 Questions by dinu78

• Dec 21st, 2007

1) First identify the current excecution plan by using showplan.In most cases, creating and then forcing usage of the indexes result in drastic improvement.1) Ensure that indexes(single or composite)&...

Answer Question  Select Best Answer

How to pass result set using refcursor from one package procedure to another package procedure? and code also

MAQDOOMAAMER

 Profile Answers by MAQDOOMAAMER Questions by MAQDOOMAAMER

• Dec 25th, 2007

CREATE TABLE employees (empid   NUMBER(5),empname VARCHAR2(30));INSERT INTO employees (empid, empname) VALUES (1, 'Dan Morgan');INSERT INTO employees (empid, empname) VALUES (2, '...

Answer Question  Select Best Answer

kirangsv

 Profile Answers by kirangsv Questions by kirangsv

• Jan 6th, 2008

Well there is no difference between is/as as they said both r one and the same when i have gone thru the book u can use either is / as

cutesree

 Profile Answers by cutesree Questions by cutesree

• Dec 17th, 2007

There is no difference between is/as ... is is used for creating a new procedure with a new name where as 'as' is used to create a new procedure with existing procedure name... it will not display "procedure already exists".

Answer Question  Select Best Answer

kirangsv

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

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

Answer Question  Select Best Answer