Sitemap

Amicuk Programming Answers

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

2015-10-11   Views:3

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, ma

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

The replay answer
Advertisement
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.

Go to See the other 2 answers

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

Category:DefaultRelease time:2015-10-11Views:130

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[More]

Error while inserting data in SQL Server.

Category:DefaultRelease time:2015-10-11Views:130

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/[More]

Error by inserting data into a field of datatype LONG

Category:DefaultRelease time:2015-10-11Views:130

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[More]

Error while inserting data in datagrid

Category:DefaultRelease time:2015-10-11Views:130

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[More]

Help with ORA 14400 error while inserting data

Category:DefaultRelease time:2015-10-11Views:130

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[More]

INSERTING DATA INTO A SQL SERVER 2005 TABLE, WHICH HAS A IDENTITY COLUMN

Category:DefaultRelease time:2015-10-11Views:130

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[More]

Hot
We've complete our form and are testing all of the fields.  When we review the responses, the data is not populating some of our fields.  Any idea why this might be happening?Hi! Thanks for your reply. We solved the problem yesterday night. There was [More]
I have an 20 inch desktop iMac with a broken screen. It is the files on the computer that I wish to recover, after that, I can be on my merry way. Unfortunately, VileVault was on (Not my choice), and for some reason, when I target the disc image of t [More]
Hi All, I have updated my mediacenter. Now tv_grab_nl_py does not work anymore: [[email protected] ~]$ tv_grab_nl_py --output ~/listings.xml --fast File "/usr/bin/tv_grab_nl_py", line 341 print 'tv_grab_nl_py: A grabber that grabs tvguide data from tvgi [More]
When I install WL6.0SP1 on Sun Solaris, it gives such message: weblogic600sp1_sol.bin Preparing to install... Error: failed /tmp/install.dir.16036/Solaris/resource/jre/jre/lib/sparc/client/libjvm.so, because ld.so.1: /tmp/install.dir.16036/Solaris/re [More]
Folks, Hello. I am installing PeopleTools 8.53 with Oracle Database Server 11gR1 and OS Oracle Linux 5.10. Data Mover Bootstrap and Application Designer can log into Database instance successfully. My procedure to run PIA is below: Step 1: start Orac [More]
Dear Experts Once the Sale order is created the SO committment amount gets updated in reports  (commitment Vs Actual report) with"Forecast of Revenue" and Amount type 0100 (Original). But after creation of Billing document (actuals) the same  'F [More]
Hi experts, i have an issue regarding background job.i have to run multple programs in background one after another in one program.so i am using function modules job_open,submit and job_close. my problem all the jobs are statrting at same time .but i [More]
hi! We have RDS 2012 R2 setup up and running with session host, connection broker, rd gateway and rd web. AS office 2013 can be deployed using APP-V 5 SP2, we have created .appv pkg using ODT as mentioned in this guide . It was working fine for 2 day [More]
iMac G5 PPC OS 10.4.11, Mail app. Where can I find my Mail Setup? I had lost my Mail setup and I had to phone my provider so he could steer me to redo my Setup. I followed his instructions and was able to reconnect to my mailboxes. However I did not [More]
How can i get a power bar code i only have a ee mobile wifi which doesnt allow me to sent texts .. ive tried the sim card in a unlocked phone but it wont send textsYou could try putting your SIM into an EE phone. It's only a SIM, it should be be able [More]