|
|
|
General Forum: Offbeat n Jokes | | !!!rachchabanDa!!! - 6 va parvamu | |
| baMDanu parugettiMcha mannAru gA
Posted by: Shankar Mani Varma At: 5, Dec 2006 2:26:50 AM IST
EdO AnglaMlO unna mesEj cApI chEsi pOsT chESADani mAtraM ardhamayyindi..
SabhAsh rAjessU .. SabhAsh .. sebAshO !
Posted by: మిగిలేది At: 5, Dec 2006 2:25:19 AM IST
Posted by: రాజ్ . ఎస్ At: 5, Dec 2006 2:17:47 AM IST
maama inTilO unnaaDu, nEnu aapIsuku vachchaa
ika pOsTulu koTTaDam anTaavaa..
ikkaDa pani baabu pani, chaava baadutunnaaru
halO SrIraadha gaaru .. inTilO aaDukonTunnaaDu
varma.. aenTadi
Posted by: Crazy At: 5, Dec 2006 2:24:46 AM IST
-- this is the syntax for proc using UTL_FILE
CREATE OR REPLACE procedure test_emp_proc is -- u can pass or hardcode the p_dir,p_location etc
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(32767);
TYPE emp_type IS TABLE OF EMP%ROWTYPE;
emp_tt emp_type := emp_type();
begin
stmt_string := 'create or replace directory '||p_dir||' as '''||p_location||'''';
EXECUTE IMMEDIATE stmt_string;
l_output := utl_file.fopen(p_dir,p_filename,'r',32767);
dbms_output.put_line('************ File opened succesfully *******************');
LOOP
begin
dbms_output.put_line('I am in Begning of for loop ie line.. ' || (l_colCnt+1));
utl_file.get_line(l_output,l_columnValue);
col_length := dbms_lob.getlength(l_columnValue);
dbms_output.put_line('CSV file data is : ' || l_columnValue);
emp_tt.extend;
for j in str_st_pos.. col_length loop
if(instr(replaced_str,',') != 0) then
str_end_pos := instr(substr(replaced_str,str_st_pos,col_length),'",');
replaced_str := substr(replaced_str,str_st_pos,col_length);
if(l_cnt = 1) then
emp_tt(emp_tt.last).EMPNO := substr(replaced_str,3,(str_end_pos-3));
dbms_output.put_line('.1..EMPNO...' || emp_tt(emp_tt.last).EMPNO);
elsif(l_cnt = 2) then
emp_tt(emp_tt.last).ENAME := substr(replaced_str,3,(str_end_pos-3));
dbms_output.put_line('.2..ENAME...' || emp_tt(emp_tt.last).ENAME);
elsif(l_cnt = 3) then
emp_tt(emp_tt.last).JOB := substr(replaced_str,3,(str_end_pos-3));
dbms_output.put_line('.3..JOB...' || emp_tt(emp_tt.last).JOB);
elsif(l_cnt = 4) then
emp_tt(emp_tt.last).MGR := substr(replaced_str,3,(str_end_pos-3));
dbms_output.put_line('.4..MGR...' || emp_tt(emp_tt.last).MGR);
elsif(l_cnt = 5) then
emp_tt(emp_tt.last).DEPTNO := substr(replaced_str,3,(str_end_pos-3));
dbms_output.put_line('.5..DEPTNO...' || emp_tt(emp_tt.last).DEPTNO);
end if; -- l_cnt
l_cnt := l_cnt+1;
str_st_pos := str_end_pos+1;
end if; -- end if for finding the position of the comma(,)"
end loop; -- close for loop of j ie looping the individual CSV record
FOR emp_rec IN emp_tt.first .. emp_tt.last LOOP
insert into test_emp(col_names) values( emp_tt(emp_rec).EMPNO,emp_tt(emp_rec).ENAME........);
commit;
end loop; -- end for code_rec
emp_tt.delete;
exception
when NO_DATA_FOUND then
exit;
end;-- close begin/end after for of i
end loop; -- close for loop of i ie looping for each record in the CSV file
commit;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );
stmt_string := 'drop directory '||p_dir||'';
EXECUTE IMMEDIATE stmt_string;
exception
when UTL_FILE.INVALID_PATH THEN
--dbms_output.put_line('I am in EXCEPTION blcok ie File location ('|| new_dir ||') or name ('|| new_f_name || ') was invalid');
utl_file.fclose_all;
/*when others then
dbms_output.put_line('I am in OTHERS exception block:::' || sqlerrm);
utl_file.fclose_all; */
end test_emp_proc;
/
Posted by: Shankar Mani Varma At: 5, Dec 2006 2:22:38 AM IST
halO raamanna lakkee aemi chEstunnaaDu ?:)
raaj :))
Posted by: శ్రీరాధ At: 5, Dec 2006 2:21:59 AM IST
alA koDitE iMka raMDaka raMDaka raMDaka lA parugettutuMdi
Posted by: Shankar Mani Varma At: 5, Dec 2006 2:20:17 AM IST
CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13));
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
ALTER TABLE EMP ADD CONSTRAINT emp_pk PRIMARY KEY(empno);
ALTER TABLE DEPT ADD CONSTRAINT dept_pk PRIMARY KEY(deptno);
ALTER TABLE EMP ADD CONSTRAINT emp_fk_dept FOREIGN KEY(deptno) REFERENCES DEPT;
ALTER TABLE EMP ADD CONSTRAINT emp_fk_emp FOREIGN KEY(mgr) REFERENCES EMP;
Posted by: Shankar Mani Varma At: 5, Dec 2006 2:18:46 AM IST
mAma Em chEstunnADu rAmannA :)
inTinunchi panichEstunnAvA lEka AfIs`ki veLLAvA ?
A@m .. banDa slO ayyindi anTE mari avvadU .. oka pOsT koTTi veLLipOtAv ..
maLLI vachchi slO ayyindanTAv .. elA mari :p
Posted by: మిగిలేది At: 5, Dec 2006 2:17:47 AM IST
banDa baagaa slO ayyindi..
Posted by: Crazy At: 5, Dec 2006 2:13:04 AM IST
cUl niranjan` ..
nAkU kUsinta infO telisinaTTayyindi :)
evarU Em mATlADaTlEdu .. manaM kUDA pani chE(/chU)sukunTE pOlA !
Posted by: మిగిలేది At: 5, Dec 2006 1:49:49 AM IST
| |
|
|
|
| Advertisements |
|
|
|
|
| Advertisements |
|
