-
-
Temporary Tables
How to create temporary table? Give some example
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)...
-
Writing Pl/SQL function
Write a PL/SQL fuction named say TRUN, which does the same work as the oracle predefined function "TRUNCATE".
ajaz_11i
- Jul 9th, 2008
It will truncate/delete all the rows in the table emp very fast as compared of delete command, but the table must have no Foriegn keyMr. Ajaz Ahmad Kumar
sgbang
- Jul 7th, 2008
Hi,Please check out this function:create or replace function trun(p_table_name varchar2) return number asbeginexecute immediate ' truncate table '|| p_table_name;return 0 ;end; /How to ex...
-
Long Raw Columns
How many columns you can create in table from this type "long raw"?
madhupriya.nukala
- Jun 25th, 2008
Only single column is allowed per a table of data type LONG or LONG RAW
-
How do you Optimize query
How can i choose which query is best?i.e On which factors i can choose the best query?
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.
-
What is a NOCOPY parameter? Where it is used?
raghav_sy
- Jan 15th, 2007
hi one more thing i wan to add abt NOCOPY, it is that its a hint to compiler, so its compiler wish whether to follow this thing or not, so before using this just go through the conditions when this th...
-
Analytical functions
What are analytical functions and how are they used?
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 ...
-
Filling a ref cursor
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
- 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
-
What are the advantages & disadvantages of packages ?
jagadeesh9
- Feb 24th, 2008
Package: It is an collection of related variables, cursors,procedures and functions stored at one location. They supports OOPS features like function over loading, encapsulation.ADV: ...
-
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. -
Better Tunning PL/SQL Blocks
What steps should a programmer should follow for better tunning of the PL/SQL blocks?
-
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.
PL/SQL Interview Questions
Ans