I'd like to loop through a list of strings and execute a function/procedure with each string as the argument. What's the best alternative to the following generic code (since it's not legal): set serveroutput on; begin FOR r IN ('The', 'Quick', 'brown', 'fox') LOOP dbms_output.put_line( r ); END LOOP; end; I assu...
How can i append a whitespace character- for example   /   (non-breaking space) to a field value querying oracle server? I tried SELECT myfield || ' ' FROM mytable but this is interpreted as a variable with the name #160. ...
I have two procedures A and B. Procedure A performs certain tasks. Procedure B has to monitor how many times procedure A is called in a day. How to achieve this? ...
Hello, I've researched here and elsewhere but haven't found an answer for the following. I'd like to get a list of all procedures available to my application's Oracle account (AFAIK they're part of one package), and have tried the following command in sqlplus: SELECT * from user_procedures; However this only returns one row/procedur...
I have to keep track of revisions of records in a table. What I've done is create a second table that inherits from the first and adds a revision counter. CREATE TABLE A ( id SERIAL, foo TEXT, PRIMARY KEY (id)); CREATE TABLE B ( revision INTEGER NOT NULL) INHERITS (A); Then I created a trigger that would update table B everytime A i...
I have the following statements in Oracle 11g: CREATE TYPE person AS OBJECT ( name VARCHAR2(10), age NUMBER ); CREATE TYPE person_varray AS VARRAY(5) OF person; CREATE TABLE people ( somePeople person_varray ) How can i select the name value for a person i.e. SELECT somePeople(person(name)) FROM people Thanks ...
I have this SQL statement: SELECT ABX.ABX_APO_NUMBER, COUNT(A1.PROCESS_MODE) AS NUM_PLANNING, COUNT(A2.PROCESS_MODE) AS NUM_SETUP, COUNT(A3.PROCESS_MODE) AS NUM_OUTPUT FROM ABX, USER_INSTANCE U, ACTIVE_PROCESS A1, ACTIVE_PROCESS A2, ACTIVE_PROCESS A3 WHERE U.ABX_APO_NUMBER (+) = ABX.ABX_APO_NUMBER AND A...
I would like to use essentially the same query in both T-SQL (SQL Server 2000/2005) and PL/SQL (Oracle 10g). Though it is not trivial, it is reasonably vanilla code, but it works in SQL Server yet fails in Oracle. My goal is to generate a count of rows for each unique upper/lower case combination of a particular field, omitting any field...
I need to write a regular expression in order to be able to match the following patterns: IF:en0 IF:en0:10.94.80.78 IF:en11 IF:en11:10.94.80.78 The regular expression i wrote in PL SQL was IF REGEXP_LIKE(input_str, '^IF:en([0-9])([:]?)((25[0-5]|2[0-4][0-9]|[01]?[0-9]{1,2})\.){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9]{1,2})$') then DB...
Hi. Currently I'm making sort of SQL command line interface for web-based application. It should act roughly like sqlPlus. I have encountered a problem how to execute sql's. They can be both as SQL and/or PL/SQL. First way I thought that I can split them (by ';' or ';/') and detect separately if it's sql select or delete/update/insert ...
Is Commit a DML instruction in PL\SQL? ...
Where do you normally store your PL/SQL constants? On the package-body level? In the specification? I've also seen some people holding constants in a specialized package just for constants. What are best practices in this area? Thanks. ...
I have a file with the folowing script: BEGIN ... a bunch of inserts ... COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END; When I execute this in sqlplus I get the following: SQL> @file.sql 382 It's as if he's not ending the block. I'm new to using pl/sql and sqlplus, so I don't know if I'm doing something wron...
I am taking a database class and at the beginning of the lab section of the class we usually have to drop all the tables in the database created previously. I wanted to be able to run a script that does this dynamically, but cannot seem to get it to work. Here is the code I have so far. declare tname string(50); cursor ctable is select ...
I mostly work with sql-server (when I do work with databases) and I am trying to learn pl-sql. Is there any equivalent to sql-server's auto-generated Guid as primary keys in Oracle? ...
Hi. When executing pl/sql im obtaining an error : ORA-06550: line 1, column 316: PLS-00103: Encountered the symbol "/" The symbol "/" was ignored. PLSQL example: DECLARE SQL1 VARCHAR2 (1500); SQL2 VARCHAR2 (1500); BEGIN SQL1 := 'INSERT INTO das_html_caption VALUES (''test_test'')'; SQL2 := 'DELETE FROM das_html...
When I try to pass an array of numbers to a stored procedure, the stored procedure is not able to get the array contents. Array.count = 0 in the stored procedure! The stored procedure is pretty simple. It just has to insert the parameters into a table. code looks like : TYPE arr_parmid IS TABLE OF testtable.UNID%TYPE INDEX BY BINARY_IN...
Here's my cursor: CURSOR C1 IS SELECT * FROM MY_TABLE WHERE SALARY < 50000 FOR UPDATE; I immediately open the cursor in order to lock these records for the duration of my procedure. I want to raise an application error in the event that there are < 2 records in my cursor. Using the C1%ROWCOUNT property fails because it only counts t...
Let's say I've got a large script and want to cut it into pieces and then load the pieces from a main script file. The question is how to load and execute an external script using plain SQL in Oracle DBMS or PL/SQL from another script file? ...
Hey all, I have a need to check a live table against a transactional archive table and I'm unsure of the fastest way to do this... For instance, let's say my live table is made up of these columns: Term CRN Fee Level Code My archive table would have the same columns, but also have an archive date so I can see what values the live t...