Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: DBA Question! TRACE with DYNAMIC PL/SQL
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;
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