Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 395 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
Unable to use varchar variable of input type in procedure.

646894
Member Posts: 63
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.
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.
Best Answer
-
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
-
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 -
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. -
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 -
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" -
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. -
Hi,
Your insert statement should be something like thisstmt := 'INSERT INTO ignore_columns VALUES ('''||table_name||''', '''||column_list||''')'
Regards
Anurag -
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 -
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. -
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 thisINSERT 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. -
Kapil_A wrote:Or more correctly as Twinkle showed, they should be bind variables.
text values should be in quotes.
This discussion has been closed.