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]

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

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

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

Inserting data from jsp form to multiple tables !

Category:DefaultRelease time:-0001-11-30Views:130

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

Error while inserting data into BFILE datatype

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

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

Error while inserting data into a table.

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

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

Insert data from one form to another table

Category:DefaultRelease time:-0001-11-30Views:130

I have a form which of course insert data into table1, i created a process within this form and include a sql statement to achieve this for me. My thoughts are this will take whatever is the value from the form and insert it into table2. INSERT INTO[More]

Hot
Hi Team, We have a PO with one line item where the free item check box is ticked and net price is blank. When we try to make GR with respect to the PO via T Code MB01 we are getting error as " No goods receipt possible for purchase order xxxxxxxxxxx  [More]
I replaced it with a new drive and used a new name for the drive.  Time machine uses the new drive (with new name) and backs up to it without any problem. But every day, Time Machine complains it can't back up to the old drive.  I can't delete the ol [More]
We are publishing Adobe Standard to 4 users over 6 servers and have both Acrobat Standard and Reader installed on each Citrix server.  During installation of Standard, Reader was selected as the default application. Regardless of this configuration, [More]
My text from PDF to word is all jumbled and unreadable. What did I do wrong, or what are the limitations of the program.?Hi rlgrapes, The quality of a conversion really depends on the quality of the PDF. (You didn't do anything wrong.) Do you know ho [More]
I load a page, and it disappears after loading or during loading.You appear to have Firefox 3.6.10 installed and in use, so therefore presumably on your hdd. Without upgrading to the newest major version firefox 4, the latest version available is Fir [More]
Hello everyone, The issue I have is about plash player in firefox for Kubuntu 14.04, it seems the issue is about the folder where the ligflashplayer.so must be copied or installed, I treid some folders, like..../usr/lib/firefox, /usr/lib/firefox-addo [More]
hi experts i created a SP  which is called by the following code. Dim GD As SAPbouiCOM.Grid         Dim Query As String = "exec sp_Attendance_Report'" & vmonth & "','" & vyear & "'"         GD = objForm.Items. [More]
I am using Perfmon to get #interrupts/sec. I can also drill down to single core resolution (e.g. core0, 1, 2, 3 in a 4 core CPU) This is good, but i would like to get interrupts/sec for a specific source (e.g. network interface card) or all interrupt [More]
HI All While creating the ARE 1 document, we usually combine 2 or more excise invoices in J1IA101. My requirement is that the system should check for the shipping type also while combining the invoices. Now I think the system is checking whether the [More]
Hi, We are working in 10.1.2.3 Oracle apps, can anybody let know which logs contains the information of client's users login details like Client system info, client machine name, client machine's IP etc., ? Regards Hariharan.TI`ve configure Apache ht [More]