-
What are the advantages of packages ?
laxma823
- Aug 7th, 2008
Package have more advantages ModularityEasily Application designBetter PerformenceInformation Hiding
-
Hi,We have to select some rows from a table using many conditions in the where clause. Then we have to write those selected rows to one flat file. I know that we can use UTL_FILE package to do this. Is there any better approach to do this??
rajivgupta780184
- Aug 12th, 2008
YOU CAN USE TEXT_IO.PUTLINE INBUILTSOR YOU CAN USE THE SPOOL FUNCTION BOTH ARE APPLICABLE
hindol_saha
- Mar 27th, 2007
Spool c:filename.txtselect * from tab where ................;spool off;goto c: and check it.i think it is the easyest way to store some data from oracle to flat file...u can save it in any extension like .csv.xls.txt ect............
-
In PL/SQL I need to get like this scenario. if I am doing manipulation on a table, I need to display a record as Non-Updateable. It need to show a message to the user. How it is possible.Thanks in Advance.
rajivgupta780184
- Aug 12th, 2008
CREATE OR REPLACE TRIGGER SHOW_BEFORE_DMLBEFORE INSERT OR DELETE OR UPDATE ON SERVICE_ORDER_STATUS? --(TABLE NAME)FOR EACH ROW BEGIN?DBMS_OUTPUT.PUT_LINE('NON-UPDATABLE TABLE');?ROLLBACK;END;ThanksRajiv GuptaYogik Technologies Pvt. Ltd.
SonuYadav
- Aug 11th, 2008
You can user sql%rowcount to check whether the dml does something or notegbegindelete from tbl_xxxwhere col_yyy = 'ASDF';dbms_output.put_line (sql%rowcount);end;this way it will return the no of rows deleted
-
How to add XL Sheet in PL/SQL?
rajivgupta780184
- Aug 12th, 2008
By using SQL LOAER, TOAD we can insert?EXCEL file data into respective Table & we can also create or upload ORACLE table data to?.xls file from that.ThanksRajiv GuptaYogik Technologies Pvt. Ltd.
-
Job scheduling
How can i schedule a procedure every day at 10 pm
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. -
How to take DATE as user input in PL/SQL block?
raghuprasad
- Aug 25th, 2008
Inputting the date in PL/SQL is same as in SQL. Simply pass the date as a string in the format DD-MON-YYYY.Eg: SELECT * FROM TABLE WHERE HIRE_DATE='dd-mon-yyyy'.OrYou can also use Date functions TO_DATE('25-aug-2008','......date formate......');Hope this is the answer, you are looking for.Raghu.
rajivgupta780184
- Aug 22nd, 2008
Create or replace function get_period (pDate varchar2) return varchar2is pday varchar(20);begin SELECT TO_CHAR(to_date('&DATE','MM/DD/YYYY'),'DAY') into pday FROM DUAL;return pday;end get_period;to call the function select get_period(08/03/2008) from dual;
-
In PL/SQL if we write select statement with INTO clause it may return two exceptions NO_DATA_FOUND or TOO_MANY_ROW . To do you avoid these execeptions. How do you write SQL statement in alternative way?
vnraos
- Sep 5th, 2008
First, let us understand why these errors cropup.1) NO_DATA_FOUND - is raised only for "select into" statements when? the where clause of the query does not match any rows.2)TOO_MANY_rows - is raised...
binurajnair
- Jul 30th, 2008
If you don't want an excpetion to be raised on a SELECT statement, you can avoid executing the statement by having a count just before the SELECT as shown belowSELECT count(rowid)into v_count from...
-
External Table
Define External Table
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
-
Find Sequence MAX Value
How to find the max value in a Sequence?
mstreete
- Sep 30th, 2008
If you don't own the sequence, you may look at all_sequences, which shows sequences in other schemas that you have access to:select max_valuefrom sys.all_sequenceswhere sequence_owner = '&sequence_owner' and sequence_name = '&sequence_name'
nandk.sharma
- Jun 30th, 2008
We can get the MAX value of a sequence by using the data dictionary view 'USER_SEQUENCES' .i.e. SELECT MAX_VALUE FROM USER_SEQUENCES WHERE SEQUENCE_NAME='NAME_OF_SEQUENCE'
-
Return Multiple Rows
How to return more than one row through functions? Explain with example.
rajivgupta780184
- Oct 31st, 2008
create or replace function f1 return sys_refcursor as
r1 sys_refcursor;
begin open r1 for select * from emp;
return(r1);
end;
call the function as: select f1 from dual;
Thanks & Regards
RAJIV GUPTA (ORACLE CONSULTANT)
YOGIK TECHNOLOGIES PVT LTD.raghuprasad
- Sep 1st, 2008
Eg: SELECT empname,sal FROM employee_table WHERE sal<MAX(sal) AND sal>MIN(sal);Hope the above statement executes.
-
Is the order in which the conditions are given in the 'WHERE' clause are important?
Mean I heard from Oracle 8i onwards the order of where clause condition does not matter which oracle creates the explain plan...and execute..Is it true?
manoj_behera82
- Aug 7th, 2008
Yes the order is important. But it is important for the performance of the query?and not for the final result. The output of the query will be the same irrespective of the order in which the join con...
gtomar
- Aug 7th, 2008
Hi, Where Clause conditions execution are independent of order in explain plan but it reads top to bottom.Thanks,Gunjan
-
Call PL/SQL function
How to call a PL/SQL function in another PL/SQL function? Is it possible? Give example?
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
-
Suppose, I've created a new database DB1 n i've created a table DB1.T1.Now, DESC T1 --> desplaying the table structure butselect * from DB1.T1 ---->giving--> table or view does not exist.. Can any one explain possible reason behind this.
todeepakmalhotra
- Nov 10th, 2008
You cannot use the database name before the table name like db1.t1.we can only use a user name?and then table name. eg. let we have a user "deepak" then we can write select * from deepak.t1 .? We ca...
oozaifa
- Nov 6th, 2008
Type schema name before table name or your id dont have the access to view the data from that table
-
-
Send Email from Oracle Procedure
How to send Email via PL/SQL Procedure?
rajivgupta780184
- Nov 24th, 2008
rem -----------------------------------------------------------------------rem Filename: smtp.sqlrem Purpose: Send e-mail messages from PL/SQLrem Notes: ...
-
How do i write a function that returnsmore than 1 records from a tablefor example in sql server there is a solution as create function f1() return table asselect * from emphow do i do the same in oracle. plz help
rajivgupta780184
- Nov 24th, 2008
If we want to return multiple row from a function then we can use ref cursor function .Example is given below: create or replace function f1 return sys_refcursor as r1 sys_refcursor;begin open r1 for ...
pardhu323
- Jul 20th, 2008
create or replace function f1 return sys_refcursor as r1 sys_refcursor;begin open r1 for select * from emp; return(r1); end;call the funtion as :select f1 from dual;then it returns :CURSOR STATEMENT :...
-
BulkBind
What Is BulkBind? When it is used?
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...
-
What is the difference between In, Out, InOut Parameters. Can we pass value or reference or both to the In Out Parameter.
saishradha
- Nov 27th, 2008
Here is the answer in 1 lineIN-- value is used in a program. It cannot be a variable .COuld be literal,expression,value .OUT-- value is returned back from the progr...
raghav_sy
- Jan 15th, 2007
hi friends,To make it simple, keep in mind,IN : It is a CONSTANT in the sub-program and u can not modify its value in sub- program. if its value is modified in the s...
-
What is PRAGMA RESTRICT_REFERENCES:
saishradha
- Dec 4th, 2008
Pragma RESTRICT_REFERENCES is a compiler directive.It makes sure that the function maintains the purity rules and code in fuction creation satisfy the purity rules.There are 4 values for it.WNDS,RNDS,WNPS,RNPS.ThanksJai Sainath
Kullai Naidu
- Mar 5th, 2007
By using pragma_restrict_references we can give the different status to functions,Like WNDB(WRITE NO DATA BASE),RNDB(read no data base),Write no package state,read no packge state.W
-
Passing the parameter to TOP keyword
Hi All,I want create a stored procedure by passing the parameter to top nth recordfor Excreate proc test1(@x integer)asbeginselect top @x empid from emp order by empid descendwhen iam executing iam getting error .Please let me know any other alternative way
shruti.potdar
- Dec 10th, 2008
Solution to your problem:Create Procedure TEST1( p_num number)isv_eid number;begin?? select empid into v_eid from EMPwhere empid=(select A.empid from (SELECT empid, rank() over (order by empid desc) RNK FROM emp) A where A.RNK=p_num);dbms_output.put_line('Top'|| p_num || 'emp is ::'|| v_eid);End;/
xambrose
- Jul 19th, 2008
create proc test1(@x integer)asbeginSELECT empid FROM (SELECT empid FROM emp ORDER BY empid ) WHERE ROWNUM = @x;end
PL/SQL Interview Questions
Ans