Home
All Oracle Error Codes
Oracle DBA Forum

Frequent Oracle Errors

TNS:could not resolve the connect identifier specified
Backtrace message unwound by exceptions
invalid identifier
PL/SQL compilation error
internal error
missing expression
table or view does not exist
end-of-file on communication channel
TNS:listener unknown in connect descriptor
insufficient privileges
PL/SQL: numeric or value error string
TNS:protocol adapter error
ORACLE not available
target host or object does not exist
invalid number
unable to allocate string bytes of shared memory
resource busy and acquire with NOWAIT specified
error occurred at recursive SQL level string
ORACLE initialization or shutdown in progress
archiver error. Connect internal only, until freed
snapshot too old
unable to extend temp segment by string in tablespace
Credential retrieval failed
missing or invalid option
invalid username/password; logon denied
unable to create INITIAL extent for segment
out of process memory when trying to allocate string bytes
shared memory realm does not exist
cannot insert NULL
TNS:unable to connect to destination
remote database not found ora-02019
exception encountered: core dump
inconsistent datatypes
no data found
TNS:operation timed out
PL/SQL: could not find program
existing state of packages has been discarded
maximum number of processes exceeded
error signaled in parallel query server
ORACLE instance terminated. Disconnection forced
TNS:packet writer failure
see ORA-12699
missing right parenthesis
name is already used by an existing object
cannot identify/lock data file
invalid file operation
quoted string not properly terminated

RE: dynamic dynamic sql within a procedure - ora_00911

Ruth Gramolini

2004-04-16

Replies:
I have been trying to write an sql script that will do the same thing. I am
having a bear of a time. I can get the pieces easily enough but I can't put
them together in a meaningful way. I have been trying with a union to get
the create or replace part unioned to the stuff from source$. If you comeup
with a solution let me know, if I do I will share too.

Ruth

-----Original Message-----
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)
Sent: Thursday, April 15, 2004 11:52 AM
To: 'oracle-l@(protected)'
Subject: RE: dynamic dynamic sql within a procedure - ora_00911


Ruth,
What I am trying to do is create a code generator..... I am creating new
tables and want to create insert, delete, update and select procedures
for all of the tables. Rather than do them one by one and having to fix
them every time the developers want to change the name, etc.... I
decided to try to build a code generator. So I want my admin procedure
which is passed an owner and table name to spit out
select, delete, insert and update procedure for that user. This is what I
have which WORKS for the select all proc.
You need to create a type package for the cursorvar and the dba user needs
create any and drop any priveledges for procedures.

It will have to be executed by the user exec
TABLE_NAME_select_all_proc in
this case.

v_sql := 'CREATE or REPLACE PROCEDURE '|| upper(P_OWNER_NAME) ||'.'||
upper(P_TABLE_NAME) || '_SELECT_ALL_PROC ( ' || chr(10)||
'  P_ALL_ROWS_CURSOR out types.cursorvar ' || chr(10) ||
'   ) is ' || chr(10) ||
'   Begin ' || chr(10) ||
'     open P_ALL_ROWS_CURSOR for ' || chr(10) ||
'     select * ' || chr(10) ||
'     from ' || upper(P_OWNER_NAME) ||'.'|| upper(P_TABLE_NAME) ||
chr(10)||
'     order by 1; ' || chr(10)||
'   End;' ;

v_sql := rtrim(v_sql,' ');

execute immediate v_sql;

This is what I ended up for this one.... I was doing to much work for the
select all procedure , but I face the problem with delete, select
and insert where I need to dynamic dynamic.... I am going to try
to execute
sql into a variable I think then try to || them together
Unless someone here comes up with a better plan...:)

Thanks,

Kathy

-----Original Message-----
From: Ruth Gramolini [mailto:rgramolini@(protected)]
Sent: Thursday, April 15, 2004 9:23 AM
To: oracle-l@(protected)
Subject: RE: dynamic dynamic sql within a procedure - ora_00911


Once this procedure is created, how do you run it. I asked my boss, the
super duhveloper, but he didn't know. I know this is a stupic
question, but
I don't do much sql.

Thanks in advance,
Ruth

  -----Original Message-----
  From: oracle-l-bounce@(protected)
  [mailto:oracle-l-bounce@(protected)
  Sent: Thursday, April 15, 2004 7:57 AM
  To: 'oracle-l@(protected)'
  Cc: 'kduret@(protected)'
  Subject: RE: dynamic dynamic sql within a procedure - ora_00911


  Kathy,

  I modified your proc a bit and got it to create the procedure for
  me - but I
  was missing the types.cursorvar variable. I'm assuming that
you have this
  created in your database someplace.

  This is a pretty interesting idea! Here is the new proc:


  CREATE OR REPLACE PROCEDURE Generate_Select_All_Proc (
  P_OWNER_NAME IN DBA_TABLES.OWNER%TYPE,
  P_TABLE_NAME IN DBA_TABLES.TABLE_NAME%TYPE)

  IS

  err_num BINARY_INTEGER;
  err_msg VARCHAR(300);
  v_sql VARCHAR2(2000) := NULL;

  BEGIN

  v_sql := 'CREATE OR REPLACE PROCEDURE ' ||
        P_TABLE_NAME || '_SELECT_ALL_PROC ( ' ||
        ' P_ALL_ROWS_CURSOR OUT types.cursorvar ' ||
        ' ) IS ' ||
        ' BEGIN ' ||
        ' OPEN P_ALL_ROWS_CURSOR FOR ' ||
        ' SELECT * FROM ' ||
        P_OWNER_NAME||'.'|| P_TABLE_NAME ||
        ' ORDER BY 1; ' ||
        'END;';

  v_sql := RTRIM(v_sql,' ');

  dbms_output.put_line (SUBSTR(v_sql,1,255));
  dbms_output.put_line (SUBSTR(v_sql,256,255));

  EXECUTE IMMEDIATE v_sql;

  EXCEPTION

  WHEN OTHERS
  THEN
  err_num := SQLCODE;
  err_msg := SQLERRM;

  RAISE_APPLICATION_ERROR(-20011,'Oracle error '||err_num||
                      'generate_select_all_proc : '||err_msg);

  END Generate_Select_All_Proc;

  Good Luck!

  Tom Mercadante
  Oracle Certified Professional


  -----Original Message-----
  From: Duret, Kathy [mailto:kduret@(protected)]
  Sent: Wednesday, April 14, 2004 5:50 PM
  To: oracle-l@(protected)
  Subject: dynamic dynamic sql within a procedure - ora_00911


  Ok, I am trying to create a sql generator. I am trying to start
  out simple.
  What I want to do it to pass an owner, table name into a
  procedure and have
  it create a procedure for this owner table name
  In this case I want to create a procedure that will create a procedure
  tablename_select_all_proc that will select * from owner.tablename

  The syntax when I pull it apart looks ok. But when I run it I get an
  ora_00911, the procedure and output are below.

  It looks to me as if it is splicing the lines up. Instead of using the
  execute immediate do I have to use dbms_sql to parse and fetch
  this sql? Or
  is there a way to do this?

  platform 10G, Solaris.

  Thanks,

  Kathy

  -------------- Procedure

  CREATE OR REPLACE PROCEDURE GENERATE_SELECT_ALL_PROC (
  P_OWNER_NAME in DBA_TABLES.OWNER%TYPE,
  P_TABLE_NAME in DBA_TABLES.TABLE_NAME%TYPE)

  IS

  err_num binary_integer;
  err_msg varchar(300);
  v_sql varchar2(2000) := null;

  BEGIN

  v_sql := 'Select ''CREATE OR REPLACE PROCEDURE '' ||
  P_TABLE_NAME || ''_SELECT_ALL_PROC ( '' ||
  chr(10)||
  '' P_ALL_ROWS_CURSOR out types.cursorvar '' ||
  '' ) is '' ||
  '' Begin '' || chr(10) ||
  '' open P_ALL_ROWS_CURSOR for '' ||
  '' select * from '' ||
  P_OWNER_NAME||''.''|| P_TABLE_NAME ||
  '' order by 1; '' || chr(10) ||
  '' END '' || P_TABLE_NAME||''_SELECT_ALL_PROC;''
  from dual;';

  v_sql := rtrim(v_sql,' ');

  dbms_output.put_line (substr(v_sql,1,255));
  dbms_output.put_line (substr(v_sql,256,255));

  execute immediate v_sql;

  EXCEPTION

  WHEN OTHERS
  THEN
  err_num := SQLCODE;
  err_msg := SQLERRM;

  raise_application_error(-20011,'Oracle error '||err_num||'
  generate_select_all_proc : '||err_msg);

  END generate_select_all_proc;

  ----------------------- OUTPUT FROM DBMS_PUT_LINE

  Select 'CREATE OR REPLACE PROCEDURE ' ||
  P_TABLE_NAME || '_SELECT_ALL_PROC ( '
  ||
  chr(10)||
  '  P_ALL_ROWS_CURSOR out types.cursorvar ' ||
  '   ) is ' ||
  '
  Begin ' || chr(10) ||
  '   open P_ALL_ROWS_CURSOR for ' ||
  '       select *
  from ' ||
  P_OWNER
  _NAME||'.'|| P_TABLE_NAME ||
  '     order by 1; ' || chr(10) ||
  '   END ' ||
  P_TABLE_NAME||'_SELECT_ALL_PROC;'
  from dual;
  declare
  *
  ERROR at line 1:
  ORA-00911: invalid character
  ORA-06512: at line 26








  This transmission contains information solely for intended
  recipient and may
  be privileged, confidential and/or otherwise protect from
disclosure. If
  you are not the intended recipient, please contact the sender and
  delete all
  copies of this transmission. This message and/or the materials
contained
  herein are not an offer to sell, or a solicitation of an offer
to buy, any
  securities or other instruments. The information has been obtained or
  derived from sources believed by us to be reliable, but we do not
  represent
  that it is accurate or complete. Any opinions or estimates contained in
  this information constitute our judgment as of this date and are
  subject to
  change without notice. Any information you share with us will
be used in
  the operation of our business, and we do not request and do not want any
  material, nonpublic information. Absent an express prior written
  agreement,
  we are not agreeing to treat any information confidentially and
  will use any
  and all information and reserve the right to publish or disclose any
  information you share with us.


  ----------------------------------------------------------------
  Please see the official ORACLE-L FAQ: http://www.orafaq.com
  ----------------------------------------------------------------
  To unsubscribe send email to: oracle-l-request@(protected)
  put 'unsubscribe' in the subject line.
  --
  Archives are at http://www.freelists.org/archives/oracle-l/
  FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  -----------------------------------------------------------------
  ----------------------------------------------------------------
  Please see the official ORACLE-L FAQ: http://www.orafaq.com
  ----------------------------------------------------------------
  To unsubscribe send email to: oracle-l-request@(protected)
  put 'unsubscribe' in the subject line.
  --
  Archives are at http://www.freelists.org/archives/oracle-l/
  FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  -----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------



This transmission contains information solely for intended
recipient and may
be privileged, confidential and/or otherwise protect from disclosure. If
you are not the intended recipient, please contact the sender and
delete all
copies of this transmission. This message and/or the materials contained
herein are not an offer to sell, or a solicitation of an offer to buy, any
securities or other instruments. The information has been obtained or
derived from sources believed by us to be reliable, but we do not
represent
that it is accurate or complete. Any opinions or estimates contained in
this information constitute our judgment as of this date and are
subject to
change without notice. Any information you share with us will be used in
the operation of our business, and we do not request and do not want any
material, nonpublic information. Absent an express prior written
agreement,
we are not agreeing to treat any information confidentially and
will use any
and all information and reserve the right to publish or disclose any
information you share with us.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------