?
Solved

Error in a procedure of DBMS_PIPE ?

Posted on 2003-03-04
21
Medium Priority
?
4,299 Views
Last Modified: 2012-05-04
I'm getting errors when I compile this procedure.

create or replace procedure send_message
(v_message in varchar2)
is s integer;
begin
dbms_pipe.pack_message(v_message);
s:=dbms_pipe.send_message('DEMO_PIPE');
if s<>0
then raise_application_error(-20200,'ERROR'||to_char(s)||'sending on pipe');
end if;
end send_message;
 
 The errors are ..
   PLS-00201: identifier 'SYS.DBMS_PIPE' must be declared
   PL/SQL: Statement ignored
   PL/SQL: Statement ignored
   PLS-00201: identifier 'SYS.DBMS_PIPE' must be declared
So whats wrong? Thanks
0
Comment
Question by:gopikrish
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 5
21 Comments
 
LVL 11

Expert Comment

by:pennnn
ID: 8066942
You need to have the EXECUTE privilege on the sys.dbms_pipe package explicitely granted to you in order to be able to use it in a stored procedure. I guess that you only can use it in SQL because you currently have the privilege but it's granted to you via a role (EXECUTE_CATALOG_ROLE). Or maybe you don't have it at all...
Hope that helps!
0
 
LVL 1

Expert Comment

by:saxena_mohit
ID: 8066962
Hi Gopi,
 Ask the dba to do two things.

Grant you execute privelege on dbms_pipe package
2. check is the public synonym exists for dbms_pipe if not the create one and you should be fine aftyer that.
Thanks
Mohit
0
 

Author Comment

by:gopikrish
ID: 8067037
So how to see whether I have the EXECUTE privilege on sys.dbms_pipe package? Can you explain how to make the privilege so that I can execute that procedure please? Thanks.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:gopikrish
ID: 8067056
Hi. No but I'm using personal oracle 8i for learning.So Cant I not make privilege myself?
0
 
LVL 11

Expert Comment

by:pennnn
ID: 8067316
Just connect as SYS and run the following command:
GRANT EXECUTE ON sys.dbms_pipe TO <your_username>;

The public synonym obviously already exists so you don't need to do anything about that.
Hope that helps!
0
 

Author Comment

by:gopikrish
ID: 8067389
Hi pennnn.But When I connected as user "system" and I gave the command "grant execute on sys.dbms_pipe to scott" it says error "insufficient privileges" .So what else can I do?
0
 
LVL 11

Expert Comment

by:pennnn
ID: 8067402
As I said before "connect as SYS", not system. The owner of the package is SYS, not system.
Hope that helps!
0
 

Author Comment

by:gopikrish
ID: 8067444
Oh sorry but whats the password for it? When i gave as "manager" its not accepting? Thanks.
0
 

Author Comment

by:gopikrish
ID: 8067497
ah got it its "Change_on_install". Thanks a lot pennnn.I will give you points surely :-) Cheers
0
 
LVL 1

Expert Comment

by:saxena_mohit
ID: 8067504
the password for sys is change_on_install

try these commands on sql prompt.

sql>connect sys as sysdba
sql>password: change_on_install
sql> grant execute on dbms_pipe to public;
sql>create public synonym dbms_pipe for dbms_pipe;
sql>conn scott/tiger

and you are all done


0
 

Author Comment

by:gopikrish
ID: 8067622
yes I am able to succesfully compile both send_message and receive_message procedures.But i gave
"Execute send_message('Hello')" and it showed PL/SQL procedure successfully compiled.But when I gave "Execute receive_message" the SQL plus is getting hanged up.So cant I not see that sent message?.My receive_message procedure is as follows...
create or replace procedure receive_message
is s integer;
v_message varchar2(50);
begin
s:=dbms_pipe.receive_message('DEMO_PIPE');
if s<>0
then raise_application error(-20201,'Error'||to_char(s)||'reading pipe');
end if;
dbms_pipe.unpack_message(v_message);
dbms_output.put_line(v_message);
end receive_message;
0
 
LVL 1

Expert Comment

by:saxena_mohit
ID: 8068229
Hi Gopi,
 
 its not hung but its waiting for your message.Just conside this procedure as an interactive program you receive_message will wait at the sql prompt for the message .
Try open up a new window and execute send_message procedure and supply some value and you will see that receive_message will receive it and come back to sql prompt.

I you dont want you receive_message procedure to wait for ever you this line in your code

s:=dbms_pipe.receive_message('DEMO_PIPE',n);

where n can be any integer. If you want your program not to wait put n=0
if you want to wait 10 sec n=10
and default is maxwait which mean it will wait forever and that's what happened in your case
Thanks
Mohit
0
 
LVL 1

Expert Comment

by:saxena_mohit
ID: 8068235
Hi Gopi,
 
 its not hung but its waiting for your message.Just conside this procedure as an interactive program you receive_message will wait at the sql prompt for the message .
Try open up a new window and execute send_message procedure and supply some value and you will see that receive_message will receive it and come back to sql prompt.

I you dont want you receive_message procedure to wait for ever you this line in your code

s:=dbms_pipe.receive_message('DEMO_PIPE',n);

where n can be any integer. If you want your program not to wait put n=0
if you want to wait 10 sec n=10
and default is maxwait which mean it will wait forever and that's what happened in your case
Thanks
Mohit
0
 

Author Comment

by:gopikrish
ID: 8070645
Yes thanks I'm getting it now.And suppose from user "scott" I have to send a message to user "system" how shall I give the SQL command? Thanks.
0
 
LVL 11

Expert Comment

by:pennnn
ID: 8071883
With dbms_pipe you don't send messages from one user to another. You send messages to a pipe and read the messages from the pipe. If it's a public pipe then everyone can read/write messages to that pipe.
So if user "scott" want to send a message to user "system" then "scott" just sends a message to a pipe and "system" reads it. Note that any other user can read the message, not only "system" (unless it's a private pipe).
Here's a link to the documentation in case you need more details:
http://download-west.oracle.com/docs/cd/A97630_01/appdev.920/a96612/d_pipe.htm#998100

Hope that helps!
0
 
LVL 1

Expert Comment

by:saxena_mohit
ID: 8072282
Hi Gopi create a public pipe and any user can receive it.
Here is the syntax which you should use while creating public pipe.

create or replace procedure send_message
(v_message in varchar2)
is s integer;
a integer;
begin
a:=dbms_pipe.create_pipe('DEMO_PIPE',8192,FALSE);
dbms_pipe.pack_message(v_message);
s:=dbms_pipe.send_message('DEMO_PIPE');
if s<>0
then raise_application_error(-20200,'ERROR'||to_char(s)||'sending on pipe');
end if;
end send_message;


iN THE ABOVE EXAMPLE 8192 IS THE DEFAULT PIPE SIZE YOU CAN PUT YOUR OWN AND "FALSE" MENAS THIS PIPE IS PUBLIC.
tHANKS
MOHIT
0
 
LVL 11

Expert Comment

by:pennnn
ID: 8072388
In fact if you don't explicitely create the pipe it is creates as a public pipe by default, so your original procedure is perfectly OK and you don't need the additions proposed by Mohit.
Hope that helps!
0
 

Author Comment

by:gopikrish
ID: 8072805
OK Thanks but sorry for asking one more question in DBMS_SQL.Actually I want to execute a procedure which deletes all rows from "emp" table using DBMS_SQL package.But I'm getting error saying "table or view does not exist" .The procedure is..

create or replace procedure delete_all_rows
(tab_name in varchar2,
rows_del out number)
is
cursor_name integer;
begin
cursor_name:=dbms_sql.open_cursor;
dbms_sql.parse(cursor_name,'delete from'||tab_name,dbms_sql.native);
rows_del:=dbms_sql.execute(cursor_name);
dbms_sql.close_cursor(cursor_name);
end;

Actually this procedure I'm practising as its given as an example in my oracle book.So whats wrong? Thanks.
0
 
LVL 1

Expert Comment

by:saxena_mohit
ID: 8073226
I guess you are not giving space


Try using this code

dbms_sql.parse(cursor_name,'delete from  '||tab_name,dbms_sql.native);


Thanks
Mohit
0
 
LVL 1

Accepted Solution

by:
saxena_mohit earned 80 total points
ID: 8073244
I guess you are not giving space


Try using this code

dbms_sql.parse(cursor_name,'delete from  '||tab_name,dbms_sql.native);


Thanks
Mohit
0
 

Author Comment

by:gopikrish
ID: 8073276
Yes Correct.Now its working :-) Thanks a lot.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question