Home » SQL & PL/SQL » SQL & PL/SQL » Error while opening cursor when using TRANSLATE (10g,Linux)
Error while opening cursor when using TRANSLATE [message #347915] Mon, 15 September 2008 01:21 Go to next message
lamb
Messages: 3
Registered: September 2008
Junior Member
Hi,
When i open a cursor this way

OPEN cur_ex FOR translate(vsql USING CHAR_CS);

where
vsql NVARCHAR2(100);
vsql := 'SELECT * from some_table';
I get an ORA-00900: invalid SQL statement error.
I'm Using oracle 10g.

Could anyone please tell me what could be wrong or point me to the place where this may have already been discussed.
Thanks a lot.

Re: Error while opening cursor when using TRANSLATE [message #347918 is a reply to message #347915] Mon, 15 September 2008 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

2/
Always post your Oracle version (4 decimals).

3/
Use SQL*Plus and copy and paste your session.

4/
OPEN cur_ex FOR translate(vsql USING CHAR_CS);
Where did you see this is a valid syntax?
What do you want to do?

Regards
Michel
Re: Error while opening cursor when using TRANSLATE [message #347944 is a reply to message #347915] Mon, 15 September 2008 02:39 Go to previous messageGo to next message
lamb
Messages: 3
Registered: September 2008
Junior Member
Michel,
I have given below as an example of the code which has a problem.
The function compiles properly but when executing it gives an Invalid statement error
when opening the cursor.
I have imported the data from another database using exp/imp.
The database was exported from oracle 9.2.0.1 and imported to oracle 10.2.0.1.
I exported/imported using exp/imp of oracle 9.2.0.1.
Both the character set and the national character set of both the databases are the same i.e
WE8ISO8859P1 and AL16UTF16.
Thanks for your response.

CREATE OR REPLACE
FUNCTION test_func
    (
      po_sql_code OUT INTEGER,
      po_sql_message OUT NVARCHAR2 
    )
    RETURN INTEGER IS
    v_sql NVARCHAR2(200);
    v_count	INTEGER;
    cur_count pkg_util.cursortype;
  BEGIN
    v_sql := 'SELECT COUNT(*) from some_table' ;
    OPEN cur_count FOR TRANSLATE(v_sql USING CHAR_CS);
    FETCH cur_count INTO v_count;
    CLOSE cur_count;
    IF v_count > 0 THEN 
      RETURN 0;
    ELSE
      RETURN -1;
    END IF;
  EXCEPTION
  WHEN OTHERS THEN
    po_sql_code    := SQLCODE;
    po_sql_message := SQLERRM;
    RETURN -1;
  END;


CREATE OR REPLACE
PACKAGE PKG_UTIL
AS
TYPE cursortype
IS
  REF  CURSOR;
  FUNCTION getUtcDateMSeconds RETURN NUMBER;
  FUNCTION getMiliseconds  RETURN NUMBER;
  PROCEDURE sleep(pi_interval NUMBER );
END PKG_UTIL;

Re: Error while opening cursor when using TRANSLATE [message #347949 is a reply to message #347944] Mon, 15 September 2008 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to translate into an intermediate variable.
In addition, NVARCHAR2 should be initialized with N'string' not 'string'.
SQL> CREATE OR REPLACE
  2  FUNCTION test_func
  3      (
  4        po_sql_code OUT INTEGER,
  5        po_sql_message OUT NVARCHAR2 
  6      )
  7      RETURN INTEGER IS
  8      v_sql NVARCHAR2(200);
  9      v_count INTEGER;
 10      cur_count pkg_util.cursortype;
 11    BEGIN
 12      v_sql := 'SELECT COUNT(*) from some_table' ;
 13      OPEN cur_count FOR TRANSLATE(v_sql USING CHAR_CS);
 14      FETCH cur_count INTO v_count;
 15      CLOSE cur_count;
 16      IF v_count > 0 THEN 
 17        RETURN 0;
 18      ELSE
 19        RETURN -1;
 20      END IF;
 21    END;
 22  /

Function created.

SQL> exec :ret := test_func(:code,:msg) 
BEGIN :ret := test_func(:code,:msg); END;

*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at "MICHEL.TEST_FUNC", line 12
ORA-06512: at line 1

SQL> CREATE OR REPLACE
  2  FUNCTION test_func
  3      (
  4        po_sql_code OUT INTEGER,
  5        po_sql_message OUT NVARCHAR2 
  6      )
  7      RETURN INTEGER IS
  8      v_sql NVARCHAR2(200);
  9      v_sql2 VARCHAR2(200);
 10      v_count INTEGER;
 11      cur_count pkg_util.cursortype;
 12    BEGIN
 13      v_sql := N'SELECT COUNT(*) from some_table' ;
 14      v_sql2 := TRANSLATE(v_sql USING CHAR_CS);
 15      OPEN cur_count FOR v_sql2;
 16      FETCH cur_count INTO v_count;
 17      CLOSE cur_count;
 18      IF v_count > 0 THEN 
 19        RETURN 0;
 20      ELSE
 21        RETURN -1;
 22      END IF;
 23    END;
 24  /

Function created.

SQL> exec :ret := test_func(:code,:msg) 
BEGIN :ret := test_func(:code,:msg); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "MICHEL.TEST_FUNC", line 14
ORA-06512: at line 1

Regards
Michel

[Updated on: Mon, 15 September 2008 04:31]

Report message to a moderator

Re: Error while opening cursor when using TRANSLATE [message #347977 is a reply to message #347949] Mon, 15 September 2008 04:17 Go to previous message
lamb
Messages: 3
Registered: September 2008
Junior Member
Michel,
Thanks a lot. Using an intermediate variable and using an N'string' is the way to go.
Regards,
Lambor.
Previous Topic: Truncate the full database
Next Topic: What stops a trigger fireing
Goto Forum:
  


Current Time: Wed Dec 07 12:57:33 CST 2016

Total time taken to generate the page: 0.14163 seconds