Home > Default > Error while insert data using execute immediate in dynamic table in oracle

Error while insert data using execute immediate in dynamic table in oracle

October 11Hits:2
Advertisement
Error while insert data using execute immediate in dynamic table created in oracle 11g .
first the dynamic nested table (op_sample) was created using the executed immediate...
object is
CREATE OR REPLACE TYPE ASI.sub_mark AS OBJECT (
mark1 number,
mark2 number
t_sub_mark is a class of type sub_mark
CREATE OR REPLACE TYPE ASI.t_sub_mark is table of sub_mark;
create table sam1(id number,name varchar2(30));
nested table is created below:
begin
EXECUTE IMMEDIATE ' create table '||op_sample||'
(id number,name varchar2(30),subject_obj t_sub_mark) nested table subject_obj store as nest_tab return as value';
end;
now data from sam1 table and object (subject_obj) are inserted into the dynamic table
declare
subject_obj t_sub_mark;
begin
subject_obj:= t_sub_mark();
EXECUTE IMMEDIATE 'insert into op_sample (select id,name,subject_obj from sam1) ';
end;
and got the below error:
ORA-00904: "SUBJECT_OBJ": invalid identifier
ORA-06512: at line 7
then when we tried to insert the data into the dynam_table with the subject_marks object as null,we received the following error..
execute immediate 'insert into '||dynam_table ||'
(SELECT

Answers

887684 wrote:
ORA-00904: "SUBJECT_OBJ": invalid identifier
ORA-06512: at line 7The problem is that your variable subject_obj is not in scope inside the dynamic SQL you are building. The SQL engine does not know your PL/SQL variable, so it tries to find a column named SUBJECT_OBJ in your SAM1 table.
If you need to use dynamic SQL for this, then you must bind the variable. Something like this:
EXECUTE IMMEDIATE 'insert into op_sample (select id,name,:bind_subject_obj from sam1) ' USING subject_obj;Alternatively you might figure out to use static SQL rather than dynamic SQL (if possible for your project.) In static SQL the PL/SQL engine binds the variables for you automatically.

Read other 2 answers

Tags:

Related Articles

  • Error while insert data using execute immediate in dynamic table in oracleOctober 11

    Error while insert data using execute immediate in dynamic table created in oracle 11g . first the dynamic nested table (op_sample) was created using the executed immediate... object is CREATE OR REPLACE TYPE ASI.sub_mark AS OBJECT ( mark1 number, ma

  • *Urgent*How to insert data from MS SQL to the table that create at the adobe form?October 11

    Hi, I'm using Adobe life cycle designer 8 to do my interactive form. I would like to ask how to insert data from MS SQL to the table that i have created in my adobe interactive form? I really need the information ASAP as i need to hand in my project

  • Inserting data from jsp form to multiple tables !November 30

    Hi, I want to insert data from jsp form to two tables tables are (1) Form formId (PK) deptName (2) Data formId (FK) sNo description itemCode and the problem is that i want to save information form a jsp form to above two tables and i have only one fo

  • Error while inserting data in SQL Server.October 11

    Hi', I am using SOA 11.1.1.5 I am inserting data using DB adapter in MS SQL Server. Some times I get below error. Error Message: {http://schemas.oracle.com/bpel/extension}bindingFault Fault ID     rrr/xxx!1.1*soa_313bd437-7bb9-41c6-a719-d1775b9afc4b/

  • Error by inserting data into a field of datatype LONGOctober 11

    Hi, When inserting data into a field of datatype long in oracle8 database via SQL Plus or SQL worksheet there occurs the following error message: "field in data file exceeds the maximum length". If I access to the database from a selve written p

  • Error while inserting data in datagridOctober 11

    Hi, I am trying to insert data into a datagrid getting results from a cfc using remoteobject. If I use everything inline then it works fine. I have a button when clicked give me the results. if I have something like this: <mx:Button label="get Que

  • Help with ORA 14400 error while inserting dataOctober 11

    Hi all, i am facing an ora 14400 error in the following scenario , please help. i have created a table using the syntax: CREATE TABLE temp_table GRPKEY NUMBER(20, 0) NOT NULL, UKEY NUMBER(10, 0), ANUM VARCHAR2(250 BYTE), APC VARCHAR2(2 BYTE), SID VAR

  • Error while inserting data into BFILE datatypeOctober 11

    Dear Experts, I am trying to insert data into resume table, but it is throwing error: create table resume(resume blob); declare f_lob bfile; b_lob blob; begin insert into resume(resume) values(empty_blob()) return documents into b_lob; f_lob := bfile

  • Error while inserting data into a table.October 11

    Hi All,   I created a table.While inserting data into the table i am getting an error.Its telling "Create data Processing Function Module".Can any one help me regarding this? Thanx in advance anirudhHi Anirudh,   Seems there is already an entry

  • INSERTING DATA INTO A SQL SERVER 2005 TABLE, WHICH HAS A IDENTITY COLUMNOctober 11

    Hi All, I have to insert the data into a SQL SERVER 2005 Database table. I am able to insert the data into a normal SQL Server table. When I am trying to insert the data into a SQL Server table, which has a identity column (i.e. auto increment column

Copyright (C) 2019 wisumpire.com, All Rights Reserved. webmaster#wisumpire.com 14 q. 0.817 s.