Re: DBMS_PIPE Experts ! Help !^%$%

Couple of things. First of all, you don't use 'execute' to call a procedure inside a PL/SQL block. After the begin, the line should just be 'DBMS_PIPE.PACK_MESSAGE(info)'. No execute. The main problem, though, is that you don't have execute permissions on the DBMS_PIPE package. Since this is a stored procedure, you need to be granted access directly (not through a role). So if you are creating this procedure as scott, then connect as sys (the owner of DBMS_PIPE), and 'grant execute on dbms_pipe to scott'. That should allow you to compile the procedure. Most likely a similar sort of thing for the Pro*C program. You need to precompile with sqlcheck=full, and you will also need the userid option. Again, if the program connects as scott/tiger, you would use USERID=scott/tiger on the precompile step. If you use our makefile (proc.mk, found in $ORACLE_HOME/proc/demo, which I recommend) then you would add sqlcheck=full and userid=scott/tiger to the PROFLAGS macro in this makefile.

This example isn't sloppy, you just need the correct permissions (which probably should be made more clear in this example).

As far as the pointer assignments, check the use of varchar .arr fields. These are expanded as unsigned char *, not char *. So if you are using an ANSI compiler, you can get warnings (not errors, usually) on statements like

strcpy(x.arr, "hello");

To fix the warnings, you need to typecast the pointer:

strcpy((char *)x.arr, "hello");

That ought to do it.

In article <382u0f$5qj_at_raffles.technet.sg>, scornd11_at_solomon.technet.sg (Nicholas Wee) writes:
|>
|> Does anyone out there have some SOLID code that really works ???
|> The Oracle code's fraught with errors and I can't get DBMS_PIPE
|> to work. For example:
|>
|> Does anyone know why the following code when compiled
|> comes out with errors. Afterall, it was in the Application
|> Developer's Guide:A-23
|>
|> create or replace procedure DEBUG (info varchar2) is
|> return_val integer;
|> begin
|> execute DBMS_PIPE.PACK_MESSAGE(info);
|> return_val := DBMS_PIPE.SEND_MESSAGE('plsql_debug');
|> if return_val <> 0 then
|> RAISE_APPLICATION_ERROR(-2000, 'Debug Error');
|> end if;
|> end;
|> /
|>
|> show errors procedure DEBUG;
|>
|> exit;
|>
|>
|> The errors are listed below:
|>
|> Connected to:
|> ORACLE7 Server Release 7.0.16.4.0 - Production
|> With the procedural, distributed, and Parallel Server options
|> PL/SQL Release 2.0.18.1.0 - Production
|>
|>
|> Warning: Procedure created with compilation errors.
|>
|> Errors for PROCEDURE DEBUG:
|>
|> LINE/COL ERROR
|> -------- -----------------------------------------------------------------
|> 4/5 PLS-00201: identifier 'DBMS_PIPE' must be declared
|> 4/5 PL/SQL: Statement ignored
|> 5/5 PL/SQL: Statement ignored
|> 5/19 PLS-00201: identifier 'DBMS_PIPE' must be declared
|> Disconnected from ORACLE7 Server Release 7.0.16.4.0 - Production
|> With the procedural, distributed, and Parallel Server options
|> PL/SQL Release 2.0.18.1.0 - Production
|>
|>
|> Even the PRO*C part of the example has got errors when I compile
|> it. It tells me that the pointers can't be assigned in the way
|> the Oracle Manual said it could !!!
|>
|> What I really like about this newsgroup is that I can get good answers.
|> What I don't like is that ORACLE gets really SLOPPY with its code
|> examples and expects the ORACLE community to support itself !!!
Received on Mon Oct 24 1994 - 03:25:24 CET