Answer / tulsi

NO CYCLE is default when sequence is created

CYCLE statement is given when the sequence has to repeat
its number generation between START WITH and MAX VALUE.

Answer / gvmahesh

NOCYCLE keyword is default in sequences.

CYCLE :If we want use cycle keyword first we check the table
having primary key or not.If there is no primary key we
absolutely use the CYCLE keyword.

In hierarchical structure,the descendant act as a
ancestor,is called CYCLE.

For eliminating ancestor descendant problem we use NOCYCLE
KEYWORD.

for example we take the EMP table

In EMP table the MGR column has a NULL managerial number.In
that column we insert some of the empno.Then ancestor
descendant problem occur.

select * from emp;

update emp set mgr=7566 where mgr is null;

Then we write a hierarchical query

select empno,ename,sal,mgr from emp
start with ename='KING'
connect by prior empno=mgr;

we execute this query it shows an error

ERROR:
ORA-01436: CONNECT BY loop in user data.

in this situation also we want to see the data through
NOCYCLE keyword.

select empno,ename,sal,mgr from emp
start with ename='KING'
connect by NOCYCLE prior empno=mgr;

What is the left table in sql?

0 Answers  

---

What are the different types of database management systems?

0 Answers  

---

Can we want to pass a parameter payroll_id to this external pl/sql function, how do we do it?

0 Answers  

---

create or replace procedure search_matter(empno varchar2) as sql_stmt varchar2(200); stmt varchar2(200); v_table_name varchar2(200); val_pres number; inp_value varchar2(200); type obj_typ is table of cols.column_name%type index by binary_integer; type all_col is table of varchar2(100) index by binary_integer; typ_obj_typ obj_typ; typ_all_col all_col; begin select object_name bulk collect into typ_obj_typ from user_tables,user_objects where table_name = object_name AND object_type = 'TABLE'; select empno into inp_value from dual; dbms_output.put_line('inp value : '||inp_value); for i in typ_obj_typ.first..typ_obj_typ.last loop v_table_name := NULL; v_table_name := typ_obj_typ(i); dbms_output.put_line(i||':'||typ_obj_typ(i)); dbms_output.put_line('....................'); sql_stmt := 'select column_name from cols where table_name = :1 and data_type in (''CHAR'', ''VARCHAR2'', ''NCHAR'', ''NVARCHAR2'',''NUMBER'')'; EXECUTE IMMEDIATE sql_stmt bulk collect into typ_all_col using typ_obj_typ(i); for inside in typ_all_col.first..typ_all_col.last loop dbms_output.put_line('sql stmt: '||sql_stmt); dbms_output.put_line('column name: '||typ_all_col(inside)||'table name: '||typ_obj_typ(i)); stmt := 'select count(*) from ||typ_obj_typ(i)||'; EXECUTE_IMMEDIATE stmt into val_pres ; if val_pres = 1 then dbms_output.put_line('value present col name: '||typ_all_col(inside)||'table name :'||typ_obj_typ(i)); end if; end loop; dbms_output.put_line('....................'); end loop; exception when others then dbms_output.put_line('sql code '||sqlcode||'Table name: '||v_table_name); dbms_output.put_line('sql message '||sqlerrm); end; Compile-time I am getting below error, Plz help to resolve. LINE/COL ERROR -------- ----------------------------------------------------------------- 47/23 PLS-00103: Encountered the symbol "STMT" when expecting one of the following: := . ( @ % ;

0 Answers  

---

How long it takes to learn pl sql?

0 Answers  

---

How do you use collections in procedure to return the resultset?

0 Answers  

---

What is the use of count (*) in sql?

0 Answers  

---

Can you have more than one key in a database?

0 Answers  

---

What are analytic functions in sql?

0 Answers  

---

How do I write a cron which will run a sql query and mail the results to agroup?

0 Answers  

---

How many functions are there in sql?

0 Answers  

---

what are the different type of sql's statements ? : Sql dba

0 Answers  

---