Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: DBA Question! TRACE with DYNAMIC PL/SQL

Re: DBA Question! TRACE with DYNAMIC PL/SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 25 Aug 1999 11:43:49 GMT
Message-ID: <37c4d59f.1858742@newshost.us.oracle.com>


A copy of this was sent to Nandakumar <N.Kumar_at_rocketmail.com> (if that email address didn't require changing) On Tue, 24 Aug 1999 19:55:30 GMT, you wrote:

>Hi,
>
>Where is this TRACE file available for inspection?
>i received 'ORA-03113 : end-of-file on communication channel'.
>All i tried to do is bind a variable in a dynamic pl/sql block.
>i've given the pl/sql code at the end of this mail.
>
>What the ora error document says is:
>
>ORA-03113: end-of-file on communication channel
>
>Cause: An unexpected end-of-file was processed on the communication
>chan-nel. The problem could not be handled by the Net8, two task,
>software. This message could occur if the shadow two-task process
>associated with a Net8 connect has terminated abnormally, or if there is
>a physical failure of the inter-process communication vehicle, that is,
>the network or server machine went down.
>
>Action: If this message occurs during a connection attempt, check the
>setup files for the appropriate Net8 driver and confirm Net8 software is
>correctly installed on the server. If the message occurs after a
>connection is well estab-lished, and the error is not due to a physical
>failure, check if a trace file was gen-erated on the server at failure
>time. Existence of a trace file may suggest an Oracle internal error
>that requires the assistance of customer support.
>
>i guess in my case, there couldn't have been a failure in connection!
>so how do i check the TRACE now?
>

trace files are in the user_dump_dest (init.ora parameter) directory on the server.

I've tested this.

In 7.x -- this procedure returns (correctly) the error ERROR at line 1:

ORA-01003: no statement parsed
ORA-06512: at "SYS.DBMS_SYS_SQL", line 252
ORA-06512: at "SYS.DBMS_SQL", line 44
ORA-06512: at "TKYTE.P_DUMMY", line 7
ORA-06512: at line 1


when the exception handler is removed. that is because you are binding before parsing but you must PARSE before you bind.

Also, your procedure would not work even when you have the steps in the right order -- you cannot bind identifiers. You cannot bind a TABLENAME or COLUMN name into a sql statement ever.

You must concatenate in the table/view name. eg:

  v_csr_str := 'declare

                  cursor view_cursor is select * from ' || v_from || '
                begin
                  dbms_output.put_line( ''Hi, i am dynamic'' );
                end;'

You can only bind things that you could put in a character string constant in sqlplus. for example:

select * from emp where ename = :x

is valid because in sqlplus you can code "select * from emp where ename = 'KING'" on the other hand:

select * from :emp where ename = 'KING'

is *not* valid since you cannot code:

select * from 'EMP' where ename = 'KING'

in sqlplus.

I've reproduced your error in 8.0 and 8.1 with the following very small routine:

tkyte_at_8i> declare
  2 v_cursor_id integer;
  3 begin

  4          v_cursor_id :=  DBMS_SQL.OPEN_CURSOR;
  5          DBMS_SQL.BIND_VARIABLE(v_cursor_id,':x','x');
  6 end;
  7 /
ERROR:
ORA-01041: internal error. hostdef extension doesn't exist

declare
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

it appears that binding to an un-parsed cursor is not being handled correctly as it was in v7. I've filed a bug on this.

>Any help is appreciated.
>---------------------------------
>PL/SQL CODE
>
>
>create or replace procedure p_dummy (
> v_from varchar2
>) as
> v_cursor_id integer;
> v_csr_ret integer;
> v_csr_str varchar2(2048);
> v_from_view varchar2(32) := v_from;
>begin
>
> v_cursor_id := DBMS_SQL.OPEN_CURSOR;
> v_csr_str := 'declare
> cursor view_cursor is select * from :from_view;
> begin
> dbms_output.put_line(''hi i am executed dynamically'');
> end;';
> DBMS_SQL.BIND_VARIABLE(v_cursor_id,':from_view',v_from_view);
> DBMS_SQL.PARSE(v_cursor_id,v_csr_str,DBMS_SQL.NATIVE);
> v_csr_ret := DBMS_SQL.EXECUTE(v_cursor_id);
> DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
>
>EXCEPTION
> when OTHERS then
> DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
>end;

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Aug 25 1999 - 06:43:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US