Re: problem with stored procedure

On Fri, 17 Jan 2003 09:01:43 -0800, DA Morgan <damorgan_at_exesolutions.com> wrote:

>Ed Stevens wrote:
>
>> Oracle 8.1.7 on NT
>>
>> Stored procedure that begins with
>>
>> CREATE OR REPLACE PROCEDURE nmm.Extract_User_Table
>> (pi_FileName IN VARCHAR2,
>> pi_FileDir IN VARCHAR2,
>> pi_TableName IN VARCHAR2,
>> pi_Delimeter IN OUT VARCHAR2,
>> po_ErrorOut OUT NUMBER) IS
>> /*******************************************************************************
>> * DESCRITION:
>> *
>> ********************************************************************************/
>>
>> Runs fine in TOAD, supplying values for the input variables in the
>> Debug | Set Parameters dialog.
>>
>> But in SQLPlus, I get this (the EXEC statement -- which may be wrapped
>> in this msg -- is what I key in. The BEGIN statement is echoed back.)
>>
>> Connected.
>> SQL> exec nmm.extract_user_table ('attribute_master.csv',
>> 'abpserver1\data_log_files\logs', 'dsa.attribute_master', ',', '0')
>> BEGIN nmm.extract_user_table("attribute_master.csv",
>> "abpserver1\data_log_files\logs", "dsa.attribute_master", ",", 0);
>> END;
>>
>> *
>> ERROR at line 1:
>> ORA-06550: line 1, column 30:
>> PLS-00201: identifier 'attribute_master.csv' must be declared
>> ORA-06550: line 1, column 7:
>> PL/SQL: Statement ignored
>>
>> SQL>
>>
>> It seems to be takeing a parameter value and interpreting it as an
>> identifier name.
>>
>> ???
>
>Perhaps. But I'm more concerned by what appears to be passing in single ticks or a
>comma and the back-slashes. Try removing all parameters after the first one, just
>replace with variables in the proc temporarily, and then add them in one at a time.
>Debug the first parameter alone and see what happens. I suspect it is not the
>problem. But then again I could learn something here.
>
>Daniel Morgan

Daniel,

Thx for the reply. The backslashes should be good, this in in Widoze. After some other playing around with this, it appears the problem is in a SELECT . . FROM ALL_TABLES in the procedure. Given this setup:

CREATE OR REPLACE PROCEDURE scott.my_procedure . . . .. .
.
v_Owner := 'MARY'

.
.
.
     FOR irec in
	    ( SELECT column_name FROM all_tab_columns
	     WHERE TABLE_NAME = UPPER(pi_TableName)
	      AND OWNER = UPPER(v_Owner)
		 ORDER BY COLUMN_ID)
	LOOP
	  -- do some stuff 
     END LOOP;

END my_procedure;

C:> sqlplus fred/fred
SQL> execute scott.my_procedure (...)

So, we have fred connecting and executing a procedure owned by scott, which does a select from an ALL_ view where owner = 'MARY'. Is the ALL_* view that of scott, who owns the procedure, or fred, who is connected and executing the procedure? Who has to have SELECT privileges on MARY's objects? Received on Thu Jan 23 2003 - 09:30:05 CST