-
Hi Friends!! How to retrive the no.of fields in a table..Can anybody Assist me?Thanks&RegdsRamki
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
- 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 ...
-
How do you update the table implicitly using Trigger
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
- 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.
-
What are the built-ins used in PL/SQL?
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
-
If you write a function and procedure with performs same task and return a value, which will execute fast and why.
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
- 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...
-
-
Which error is associated with this ORA-01426?
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
-
-
Suppose I have 2 triggers on table T, tr1- a before insert trigger & tr2- a before update trigger.tr1 has update (T) statement inside body of tr1andtr2 has insert (T) statement inside body of tr2Now, I'm tring to insert a row into T.What will hppn??
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!! -
-
What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?
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
- 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....................?
-
Hi Friends!! Can anybody answer what are the constraints on Mutating tables? How to remove the mutating errors in triggers? Urgent plzzzzzzzzzzzzzzzzThanks in Advance..Thanks&RegdsRamki,TCS
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.
-
Hi 1.What is difference between procedure and function?1. procedure may or may not return values whereas function must return value2. we can call the function in the sql statements whereas we can't call the procedureExcept these two, is there any difference between these two.2. What is pl/sql table and what is use of it. In which case, we can use it3. What is ref cursor, what is the use of it.4.how the function will return multiple values. Pls give me with examples
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 -
Function which returns previous business date of given input date
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
- 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
- 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...
-
What is the syntax for using triggers?
vaibhav Tyagi
- Sep 26th, 2007
FOR- Sql ServerCreate trigger TestTriggeron tableaNameinstead of insertasbegin transactiondeclare @aid intset @aid=0select @aid = max([columnName]) from tableNameif @aid 0--do operationcommit transaction
madhuk17
- Feb 18th, 2007
Create or replace trigger {Before/After} {Insert/Update/Delete} on [Referencing [ New as ] [ Old as ][For each row [When ]]
-
-
-
-
-
-
PL/SQL Interview Questions
Ans