Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00363: expression 'P_HANDLE_I' cannot be used as an assignment target (11g R1, Server 2003)
PLS-00363: expression 'P_HANDLE_I' cannot be used as an assignment target [message #428844] Fri, 30 October 2009 05:37 Go to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi all,

I have some doubts in using FSEEK procedure of UTL_FILE package.

SQL>select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 64-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

5 rows selected.


In FSEEK procedure, first parameter is file_handle, which is of IN type parameter.
I am passing file handle as shown in below code.

SQL>R
  1  CREATE OR REPLACE FUNCTION COUNT_LINKS(P_HANDLE_I IN UTL_FILE.FILE_TYPE)
  2      RETURN INTEGER
  3      AS
  4          V_COUNT INTEGER;
  5          V_BUFFER VARCHAR2(32000);
  6          V_SQLERRM VARCHAR2(500);
  7      BEGIN
  8          UTL_FILE.FSEEK(P_HANDLE_I, 1, NULL);
  9          BEGIN
 10              LOOP
 11                  UTL_FILE.GET_LINE(P_HANDLE_I, V_BUFFER, 32000);
 12                  IF (INSTR(V_BUFFER, '<!--#') > 0) THEN
 13                      V_COUNT := V_COUNT + 1;
 14                  END IF;
 15              END LOOP;
 16          EXCEPTION
 17              WHEN NO_DATA_FOUND THEN
 18                  NULL;
 19              WHEN OTHERS THEN
 20                  V_SQLERRM := SQLERRM;
 21                  INSERT INTO TRACE_TABLE VALUES('Error in SEO_AUTO_EXCHANGE.COUNT_LINKS => ' ||
 V_SQLERRM, SYSTIMESTAMP);
 22          END;
 23          RETURN V_COUNT;
 24*     END;

Warning: Function created with compilation errors.


And as P_HANDLE_I is neither on the RHS of the expression nor OUT or IO OUT parameter, it should work properly.
But it gives me error as...

SQL>SHOW ERROR
Errors for FUNCTION COUNT_LINKS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/9      PL/SQL: Statement ignored
8/24     PLS-00363: expression 'P_HANDLE_I' cannot be used as an
         assignment target


What can be the cause and its solution?

regards,
Delna.

[Updated on: Fri, 30 October 2009 06:29] by Moderator

Report message to a moderator

Re: PLS-00363: expression 'P_HANDLE_I' cannot be used as an assignment target [message #428846 is a reply to message #428844] Fri, 30 October 2009 05:41 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
And in Oracle docs, it is clearly stated that, this FILE_TYPE parameter is of IN type parameter.

Quote:
Syntax

UTL_FILE.FSEEK (
fid IN utl_file.file_type,
absolute_offset IN PL_INTEGER DEFAULT NULL,
relative_offset IN PLS_INTEGER DEFAULT NULL);


regards,
Delna
Re: PLS-00363: expression 'P_HANDLE_I' cannot be used as an assignment target [message #428847 is a reply to message #428846] Fri, 30 October 2009 05:46 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

p_handle_i IN OUT UTL_FILE.file_type

[Updated on: Fri, 30 October 2009 05:46]

Report message to a moderator

Re: PLS-00363: expression 'P_HANDLE_I' cannot be used as an assignment target [message #428848 is a reply to message #428844] Fri, 30 October 2009 05:46 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
When WHEN OTHERS is not followed by RAISE its always almost a bug.It hides the real error.


Re: PLS-00363: expression 'P_HANDLE_I' cannot be used as an assignment target [message #428849 is a reply to message #428848] Fri, 30 October 2009 05:48 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Its_me_ved wrote on Fri, 30 October 2009 11:46
When WHEN OTHERS is not followed by RAISE its always almost a bug.It hides the real error.

Read the post first , it's a compilation error
Re: PLS-00363: expression 'P_HANDLE_I' cannot be used as an assignment target [message #428851 is a reply to message #428847] Fri, 30 October 2009 05:52 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Yes tahpush,

As specified in other docs, FILE_TYPE parameter is passed as IN OUT type, which is correct one rather than Oracle docs.

Quote:
utl_file.fseek(
file IN OUT file_type,
absolute_offset IN BINARY_INTEGER DEFAULT NULL,
relative_offset IN BINARY_INTEGER DEFAULT NULL);


And this matches with my problem.
And the solution is specified by you.

regards,
Delna
Re: PLS-00363: expression 'P_HANDLE_I' cannot be used as an assignment target [message #428852 is a reply to message #428844] Fri, 30 October 2009 05:58 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
When WHEN OTHERS is not followed by RAISE its always almost a bug.It hides the real error.


This is not the solution of my problem, I have described in OP.
Don't post anything, irrelavent.
See that, I have captured that error in one table.

regards,
Delna
Re: PLS-00363: expression 'P_HANDLE_I' cannot be used as an assignment target [message #428854 is a reply to message #428852] Fri, 30 October 2009 06:06 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
delna.sexy wrote on Fri, 30 October 2009 10:58
Quote:
When WHEN OTHERS is not followed by RAISE its always almost a bug.It hides the real error.


This is not the solution of my problem, I have described in OP.
Don't post anything, irrelavent.
See that, I have captured that error in one table.

regards,
Delna


You should know by now that we always pick up on stuff like that even if it isn't the source of the error.

If you'll going to insert information into a log table from an exception handler you should create a procedure to do so and make it an autonomous_transaction.
That way you can commit it without messing with anything else.
And you should still have a raise otherwise the calling program won't know that anything went wrong.
Re: PLS-00363: expression 'P_HANDLE_I' cannot be used as an assignment target [message #428857 is a reply to message #428852] Fri, 30 October 2009 06:12 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

Don't post anything, irrelavent.

@Delna,
Cookiemonster Sir says,
Quote:


And you should still have a raise otherwise the calling program won't know that anything went wrong.

Smile Does it sounds so irrelavent?
This is a forum. I have just added an advice though and just to help you and to learn from you as well.
Best of Luck!

Regards,
Ved

[Updated on: Fri, 30 October 2009 06:14]

Report message to a moderator

Re: PLS-00363: expression 'P_HANDLE_I' cannot be used as an assignment target [message #428860 is a reply to message #428857] Fri, 30 October 2009 06:27 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Oh...
Sorry Ved. I didn't notice that.
But as you hadn't provided any specification as done by cookiemonster sir, I couldn't get that.

By the way, thanks all.

regards,
Delna

[Updated on: Fri, 30 October 2009 06:27]

Report message to a moderator

Re: PLS-00363: expression 'P_HANDLE_I' cannot be used as an assignment target [message #428863 is a reply to message #428860] Fri, 30 October 2009 06:30 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
delna.sexy wrote on Fri, 30 October 2009 06:27
Oh...
Sorry Ved. I didn't notice that.
But as you hadn't provided any specification as done by cookiemonster sir, I couldn't get that.

By the way, thanks all.

regards,
Delna



Here's something more for you..Smile
http://tkyte.blogspot.com/2008/06/when-others-then-null-redux.html
http://tkyte.blogspot.com/2008/01/why-do-people-do-this.html

[Updated on: Fri, 30 October 2009 06:32]

Report message to a moderator

Re: PLS-00363: expression 'P_HANDLE_I' cannot be used as an assignment target [message #428864 is a reply to message #428852] Fri, 30 October 2009 06:31 Go to previous message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
delna.sexy wrote on Fri, 30 October 2009 11:58
Quote:
When WHEN OTHERS is not followed by RAISE its always almost a bug.It hides the real error.


This is not the solution of my problem, I have described in OP.
Don't post anything, irrelavent.
See that, I have captured that error in one table.

regards,
Delna

Don't post something we repeated you it is STUPID.
YES, it is a REAL BUG.

Regards
Michel

[Updated on: Fri, 30 October 2009 06:32]

Report message to a moderator

Previous Topic: How do I make use of the type that I had created in if then code?
Next Topic: LOADING DATA FROM ASCII FILE DELIMITED BY (;) TO ORACLE TABLE USING PL/SQL
Goto Forum:
  


Current Time: Sat Sep 24 21:28:16 CDT 2016

Total time taken to generate the page: 0.07803 seconds