SQL & PL/SQL :: Oracle Stored Procedure Which Sorts Date
Apr 23, 2010oracle stored procedure which sorts date, meaning the recent records needs to be shown first.
View 6 Replies
ADVERTISEMENT
SQL & PL/SQL :: Stored Procedure Using NVL To Allow Using Optional Date Parameter?
Mar 26, 2012I have need to know the best (least expensive) way to create a stored procedure that creates a new records in a table using a sequence and return the primary key (sequence value) for this inserted record:
CREATE TABLE TEST_A (SERIAL NUMBER PRIMARY KEY, NAME VARCHAR2(20));
CREATE SEQUENCE SEQ_TESTA_PK START WITH 1
NOCACHE
NOCYCLE;
CREATE OR REPLACE TRIGGER TRG_TESTA_PK
BEFORE INSERT ON TEST_A
[code]....
DBMS_JOB - Pass Date As Parameter To Another Stored Procedure?
Mar 28, 2012I have created a stored procedure that checks if a file exists and gets a date from the file if it exists. The date is then used as a paramter. See below:
CODEcreate or replace
PROCEDURE "P_Load_Stamp" AS
v_exists BOOLEAN;
v_length NUMBER;
v_blocksize NUMBER;
[code]...
The above codes works perfectly and I scheduled it using SQLPLUS as follows:
CODEvariable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
[code]...
My problem is that I need to pass the date from the above procedure as a parameter to another stored procedure. So I modified my code as follows (the parts in red):
CODEcreate or replace
PROCEDURE "P_Load_Stamp" (vCTIDATE OUT varchar2) AS
v_exists BOOLEAN;
v_length NUMBER;
[code]...
Now it doesn't strike me as a rights issue since I created it in the schem schema. What could I be doing wrong here?
How 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.
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!
Create A List In Oracle Stored Procedure?
May 14, 2013How to create a list in oracle stored procedure and that list contains two columns.
View 1 Replies View RelatedOracle 10g - Create A Stored Procedure Without Parameters
Oct 11, 2010Iam using oracle10g . when i created a simple stored procedure,got an error
PLS-00428: an INTO clause is expected in this SELECT statement
here is my code
create or replace procedure sp_TrialLiswt
as begin
select * from mivrs_studyinfo;
end;
Pass Array To Oracle Stored Procedure?
Oct 3, 2010I am passing a string from vb.net through oracle store procedure. String consists of varchar values for eg '123456;I|N,2424424;O|A'. But how can i call these string values into array using oracle stored procedure? After getting these values into array , i wanted to loop through these values.
View 1 Replies View RelatedSQL & PL/SQL :: Execute File In Oracle Stored Procedure
May 16, 2012I need to execute a .sql file in an oracle stored procedure.
View 9 Replies View RelatedSQL & PL/SQL :: Handling Special Character (&) In Oracle Stored Procedure?
Apr 23, 2013How to handle special charater '&' in stored procedure.
Following is a test code I want to Implement
CREATE OR REPLACE PROCEDURE SP_Test(QueryID NUMBER,DateFirst DATE,DateLast DATE) IS
BEGIN
INSERT
INTO TempTable(QueryID,LineID,data,Datetime)
SELECT
1,6,Emp,startdate FROM salary
[code]....
The code above resuts into INVALID state of stored procedure and cannot be executed.
Forms :: Call Oracle Form Using Stored Procedure?
Mar 14, 2011How to call Oracle Form using a Stored Procedure?
Under
create or replace procedure ........
Begin
.......
End
Inside such procedures , is it possible to call a form ?
Input Parameter From Arraylist To Stored Procedure In Oracle?
Nov 7, 2013i have created a package & stored procedure which is working fine when i am passing single value to my package or stored procedure. But what approach i should take if i have to pass multiple values (ArrayList) like (from eg empid like I1001,I1002,I1003,I1004,...) in my input parameter . I am using C# & Oracle
CREATE OR REPLACE PACKAGE PKG_x AS type t_cursor is ref cursor;
procedure Proc(cur_x out t_cursor,
param_emp in varchar2 DEFAULT NULL);
END PACKAGE PKG_x;
[Code].....
SQL & PL/SQL :: Oracle Stored Procedure To Load Flat File Data
Oct 24, 2013I 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?
View 14 Replies View RelatedWindows / .NET :: Return String From Oracle Stored Procedure Using Excel 2003 VBA
Apr 12, 2013I am trying to return a value of an oracle stored procedure using Excel VBA.
OS: Win XP SP3
Excel 2003
Ora Client: 11g
By trying different things I have noticed, that I could have troubles with the ODBC-connection. Maybe I am not using the right one. To store data returned from select statements I have an ODBC-Connection (Driver: Oracle in XE), which works perfectly, e.g.:
'-----------------------
Sub Extract_Data()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim db_name, UserName, Password As String
cn.Open db_name, USerName, Password
[code]....
When debugging the connection string, I find the Provider=MSDASQL5.1.
SQL & PL/SQL :: Oracle Stored Function / Procedure To Calculate Number Of Working Days Between Two Dates
Feb 17, 2010I want Oracle stored function/procedure to calculate number of working days between two dates. We need to exclude Firdays and Saturdays as there are weekend holidays and also exclude official holidasy that lie between two dates.
View 7 Replies View RelatedPL/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;
Reports & Discoverer :: How To Call Oracle Reports Inside A Stored Procedure
Oct 14, 2010Currently some jobs created in WBT scripting need to converted into oracle,plsql.There is one job in WBT scripting, which will invoke the oracle reports inside and generate the PDF files in the destination path as follows:
a = runhide("c:Program FilesInternet Exploreriexplore.exe", "http://pscm9722:7778/reports/rwservlet?USERID=%LOGONINFO%+server=rep_pscm9722+destype=file+desname=D:ORACLE10G\%CCALLRptName%+desformat=PDF+PARAMFORM=no+report=PCCALL.RDF")
a = runhide("c:Program FilesInternet Exploreriexplore.exe", "http://pscm9722:7778/reports/rwservlet?
[code]...
Now, i want to convert this into oracle,plsql? Is it possible or not?
JDeveloper, Java & XML :: Calling Java Stored Procedure From Oracle
Jun 20, 2011I am writing a java stored procedure in my package. In the java procedure I want to issue a query to be fired on the user table from which I am calling the java procedure.
Some thing like following:
I am logged in to USER1 and my package is inside USER1. From this package I am calling the Java Stored Procedure. In java procedure I want to make a jdbc connection to USER2. The details of USER2(password, hostname,portno) are stored in a table called 'connection_details' and this table is in the USER1.
My problem is how can I issue the
SELECT details FROM CONNECTION_DETAILS for USER2
so that i can get the details..?
Do i need to first make jdbc connection to USER1 and then execute the above query for which i need similar details of USER1. How can i obtain the details of USER1 here.
I am thinking if I am already in USER1 then do I need to make the jdbc connection to USER1 and then execute the query?
SQL & PL/SQL :: Date Stored As Number
May 7, 2010I have a table
CREATE TABLE TEST
(
X NUMBER(8),
SNUM NUMBER
)
SET DEFINE OFF;
Insert into TEST (X, SNUM) Values (20100409, 1);
Insert into TEST (X, SNUM) Values (20100317, 1);
[Code]...
For some reason, column x is a number data type, but the data is actually date.
My aim:
1. select the maximum of the dates stored in column x (in numeric data)..for a given snum.
2. check if it is within 3 days range and return 1 or 0
My program
CREATE OR REPLACE FUNCTION f_chk (in_num number)
RETURN NUMBER
IS
v_chk NUMBER;
v_max_dat number;
BEGIN
SELECT COUNT (*) INTO v_chk
[Code]....
I have following assumptions, correct me if I am wrong:
SELECT max(x) INTO v_max_dat FROM test WHERE snum = in_num;
1. The above will actually give me the maximum date for a given snum (correct result), since the data is stored in yyyymmdd format, it does actually picks up the maximum date (stored in number format) for a given snum ... or should i use to_date to convert it to date and declare v_max_dat as date ?
SQL & PL/SQL :: Date Datatype Being Stored As DD:MM:YY HH:MI:SS?
Feb 21, 2012I have a table in my database with a column called theme_night_date that i want to store just a date and no time.
View 2 Replies View RelatedScheduling 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 RelatedSQL & PL/SQL :: Date Values Are Not Stored In Specific Pattern
Oct 15, 2010I have a date column, where the date values are not stored in a specific pattern. following are the sample value from the column.
8/10/10 12:00 AM
9/22/2010 1:00AM
01/01/2001
9/1/10 6:00 PM
9/22/2009 1:00AM
i want to convert this to a standard format, 'dd/mm'yyyy'.
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"?