Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00306: wrong number or types of arguments
PLS-00306: wrong number or types of arguments [message #246133] Wed, 20 June 2007 01:26 Go to next message
rajesh_bhadu
Messages: 48
Registered: June 2007
Member
Hi
I m new to PL SQL.
I am trying to learn dynamic querry execute.
Here is my procedure.


create or replace
PROCEDURE nu_spDecryptData11

AS

v_rc VARCHAR2(100);
v_table VARCHAR2(30);
v_col VARCHAR2(30) ;
v_sqlString VARCHAR2(1000);

BEGIN
v_table := 'xptest_enc';
dbms_output.put_line(v_table);
v_col := 'name';
dbms_output.put_line(v_col);

v_sqlString := 'Select name from xptest_enc';
dbms_output.put_line(v_sqlString);
EXECUTE IMMEDIATE v_sqlString INTO v_rc;
dbms_output.put_line(v_rc);

EXECUTE IMMEDIATE 'Select '|| v_col || 'from' || v_table ||';' INTO v_rc;

dbms_output.put_line(v_rc);


END;


I don't know why i m getting
PLS-00306: wrong number or types of arguments

Please help me............. Embarassed
Re: PLS-00306: wrong number or types of arguments [message #246136 is a reply to message #246133] Wed, 20 June 2007 01:37 Go to previous messageGo to next message
kukreja
Messages: 33
Registered: February 2007
Member
Hi bhadu

in your statement
EXECUTE IMMEDIATE 'Select '|| v_col || 'from' || v_table ||';' INTO v_rc;


remove ';' because you cannot put ; inside a statement to which you treating as variable
Re: PLS-00306: wrong number or types of arguments [message #246138 is a reply to message #246136] Wed, 20 June 2007 01:45 Go to previous messageGo to next message
rajesh_bhadu
Messages: 48
Registered: June 2007
Member
Hi Dude,
I have done whatever u told me.............
But problem stil exist.........
Re: PLS-00306: wrong number or types of arguments [message #246139 is a reply to message #246133] Wed, 20 June 2007 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS.
Please always post your Oracle version (4 decimals).

Next, copy and paste your execution, do not just describe it is useless. For instance, where do you get your error message? At which line? What do you try to execute?

Regards
Michel
Re: PLS-00306: wrong number or types of arguments [message #246143 is a reply to message #246139] Wed, 20 June 2007 01:55 Go to previous messageGo to next message
kukreja
Messages: 33
Registered: February 2007
Member
bhadu

try this
EXECUTE IMMEDIATE 'Select :bo from :b1' INTO v_rc using v_col,v_table;
Re: PLS-00306: wrong number or types of arguments [message #246148 is a reply to message #246139] Wed, 20 June 2007 02:02 Go to previous messageGo to next message
rajesh_bhadu
Messages: 48
Registered: June 2007
Member
Sorry for unformatted post......
now this is formatted one.

create or replace
PROCEDURE nu_spDecryptData11

AS
    
    v_rc VARCHAR2(100);
     v_table VARCHAR2(30);
    v_col VARCHAR2(30) ;
   v_sqlString VARCHAR2(1000);
 
BEGIN
    v_table := 'xptest_enc';
    dbms_output.put_line(v_table);  
   v_col := 'name';
   dbms_output.put_line(v_col);  
  
   v_sqlString := 'Select name from xptest_enc';
   dbms_output.put_line(v_sqlString);  
      EXECUTE IMMEDIATE v_sqlString INTO v_rc;
      dbms_output.put_line(v_rc);   
   
     EXECUTE IMMEDIATE 'Select '|| v_col || 'from' || v_table || '' INTO v_rc;
      
      dbms_output.put_line(v_rc);    
     
      
END;



I m getting error:

ORA-06550: line 8 column 3
PLS-00306: wrong number or types of arguments to call nu_spDecryptData11
PL/SQL : Statment Ignored
Re: PLS-00306: wrong number or types of arguments [message #246150 is a reply to message #246148] Wed, 20 June 2007 02:08 Go to previous messageGo to next message
rajesh_bhadu
Messages: 48
Registered: June 2007
Member
Hi Parshant,

I have tried but problem is same.

Re: PLS-00306: wrong number or types of arguments [message #246157 is a reply to message #246133] Wed, 20 June 2007 02:26 Go to previous messageGo to next message
vazcbv
Messages: 1
Registered: June 2007
Junior Member
Hi Rajesh,

You can use as below:
EXECUTE IMMEDIATE 'Select '|| v_col || ' from ' || v_table INTO v_rc;


You were not putting the space before and after "FROM"...

Cheers
Vaz
Re: PLS-00306: wrong number or types of arguments [message #246158 is a reply to message #246133] Wed, 20 June 2007 02:26 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Just demonstarting remaining errors in your procedure:
SQL> -- FROM keyword not separated by whitespaces
SQL> declare
  2    v_rc VARCHAR2(100);
  3    v_table VARCHAR2(30);
  4    v_col VARCHAR2(30);
  5  begin
  6    v_table := 'table1';
  7    v_col := 'column1';
  8    EXECUTE IMMEDIATE 'Select '|| v_col || 'from' || v_table INTO v_rc;
  9  end;
 10  /
declare
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
ORA-06512: at line 8

SQL> -- fetching more than one row
SQL> declare
  2    v_rc VARCHAR2(100);
  3    v_table VARCHAR2(30);
  4    v_col VARCHAR2(30);
  5  begin
  6    v_table := 'table1';
  7    v_col := 'column1';
  8    EXECUTE IMMEDIATE 'Select '|| v_col || ' from ' || v_table INTO v_rc;
  9  end;
 10  /
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 8

As for the last error, you should ensure you get just one row by issuing the WHERE condition.

Your error
Quote:
PLS-00306: wrong number or types of arguments to call nu_spDecryptData11
, seems self-explanative and related to the way you call the procedure (you did not show it here). Just search for PLS-00306 here.
Re: PLS-00306: wrong number or types of arguments [message #246160 is a reply to message #246158] Wed, 20 June 2007 02:34 Go to previous messageGo to next message
rajesh_bhadu
Messages: 48
Registered: June 2007
Member
Thanks a lot for your help...........
Re: PLS-00306: wrong number or types of arguments [message #246161 is a reply to message #246148] Wed, 20 June 2007 02:34 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
PLS-00306: wrong number or types of arguments to call nu_spDecryptData11

How do you call it?
Post what you did.

Regards
Michel
Previous Topic: How to truncate the minutes and seconds from a date field
Next Topic: TIMESTAMP Conversion
Goto Forum:
  


Current Time: Tue Dec 06 12:30:08 CST 2016

Total time taken to generate the page: 0.13113 seconds