SQL & PL/SQL :: Take Stored Procedure Last Run Without Using DBA_AUDIT_TRAIL Sys Table
Nov 18, 2010How to take a stored procedure last run without using DBA_AUDIT_TRAIL sys table.
View 6 Replies
ADVERTISEMENT
Temp Table Within A Stored Procedure
Jul 15, 2013I'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]........
SQL & PL/SQL :: Temp Table Within Stored Procedure
Jul 15, 2013I have been developing in MS SQL for about 15. So I'm still getting use to the syntax and features within Oracle.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,
AAAA.1D1D
[code].....
PL/SQL :: Temp Table Within Stored Procedure
Jul 15, 2013I'm still getting use to the syntax and features within Oracle. 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, AAAA.1D1D FROM mySchema.FFFF_07 FFFF RIGHT OUTER JOIN mySchema.EEEE EEEE ON FFFF.9999 = EEEE.1B1B RIGHT OUTER JOIN
[code].........
SQL & PL/SQL :: How To Go Ahead Of Another Line Of Stored Procedure If Value Is Not There In Table
Oct 16, 2012i am running one store procedure and checking the value if it is there then it's storing in one variable. but by checking through breakpoint i got that if value is not there which condition it's checking it's stuck there.
is it not possible if value is not there it should ignore and don't store value in variable.
my query is like this:
BEGIN
SELECT 1 Into v_temp FROM MASTERPROCESSDAILYDATA WHERE Emp_ID = v_ReadEmpID AND PDate = v_ReadPDate AND STATUS='A';
IF v_temp = 1 THEN
BEGIN
DELETE MASTERPROCESSDAILYDATA WHERE Emp_ID = v_ReadEmpID AND PDate = v_ReadPDate;
END;
END IF;
End;
SQL & PL/SQL :: Create Stored Procedure To Update Columns In Table
Dec 8, 2010I have three tables fixtures, fixture_teams and team_tbl
fixtures consists of:
create table Fixture_tbl(
fixt_id varchar2(8),
fixt_date date,
fixt_time varchar2(10),
fixt_location location_t,
umpire_id varchar2(8),
player_of_match player_of_match,
home_team varchar2(20),
away_team varchar2(20),
[code]....
creating a stored procedure that updates the points column in the teams_tbl , the value that is updated in to the points column will be retrieved from the fixture_team table. so if team a has more goals than team b then the points column for team a will be increased by 6 else if the scores are equal they get 4 points each.
SQL & PL/SQL :: Execute Stored Procedure With Table Of Number As In Parameter?
May 31, 2011I have one stored proc with three in parameters as
number, varchar and table of number
what statement I need to write in pl/sql to execute it ...
execute getdetails(1,'xyz', ????????????)
SQL & PL/SQL :: Updating Table For Different Number Of Fields Via Single Stored Procedure?
Jun 14, 2012I've a table with fields:
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,
[Code]....
SQL & PL/SQL :: Stored Procedure With Multiple Table Join With Aggregate Functions
Jun 15, 2013I have 2 tables order_items and items.
Order_items Items
Item_id Item_id
Quantity Price
In normal sql statement: select sum(order_items.quantity*items.price) sales_price
from order_items,items
where order.item_id=items.item_id;
I have to put this logic in either a stored procedure or Function just to calculate sum(order_items.quantity*items.price) and store the aggregated value as Sales_price in DB. Then we have to call this from Informatica Stored procedure Transformation where we will have only one output port as Sales_price,this is to load into summary table. All the aggregate calculations and joining of 2 tables should be done on DB side and only one output should be populated when we execute this procedure.
PL/SQL :: Couldn't Access Dba_segments Table From Named Stored Procedure
Feb 7, 2013we couldnt access the dba_segments table from the named stored proc however its working fine if accessing from unnamed SP.
SQL> declare
2 cnt number;
3 begin
4 select count(*) into cnt from dba_segments;
5 dbms_output.put_line('cnt='||cnt);
6 end;
7 /
cnt=6746
PL/SQL procedure successfully completed.SQL> create or replace procedure test
2 is
3 cnt number;
4 begin
5 select count(*) into cnt from dba_segments;
6 dbms_output.put_line('cnt='||cnt);
7 end;
8 /
Warning: Procedure created with compilation errors.
SQL> show error
Errors for PROCEDURE TEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1 PL/SQL: SQL Statement ignored
5/31 PL/SQL: ORA-00942: table or view does not exist
SQL>oracle version
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
Stored Procedure For Searching Data From A Table By Passing Tablename As A Parameter
Feb 6, 2012This procedure is not working properly.
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
PL/SQL :: Call More Than One Stored Procedure In New Stored Procedure
Dec 24, 2012Execute 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;
Security :: Viewing Report Of Audit Trail From Dba_audit_trail
Apr 11, 2013in my environmnet audit is working audit_trail=db,extended . i am also viewing report of audit trail from dba_audit_trail or aud$. But problem is that i have to generate report on which object of schema what audit is running .
or from which tables we can get information of following commands.
AUDIT ALL BY xx_test BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY xx_test BY ACCESS;
AUDIT EXECUTE PROCEDURE BY xx_test BY ACCESS;
Scheduling Job In Stored Procedure
Dec 21, 2011PFB 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');
How To Use Synonym In Stored Procedure
Aug 28, 2009how 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.
Shellscript From Stored Procedure
Jan 10, 2012I 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.
SQL & PL/SQL :: Java Stored Procedure
Mar 12, 2010I'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 ?
PL/SQL :: Dynamic SQL In Stored Procedure
Jun 11, 2013I 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 RelatedSQL & PL/SQL :: Stored Procedure Just Hangs?
Nov 20, 2011I 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]....
SQL & PL/SQL :: Sequence In Stored Procedure
Apr 18, 2011I 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?
SQL & PL/SQL :: Stored Procedure Vs Function?
Jun 9, 2010Interviewer 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 RelatedHow To Execute A Stored Procedure In Oracle
May 22, 2012how 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.
When Are Statements Within Stored Procedure Compiled
Jul 23, 2010connect 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"?
Stored Procedure To Read XML Files?
Apr 23, 2009i 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..
Outputting Results Of Stored Procedure
Jan 11, 2008I 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).
Difference Between Stored Procedure And Function?
Jan 12, 2010I have to call oracle stored procedure or a function to update user id of one or several rows depending on what user selects.
The oracle person will be writing the stored procedure or function. I am a java developer but was never involved in writing oracle functions. So..
I want to know what is the difference betn stored procedure and function?
From java point of view are there any guidelines when to prefer one over the other(betn stored procedure and function).
Oracle 8.5.1 - Stored Procedure Not Added To DB
Mar 21, 2007After 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!
SQL & PL/SQL :: Calling Exe File From Stored Procedure
Jun 15, 2011I want to call an exe file through DB Procedure. How it is possible?
1create or replace procedure proc3
2is
3begin
4 host('calc');
6* end;
from FORMS it works fine but it does not work through DB. I found same question through this link
[URL].........
but no answer is given.
SQL & PL/SQL :: Usage Of Cursors In Stored Procedure
Feb 22, 2012i have created a stored procedure with a cursor in order to perform a function where the annual_sal from the employee_annual_sal table is refered and checked. The empno for all the records which satisfies the condition mentioned inside the loop should be displayed in an variable. My code is below
create or replace PROCEDURE sp_test_cursor(out_empno OUT number)
IS
v_get_data number;
v_get_empno number;
cursor c1 is
select annual_salary
from employee_annual_sal;
[Code]...
What should i do to return mulitple values in a single variable??
SQL & PL/SQL :: Calling Stored Procedure With Default Value?
Apr 3, 2012I have the following Stored Procedure:
CREATE OR REPLACE PROCEDURE AFESD.TEST_PROC (I_NUM IN NUMBER,
I_NUM2 IN NUMBER DEFAULT 3, D_DATE IN DATE DEFAULT sysdate,D_OUT OUT DATE)
IS
[Code]....
I dont know what I need to pass in order to give it the default value.