Home » SQL & PL/SQL » SQL & PL/SQL » merged: PLS-00321 and ORA-00900
merged: PLS-00321 and ORA-00900 [message #301069] Tue, 19 February 2008 02:23 Go to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
I'm writing a test framework to test all the functions and procedures of a database. I have made a package pkgrun with several procedures in. Each procedure calls an other procedure. The commands to test the functions and procedures are stored in the table command. Some commands returns a cursor and there I've got a problem to catch the cursor.

I get the error:
Quote:

Warning: execution completed with warning
PACKAGE BODY pkgrun Compiled.
81/14 PLS-00321: expression 'TESTRESULT_CURSOR_V' is inappropriate as the left hand side of an assignment statement



The procedure start_command (with line 81 is):
PROCEDURE start_command (testtype_v       IN VARCHAR2,
                           command_v        IN VARCHAR2,
                           testvalue_v      IN VARCHAR2, 
                           id_command_v     IN NUMBER,
                           id_run_v         IN NUMBER,
                           returntype_v     IN VARCHAR2)
  IS
  
    test_result_v              VARCHAR2(100);
    testresult_number_v       NUMBER;
    TYPE testresult_cursor_v  IS REF CURSOR;
    number_rows               NUMBER;
  
  BEGIN
    
    IF testtype_v = '' THEN
    
      EXECUTE IMMEDIATE command_v;
    
    ELSE
      
      IF returntype_v = 'NUMBER' THEN
      
        EXECUTE IMMEDIATE command_v
        INTO testresult_number_v;
        test_result_v := to_char(testresult_number_v);
      
      ELSIF returntype_v = 'CURSOR' THEN
      
        EXECUTE IMMEDIATE command_v
        INTO testresult_cursor_v;
        number_rows := testresult_cursor_v%ROWCOUNT;
        test_result_v := to_char(number_rows);
      
      ELSE
      
        EXECUTE IMMEDIATE command_v
        INTO testresult_v;
      
      END IF;
      
      testresult_v := to_char(testresult_v);
      test_value(testtype_v, testvalue_v, testresult_v, id_command_v, id_run_v);
    
    END IF;
    
  END start_command;


Line 81 is: INTO testresult_cursor_v;

ELSIF returntype_v = 'CURSOR' THEN
      
        EXECUTE IMMEDIATE command_v
        INTO testresult_cursor_v;
        number_rows := testresult_cursor_v%ROWCOUNT;
        test_result_v := to_char(number_rows);
Re: PLS-00321: expression 'TESTRESULT_CURSOR_V' is inappropriate as the left hand side of an assignm [message #301089 is a reply to message #301069] Tue, 19 February 2008 03:16 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
a couple of points.
you have declared a type as refcursor.but your are missing a variable declaration of ref cursor;
Quote:
TYPE testresult_cursor_v IS REF CURSOR;

var_name testresult_cursor_v; missing in your code



Quote:
EXECUTE IMMEDIATE command_v
INTO testresult_cursor_v;

instead you need to do this.
type ref_cur is ref cursor;
r_cur ref_cur;
begin
open r_cur for <sql_stats>...



regards,

Re: PLS-00321: expression 'TESTRESULT_CURSOR_V' is inappropriate as the left hand side of an assignm [message #301160 is a reply to message #301089] Tue, 19 February 2008 06:37 Go to previous messageGo to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
Thank you very much dhananjay. This works. Many thanks.
ORA-00900: invalid SQL statement [message #301163 is a reply to message #301069] Tue, 19 February 2008 06:44 Go to previous messageGo to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
At the moment I've got a problem with an execute immediate.
When I compile the package pkgrun, Oracle said:

Quote:

PACKAGE BODY pkgrun Compiled.
No Errors.



In my table command, I got 3 commands that return a cursor (this works and 1 that returns a number).
When I do
exec pkgrun.START_TESTRUN('RESELLER');

I get the message/error:
Error starting at line 1 in command:
exec pkgrun.START_TESTRUN('RESELLER');
Error report:
ORA-00900: invalid SQL statement
ORA-06512: at "OGTEST.PKGRUN", line 74
ORA-06512: at "OGTEST.PKGRUN", line 44
ORA-06512: at "OGTEST.PKGRUN", line 23
ORA-06512: at line 1
00900. 00000 -  "invalid SQL statement"
*Cause:    
*Action:


Here is the code from pkgrun where the problem is located:
PROCEDURE start_command (testtype_v       IN VARCHAR2,
                           command_v        IN VARCHAR2,
                           testvalue_v      IN VARCHAR2, 
                           id_command_v     IN NUMBER,
                           id_run_v         IN NUMBER,
                           returntype_v     IN VARCHAR2)
  IS
  
    testresult_v             VARCHAR2(100);
    testresult_number_v      NUMBER;
    testresult_cursor_v      t_cursor;
  
  BEGIN
    
    IF testtype_v = '' THEN
    
      EXECUTE IMMEDIATE command_v;
    
    ELSE
      
      IF returntype_v = 'NUMBER' THEN
      
        EXECUTE IMMEDIATE command_v
        INTO testresult_number_v;
        testresult_v := to_char(testresult_number_v);
      
      ELSIF returntype_v = 'CURSOR' THEN
      
        OPEN testresult_cursor_v FOR SELECT command_v FROM dual;
          testresult_v := to_char(testresult_cursor_v%ROWCOUNT);
        CLOSE testresult_cursor_v;
      
      ELSE
      
        EXECUTE IMMEDIATE command_v
        INTO testresult_v;
      
      END IF;
      
      testresult_v := to_char(testresult_v);
      test_value(testtype_v, testvalue_v, testresult_v, id_command_v, id_run_v);
    
    END IF;
    
  END start_command;


INTO testresult_number_v; --This is line 74

I hope that someone can help.
Thanks in advance

[Updated on: Tue, 19 February 2008 06:47]

Report message to a moderator

Re: ORA-00900: invalid SQL statement [message #301165 is a reply to message #301163] Tue, 19 February 2008 06:54 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
I see only the code for procedure start_command, but you call START_TESTRUN

Why didn't you post the whole content of the package pkgrun ?
With this limited information we can't help you, sorry
Re: ORA-00900: invalid SQL statement [message #301178 is a reply to message #301165] Tue, 19 February 2008 07:29 Go to previous messageGo to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
PROCEDURE start_testrun calls start_block

PROCEDURE start_block calls start_command

PROCEDURE start_command calls test_value and so on.

The only things that are in start_testrun en start_block are cursors.
In start_command I get the records from command and all the data are in here.

Hope this help.
Re: ORA-00900: invalid SQL statement [message #301181 is a reply to message #301178] Tue, 19 February 2008 07:33 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
xenium wrote on Tue, 19 February 2008 14:29

Hope this help.


Actually no Confused

Re-read what you have posted already:

exec pkgrun.START_TESTRUN('RESELLER');


PROCEDURE start_command (testtype_v       IN VARCHAR2,
                           command_v        IN VARCHAR2,
                           testvalue_v      IN VARCHAR2, 
                           id_command_v     IN NUMBER,
                           id_run_v         IN NUMBER,
                           returntype_v     IN VARCHAR2) IS 
...


How do you call start_command ?

What's in testtype_v?
What's in command_v?
...

I'm not clearvoyant Wink

(if I was, I would be in Aruba right now)
Re: ORA-00900: invalid SQL statement [message #301184 is a reply to message #301181] Tue, 19 February 2008 07:39 Go to previous messageGo to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
OK, no problem. I will attach the file 'package_pkgrun_body'. Hope that will help.
Maybe the code is not that good, but I have to rewrite some parts of the code.

Thanks in advance
Re: ORA-00900: invalid SQL statement [message #301194 is a reply to message #301184] Tue, 19 February 2008 08:22 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
I guess you're going to have to check the content of the COMMAND-table.
As far as I can see one of the records where RETURNTYPE = 'NUMBER' has somehow an invalid SQL-statement in the corresponding COMMAND column.
You could try to narrow it down to try/test it record-per-record to figure out which COMMAND is actually failing.
Re: ORA-00900: invalid SQL statement [message #301336 is a reply to message #301194] Wed, 20 February 2008 01:48 Go to previous messageGo to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
Thanks MarcS. Someone give me the next solutions and I can compile pkgrun.
EXECUTE IMMEDIATE 'begin :temp :=' || q'| 'command_v' ;|' || 'end;' using out testresult_number_v;
/*EXECUTE IMMEDIATE command_v
INTO testresult_number_v;*/
testresult_v := to_char(testresult_number_v);


When I try to execute the script (one command returns a number), I get the message:
Quote:

rror starting at line 2 in command:
exec pkgrun.START_TESTRUN('RESELLER');
Error report:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1
ORA-06512: at "OGTEST.PKGRUN", line 73
ORA-06512: at "OGTEST.PKGRUN", line 44
ORA-06512: at "OGTEST.PKGRUN", line 23
ORA-06512: at line 1
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:



The block:
PROCEDURE start_command (testtype_v       IN VARCHAR2,
                           command_v        IN VARCHAR2,
                           testvalue_v      IN VARCHAR2, 
                           id_command_v     IN NUMBER,
                           id_run_v         IN NUMBER,
                           returntype_v     IN VARCHAR2)
  IS
  
    testresult_v             VARCHAR2(100);
    testresult_number_v      NUMBER;
    testresult_cursor_v      t_cursor;
  
  BEGIN
    
    IF testtype_v = '' THEN
    
      EXECUTE IMMEDIATE command_v;
    
    ELSE
      
      IF returntype_v = 'NUMBER' THEN
      
        EXECUTE IMMEDIATE 'begin :temp :=' || q'| 'command_v' ;|' || 'end;' using out testresult_number_v;
        /*EXECUTE IMMEDIATE command_v
        INTO testresult_number_v;*/
        testresult_v := to_char(testresult_number_v);
      
      ELSIF returntype_v = 'CURSOR' THEN
      
        OPEN testresult_cursor_v FOR SELECT command_v FROM dual;
          testresult_v := to_char(testresult_cursor_v%ROWCOUNT);
        CLOSE testresult_cursor_v;
      
      ELSE
      
        EXECUTE IMMEDIATE command_v
        INTO testresult_v;
      
      END IF;
      
      testresult_v := to_char(testresult_v);
      test_value(testtype_v, testvalue_v, testresult_v, id_command_v, id_run_v);
    
    END IF;
    
  END start_command;


Line 73 is:
EXECUTE IMMEDIATE 'begin :temp :=' || q'| 'command_v' ;|' || 'end;' using out testresult_number_v;
Re: ORA-00900: invalid SQL statement [message #301341 is a reply to message #301336] Wed, 20 February 2008 02:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Check the executed command. See what happens when you execute it directly and check its return type
Re: ORA-00900: invalid SQL statement [message #301343 is a reply to message #301341] Wed, 20 February 2008 02:31 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Frank wrote on Wed, 20 February 2008 09:19
Check the executed command. See what happens when you execute it directly and check its return type


Like Frank is telling: we need to know what's inside command_v.
Your code looks fine to me, but it all depends on what's inside COMMAND_V at time of execution.

You could display the command to be executed, maybe that would tell you (and us) more

DBMS_OUTPUT.PUT_LINE( command_v ) ;

Re: ORA-00900: invalid SQL statement [message #301348 is a reply to message #301343] Wed, 20 February 2008 03:15 Go to previous messageGo to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
The command that returns a number is:
DECLARE x reseller.id%TYPE;
BEGIN
  EXECUTE IMMEDIATE 'begin :temp := ' || q'|pkgreseller.NEW('300', 'testje', 'testen', 5, 'y', 'security dashboard', 'onlineguardian', 'y');|' || 'end;' USING OUT x;

  DBMS_OUTPUT.PUT_LINE('test:' || to_char(x));
END;
/


It adds a new record to table reseller and it gives me the ID (number) of that row.
Re: ORA-00900: invalid SQL statement [message #301354 is a reply to message #301336] Wed, 20 February 2008 03:30 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
xenium wrote on Wed, 20 February 2008 08:48

EXECUTE IMMEDIATE 'begin :temp :=' || q'| 'command_v' ;|' || 'end;' using out testresult_number_v;
/*EXECUTE IMMEDIATE command_v
INTO testresult_number_v;*/
testresult_v := to_char(testresult_number_v);




I should try it as follows:

EXECUTE IMMEDIATE 'begin :temp := ' || command || ';end;' USING out testresult_number_v ;

Re: ORA-00900: invalid SQL statement [message #301359 is a reply to message #301348] Wed, 20 February 2008 03:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
- Why don't you catch your result in a varchar2 and see what value you get returned?
- Did you actually execute the command outside your dynamic sql or did you just assume it would insert a record and return the id?
icon14.gif  Re: ORA-00900: invalid SQL statement [message #301360 is a reply to message #301354] Wed, 20 February 2008 04:02 Go to previous messageGo to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
Thank you very much MarcS. You were a great help to me.
Thumbs Up Thumbs Up Thumbs Up
I hope all the problems are solved.
Re: ORA-00900: invalid SQL statement [message #301361 is a reply to message #301360] Wed, 20 February 2008 04:04 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
xenium wrote on Wed, 20 February 2008 11:02
Thank you very much MarcS. You were a great help to me.
Thumbs Up Thumbs Up Thumbs Up
I hope all the problems are solved.


Your welcome.

And I can assure you there will be more problems around the corner waiting to be solved Very Happy
Re: ORA-00900: invalid SQL statement [message #301375 is a reply to message #301361] Wed, 20 February 2008 06:21 Go to previous messageGo to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
MarcS wrote on Wed, 20 February 2008 11:04
xenium wrote on Wed, 20 February 2008 11:02
Thank you very much MarcS. You were a great help to me.
Thumbs Up Thumbs Up Thumbs Up
I hope all the problems are solved.


Your welcome.

And I can assure you there will be more problems around the corner waiting to be solved Very Happy



I wish there were no problems, but guess, I still got a problem.

Some code from start_command

ELSIF returntype_v = 'CURSOR' THEN
      
        OPEN testresult_cursor_v FOR SELECT command_v FROM dual;
          testresult_v := to_char(testresult_cursor_v%ROWCOUNT);
        CLOSE testresult_cursor_v;


Records where I have as return type CURSOR are (commands) functions, for exemple: pkgreseller.GET(1).
So normally I would write:

select pkgreseller.GET(1) from dual;


Now, in the code I do an execute immediate and I want to count the number of rows (with rowcount).

For the rowcount I get each time: 0, but when I do select pkgreseller.GET(1) from dual, I get 1 row.

Can someone help me? Does I have to write selects for the function in my package or can I work with execute immediate?

Many thanks.
Re: ORA-00900: invalid SQL statement [message #301377 is a reply to message #301375] Wed, 20 February 2008 06:40 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
you need to FETCH the records.


regards,
Re: ORA-00900: invalid SQL statement [message #301389 is a reply to message #301377] Wed, 20 February 2008 07:02 Go to previous messageGo to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
dhananjay wrote on Wed, 20 February 2008 13:40
you need to FETCH the records.


regards,


Thank you for your answer. But can you help me to write the code?

OPEN testresult_cursor_v FOR SELECT command_v FROM dual;
          testresult_v := to_char(testresult_cursor_v%ROWCOUNT);
        CLOSE testresult_cursor_v;


So fetch is it between the open line and testresult_v line? Or how does I have to write it?

Hope you can help.

Regards
Re: ORA-00900: invalid SQL statement [message #301402 is a reply to message #301389] Wed, 20 February 2008 08:16 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
I guess you're looking for something like this:

DECLARE
  TYPE testresult_cursor_type  IS REF CURSOR;
  testresult_cursor_v  testresult_cursor_type ;
  countRows NUMBER := 0 ;
BEGIN
  OPEN testrestult_cursor_v FOR command_v
  LOOP
    FETCH testrestult_cursor_v INTO ...
    countRows := countRows + 1 ;
  END LOOP ;
  CLOSE testrestult_cursor_v ;
END ;


But you'll have to know what is going to be selected as it is needed in the INTO-clause !

Re: ORA-00900: invalid SQL statement [message #301593 is a reply to message #301402] Thu, 21 February 2008 01:49 Go to previous messageGo to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
I was thinking of the problem and I found a solutions and I hope that it will works, but now I get the error:

Quote:

Warning: execution completed with warning
PACKAGE BODY pkgrun Compiled.
84/36 PLS-00221: 'CURSOR_TESTRESULT' is not a procedure or is undefined



The code where line 84 is:

PROCEDURE start_command (testtype_v       IN VARCHAR2,
                           command_v        IN VARCHAR2,
                           testvalue_v      IN VARCHAR2, 
                           id_command_v     IN NUMBER,
                           id_run_v         IN NUMBER,
                           returntype_v     IN VARCHAR2)
  IS
  
    testresult_v             VARCHAR2(100);
    testresult_number_v      NUMBER;
    cursor_testresult        t_cursor;
    counter                  NUMBER;
    --record_testresult        cursor_testresult%ROWTYPE;
  
  BEGIN
    
    IF testtype_v = '' THEN
    
      EXECUTE IMMEDIATE command_v;
    
    ELSE
      
      IF returntype_v = 'NUMBER' THEN
      
        --EXECUTE IMMEDIATE 'begin :temp :=' || q'|'command_v';|' || 'end;' using out testresult_number_v;
        EXECUTE IMMEDIATE 'begin :temp := ' || command_v || ';end;' USING out testresult_number_v ;
        /*EXECUTE IMMEDIATE command_v
        INTO testresult_number_v;*/
        testresult_v := to_char(testresult_number_v);
      
      ELSIF returntype_v = 'CURSOR' THEN
      
        OPEN cursor_testresult FOR 'SELECT ' || command_v || ' FROM DUAL;';
          FOR record_testresult IN cursor_testresult LOOP
            counter := counter + 1;
          END LOOP;
          testresult_v := to_char(counter);
          --testresult_v := to_char(testresult_cursor_v%ROWCOUNT);
          --FETCH cursor_testresult INTO record_testresult;
          --testresult_v := to_char(cursor_testresult%ROWCOUNT);
        CLOSE testresult_cursor_v;
      
      ELSE
      
        EXECUTE IMMEDIATE command_v
        INTO testresult_v;
      
      END IF;
      
      testresult_v := to_char(testresult_v);
      test_value(testtype_v, testvalue_v, testresult_v, id_command_v, id_run_v);
    
    END IF;
    
  END start_command;


Line 84 is: FOR record_testresult IN cursor_testresult LOOP

I don't understand, I have declared cursor_testresult, but why does I get this annoying error.

Hope someone can help.

Regards
Re: ORA-00900: invalid SQL statement [message #301597 is a reply to message #301593] Thu, 21 February 2008 02:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You cannot mix explicit and implicit cursor-constructs.
If you explicitly open a cursor, you cannot do a for-in loop; you will have to use a loop-fetch-end loop.

You will run into SO many problems with this, you will really regret you ever started it..
Re: ORA-00900: invalid SQL statement [message #301598 is a reply to message #301593] Thu, 21 February 2008 02:17 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
There's a difference between CURSORs and CURSOR-variables

Maybe you should read this section

In your code cursor_testresult isn't a cursor, but a cursor variable and that's a completely different thingie.

I could be mistaken on this one as I'm not so familiar with the concept of CURSOR VARIABLES (still learning new things every day Wink )

But if I'm not mistaken, with a CURSOR VARIABLE, you're bound to use OPEN / FETCH / CLOSE

Re: ORA-00900: invalid SQL statement [message #301601 is a reply to message #301598] Thu, 21 February 2008 02:23 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Frank has beaten me to it.

I don't know the level of expertise of xenium, and I don't know the exact requirements for this case.

I've just re-read the first post: he's looking for something to perform unit-testing on database-objects (functions/procedures/packages).

Anyone know some fairly good tools on database unit testing?

Re: merged: PLS-00321 and ORA-00900 [message #301604 is a reply to message #301069] Thu, 21 February 2008 02:37 Go to previous message
hobbes
Messages: 173
Registered: January 2006
Senior Member
Quote:
Anyone know some fairly good tools on database unit testing?

Quest Code Tester would quite well meet xenium's need (framework to test all the functions and procedures of a database).
Previous Topic: A Strange Number
Next Topic: deleting rows from master and child
Goto Forum:
  


Current Time: Tue Dec 06 10:23:58 CST 2016

Total time taken to generate the page: 0.10963 seconds