Block procedure PL/SQL with Oracle
Hello, I have a procedure and i would like that only one user could execute this procedure at same time. How i can do it? A lot of thanks! Regards Marc ...
plsql
Get Correct Price based on Effectivity Date
Hi, I have a problem getting the right "Price" for a product based on Effectivity date. Example, I have 2 tables: a. "Transaction" table --> this contains the products ordered, and b. "Item Master" table --> this contains the product prices and effectivity dates of those prices Inside the Trasaction table: INVOICE_NO INVOICE_DAT...
PL/SQL: Retrieve names of procedures and functions within a package
Is it possible to retrieve the names of all procedures and functions that reside within a particular package? I understand that they can be gleaned (smells hack-ish) from the ALL_SOURCE view, but I would prefer a more canonical strategy. ...
How do I Open a Stored Procedure and Edit it in SQL*Plus
I need to make some changes to an old Oracle stored procedure on an obsolete box that is being kept around to run an old legacy process. The only tool I can connect to the db with is SQL*Plus. How do I load a stored proc into memory for editing in SQL*Plus? The tutorials I've found on-line don't exlain how that's done. :-( ...
Continuing Inserts in Oracle when exception is raised
Hi, I'm working on migration of data from a legacy system into our new app(running on Oracle Database, 10gR2). As part of the migration, I'm working on a script which inserts the data into tables that are used by the app. The number of rows of data that are imported runs into thousands, and the source data is not clean (unexpected nul...
Possible to create Oracle associative array type outside of a package/procedure?
In Oracle Database 10g, is it possible to create an associative array type outside of a package or procedure? I would like to be able to do this so that I can reference this associative array type in another type. For example: create type my_type_map is table of varchar2(10) index by varchar2(10); create type my_other_type as object ...
Getting the return value of a PL/SQL function via Hibernate
I have a PL/SQL function in an Oracle database that I can't change. This function takes a parameter which identifies an entity, creates a copy of that entity and then returns the ID of the copy. This looks something like FUNCTION copy_entity(id IN NUMBER) RETURN NUMBER I need to call this function from Hibernate. I tried creating a nam...
Possible to create Oracle Database object types inside of PL/SQL?
Is it possible to create an object type inside of a package in Oracle Database 10g? Something like: create or replace package my_package as type my_type as object ( id number(15) ); end; Gives: Error(3,9): PLS-00540: object not supported in this context. What I'm ultimately looking to be able to do is use po...
pl/sql save as
hi im from madrid and i wanted to know how to save a query/cursor, in a XXXXXXX.sql. thanks!!! ...
Can an Oracle variable of type RECORD be passed between procedures without the procedures knowing its real type?
Using Oracle 10g PL/SQL. I'm trying to find a way to pass a custom defined RECORD type around without the procedures having to know its real type. Sounds like a job for SYS.ANYDATA but it doesn't look like Oracle supports wrapping RECORD types. For example: DECLARE TYPE t_rec IS RECORD (id number); v_rec t_rec; v_ad SYS.ANYDATA...
What is the syntax for creating a queue subscriber in PL/SQL?
I'm trying to create a queue and a callback that triggers when a message is queued, but I can't get the callback to trigger. What am I doing wrong? I have a trigger that enqueues a message, and I can see it on the queue message table, and I can dequeue it by hand and process it, I just can't get the callback to fire on enqueue. BEGIN ...
IP address stored as decimal - PL/SQL to display as dotted quad.
We have an Oracle database that contains IP addresses stored as decimal integers - this is incredibly painful when manipulating the data by hand instead of via the web interface, yet hand manipulation is really handy as the network guys continually ask us to do strange things that the web interface authors did not anticipate. Could some...
Oracle: Global namespace qualifier for function?
How do I qualify a function or procedure call to indicate that it should be at global scope? I have my own scn_to_timestamp() in a package that needs to call the default global function of the same name. create or replace package px as function scn_to_timestamp(scn number) return timestamp; end px; create or replace package body p...
Can dbms_utility.get_time rollover?
I'm having problems with a mammoth legacy PL/SQL procedure which has the following logic: l_elapsed := dbms_utility.get_time - l_timestamp; where l_elapsed and l_timestamp are of type PLS_INTEGER and l_timestamp holds the result of a previous call to get_time This line suddenly started failing during a batch run with a ORA-01426: num...
Quickest query to check for the existence of a row in Oracle?
I'm using Oracle, and I have a very large table. I need to check for the existence of any row meeting some simple criteria. What's the best way to go about this using simple SQL? Here's my best guess, and while it may turn out to be fast enough for my purposes, I'd love to learn a canonical way to basically do SQL Server's "exists" in...
Under what conditions does ROWNUM=1 significantly increase performance in an "exists" syle query
I read some of the discussion in this question and thought to myself that in my PL/SQL code I have "exists" style queries all over the place that don't use the ROWNUM=1 optimisation. The questions I have are: Does the introduction of ROWNUM=1 significantly increase performance? If so, under what conditions would performance be particu...
use oracle cursor within a sys_refcursor
I've got a PL/SQL package that returns a sys_refcursor based on the id that you pass it. I'd like to iterate through some ids and create a new ref cursor with one column from the original result set repeated for each id. (Sort of a cross tab.) A very simplified version of the PL/SQL block looks like: create or replace package body da...
What would you submit as an example of your best code for peer review?
If your company was being audited, or you were in an interview, and you were asked to provide an example of your best code for peer review, what would you submit? (I would like actual code examples for people to vote on). Try to keep the sample small (less than 100 lines) and for the purposes of this forum lets keep it limited to JAVA, ...
Oracle CASE with OR
Hi All, Can we use CASE statement in Oracle with OR like this: CASE WHEN A > 0 OR B >0 THEN c=1 END; I know we can use AND, but I get an error when I use OR. Can you please suggest something? Thanks. ...
Check that all rows match a given criterion
requestId Consultantid statusid 1 2 10 2 2 10 3 2 10 I want to check if every row has a statusid of 10. if (every row has a statusid of 10) then -----do this endif; ...