Difficult to put down in words, so assuming this example table: | id | col1 | col2 | -------------------- | 1 | aa | 12 | | 2 | aa | 12 | | 3 | bb | 13 | | 4 | cc | 13 | I would like a query which selects rows 3 & 4 or even just the value 13 So something like checking this assumption: "all values of col2 which a...
Is there any feature of asynchronous calling in PL/SQL? Suppose I am in a block of code would like to call a procedure multiple times and wouldn't bother when and what the procedure returns? BEGIN myProc(1,100); myProc(101,200); myProc(201,300); ... ... END; In the above case, I don't want my code to wait for myProc(1,100) ...
I've heard that it's a good practice to define your records in PL/SQL by using the %ROWTYPE attribute. This saves typing and allows your package to continue functioning even when a column is added or deleted. (Correct me if I'm wrong!) However, when I am fetching from a cursor that involves a join, I find that I have to fetch into a pro...
I mean: Table PHONE_CODES: ID CODE_NAME PHONE_CODE 1 USA 8101 2 USA 8102 3 PERU 8103 4 PERU_MOB 81031 5 PERU_MOB 81032 And I want via select to get something like this: CODE_NAME ZONE_CODES USA 8101; 8102; PERU 8103 PERU_MOB 81031; 81032; I could get it via the functio...
Hi, i'm creating a procedure to update/insert a table using merge statement(upsert).now i have a problem: using procedure parameters i have to do this upsert. procedure xyz( a in table.a%type,b in table.b%type,....) is some local variables; begin merge into target_table using source_table --instead of the source table, i have to use p...
The scenario that created this question: We have a package that is a dependency of another package, sometimes making changes to the "parent" package causes the dependent package to become invalid, but sometimes it doesn't. It has caught us by surprise before. It would be very useful to simply understand what causes invalidation so...
I have created a Web Service to send in a bunch of information to a PL/SQL procedure, however one of them is a array. What type do I use for this? I also want to put that array into a cursor after it comes in. ...
SELECT * from ALL_OBJECTS returns the names of various procedures/packages/tables/other db objects. I want to look inside the PL/SQL code for a matching string. How do I do this? Something like: (pseudocode) SELECT * FROM all_code WHERE line_of_code like '%mytext%' ...
I'm learning regex and need some help to get all possible matches for a pattern out of a string. If my input is: case a when cond1 then stmt1; when cond2 then stmt2; end case; I need to get the matches which have groups as follows Group1: "cond1" "stmt1;" and Group2: "cond2" "stmt2;" Is it possible to get such groups using...
I'd like to be able to return all columns in a table or in the resulting table of a join and still be able to transform a date to a string by name. For example Select ID, DESCRIPTION, TO_CHAR(CHANGE_DATE,'YYYY-MM-DD HH24:MI:SS') AS FORMATED_DATE FROM MY_TABLE; This is all well and good for just these three columns. But, the table will...
We have a system that allows users interfacing data into the database to set up various rules that are used to alter data before it is merged in to the main table. For example, an order might have a rule that sets up what delivery company to use based on a customer's address. This is originally intended to operate only on the data being...
I am not a database person, exactly, and most of my db work has been with MySQL, so forgive me if something in this question is incredibly naive. I need to delete 5.5 million rows from an Oracle table that has about 100 million rows. I have all the IDs of the rows I need to delete in a temporary table. If it were a just a few thousand r...
We're developing a web application which according to my spec must have the backend written entirely in PL/SQL (stored procs etc.). Anyone have any advice/links on how to write a well structured backend using stored procedures and custom types? Normally I would have a business layer where all this would happen but what the employer wants...
i want to extract the comments out of a string as a block. e.g. I have a PL/SQL code as: --comment1 select * from t_table; --i want comment 2; /*i want comment 3 */ --i want comment 4 OPEN data_cur; Here, i want all the single line and multiline comments before OPEN data_cur; but after select * from t_table; i.e. i want a full co...
I'm working on pl/sql code where i want to replace ';' which is commented with '~'. e.g. If i have a code as: --comment 1 with; select id from t_id; --comment 2 with ; select name from t_id; /*comment 3 with ;*/ Then i want my result text as: --comment 1 with~ select id from t_id; --comment 2 with ~ select name from t_id; /*com...
Suggestion for PL/SQL programming book ...
Hi, I have a pl/sql script that I run from a sqlplus session as 'sysdba' . During the execution of the script I would like to switch to another user so I can do some db link creation for that user. After this is done the script should return to 'sysdba' for some finishing up. I tried to use the following: BEGIN <do some stuff as sys...
Hi All, I am using a regular expression to find out whether the user entered value is alpha numeric, allowing some special characters. I am using the following code which works fine: CREATE OR REPLACE PROCEDURE Validate_Inputstring (input_String IN VARCHAR2) AS BEGIN IF REGEXP_LIKE(input_String,'^[A-Z0-9a-z,+-?@]*$') THEN DB...
I have the following dynamic SQL in one of my package bodies OPEN ccur for 'select c.category from test_category c where c.deptid='||PI_N_Dept || ' and c.category not in ('|| sExcludeCategories ||')'; sExcludeCategories will contain a set of integers separated by comma. I would like to eliminate this dynamic SQL ...
Where is the function sysdate stored, and in what package, e.g: select sysdate from dual; select systimestamp from dual; Also, take this query: select sys.login_user,sys.database_name ,sys.sysevent from dual; what is sys here? Is it a package? where is this package stored? can I view the source(text) in this package please provide...