ODP.NET :: Updating Dataset / Datarow If Data Is Populated From Stored Procedure
Mar 11, 2013
I am using:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Microsoft Visual Studio 2010 : VB Express
Microsoft .NET Framework : Version 4.0.30319 RTMRel
When I populate dataset using select query and use OracleCommandBuilder and update dataadapter, data is saved in database.
But if I put same select in store procedure and use this to populate dataset then data is not updated in db.
I get this error
Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
at da1.Update(ds1, "req") where I am going wrong.
Dim conn As New OracleConnection
Dim da1 As OracleDataAdapter = New OracleDataAdapter
Dim ds1 = New DataSet
With conn
If .State = ConnectionState.Open Then .Close()
I have a update statement that works fine when its run in SQL window but when i try it as part of a Stored Procedure its giving me the Insufficient privileges error on that table.
If i dont have privilege to update that table wont it not let me update while doing it outside the stored procedure as well?
create table test ( f1 varchar2(10), f2 varchar2(10), f3 varchar3(10) ) insert into test values ('d1','d2','d3'); insert into test values ('d10','d20','d30');
I want to update the fields of the table as per need i.e update only one field leaving all the data of the fields as it is. Suppose I want to update only f1 (from d1 to x1) field leaving f2, and f3 as it is. I've written stored procedure to update all the fields but do not know how to do it?
Quote:CREATE OR REPLACE PROCEDURE UPDATE_TEST ( U_F1 TEST.F1%TYPE, U_F2 TEST.F2%TYPE,
I'm using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production and TNS for Linux: Version 11.2.0.3.0 - Production.Requirement is to create a script to add a LIST partition to some selected tables in a schema (tables do not have data, they are not partitioned). There are about 300 such tables (can vary) and their names are maintained in a separate table. Example -Existing table
-CREATE TABLE test_part(id number (2), name varchar2(20), audit_userid number (9)); Expected table -CREATE TABLE test_part(id number (2), name varchar2(20), audit_userid number (9)) PARTITION BY LIST (audit_userid) (PARTITION p1_audit_userid VALUES (1));
Ultimate goal is to add more partitions based on the amount of data to be populated.
is it possible to base a Materialized View on results returned from a stored procedure?If not, do you see any other way except of filling a table with data from the stored procedure and then basing the MV on it?
The following code is a stored procedure I plan to use to populate a Data Warehouse dimension using data from two OLTP tables which already exist in my database. Notice that in my cursor select statement, I calculate an attribute using substr and instr, and I also assign a true or false value to a flag using a CASE statement.
CREATE OR REPLACE PROCEDURE populate_product_dimension AS v_Count NUMBER := 0; v_NumRecs NUMBER; /*Declare a cursor on the following query which returns mulitple rows of data from product and price_hist tables*/ [code]....
In my mind, Product_Code is declared correctly in the Cursor declaration Select statement.
I need to create an Oracle Stored Procedure to read a Flat file(pipe delimited) and load the data into an Oracle table. I believe the file should be located in any of the path as logged in dba_directories table or it can be anywhere on the local client machine?
create or replace procedure bank_search_sp ( p_tablename in varchar2, p_searchname in varchar2, p_bankcode out varchar2, p_bankname out varchar2, p_dist_code out number ) as v_tem varchar2(5000); begin v_tem :='select bankcode,bankname,dist_code from ' || UPPER (p_tablename) || ' where bankname like '''|| p_searchname||''; execute immediate v_tem into p_bankcode,p_bankname,p_dist_code using p_searchname ; commit; end bank_search_sp;
the Procedure is getting created but i dont know what actually happens when it was executed ,This is the error shown..ORA-01756: quoted string not properly terminated
ORA-06512: at "PENSIONS.BANK_SEARCH_SP", line 14 ORA-06512: at line 1
All these stored procedures deals with insert/updated transactions . i need to create a new stored procedure to execute all this in a single stored procedure which will be something like
create procedure sp4(param1...param8) as begin Execute sp1 param1...param6 rollback if any error Execute sp2 param1...param8 rollback if any error Execute sp3 param1...param4 rollback if any error end;
I have a central entity Address. I have other entities that could have many addresses (Organization, Person for eg). The relation between Organization and Address is one to many and the relation between Person and Address is also one to many.When I modelled it, I ended up having two foreign keys in Address (Organization ID and Person ID).The question that is being raised by the client is "As only one of the foreign key would be populated on each record, the other would be null". The client is not comfortable having nullable foreign keys as they won't be able to maintain integrity. One of the requirement is that No orphan records in Address. By modeling the way above, there are chances for having some orphanned records. I tried to explain that this would be controlled by the program to ensure that no orphan records would be there but their question is that what if someone goes directly and enters a record in Address table without any foreign key value (I dont know who would have an insert/update access on the tables other than the dba's).
I am trying to get some pros and cons on using the nullable foreign keys.
In my application i have a requirement as follows.I have 3 tables table1,table2,table3.I have 4 tickets for one license number which is related to client table as follows.
table1 ===== license_nbr(pk) name address =============== ===== ======= LicNo1 test testing
so now the excess paid amount to be adjusted for the remaining tickets through tk2 to tk4.and only tk4 should remain with 400 the balance amount should be updated accordinglyand i have a license number which is a foreignkey of client table. writing a procedure for updating the table2 based on the table3.
I would like to use dynamic sql for an select query with where clause and then use the dynamic sql in pl/sql stored procedure. how to create dynamic sql (select query) and how to use it in pl/sql stored procedure.
I entered the following procedure code into SQLPLUS for compilation, but it just hangs. I suspect the cause is an infinite loop, but I can't locate it.
CREATE OR REPLACE PROCEDURE populate_sales_fact AS BEGIN INSERT INTO sales_fact (orderid, prod_key, order_day_key, shipping_day_key, sales_dollar_amount, quantity, cust_key, emp_key) [code]....
I have a sequence my_seq in schema schema1. I have granted select on this sequence to schema2. Doing :
select schema1.my_seq.nextval from dual
in schema2 work as expected. However when I try to compile a package body in schema2 using my_seq in an insert statement, it fails with:
PLS-00302: component 'MY_SEQ' must be declared
What's even stranger is that I have stored procedures that are using the exact same code that are currently compiled and working. Recompiling them yields this error. How is this possible?
Interviewer asked me "Tell me Diff. between Stored procedure vs. Function ".....I given technical answer which is mentioned in my Faq..But he asked me , dont gv me answer in technical manner..He was interested in which case u use Stored procedure and Function....
connect the following concepts/information I've been collecting. This is not my field but I'm interested in filling some of mine conceptual/technical gaps.
From a JDBC perspective, one of the benefits of Prepared (and so Callable) statements have over the regular ones is that the statement is "compiled"(*) once and then reused (performance gain).
(*) for SQL statements: building of parse tree and exec.plan
In which way can this notion be extrapolated to invocation of Oracle Stored Procedures through CallableStatements? (After clearing my doubts, I may end concluding that the only relevant feature of CallableStatements is their capacity to deal with stored procedure invocations)
According to procedure's precompiled execution plan SQL compilation implies execution plans generation PL/SQL compilation implies P-code generation and, SQL statements (from PLSQL code) are treated no differently by Oracle than SQL from Java or C/C++. These SQLs will be parsed and execution plans for those SQLs created. ... When the PL code executes the SQL statement, only then does the SQL engine receive the SQL, parse it, and create an execution plan for it.
Therefore, even when the stored procedure can be parsed and cached in SGA (through the OracleConnection.preparedCall("proc") invocation), the SQL statements won't be effectively compiled until they are executed, right? And going deeper, will those SQL statements be cached to be reused in future invocations of the containing stored procedure? Is this a characteristic of the regular stored procedure execution in Oracle? or is it due to the CallableStatement "origin"?
create or replace PROCEDURE INSERTXML2( p_xml_in XMLType, p_table IN VARCHAR2 ) AS v_context DBMS_XMLStore.ctxType; v_rows NUMBER; BEGIN
[code]....
that works well in little XML files but in XML files that are bigger the stored procedures to not work because string maximum length in Oracle is 4000.
im working with Oracle SQL Developer and Sap Mii, the XML file is generated in Sap Mii and then i have to pass it in one step to database..
I'm trying to create a stored procedure that has two temporary tables within it, and then queries both them tables and inserts the results into a table. I created the script but when they try to run in on the server it wont run.
CREATE OR REPLACE PROCEDURE UpdateFIDB_SP IS BEGIN CREATE GLOBAL TEMPORARY TABLE myAAAA AS (SELECT AAAA.1111, AAAA.2222, BBBB.3333_EXT, CCCC.4444, DDDD.5555, DDDD.6666, DDDD.7777, DDDD.8888, AAAA.9999, EEEE.1010, EEEE.1A1A, EEEE.1B1B, FFFF.3333_LO, FFFF.1C1C, [code]........
I am new to the wonderful world of Oracle. I want to be able to view the results of a stored procedure in an output window, say out of Oracle SQL developer. Unfortunately it appears I need to write some more code to actually view the data. Consider the following:
CREATE OR REPLACE PROCEDURE TESTSPROC2(c_test out sys_refcursor) AS BEGIN open c_test for select * from test_table order by id_no; END TESTSPROC2;
to view this I need something on the order of:
DECLARE cc sys_refcursor; r cc%rowtype; BEGIN TESTSPROC2(C_TEST => cc); loop fetch cc into r; exit when cc%notfound; DBMS_OUTPUT.PUT_LINE('C_TEST = ' || r.data_element); end loop; close cc; END;
But this is weakly typed so I get all sorts of errors associated with the row definition of line 3. create the record based on the table (something like: r test_table%rowtype).
What I really want is a generic reader than can be ported around to output any sproc I put together.
On a more generic note, why Oracle has chosen to make PL/SQL inordinately more complicated than say MS SQL/Servers tSQL? I mean in tSQL I would just write:
CREATE OR REPLACE PROCEDURE TESTSPROC2 AS select * from test_table order by id_no; GO
and viola, a nice result set spits out in Query Analyzer (or a .net application).
After compiling a simple valid SP in Toad for Oracle 8.5.1:
CREATE OR REPLACE PROCEDURE proc_test AS DECLARE v_name VARCHAR2(20) := 'Ed Edson'; BEGIN DBMS_OUTPUT.PUT_LINE('Hi, my name is ' || v_name); END;
The SP is not added to the schema. Also the three status fields (status, created and Last Update in the Procedure Editor remain <Unknown>. It is not a rights or privilege issue, because the creation works perfectly in SQL*Plus!
