Here is how you could create such a table: SELECT LEVEL AS id, REGEXP_SUBSTR(‘A,B,C,D’, ‘[^,]+’, 1, LEVEL) AS data FROM dual CONNECT BY REGEXP_SUBSTR(‘A,B,C,D’, ‘[^,]+’, 1, LEVEL) IS NOT NULL; With a little bit of tweaking (i.e., replacing the , in [^,] with a variable) you could write such a function to return a table.
If you’re talking about PL/SQL, you should put it in an anonymous block. DECLARE v_text VARCHAR2(10); — declare BEGIN v_text := ‘Hello’; –assign dbms_output.Put_line(v_text); –display END;
You have to swap the TRUNCATE statement to DELETE statements, slower and logged but that’s the way to do it when constraints are in place. DELETE mytablename; Either that or you can find the foreign keys that are referencing the table in question and disable them temporarily. select ‘ALTER TABLE ‘||TABLE_NAME||’ DISABLE CONSTRAINT ‘||CONSTRAINT_NAME||’;’ from … Read more
Revised Answer If you’re not calling this code from another program, an option is to skip PL/SQL and do it strictly in SQL using bind variables: var myname varchar2(20); exec :myname := ‘Tom’; SELECT * FROM Customers WHERE Name = :myname; In many tools (such as Toad and SQL Developer), omitting the var and exec … Read more
Use: SELECT t1.id, t1.parent_id, t1.name, t2.name AS parent_name, t2.id AS parent_id FROM tbl t1 LEFT JOIN tbl t2 ON t2.id = t1.parent_id START WITH t1.id = 1 CONNECT BY PRIOR t1.id = t1.parent_id
If is a package then you can get the source for that with: select text from all_source where name=”PADCAMPAIGN” and type=”PACKAGE BODY” order by line; Oracle doesn’t store the source for a sub-program separately, so you need to look through the package source for it. Note: I’ve assumed you didn’t use double-quotes when creating that … Read more
Normally, it is simplest to drop and add the user. This is the preferred method if you have system or sysdba access to the database. If you don’t have system level access, and want to scrub your schema, the following sql will produce a series of drop statments, which can then be executed. select ‘drop … Read more
One additional idea, mentioned here is to use a regular expression to check: SELECT foo FROM bar WHERE REGEXP_LIKE (foo,’^[[:digit:]]+$’); The nice part is you do not need a separate PL/SQL function. The potentially problematic part is that a regular expression may not be the most efficient method for a large number of rows.
You can loop through the elements using EXTRACT and XMLSequence (splits the XML into distinct chunks — here users) like this: SQL> SELECT extractvalue(column_value, ‘/user/name’) “user” 2 FROM TABLE(XMLSequence(XMLTYPE( 3 ‘<?xml version=”1.0″?> 4 <users> 5 <user> 6 <name>user1</name> 7 </user> 8 <user> 9 <name>user2</name> 10 </user> 11 <user> 12 <name>user3</name> 13 </user> 14 </users>’).extract(‘/users/user’))) t; … Read more
I believe the problem is the use of UNION instead of UNION ALL. The UNION operator will combine the two sets and eliminate duplicates. Since CLOB types cannot be compared, the duplicate elimination part is not possible. Using UNION ALL won’t attempt to do duplicate elimination (you probably don’t have duplicates anyways) so it should … Read more