explain step wise
Sam, January 13, 2002 - 11:37 am UTC
as to how we are
"The workaround is to use dynamic sql to open the cursor (bypassing the PLSQL SQL parser). "
January 13, 2002 - 1:05 pm UTC
that doesn't make sense. If you are asking "how do we use dynamic sql to workaround this issue", read the above answer I have. The example is right there. Dynamically open a ref cursor.
PARESH PATEL, January 21, 2002 - 7:21 am UTC
I LIKE YOUR REPLING CAPACITY AND YOUR FABULOUS SENSERITY OF
ANSWERING OUR QUESTIONS.
I LIKE IT.
PARESH PATEL, January 21, 2002 - 7:22 am UTC
I LIKE YOUR REPLING CAPACITY AND YOUR FABULOUS SENSERITY OF
ANSWERING OUR QUESTIONS.
I LIKE IT.
what to do in cursor?
Charlie, March 27, 2002 - 1:34 pm UTC
Hi Tom,
I want to use rollup function in pl/sql with a CURSOR NOT a Ref Cursor. I searched everything in your site, but it all comes with REF CURSOR example. Can you help me compile the following example?
create or replace procedure sp_get_stats
is
cursor c_cursor is
select id, sum(salary) as total
from employee
group by rollup(id);
begin
for x in c_cursor loop
dbms_ouput.put_line(x.id || '...' || x.total);
end loop;
end;
/
The compile failed 'cause there is a rollup even though I use your trick put it in quote. It still doesn't work. Can you help me out?
Thanks,
March 27, 2002 - 1:55 pm UTC
options:
o ref cursor (my preference, not much different from a "cursor cursor"
o upgrade to 9i, this is not an issue.
o hide the group by rollup in a VIEW, query the view in PLSQL
ref cursor
Charlie, March 27, 2002 - 2:49 pm UTC
Followup:
options:
o ref cursor (my preference, not much different from a "cursor cursor"
Hi Tom, can you show me how to change the following procedure to use ref cursor, AND use dbms_output to output results in a loop within one pl/sql?
Thanks,
----------------
create or replace procedure sp_get_stats
is
cursor c_cursor is
select id, sum(salary) as total
from employee
group by rollup(id);
begin
for x in c_cursor loop
dbms_ouput.put_line(x.id || '...' || x.total);
end loop;
end;
/
March 27, 2002 - 3:22 pm UTC
either of these do it. The second shows how, if you don't want to define individual columns to fetch into, you can set up a record to fetch into:
scott@ORA817DEV.US.ORACLE.COM> create or replace procedure sp_get_stats
2 is
3 type rc is ref cursor;
4 c_cursor rc;
5 l_id emp.empno%type;
6 l_total emp.sal%type;
7 begin
8 open c_cursor for
9 'select empno, sum(sal) as total
10 from emp
11 group by rollup(empno)';
12 loop
13 fetch c_cursor into l_id, l_total;
14 exit when c_cursor%notfound;
15 dbms_output.put_line(l_id || '...' || l_total);
16 end loop;
17 close c_cursor;
18 end;
19 /
Procedure created.
scott@ORA817DEV.US.ORACLE.COM>
scott@ORA817DEV.US.ORACLE.COM> create or replace procedure sp_get_stats
2 is
3 type rc is ref cursor;
4 c_cursor rc;
5
6 cursor template is select empno, sum(sal) total from emp;
7 l_rec template%rowtype;
8 begin
9 open c_cursor for
10 'select empno, sum(sal) as total
11 from emp
12 group by rollup(empno)';
13 loop
14 fetch c_cursor into l_rec;
15 exit when c_cursor%notfound;
16 dbms_output.put_line(l_rec.empno || '...' || l_rec.total);
17 end loop;
18 close c_cursor;
19 end;
20 /
Procedure created.
EXCELLENT
Charlie, March 27, 2002 - 4:47 pm UTC
Thank you for your follow up!!!
Bulk collect the cursor into a pl/sql table
bala, February 19, 2003 - 7:26 am UTC
Tom how do I bulck collect in this case. I mean bulk collecting into a pl/sql table from the cursor.
I am trying to
select a.* bulk collect into pl_tb from (select a,b,(select x from abc) x from t);
the inline view doesnt solve the problem
still end up getting the same
PLS-00103: error
Need some help
February 19, 2003 - 8:51 am UTC
you need to supply a full example of what you are trying -- versions are needed too.
depends on the release whether you can bulk collect into a table of records or whether you need a RECORD of TABLES.
You probably need a record of tables -- that is, you cannot in your version bulk collection into an array of records. In your version you have to bulk collect into simple scalar arrays only.
A reader, December 19, 2003 - 4:30 am UTC
PLS-00201 on sys objects in PL/SQL (Oracle 9i release 2)
Mickey, January 27, 2004 - 4:20 pm UTC
Tom,
I had this function in Oracle 8.1.7.3.4 (HP Unix)..that gave no error (where manager had dba role)...but when I created it in 9i release 2 (HP Unix)..
I get an error:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/41 PLS-00201: identifier 'DBA_OBJECTS' must be declared
if I login as Manager in sql plus..I could get to/query dba_objects just fine...
Help....
CREATE FUNCTION MANAGER.GET_DDL_TIME_FUNC (pOBJECTNAME DBA_OBJECTS.OBJECT_NAME%TYPE,
pOBJECTTYPE DBA_OBJECTS.OBJECT_TYPE%TYPE := 'TABLE')
RETURN VARCHAR2 AS
VRETURN VARCHAR2(4000);
BEGIN
SELECT TO_CHAR(LAST_DDL_TIME, 'MM/DD/YYYY HH:MI PM') INTO VRETURN FROM DBA_OBJECTS
WHERE OBJECT_NAME = pOBJECTNAME AND OBJECT_TYPE = pOBJECTTYPE;
RETURN VRETURN;
END GET_DDL_TIME_FUNC;
January 28, 2004 - 8:11 am UTC
the owner had not only the dba_role but was granted select on dba_objects directly.
go back to that 8i database and issue:
desc dba_objects
set role none;
desc dba_objects
you'll find that both work. do that in your new database -- the first will work, the second will not.
</code>
http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>
Invokers Rights....
Mickey, January 28, 2004 - 9:48 am UTC
Tom,
I tried..
desc dba_objects
set role none;
desc dba_objects
and results were just as you said...
I have v$views, and dba views in some of my procedures..
and they all worked fine in 8i...but do not compile in 9i..
I was checking DBA_TAB_PRIVS...in 8i...and saw no explicit selects for DBA_OBJECTS or any other v$ views...still it worked O.K. through procedures and now failing in 9i.
Is there another view to verify explicit privileges.
Thanks.
January 28, 2004 - 9:53 am UTC
In 8i -- you either
a) have been granted SELECT directly on the objects
b) have been granted the all powerful SELECT ANY TABLE priv (dba_sys_privs) directly.
SELECT ANY..
Mickey, January 28, 2004 - 11:12 am UTC
O.K. both 8i and 9i had SELECT ANY TABLE
as well as SELECT_CATALOG_ROLE.
The difference was O7_DICTIONARY_ACCESSIBILITY..
true in 8i...and false in 9i.
Now..the documentation says:
"When this parameter is not set to FALSE, the ANY privilege applies to the data dictionary, and a malicious user with ANY privilege could access or alter data dictionary tables."
Is it O.K. if MANAGER is the only one with DBA role...to have this parameter set to true...because otherwise..I guess I have to give explicit access to MANAGER for the objects I access through procedure.
Comments...
January 28, 2004 - 2:22 pm UTC
i would use DIRECT GRANTS. Get direct access to the tables/views you need.
My Problem is:--
Navneet, June 13, 2011 - 2:24 am UTC
--CREATE TYPE issues AS TABLE OF VARCHAR2(64);
--create table a1(a1 varchar2(5), issuedesc issues)NESTED TABLE issuedesc STORE AS issue_tab
--Insert into a1 values('1',issues('12a-abc','12b-xyz','12c-pqr'))
declare
issues_here issues;
a varchar2(5);
begin
select issuedesc into issues_here from a1 where a1 = '1';
if issues_here.count>1
then
null;--dbms.output.put_line('Count > 1');
for i in issues_here.first..issues_here.last
loop
dbms.output.put_line(issues_here.next(i));
end loop;
end if;
if issues_here(1) = '12a-abc'
then
a:='True';
end if;
dbms.output.put_line(a);
exception
when others then
dbms_output.put_line('Code: '||sqlcode||' Error: '||sqlerrm);
end;
June 17, 2011 - 11:04 am UTC
I don't see any problems.
I see a bunch of comments.
I see a bunch of code.
I see a really horrible coding practice, the one I see made by novice programmers all of the time.
http://asktom.oracle.com/pls/asktom/asktom.search?p_string=%22i+hate+your+code%22 exception
when others then
dbms_output.put_line('Code: '||sqlcode||' Error: '||sqlerrm);
end;why why why WHY do people do that.
So, I have to guess your problem is - you use when others incorrectly and this is causing lots and lots of bugs in your developed code.
But short of that, I have no idea what your problem is - since you, well, didn't really mention what it was????@?!?!!
PLS-00201: identifier 'DBMS.OUTPUT' must be declared
Navneet, June 13, 2011 - 2:29 am UTC
Hi Everyone,
I created table that have a column of nested table(shown in comments create table a1....)
And now when i am using it in PLSQL Block, and trying to get output, its giving error- PLS-00201: identifier 'DBMS.OUTPUT' must be declared.
June 17, 2011 - 11:06 am UTC
dbms.output.put_line doesn't exist.
dbms_output.put_line does exist.