SQL & PL/SQL :: Select Into Temporary Variable On Stored Procedure For Second Insert?
Mar 30, 2011
the moment my 11g database is connecting to a php web front end. this following procedure is the one I'm having trouble with.
CREATE OR REPLACE PROCEDURE "BSISSONS"."CREATE_EXCURSION" (
min_places IN excursion.min_places%TYPE,
max_places IN excursion.max_places%TYPE,
additional_charge IN excursion.additional_charge%TYPE,
[code]...
I can select into an output variable to return the value of the primary key of the newly inserted row back into the webpage, but i need to be able to 'select into' a temp variable to insert this value into another table on the same procedure. I get complie errors when i try to 'DECLARE' a variable after the 'AS' keyword
View 2 Replies
ADVERTISEMENT
Mar 21, 2013
We are trying insert records from a select query into temporary table, some of the records is missing in the temporary table. The select statement is having multiple joins and union all which it little complex query. In simple terms the script contains 2 part 1st Part Insert in to temporary table 2nd part Select query with multiple joins, inline sub queries, unions and group by classes and conditions Eg. If we execute select statement alone it returns some count for example => 60000 After inserting into the temp table, in temp table the count is around 42000 why is the difference?
It is simple bulk inserts... insert in to temp table select * from xxx. also, there is no commit in between. The problem is all the records populated by the select statement are not inserted in to temp table. some records are not inserted.
Also, we had some other observation. It only happens in its 2nd execution and not its first run. Hope there might be some cache problem Even, we also did not believe that. We are wondering. In TOAD, we tested however at times it happens. In application jar file, after "insert in to temp select * from xxx" we take the i. record count of temp table and ii. record count of "select * from xxx" separately but both doesn't match. Match only at 1st time.
View 16 Replies
View Related
Dec 21, 2012
how do you declare a variable in a store procedure
View 11 Replies
View Related
Mar 6, 2012
i want to store all rows of columns into single variable and then use in inside of SP
declare
CUR_REC SECURITY_TYPE%rowtype;
begin
select *
into CUR_REC
from SECURITY_TYPE;
[code]....
it return ORA-01422: exact fetch returns more than requested number of rows error. Is any chance to implemented above scenario in oracle 10g
View 4 Replies
View Related
May 27, 2011
i Have Write A SP But Show me Error when i Compile It.
Create or Replace Procedure PREPAIDEXPENSE(v_OperationType varchar2(1))
v_ATTM_TXN_TYPES_CODE ACC_TXN_TYPES_MST.ATTM_TXN_TYPES_CODE%TYPE;
v_ATTM_TXN_TYPES_DESC ACC_TXN_TYPES_MST.ATTM_TXN_TYPES_DESC%TYPE;
v_ATTM_STATUS ACC_TXN_TYPES_MST.ATTM_STATUS%TYPE;
v_ATSM_STAGE_ID ACC_TXN_TYPES_MST.ATSM_STAGE_ID%TYPE;
v_PP_ACCOUNT_GL ACC_TXN_TYPES_MST.PP_ACCOUNT_GL%TYPE;
[code]....
Error:SQL command not properly ended
View 5 Replies
View Related
May 12, 2009
I have an array of C structs say
struct S
{
int a, long b, double c;
};
S s[100];
Further suppose I have an Oracle table T like this:
create table T
(
a number(10),
b number(10),
c float
);
I want to bulk insert all 100 instances of S from a client application into T. I've seen code that does this for *one* field or column. The code defines a stored procedure which accepts a single argument which is a TABLE and then does a FORALL ... insert. The client application passes in the array of data.
What I need is N columns. In my example above struct S has N=3 fields which conform to the N=3 columns in T. In reality my N will be 50+. I am trying to avoid creating stored procedures which will take the 50 or so arguments it will eventually need.
So does my stored procedure need to accept N TABLE arguments? Or can I cajole OCI/OTL/ODBC and PL/SQL so that the stored procedure can take an array of rows which the type of row conforms to T by defining a record or something? That is, do I need:
Option 1: // declares one type and one argument each for N cols
create or replace procedure insert_S(
a_array IN A_TABLE, -- type A_TABLE is TABLE of number;
b_array IN B_TABLE, -- type B_TABLE is TABLE of number;
c_array IN C_TABLE) -- type C_TABLE is ...
begin ... end
Option 2: // this somehow accepts an array compatible with T
// if I could get a OCI/OCCI/OTL/ODBC application
// to send this data, this procedure would have
// only one argument
create or replace procedure insert_S(
row_array IN ?????????? type -- some sort of array of rows
)
begin ... end
Or should I pass the whole memory chunk of data in as an image or varchar array -- basically an opaque block of data -- and then internally decypher/decode the memory block inside the stored procedure as discussed on [URL].
best way to pass an array of N C-structs of M fields to a stored procedure for insertion into a table with M compatible columns? One TABLE per column? with an array of a custom type compatible with a row in T? As glob of data? Another option is to populate some host variables ... but, again, I'd need N host variables.
View 1 Replies
View Related
Mar 25, 2013
I want to insert multiple records on a database using a stored procedure.
View 11 Replies
View Related
Jun 5, 2005
I migrate procedures MS SQL Server to Oracle.In MS SQL SSERVER the use of instructions INSERT with procedure results which are in storage or dynamic instructions EXECUTE in place of VALUES clause is permissible. This construction is similar to INSERT/SELECT but we have to do with EXEC instead of SELECT. The part of EXEC should include exactly one resulted collection about the equivalent types to the types of table columns. In case of the stored procedure, we can pass on proper parameters, use the form of EXEC('string') and even call up wideranging procedures or remote control procedures from different servers. Calling up remote control procedures from different server, which place data in temporary table, and later realizing join with obtainable data, we can construct diffuse joins. For example. I want insert results stored procedures sp_configure, proc_obj in temporary table.
1)INSERT #konfig
exec sp_configure.
2)
CREATE PROCEDURE proc_test
@Object_ID int,
AS
SET XACT_ABORT ON
BEGIN TRAN
CREATE TABLE #testObjects ( Object_ID int NOT NULL )
INSERT
#testObjects
EXEC
proc_obj @Object_ID,3,1
COMMIT TRAN
RETURN(0)
go
how migrate for example code to Oracle?
View 11 Replies
View Related
Nov 7, 2012
how made a success of insert data into table temporary in job_schedule ? because when i tried to change insert into table permanent always successfully.
CREATE GLOBAL TEMPORARY TABLE YG_PAYMENT_TMP
( SITE_CODE VARCHAR2(100 BYTE),
KBON VARCHAR2(100 BYTE),
FUTURE_DATE DATE
[code]...
why insert into table temporary not successfully in job_submit.
View 3 Replies
View Related
Dec 24, 2012
Execute sp1 param1...param6
Execute sp2 param1...param8
Execute sp3 param1...param4
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;
View 6 Replies
View Related
May 15, 2013
Global temporary Table Name:
1.MT_GBL
2.DT_GBL
3.DT2_GBL
Base Table Name:
1.MT
2.DT
3.DT2
My Steps:
1.Insert all data from global table to base table.
2.Update all data (that means retrieved all data from base table to global table and update this data). Question: How to Insert and Update from Global temporary table ??
View 4 Replies
View Related
Mar 3, 2010
create or replace procedure test
as
stmt varchar2(2000);
begin
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tt_Local(ID VarChar2(38)) ON COMMIT PRESERVE ROWS';
stmt := 'INSERT INTO tt_Local SELECT cardnumber FROM cards';
execute immediate stmt;
end;
-- when am trying to execute this
begin
test;
end;
-- showing ora-01031, insufficient privileges.
View 9 Replies
View Related
Sep 26, 2011
I am working on form 10g, here i am using post command in save procedure to apply some record in database temporary.
But Frm-40404 Message raise during saving record i need post commond but i dont want to this message should popup, is there any method to avoid the message only?
View 7 Replies
View Related
May 7, 2013
In 11g, When I am trying to insert the records with select insert option it is failing.
Below is my Query:
insert into table_1 select * from table_2
The above query is not inserting any records into table_1. But when i query select * from table it is returning records.
The same select Insert query is inserting records properly in 10g.
View 5 Replies
View Related
Dec 14, 2012
My ORACLE DB version is:
('Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production');
('PL/SQL Release 11.2.0.2.0 - Production');
('CORE 11.2.0.2.0 Production');
('TNS for Linux: Version 11.2.0.2.0 - Production');
('NLSRTL Version 11.2.0.2.0 - Production');
I have this XML data stored in a CLOB column:
<Activity>
<Changes>
</Changes>
<Inserts>
</Inserts>
<Definition>
[code]....
I need creating an update that will insert another <Activity>SomeGUID</Activity> into the <Spawned> parent.
View 3 Replies
View Related
Jul 5, 2011
I'm trying to write a simple query so I can do some testing on my application. I am trying to do something like this:
SELECT
Location,
LEVEL,
FROM
S_ORG_EXT
where
Location = 'North America' and LEVEL ='Software'
OR location = 'North America'
and Active = 'N'
in the where statement, I have put in the 'Active' that isn't a column. I want to be able to be able to change that in the select part. But I am not able to do so.
this is what I have tried:
SELECT
Location,
LEVEL,
Active = 'N' --I want to change this in the to N or Y so I can get different results.
FROM
S_ORG_EXT
where
Location = 'North America' and LEVEL ='Software'
OR location = 'North America'
and Active = 'N'
View 7 Replies
View Related
Dec 21, 2011
PFB code i used to schedule a job as per my requirement. And the procedure is executing fine, but when im about to run it is getting hang.
create or replace procedure scheduler_alert(frq varchar2,intrvl number) is
begin
dbms_scheduler.create_job(
job_name=>'scheduler_alert',
job_type=>'stored_procedure',
job_action=>'alertlog_error',
start_date=>SYSTIMESTAMP,
repeat_interval=>'FREQ='||frq||';INTERVAL='||intrvl,
enabled=>true,
auto_drop=>false);
end;
/
When im trying to run the job it is getting hang.
exec dbms_scheduler.run_job('scheduler_alert');
View 3 Replies
View Related
Aug 28, 2009
how to use a synonym in a stored procedure.
I have created a public synonym for a remote table on a different schema.
Im now trying to use the synonym to load that data into a temporary table in my schema using a stored procedure and im getting an error.
how to use a synonym in a stored procedure.
View 5 Replies
View Related
Jan 10, 2012
I want to call a shellscript which is in application sever through a stored procedure in database sever.i dont do this by dbms scheduler.
May i knw some sytax with examples and the settings to change to accept the external procedure call.
View 1 Replies
View Related
Mar 12, 2010
I've written a Java stored procedure that deletes some provided file. The PL/SQL procedure looks like :
procedure delete (file in varchar);
The procedure does the work correctly when the provided file name exists on the DB server, but doesn't when the file is ou of the DB server.
Is there a way to resolve this ?
View 15 Replies
View Related
Jun 11, 2013
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.
View 4 Replies
View Related
Nov 20, 2011
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]....
View 11 Replies
View Related
Apr 18, 2011
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?
View 7 Replies
View Related
Jun 9, 2010
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....
View 3 Replies
View Related
Apr 5, 2011
here is what i am trying to do: im as using oracle 8 with sqltool
i have a Very large query. and i notice that many things are repeating. so i want to add them to a variable, instead of re-typing them. for example:
select SomeID from SomeTable;
i want SomeID to be put into a variable. but i still want to be able to get a normal select query at the end so that i can see the returned value:
i tried things like:
declare x number;
begin
set x=45454
select x from SomeTable;
end;
but could not get it to work.
View 2 Replies
View Related
May 22, 2012
how to you execute a stored procedure in ORACLE..For example in SQL SERVER its just
EXEC Proc_Name ParameterValues
How the hell do you do this in oracle i just want to test if my stored procedure works.
View 1 Replies
View Related
Jul 23, 2010
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"?
View 2 Replies
View Related
Apr 23, 2009
i have this stored procedures:
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..
View 2 Replies
View Related
Jul 15, 2013
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]........
View 1 Replies
View Related
Jan 11, 2008
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).
View 14 Replies
View Related
