Answer / swapnareddy

Basically, Reference cursor is datatype.Reference cursor works
as a cursor variable.the advantage of using reference cursor
is it pass the result sets to the subprograms
(ie,procedures,packages and functions etc).
example for reference cursor usage:
type r_cursor is ref cursor;
c_emp r_cursor;
ename1 emp.ename%type;
begin
open c_emp is select ename from emp;
loop
fetch c_emp into ename1;
exit when c_emp% notfound;
dbms_output.put_line(ename1);
end loop;
close c_emp;
end;

Answer / narendrareddy

Generally, through static cursors we are using only one select statement at a time for single active set area. Whereas, in ref cursor we are executing number of select statements dynamically for single active set area. That’s why these types of cursors are called dynamic cursors.

1) Strong Ref Cursor: Having return type.
2) Weak Ref Cursor: Does not have return type.

Answer / rahul

1) It's a datatype.
2) variable created based on this is called cursor variable
3) advantage is their capability to pass result sets between
programs

Answer / a.jyothsna

Difference between cursor and ref-cursors is same as the
difference between variable and constant.
With ref-cursors we can use the cursors variable.

What is pls_integer in pl sql?

0 Answers  

---

What are user defined functions?

0 Answers  

---

How to get unique records from a table?

0 Answers  

---

What does desc stand for?

0 Answers  

---

Can we declare a column having number data type and its scale is larger than pricesionex: column_name number(10,100),column_name numbaer(10,-84)

0 Answers  

---

What is dml and ddl?

0 Answers  

---

what is collation? : Sql dba

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 to extract a unit value from a date and time? : Sql dba

0 Answers  

---

What is the use of nvl function?

0 Answers  

---

What does where 1 1 mean in sql?

0 Answers  

---

How do you modify a column in sql?

0 Answers  

---