SELECT COUNT(column_name)
FROM user_tab_columns
WHERE table_name = 'MYTABLE';
dbms_output.enable(4000); /*allows the output buffer
to be increased to the specified number of bytes */
DECLARE
BEGIN
dbms_output.enable(4000);
FOR i IN 1..400
LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
/
Set the following to some other character. By default it
is &.
set define '~'
44. How do you
remove Trailing blanks in a spooled
file :
Change
the Environment Options Like this :
set trimspool on
set trimout on
set trimspool on
set trimout on
Sample :1
CREATE OR REPLACE PROCEDURE CNT(P_TABLE_NAME IN
VARCHAR2)
AS
SqlString VARCHAR2(200);
tot number;
BEGIN
SqlString:='SELECT COUNT(*) FROM '|| P_TABLE_NAME;
EXECUTE IMMEDIATE SqlString INTO tot;
DBMS_OUTPUT.PUT_LINE('Total No.Of Records In ' ||
P_TABLE_NAME || ' ARE=' || tot);
END;
Sample :2
DECLARE
sql_stmt
VARCHAR2(200);
plsql_block
VARCHAR2(500);
emp_id
NUMBER(4) := 7566;
salary
NUMBER(7,2);
dept_id
NUMBER(2) := 50;
dept_name
VARCHAR2(14) := ’PERSONNEL’;
location
VARCHAR2(13) := ’DALLAS’;
emp_rec
emp%ROWTYPE;
BEGIN
EXECUTE
IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
sql_stmt
:= 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE
IMMEDIATE sql_stmt USING dept_id, dept_name, location;
sql_stmt
:= 'SELECT * FROM emp WHERE empno = :id';
EXECUTE
IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
plsql_block
:= 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
EXECUTE
IMMEDIATE plsql_block USING 7788, 500;
sql_stmt
:= 'UPDATE emp SET sal = 2000 WHERE empno = :1
RETURNING
sal INTO :2';
EXECUTE
IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
EXECUTE
IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
USING
dept_id;
EXECUTE
IMMEDIATE ’ALTER SESSION SET SQL_TRACE TRUE’;
END;
Sample 3
CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN
DEPT.DEPTNO%TYPE) AS
v_cursor integer;
v_dname
char(20);
v_rows
integer;
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, 'select dname from
dept where deptno > :x', DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1,
v_dname, 20);
v_rows := DBMS_SQL.EXECUTE(v_cursor);
LOOP
IF DBMS_SQL.FETCH_ROWS(v_cursor) = 0 THEN
EXIT;
END IF;
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1,
v_dname);
DBMS_OUTPUT.PUT_LINE('Deptartment name:
'||v_dname);
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(v_cursor);
raise_application_error(-20000, 'Unknown
Exception Raised: '||sqlcode||' '||sqlerrm);
END;
Difference
1:
Oracle
: select name from table1 where name like 'k%';
Access:
select name from table1 where name like 'k*';
Difference
2:
Access:
SELECT TOP 2 name FROM Table1;
Oracle :
will not work there is no such TOP key word.
SELECT organization_id,name
FROM hr_all_organization_units
WHERE organization_id in
(
SELECT ORGANIZATION_ID_CHILD FROM PER_ORG_STRUCTURE_ELEMENTS
CONNECT BY PRIOR
ORGANIZATION_ID_CHILD = ORGANIZATION_ID_PARENT
START WITH
ORGANIZATION_ID_CHILD = (SELECT organization_id
FROM hr_all_organization_units
WHERE name = 'EBG
Corporate Group'));
CREATE OR REPLACE PROCEDURE read_data
AS
c_path varchar2(100)
:= '/usr/tmp';
c_file_name varchar2(20) := 'EKGSEP01.CSV';
v_file_id utl_file.file_type;
v_buffer varchar2(1022)
:= This is a sample text’;
BEGIN
v_file_id := UTL_FILE.FOPEN(c_path,c_file_name,'w');
UTL_FILE.PUT_LINE(v_file_id, v_buffer);
UTL_FILE.FCLOSE(v_file_id);
v_file_id := UTL_FILE.FOPEN(c_path,c_file_name,'r');
UTL_FILE.GET_LINE(v_file_id, v_buffer);
DBMS_OUTPUT.PUT_LINE(v_buffer);
UTL_FILE.FCLOSE(v_file_id);
END;
/
SELECT DBMS_RANDOM.VALUE (1,2) FROM DUAL;
SELECT
FLOOR((date1-date2)*24*60*60)/3600)
|| ' HOURS
' ||
FLOOR((((date1-date2)*24*60*60) -
FLOOR(((date1-date2)*24*60*60)/3600)*3600)/60)
|| '
MINUTES ' ||
ROUND((((date1-date2)*24*60*60) -
FLOOR(((date1-date2)*24*60*60)/3600)*3600 -
(FLOOR((((date1-date2)*24*60*60) -
FLOOR(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))
|| ' SECS '
time_difference
FROM my_table;
I have this string in a column named location
LOT 8 CONC3 RR
Using instr and substr, I want to take whatever value
follows LOT and put
it into a different column and whatever value follows
CONC and put it into
a different column
select substr('LOT 8 CONC3 RR',4,instr('LOT 8 CONC3
RR','CONC')-4) from
dual;
select substr('LOT 8 CONC3 RR',-(length('LOT 8 CONC3
RR')-(instr('LOT 8
CONC3 RR','CONC')+3)))
from dual
select text from all_source where name = 'X'
order by line;
select text from user_source where name = 'X'
select text from user_source where type = 'procedure'
and
name='procedure_name';
select name,text from dba_source where
name='ur_procedure'
and owner='scott';
select
to_number('-999,999.99', 's999,999.99')
from dual; -999,999.99
select
to_number('+0,123.45', 's999,999,999.99') from dual; 123.45
select
to_number('+999,999.99', 's999,999.99')
from dual; 999,999.99
select column_name from user_tab_columns where
TABLE_NAME = 'EMP'
select column_name from all_tab_columns where TABLE_NAME
= 'EMP'
select column_name from dba_tab_columns where TABLE_NAME
= 'EMP'
select column_name from cols where TABLE_NAME = 'EMP'
I have a table have
a,b,c field,
a,b should be unique, and leave max(c) row in.
How can I delete other rows?
delete from 'table'
where (a,b,c) not
in (select a,b,max(c) from 'table' group by a,b);
1) This function
helps if your clob column value not exceed 4000 bytes
(varchar2 limit).if clob column's data exceeds 4000
limit, you have to
follow different approach.
create or replace
function lob_to_char(clob_col clob) return varchar2 IS
buffer varchar2(4000);
amt BINARY_INTEGER := 4000;
pos INTEGER := 1;
l clob;
bfils bfile;
l_var varchar2(4000):='';
begin
LOOP
if dbms_lob.getlength(clob_col)<=4000 THEN
dbms_lob.read (clob_col, amt, pos, buffer);
l_var := l_var||buffer;
pos:=pos+amt;
ELSE
l_var:= 'Cannot convert to
varchar2..Exceeding varchar2 field
limit';
/
exit;
END IF;
END LOOP;
return l_var;
EXCEPTION
WHEN NO_DATA_FOUND THEN
return l_var;
END;
2)
CREATE GLOBAL TEMPORARY TABLE temp_tab(id
number,varchar_col
varchar2(4000));
SQL> var r refcursor
SQL> exec lobpkg.lob_to_char(:r);
SQL> print r
create or replace package lobpkg is
type ref1 is ref cursor;
n number:=0;
PROCEDURE lob_to_char(rvar IN OUT lobpkg.ref1) ;
end;
/
create or replace package body lobpkg is
PROCEDURE lob_to_char(rvar IN OUT lobpkg.ref1) IS
buffer varchar2(4000);
amt BINARY_INTEGER := 4000;
pos INTEGER := 1;
l clob;
r lobpkg.ref1;
bfils bfile;
l_var varchar2(4000):='';
CURSOR C1 IS SELECT * FROM clob_tab;
-- change clob_tab to your_table_name
begin
n:=n+1;
FOR crec IN c1 LOOP
amt:=4000;
pos:=1;
BEGIN
LOOP
--change crec.clob_col to crec.your_column_name
dbms_lob.read
(crec.clob_col, amt, pos, buffer);
--change next line if you create temporary table with
different name
insert
into temp_tab values (n,buffer);
pos:=pos+amt;
END LOOP;
EXCEPTION
WHEN
NO_DATA_FOUND THEN
NULL;
END;
END LOOP;
--change next line if you create temporary table with
different name
open rvar for select vchar from temp_tab where id=n;
END;
END;
57. Change Settings
Open file oracle_home\plus32\glogin.sql and
add this
set linesize 100
set pagewidth 20
and save the file
and exit from sql and reload then it will set it.
declare
-- we need one
here to get a single quote into the variable
v_str varchar2
(20) := 'O''reilly''s';
begin
DBMS_OUTPUT.PUT_LINE ( 'original single quoted v_str= ' || v_str );
v_str :=
replace(v_str, '''', '''''');
DBMS_OUTPUT.PUT_LINE ( 'after double quoted v_str= ' || v_str );
end;
SQL> /
original single quoted v_str= O'reilly's
after double quoted v_str= O''reilly''s
59. Time
Conversion
CREATE OR REPLACE FUNCTION to_hms (i_days IN number)
RETURN varchar2
IS
BEGIN
RETURN TO_CHAR
(TRUNC (i_days)) || ' days ' ||
TO_CHAR (TRUNC (SYSDATE) + MOD (i_days, 1),
'HH24:MI:SS');
END to_hms;
select to_hms(to_date('17-Jan-2002 13:20:20',
'dd-Mon-yyyy hh24:mi:ss') -
to_date('11-Jan-2002 11:05:05', 'dd-Mon-yyyy hh24:mi:ss')) from
dual;
60. Table
comparison
The table in both the schemas should have exactly the
same structure. The data in
it could be same or different
a-b and b-a
select * from
a.a minus select * from b.a
and select * from b.a minus select * from a.a
select *
from user_jobs;
exec
dbms_job.remove(job_no);
62. Switching
Columns
Update tblname
Set column1 = column2,
Column2 = column1;
I have
the number e.g. 63,9823874012983 and I want to round it to 63,98 and at the
same time change the , to a .
select
round(replace('63,9823874012983',',','.'),2) from dual;
64. First
date of the year
select trunc(sysdate, 'y') from
dual;
01-jan-2002
last year
this month through a select statement
select
add_months(sysdate, -12) from dual;
05-APR-01
65. Create
Sequence
create sequence sh increment by
1 start with 0;
66. Cursors
cursor is
someting like pointers in C language.
u fetch
the data using cursor.( wiz...store it somewhere temporarily). u
can do
any manipulation to the data that is fetched by the cursor. like
trim,
padd, concat or validate. all this are done in temporary areas called
as
context area or the cursor area. u can insert this data again in some
other
table or do anything u want!!...like setting up some flags etc.
U can
display the contents of cursor using the dbms_output only. U can
create an
anonymous plsql block or a stored procedure. the major advantage
of
cursors is that you can fetch more thatn one row and u can loop through
the
resultset and do the manupulations in a secure manner.
set
serveroutput on;
declare
cursor c1
is select * from emp;
begin
for var
in c1 loop
exit when
c1%notfound;
dbms_output.put_line('the
employee' || var.ename ||'draws a
salary of
'|| var.sal);
end loop;
end;
select next_day(sysdate-7,'SUNDAY'),
next_day(sysdate,'SATURDAY') from dual;
NEXT_DAY( NEXT_DAY(
--------- ---------
07-APR-02 13-APR-02
No comments:
Post a Comment