Home » SQL & PL/SQL » SQL & PL/SQL » Again? ORA-00911: invalid character Error (Oracle 10g)
icon9.gif  Again? ORA-00911: invalid character Error [message #344924] Mon, 01 September 2008 21:39 Go to next message
xeophillus
Messages: 6
Registered: September 2008
Junior Member

Embarassed

I know this is a common error in oracle and many people has asked this before, but this one is quite annoying.
Background: I have to import data from another Oracle database which has multiple schemas in it, and the tables in each schema is identical each other.
I want to make an Procedure that can automatically import all data from specific table from all schemas with dynamic SQL(so I don't have to hard code the schema name).
REFCURSOR is used but there is an error in SQL Statement
DECLARE 
  -- Local variables here
CURSOR C_SCHEMA IS
       SELECT SCHEMA
       FROM IFSAPP.M_ENTITY_TAB;

TYPE C_REF IS REF CURSOR;
DATA      C_REF;
TOTAL_HECTARAGE NUMBER;
SCHEMA_CODE     VARCHAR2(30 CHAR);
FIELD_CODE      VARCHAR2(30 CHAR);



BEGIN
  -- Test statements here
FOR REC_ IN C_SCHEMA LOOP
    
      BEGIN
      OPEN DATA FOR 'SELECT FIELDCODE,TOTALHECTARAGE FROM '||REC_.SCHEMA'||'.IBU_FIELDHISTORY@TDCONSOL;';
      FETCH DATA INTO FIELD_CODE, TOTAL_HECTARAGE;
      
      INSERT INTO IFSAPP.PARAMETER_TABLE(CODE, HECTARAGE)
      VALUES (FIELD_CODE, TOTAL_HECTARAGE);
      COMMIT;
      
      CLOSE DATA;
      END;
    
  END LOOP;       
     
END;


When I compile it, there is an error message, ORA-00911: invalid character right on the OPEN DATA FOR etc..

Can anyone help me? thanks before for any answer Confused Smile

[Updated on: Mon, 01 September 2008 22:06] by Moderator

Report message to a moderator

Re: Again? ORA-00911: invalid character Error [message #344927 is a reply to message #344924] Mon, 01 September 2008 22:12 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

>OPEN DATA FOR 'SELECT FIELDCODE,TOTALHECTARAGE FROM '||REC_.SCHEMA'||'.IBU_FIELDHISTORY@TDCONSOL;';

Single quote marks are supposed to happen in pairs. Right?
In other words SQL statement should not have an odd number of single quote marks.

What about (ab)using:
ALTER SESSION SET CURRENT_SCHEMA=NEW_SCHEMA;

PL/SQL is NOT required.
You can write SQL to write SQL.

[Updated on: Mon, 01 September 2008 22:15] by Moderator

Report message to a moderator

Re: Again? ORA-00911: invalid character Error [message #344928 is a reply to message #344927] Mon, 01 September 2008 22:22 Go to previous messageGo to next message
xeophillus
Messages: 6
Registered: September 2008
Junior Member

Anacedent,

I've changed the quote marks into pairs now..
OPEN DATA FOR 'SELECT FIELDCODE,TOTALHECTARAGE FROM '||REC_.SCHEMA ||'.IBU_FIELDHISTORY@TDCONSOL;';


but unfortunately it's still error.

Ok, i'll try the alter session method and write back to you.

[Updated on: Mon, 01 September 2008 22:32]

Report message to a moderator

Re: Again? ORA-00911: invalid character Error [message #344939 is a reply to message #344928] Mon, 01 September 2008 23:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but unfortunately it's still error.

Which one? Use SQL*Plus and copy and paste your session.

';' should not be inside the dynamic SQL string.

Regards
Michel

[Updated on: Mon, 01 September 2008 23:15]

Report message to a moderator

Problem's solved [message #344941 is a reply to message #344924] Mon, 01 September 2008 23:17 Go to previous messageGo to next message
xeophillus
Messages: 6
Registered: September 2008
Junior Member

It seems I have solved the problem.. Laughing

My original code would be
OPEN DATA FOR 'SELECT FIELDCODE, TOTALHECTARAGE FROM '||REC_.SCHEMA||'.IBU_FIELDHISTORY@TDCONSOL WHERE ROWNUM<2;';


And then I modified it by eliminating the last semicolon
OPEN DATA FOR 'SELECT FIELDCODE, TOTALHECTARAGE FROM '||REC_.SCHEMA||'.IBU_FIELDHISTORY@TDCONSOL WHERE ROWNUM<2';


But, the problem didn't stop there. After I eliminate the semi-colon it still returned error, but this time it comes from the data in the table which I refer (or I think) contains the schema list in another database. There are some data that doesn't match with the real schema name.. This is quite frustating, remembering that I got the data from my DBA Shocked

But what's done is done.

Many thanks to Anacedent who helped me earlier Razz
Re: Again? ORA-00911: invalid character Error [message #344944 is a reply to message #344939] Mon, 01 September 2008 23:23 Go to previous messageGo to next message
xeophillus
Messages: 6
Registered: September 2008
Junior Member

Michel Cadot wrote on Mon, 01 September 2008 23:13
Quote:
but unfortunately it's still error.

Which one? Use SQL*Plus and copy and paste your session.

';' should not be inside the dynamic SQL string.

Regards
Michel




The error I meant was still the ORA-00911 one.

Actually before I realized that the data in my C_SCHEMA is error I have tried to remove the semicolon but it didn't work

Yes the semi colon shouldn't be inside the dynamic SQL string ( I posted my last post before I read your message Michel but many thanks to you too Smile )

The real problem here is the data in my C_SCHEMA should contains the schema names in another database, BUT it didn't. so the script returned error all the way..

Hope this information is useful for others.

Re: Again? ORA-00911: invalid character Error [message #345034 is a reply to message #344944] Tue, 02 September 2008 03:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why wouldn't you just loop through the list of Schemas, and use execute immediate to run a statement like
INSERT INTO my_table (col_1,col_2) SELECT col_1,col_2 from <schema_name>.my_table;
Re: Again? ORA-00911: invalid character Error [message #345187 is a reply to message #345034] Tue, 02 September 2008 09:53 Go to previous messageGo to next message
xeophillus
Messages: 6
Registered: September 2008
Junior Member

I've tried to do that. but we can not directly insert schema name from looping.

for example, get the schema list data and put it on code like

DECLARE

V_SCHEMA VARCHAR2(20 CHAR);

BEGIN

--This is just an example to get 1 row from the table, I skip all the looping section.
SELECT SCHEMA_NAME INTO V_SCHEMA
FROM SCHEMA_LIST_TABLE WHERE ROWNUM < 2;

INSERT INTO FIELD_HISTORY ( SCHEMA_NAME, FIELD_CODE, PARAM_A )
SELECT SCHEMA, FLDCODE, TOTHECTARAGE
FROM V_SCHEMA.TR_FIELD_HIST@MYDBLINK;

END;


I've tried to do that method and didn't work out. Oracle seems to read that there is a schema named 'V_SCHEMA' which is of course doesn' exist; while what we meant is to insert V_SCHEMA from above select statement.

I hope that answers you Razz Cool
Re: Again? ORA-00911: invalid character Error [message #345195 is a reply to message #345187] Tue, 02 September 2008 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The important part is (emphasize is mine):
Quote:
use execute immediate to run a statement like...

You didn't do that.

Regards
Michel
Re: Again? ORA-00911: invalid character Error [message #345203 is a reply to message #345195] Tue, 02 September 2008 10:35 Go to previous messageGo to next message
xeophillus
Messages: 6
Registered: September 2008
Junior Member

Michel Cadot wrote on Tue, 02 September 2008 10:11
The important part is (emphasize is mine):
Quote:
use execute immediate to run a statement like...

You didn't do that.

Regards
Michel




Could you explain the Execute Immediate one? Shocked I'm unfamiliar with that and never use one.
Re: Again? ORA-00911: invalid character Error [message #345210 is a reply to message #345203] Tue, 02 September 2008 10:53 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/dynamic.htm#sthref1559

Regards
Michel
Previous Topic: DB2 to Oracle SQL return codes
Next Topic: Resume Data
Goto Forum:
  


Current Time: Sun Dec 11 03:59:57 CST 2016

Total time taken to generate the page: 0.09771 seconds