-
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
-
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
-
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
-
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.
-
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'
-
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
-
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...
-
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;
-
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 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.
-
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
-
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............
-
What are the advantages of packages ?
laxma823
- Aug 7th, 2008
Package have more advantages ModularityEasily Application designBetter PerformenceInformation Hiding
-
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: ...
-
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
-
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 ...
-
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...
-
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.
-
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
-
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...
PL/SQL Interview Questions
Ans