Unable to use varchar variable of input type in procedure.

646894

Hello,

I have created as simple procedure.. its as follows..

SQL> CREATE OR REPLACE PROCEDURE
2 input_columns
3 (
4 table_name IN user_tab_columns.table_name%TYPE,
5 ignore_columns IN VARCHAR2(90)
6 ) IS
7 stmt VARCHAR2(3000);
8 BEGIN
9 stmt := 'INSERT INTO ignore_columns VALUES ('||table_name||', '||ignore_col
umns||')';
10
11 EXECUTE IMMEDIATE stmt;
12
13 END;
14 /

Warning: Procedure created with compilation errors.

SQL> show errors;
Errors for PROCEDURE INPUT_COLUMNS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/27 PLS-00103: Encountered the symbol "(" when expecting one of the
following:
:= . ) , @ % default character
The symbol ":=" was substituted for "(" to continue.

The table ignore_columns has been created.

If i remove the size of the variable ignore_columns or if i define it as a number the procedure gets created successfully.

When i don't define the size of the variable, I cannot pass parameters for ignore_columns column. Could you please help me.

Thanks.

Twinkle

Azeroth wrote:
@Twinkle.. it doesnt work

SQL> create table ignore_columns
  2  (a varchar2(90),
  3  b varchar2(90));

Table created.

SQL> CREATE OR REPLACE PROCEDURE
  2   input_columns
  3   (
  4   table_name IN user_tab_columns.table_name%TYPE,
  5   column_list IN VARCHAR2
  6   ) IS
  7   stmt VARCHAR2(3000);
  8   BEGIN
  9   stmt := 'INSERT INTO ignore_columns VALUES (:1,:2)';
 10  
 11   EXECUTE IMMEDIATE stmt using table_name,column_list;
 12  
 13   END;
 14   /

Procedure created.

SQL> execute input_columns('HELL', 'HELLO');

PL/SQL procedure successfully completed.

SQL> select * from ignore_columns;

A
-----------------------------------------------------------------------
B
-----------------------------------------------------------------------
HELL
HELLO

Twinkle

Twinkle

CREATE OR REPLACE PROCEDURE
 input_columns
 (
table_name IN user_tab_columns.table_name%TYPE,
 ignore_columns IN VARCHAR2 ) IS
 stmt VARCHAR2(3000);
 BEGIN
 stmt := 'INSERT INTO ignore_columns VALUES (:1, :2)';

 EXECUTE IMMEDIATE stmt using table_name, ignore_columns;

 END;
 /

Twinkle

Anurag Tibrewal

Hi,

In parameter datatypes cannot specify length, precision, or scale. For example, VARCHAR2(10) is not valid, but VARCHAR2 is valid

So instead of ignore_columns IN VARCHAR2(90) try just ignore_columns IN VARCHAR2

Regards
Anurag

PS: I would not keep variable name same as the table name in my database.

728534

CREATE OR REPLACE PROCEDURE 
 input_columns
 (
 table_name IN user_tab_columns.table_name%TYPE,
 ignore_columns IN VARCHAR2)
  IS
 stmt VARCHAR2(3000);
 BEGIN
null;

 END;

Cannot specify teh length in the signature. VARCHAR2(90) is not correct.

Cheers!!!
Bhushan

659537

ignore_columns IN VARCHAR2(90)

This line contains the error.
for parametrs no need to specify the length.

It shud be "ignore_columns in varcahr2"

646894

Hello,

Here is the result..

SQL> CREATE OR REPLACE PROCEDURE
2 input_columns
3 (
4 table_name IN user_tab_columns.table_name%TYPE,
5 column_list IN VARCHAR2
6 ) IS
7 stmt VARCHAR2(3000);
8 BEGIN
9 stmt := 'INSERT INTO ignore_columns VALUES ('||table_name||', '||column_lis
t||')';
10
11 EXECUTE IMMEDIATE stmt;
12
13 END;
14 /

Procedure created.

SQL> select * from ignore_columns;

no rows selected

SQL> exec input_columns('HELL', 'HELLO');
BEGIN input_columns('HELL', 'HELLO'); END;

*
ERROR at line 1:
ORA-00984: column not allowed here
ORA-06512: at "PUSHKAR.INPUT_COLUMNS", line 11
ORA-06512: at line 1

Any more ideas?

@Twinkle.. it doesnt work

Thanks.

Anurag Tibrewal

Hi,

Your insert statement should be something like this

stmt := 'INSERT INTO ignore_columns VALUES ('''||table_name||''', '''||column_list||''')'

Regards
Anurag

Twinkle

Azeroth wrote:
@Twinkle.. it doesnt work

SQL> create table ignore_columns
  2  (a varchar2(90),
  3  b varchar2(90));

Table created.

SQL> CREATE OR REPLACE PROCEDURE
  2   input_columns
  3   (
  4   table_name IN user_tab_columns.table_name%TYPE,
  5   column_list IN VARCHAR2
  6   ) IS
  7   stmt VARCHAR2(3000);
  8   BEGIN
  9   stmt := 'INSERT INTO ignore_columns VALUES (:1,:2)';
 10  
 11   EXECUTE IMMEDIATE stmt using table_name,column_list;
 12  
 13   END;
 14   /

Procedure created.

SQL> execute input_columns('HELL', 'HELLO');

PL/SQL procedure successfully completed.

SQL> select * from ignore_columns;

A
-----------------------------------------------------------------------
B
-----------------------------------------------------------------------
HELL
HELLO

Twinkle

659537

create table ignore_columns (table_name varchar2(1000),column_list varchar2(1000));

CREATE OR REPLACE PROCEDURE
 input_columns
 (
 table_name IN user_tab_columns.table_name%TYPE,
 column_list IN VARCHAR2
 ) IS
 stmt VARCHAR2(3000);
 BEGIN
 stmt := 'INSERT INTO ignore_columns VALUES ('||''''||table_name||''''||', '||''''||column_list||''''||')';

 EXECUTE IMMEDIATE stmt;

 END;
 /
 

  BEGIN input_columns('HELL', 'HELLO'); END;
 

text values should be in quotes.

Karthick2003

Why are you using Dynamic SQL. Its not required you can just

CREATE OR REPLACE PROCEDURE input_columns 
( 
  table_name IN user_tab_columns.table_name%TYPE,
  column_list IN VARCHAR2
) 
IS
BEGIN
  INSERT INTO ignore_columns VALUES (table_name,column_list);
END;

And another thing when ever you use a insert statement specify the column list after the table like this

INSERT INTO ignore_columns(table_name, column_list) VALUES (table_name,column_list);

Here i have considered table_name, column_list are columns in ignore_columns.

BluShadow

Kapil_A wrote:
text values should be in quotes.

Or more correctly as Twinkle showed, they should be bind variables.