Forum Stats

  • 3,816,026 Users
  • 2,259,131 Discussions
  • 7,893,365 Comments

Discussions

Unable to use varchar variable of input type in procedure.

646894
646894 Member Posts: 63
edited Jan 19, 2010 4:17AM in SQL & PL/SQL
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.
Tagged:

Best Answer

  • Twinkle
    Twinkle Member Posts: 740 Silver Badge
    Answer ✓
    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

Answers

  • Twinkle
    Twinkle Member Posts: 740 Silver Badge
    edited Jan 19, 2010 3:56AM
    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
    Anurag Tibrewal Member Posts: 3,901 Gold Trophy
    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
    728534 Member Posts: 1,386
    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
    659537 Member Posts: 309
    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
    646894 Member Posts: 63
    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
    Anurag Tibrewal Member Posts: 3,901 Gold Trophy
    Hi,

    Your insert statement should be something like this
    stmt := 'INSERT INTO ignore_columns VALUES ('''||table_name||''', '''||column_list||''')'
    Regards
    Anurag
  • Twinkle
    Twinkle Member Posts: 740 Silver Badge
    Answer ✓
    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
    659537 Member Posts: 309
    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
    Karthick2003 Member Posts: 13,711 Bronze Badge
    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
    BluShadow Member, Moderator Posts: 41,878 Red Diamond
    Kapil_A wrote:
    text values should be in quotes.
    Or more correctly as Twinkle showed, they should be bind variables.
This discussion has been closed.