Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4615
  • Last Modified:

Error in a procedure of DBMS_PIPE ?

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
gopikrish
Asked:
gopikrish
  • 9
  • 7
  • 5
1 Solution
 
pennnnCommented:
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
 
saxena_mohitCommented:
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
 
gopikrishAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
gopikrishAuthor Commented:
Hi. No but I'm using personal oracle 8i for learning.So Cant I not make privilege myself?
0
 
pennnnCommented:
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
 
gopikrishAuthor Commented:
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
 
pennnnCommented:
As I said before "connect as SYS", not system. The owner of the package is SYS, not system.
Hope that helps!
0
 
gopikrishAuthor Commented:
Oh sorry but whats the password for it? When i gave as "manager" its not accepting? Thanks.
0
 
gopikrishAuthor Commented:
ah got it its "Change_on_install". Thanks a lot pennnn.I will give you points surely :-) Cheers
0
 
saxena_mohitCommented:
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
 
gopikrishAuthor Commented:
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
 
saxena_mohitCommented:
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
 
saxena_mohitCommented:
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
 
gopikrishAuthor Commented:
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
 
pennnnCommented:
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
 
saxena_mohitCommented:
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
 
pennnnCommented:
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
 
gopikrishAuthor Commented:
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
 
saxena_mohitCommented:
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
 
saxena_mohitCommented:
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
 
gopikrishAuthor Commented:
Yes Correct.Now its working :-) Thanks a lot.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 9
  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now