I need to write a procedure to normalize a record that have multiple tokens concatenated by one char. I need to obtain these tokens splitting the string and insert each one as a new record in a table. Does Oracle have something like a &quot;split&quot; function?

Is there a function to split a string in PL/SQL?

What does `:` stand for in a query?

    INSERT INTO MyTable (ID) VALUES (:myId)

How does it fetch the desired value?


**Edit:** Also what is that sign called? I wanted to search on google, but what&#39;s the name for `:`?


  [1]: http://stackoverflow.com/questions/2177978/whats-the-in-a-query/2181662#2181662

What does the colon sign &quot;:&quot; do in a SQL query?

Is it possible, in PLSQL, to select all of the fields in a table except for 1 or 2, without having to specify the fields you want?

Example, the employee table has the fields:   


 - id
 - firstname
 - lastname
 - hobbies

Is it still possible to write a query similar to 

    select * from employee

while leaving the field `hobbies` without with having to write something like this?

    select id, firstname, lastname from employee



Can you SELECT everything, but 1 or 2 fields, without writer&#39;s cramp?

I was writing some tasks yesterday and it struck me that I don&#39;t really know **THE PROPER and ACCEPTED way** of checking if row exists in table when I&#39;m using  PL/SQL. 

For examples sake let&#39;s use table: 

    PERSON (ID, Name); 

Obviously I **can&#39;t do** (unless there&#39;s some secret method) something like:

    BEGIN 
      IF EXISTS SELECT id FROM person WHERE ID = 10; 
        -- do things when exists
      ELSE
        -- do things when doesn&#39;t exist
      END IF;
    END;
So my standard way of solving it was:

    DECLARE
      tmp NUMBER;
    BEGIN 
      SELECT id INTO tmp FROM person WHERE id = 10; 
      --do things when record exists
    EXCEPTION
      WHEN no_data_found THEN
      --do things when record doesn&#39;t exist
    END; 

However I don&#39;t know if it&#39;s accepted way of doing it, or if there&#39;s any better way of checking, I would really apprieciate if someone could share their wisdom with me. 

Proper way of checking if row exists in table in PL/SQL block

I have an SQL script that is called from within a shell script and takes a long time to run. It currently contains `dbms_output.put_line` statements at various points. The output from these print statements appear in the log files, but only once the script has completed.

Is there any way to ensure that the output appears in the log file as the script is running?

Is there any way to flush output from PL/SQL in Oracle?

I&#39;m trying to run a query that has a few columns that are a CLOB datatype. If i run the query like normal, all of those fields just have `(CLOB)` as the value.

I tried using `DBMS_LOB.substr(column`) and i get the error 

    ORA-06502: PL/SQL: numeric or value error: character string buffer too small

How can i query the CLOB column?

How to query a CLOB column in Oracle

i created a sequence using the following query,

    create sequence qname_id_seq start with 1 increment by 1 nocache;

Now when i try to create a table which uses the above sequence, it is throwing the following error,

    Error report:
    SQL Error: ORA-00907: missing right parenthesis
    00907. 00000 -  &quot;missing right parenthesis&quot;

I used the following query to create a table with sequence.nextval,

    CREATE TABLE qname
    (
        qname_id integer NOT NULL default qname_id_seq.nextval PRIMARY KEY,
        qname    VARCHAR2(4000) NOT NULL -- CONSTRAINT qname_uk UNIQUE
    );

create table with sequence.nextval in oracle

How do I convert a comma separated string to a array?

I have the input &#39;`1,2,3&#39;` , and I need to convert it into an array.


Convert comma separated string to array in PL/SQL

I have seen many queries with something as follows.

    Select 1  
    From table

What does this `1` mean, how will it be executed and, what will it return?

Also, in what type of scenarios, can this be used?

What does it mean by select 1 from table?

What is the string concatenation operator in Oracle SQL?  

Are there any &quot;interesting&quot; features I should be careful of?  

(This seems obvious, but I couldn&#39;t find a previous question asking it).  



What is the string concatenation operator in Oracle?

I have a PL/SQL function (running on Oracle 10g) in which I update some rows. Is there a way to find out how many rows were affected by the UPDATE? When executing the query manually it tells me how many rows were affected, I want to get that number in PL/SQL.

Number of rows affected by an UPDATE in PL/SQL

In the Oracle PL/SQL, how to escape single quote in a string ? I tried this way, it doesn&#39;t work. 

    declare
      stmt varchar2(2000);
    begin
      for i in 1021 .. 6020
      loop
        stmt := &#39;insert into MY_TBL (Col) values(\&#39;ER0002\&#39;)&#39;;
    
        dbms_output.put_line(stmt);
        execute immediate stmt;
        commit;
      end loop;
    exception
      when others then
        rollback;
        dbms_output.put_line(sqlerrm);
    end;
    /

PL/SQL, how to escape single quote in a string?

I&#39;d like to create an in-memory array variable that can be used in my PL/SQL code.  I can&#39;t find any collections in Oracle PL/SQL that uses pure memory, they all seem to be associated with tables.  I&#39;m looking to do something like this in my PL/SQL (C# syntax):

    string[] arrayvalues = new string[3] {&quot;Matt&quot;, &quot;Joanne&quot;, &quot;Robert&quot;};

**Edit:**
Oracle: 9i

Oracle PL/SQL - How to create a simple array variable?

Is it possible to search every field of every table for a particular value in Oracle?

There are hundreds of tables with thousands of rows in some tables so I know this could take a very long time to query. But the only thing I know is that a value for the field I would like to query against is `1/22/2008P09RR8`.
&lt;

I&#39;ve tried using this statement below to find an appropriate column based on what I think it should be named but it returned no results.

    SELECT * from dba_objects 
    WHERE object_name like &#39;%DTN%&#39;

There is absolutely no documentation on this database and I have no idea where this field is being pulled from.

Any thoughts?

Search All Fields In All Tables For A Specific Value (Oracle)

In SQL Server we can type `IsNull()` to determine if a field is null.  Is there an equivalent function in PL/SQL?

What is the Oracle equivalent of SQL Server&#39;s IsNull() function?

A very easy one for someone, 
The following insert is giving me the

&gt; ORA-01722: invalid number

why?

    INSERT INTO CUSTOMER VALUES (1,&#39;MALADY&#39;,&#39;Claire&#39;,&#39;27 Smith St Caulfield&#39;,&#39;0419 853 694&#39;);
    INSERT INTO CUSTOMER VALUES (2,&#39;GIBSON&#39;,&#39;Jake&#39;,&#39;27 Smith St Caulfield&#39;,&#39;0415 713 598&#39;);
    INSERT INTO CUSTOMER VALUES (3,&#39;LUU&#39;,&#39;Barry&#39;,&#39;5  Jones St Malvern&#39;,&#39;0413 591 341&#39;);
    INSERT INTO CUSTOMER VALUES (4,&#39;JONES&#39;,&#39;Michael&#39;,&#39;7  Smith St Caulfield&#39;,&#39;0419 853 694&#39;);
    INSERT INTO CUSTOMER VALUES (5,&#39;MALADY&#39;,&#39;Betty&#39;,&#39;27 Smith St Knox&#39;,&#39;0418 418 347&#39;);



SQL error &quot;ORA-01722: invalid number&quot;

I know this has been answered to some degree with PHP and MYSQL, but I was wondering if someone could teach me the simplest approach to splitting a string (comma delimited) into multiple rows in Oracle 10g (preferably) and 11g. 

The table is as follows:

    Name | Project | Error 
    108    test      Err1, Err2, Err3
    109    test2     Err1

I want to create the following:

    Name | Project | Error
    108    Test      Err1
    108    Test      Err2 
    108    Test      Err3 
    109    Test2     Err1

I&#39;ve seen a few potential solutions around stack, however they only accounted for a single column (being the comma delimited string). Any help would be greatly appreciated. 


Splitting string into multiple rows in Oracle

When executing the following code, it just says the procedure is completed and doesn&#39;t print the infomation i want it to (firstName, lastName) and then the other values from the select query in a table below.

     CREATE OR REPLACE PROCEDURE PRINT_ACTOR_QUOTES (id_actor char)
    AS
    CURSOR quote_recs IS
    SELECT a.firstName,a.lastName, m.title, m.year, r.roleName ,q.quotechar from quote q, role r,   
    rolequote rq, actor a, movie m
    where
    rq.quoteID = q.quoteID
    AND
    rq.roleID = r.roleID
     AND
    r.actorID = a.actorID
    AND
    r.movieID = m.movieID
    AND
     a.actorID = id_actor;
    BEGIN
    FOR row IN quote_recs LOOP
    DBMS_OUTPUT.PUT_LINE(&#39;a.firstName&#39; || &#39;a.lastName&#39;);

    end loop;
    END PRINT_ACTOR_QUOTES;
    / 
When setting server output on, I get 

    a.firstNamea.lastName
    a.firstNamea.lastName
    a.firstNamea.lastName
    a.firstNamea.lastName
multiple times!

DBMS_OUTPUT.PUT_LINE not printing

I wanted to print the value of a particular variable which is inside an anonymous block. I am using Oracle SQL Developer. I tried using `dbms_output.put_line`. But it is not working. The code which I am using is shown below.

    SET SERVEROUTPUT ON

    DECLARE

      CTABLE USER_OBJECTS.OBJECT_NAME%TYPE;
      CCOLUMN ALL_TAB_COLS.COLUMN_NAME%TYPE;
      V_ALL_COLS VARCHAR2(500);
  
      CURSOR CURSOR_TABLE
        IS
        SELECT OBJECT_NAME 
        FROM USER_OBJECTS 
        WHERE OBJECT_TYPE=&#39;TABLE&#39;
        AND OBJECT_NAME LIKE &#39;tb_prm_%&#39;;
      
      CURSOR CURSOR_COLUMNS (V_TABLE_NAME IN VARCHAR2)
        IS
        SELECT COLUMN_NAME
        FROM ALL_TAB_COLS
        WHERE TABLE_NAME = V_TABLE_NAME;
   
    BEGIN
    
      OPEN CURSOR_TABLE;

      LOOP
        FETCH CURSOR_TABLE INTO CTABLE;
        EXIT WHEN CURSOR_TABLE%NOTFOUND;
    
        OPEN CURSOR_COLUMNS (CTABLE);
    
        V_ALL_COLS := NULL;
    
        LOOP
          FETCH CURSOR_COLUMNS INTO CCOLUMN;
          V_ALL_COLS := V_ALL_COLS || CCOLUMN;
          IF CURSOR_COLUMNS%FOUND THEN
            V_ALL_COLS := V_ALL_COLS || &#39;, &#39;;
          ELSE
            EXIT;
          END IF;
        END LOOP;
     
        DBMS_OUTPUT.PUT_LINE(V_ALL_COLS);
    
      END LOOP;
      CLOSE CURSOR_TABLE;

    END;

And I am getting the output only as `anonymous block completed`. 


Printing the value of a variable in SQL Developer

I wanted to delete some unused schemas on our oracle DB. 

How can I query for all schema names ?

Oracle SQL Query for listing all Schemas in a DB

I have a database which has a NOT NULL constraint on a field, and I want to remove this constraint.  The complicating factor is that this constraint has a system-defined name, and that constraint&#39;s name differs between the production server, integration server, and the various developer databases.  Our current process is to check in change scripts, and an automated task executes the appropriate queries through sqlplus against the target database, so I&#39;d prefer a solution that could just be sent straight into sqlplus.

On my own database, the SQL to drop this would be:

    alter table MYTABLE drop constraint SYS_C0044566

I can see the constraint when I query the `all_constraints` view:

    select * from all_constraints where table_name = &#39;MYTABLE&#39;

but I am not sure how to work with the `SEARCH_CONDITION`&#39;s `LONG` data type or how best to dynamically delete the looked-up constraint even after I know its name.

So, how can I create a change script that can drop this constraint based on what it is, rather than what its name is?

----
EDIT:
@Allan&#39;s answer is a good one, but I am concerned (in my lack of Oracle expertise) that it may not be universally true that any constraint that might have a system-generated name will have associated with it a way to remove the constraint without having to know its name.  Is it true that there will always be a way to avoid having to know a system-named constraint&#39;s name when logically dropping that constraint?

How can I drop a &quot;not null&quot; constraint in Oracle when I don&#39;t know the name of the constraint?

For `INSERT`, `UPDATE` and `DELETE` SQL statements executed directly against the database, most database providers return the count of rows affected. For stored procedures, the number of records affected is always `-1`.

How do we get the number of records affected by a stored procedure?





How can I get the number of records affected by a stored procedure?

Is there a way in to just run a query once to select into a variable, considering that the query might return nothing, then in that case the variable should be null.

Currently, I can&#39;t do a `select into` a variable directly, since if the query returns nothing, the PL/SQL would complain variable not getting set. I can only run the query twice, with the first one do the count and if the count is zero, set the variable to null, and if the count is 1, select into the variable.

So the code would be like:
&lt;!-- language: lang-sql --&gt;


    v_column my_table.column%TYPE;
    v_counter number;
    select count(column) into v_counter from my_table where ...;
    if (v_counter = 0) then
        v_column := null;
    elsif (v_counter = 1) then
        select column into v_column from my_table where ...;
    end if;

thanks.

Update:
The reason I didn&#39;t use exception is I still have some following logic after assigning the `v_column`, and I have to use `goto` in the exception section to jump back to the following code. I&#39;m kind of hesitate of `goto` lines.

How to select into a variable in PL/SQL when the result might be null?

Is it possible to create user-defined exceptions and be able to change the SQLERRM?

For example:


    DECLARE
        ex_custom       EXCEPTION;
    BEGIN
        RAISE ex_custom;
    EXCEPTION
        WHEN ex_custom THEN
            DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
    /


The output is &quot;User-Defined Exception&quot;.  Is it possible to change that message?



EDIT:  Here is some more detail.

I hope this one illustrates what I&#39;m trying to do better.

    DECLARE
        l_table_status      VARCHAR2(8);
        l_index_status      VARCHAR2(8);
        l_table_name        VARCHAR2(30) := &#39;TEST&#39;;
        l_index_name        VARCHAR2(30) := &#39;IDX_TEST&#39;;
        ex_no_metadata      EXCEPTION;
    BEGIN
        
        BEGIN
            SELECT  STATUS
            INTO    l_table_status
            FROM    USER_TABLES
            WHERE   TABLE_NAME      = l_table_name;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                -- raise exception here with message saying
                -- &quot;Table metadata does not exist.&quot;
                RAISE ex_no_metadata;
        END;
        
        BEGIN
            SELECT  STATUS
            INTO    l_index_status
            FROM    USER_INDEXES
            WHERE   INDEX_NAME      = l_index_name;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                -- raise exception here with message saying
                -- &quot;Index metadata does not exist.&quot;
                RAISE ex_no_metadata;
        END;
        
    EXCEPTION
        WHEN ex_no_metadata THEN
            DBMS_OUTPUT.PUT_LINE(&#39;Exception will be handled by handle_no_metadata_exception(SQLERRM) procedure here.&#39;);
            DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
    /



In reality, there are dozens of those sub-blocks.  I&#39;m wondering if there&#39;s a way to have a single user-defined exception for each of those sub-blocks to raise, but have it give a different message, instead of creating a separate user-defined exception for each sub-block.

In .NET, it would be sort of like having a custom exception like this:

        public class ColorException : Exception
        {
            public ColorException(string message)
                : base(message)
            {
            }
        }

And then, a method would have something like this:

            if (isRed)
            {
                throw new ColorException(&quot;Red is not allowed!&quot;);
            }

            if (isBlack)
            {
                throw new ColorException(&quot;Black is not allowed!&quot;);
            }

            if (isBlue)
            {
                throw new ColorException(&quot;Blue is not allowed!&quot;);
            }

Oracle PL/SQL - Raise User-Defined Exception With Custom SQLERRM

I am trying to call `saveOrUpdate()` in hibernate to save data. Since columns have unique index, so its throws `ConstraintViolationException` when I look through via Eclipse debugger.  

Since root cause could be different for different exception while inserting data to table.  
I wanted to know, how can I loop / traverse through `getCause()` to check what is the root cause of exception and its message.

Update:  
Thanks everyone for your kind response, thing is I want output like in below image:  
![enter image description here][1]  
I need to access  **detailMessage** field.  
(I am really sorry If could not make my question more clear.)

Thanks.


  [1]: http://i.stack.imgur.com/n0Laj.png

How can I loop through Exception getCause() to find root cause with detail message

In SQL I (sadly) often have to use &quot;`LIKE`&quot; conditions due to databases that violate nearly every rule of normalization. I can&#39;t change that right now. But that&#39;s irrelevant to the question.

Further, I often use conditions like `WHERE something in (1,1,2,3,5,8,13,21)` for better readability and flexibility of my SQL statements.

Is there any possible way to combine these two things without writing complicated sub-selects?

I want something as easy as `WHERE something LIKE (&#39;bla%&#39;, &#39;%foo%&#39;, &#39;batz%&#39;)` instead of this:

    WHERE something LIKE &#39;bla%&#39;
    OR something LIKE &#39;%foo%&#39;
    OR something LIKE &#39;batz%&#39;

I&#39;m working with SQl Server and Oracle here but I&#39;m interested if this is possible in any RDBMS at all.

Is there a combination of &quot;LIKE&quot; and &quot;IN&quot; in SQL?

How can I get a PL/SQL block to output the results of a `SELECT` statement the same way as if I had done a plain `SELECT`?

For example how to do a `SELECT` like:

    SELECT foo, bar FROM foobar;
 

Hint :

    BEGIN
    SELECT foo, bar FROM foobar;
    END;

doesn&#39;t work.

Is it possible to output a SELECT statement from a PL/SQL block?


What is the difference between function and procedure in PL/SQL ?


  [1]: http://stackoverflow.com/questions/721090/what-is-the-difference-between-a-function-and-a-procedure/721107#721107

What is the difference between function and procedure in PL/SQL?

What is the difference between SQL, PL-SQL and T-SQL?

Can anyone explain what the differences between these three are, and provide scenarios where each would be relevantly used?


What is the difference between SQL, PL-SQL and T-SQL?

I am using oracle 10g express edition. It has a nice ui for db developers. But i am facing some problems executing stored procedures. 

Procedure:

    create or replace procedure temp_proc is
    begin
      DBMS_OUTPUT.PUT_LINE(&#39;Test&#39;);
    end

it is created successfully. But when i execute:

`execute temp_proc;`

it shows **ORA-00900: invalid SQL statement** 

So help needed here

How to execute an oracle stored procedure?

I have &quot;read only&quot; access to a few tables in an Oracle database. I need to get schema information on some of the columns. I&#39;d like to use something analogous to MS SQL&#39;s `sp_help`. 

I see the table I&#39;m interested in listed in this query:

    SELECT * FROM ALL_TABLES

When I run this query, Oracle tells me &quot;table not found in schema&quot;, and yes the parameters are correct.

    SELECT 
    DBMS_METADATA.GET_DDL(&#39;TABLE&#39;, &#39;ITEM_COMMIT_AGG&#39;, &#39;INTAMPS&#39;) AS DDL
    FROM DUAL;

After using my Oracle universal translator 9000 I&#39;ve surmised this doesn&#39;t work because I don&#39;t have sufficient privileges. Given my constraints how can I get the datatype and data length of a column on a table I have read access to with a PL-SQL statement?

How do I get column datatype in Oracle with PL-SQL with low privileges?

How do I add a simple check before adding a column to a table for an oracle db? I&#39;ve included the SQL that I&#39;m using to add the column.

    ALTER TABLE db.tablename 
      ADD columnname NVARCHAR2(30);



How to check if a column exists before adding it to an existing table in PL/SQL?

After executing this SQL in oracle 10g:

    SELECT SYSDATE, CURRENT_TIMESTAMP  FROM DUAL
I receive this strange output:
![Toad output for query][1]


  [1]: http://i.stack.imgur.com/FiPgB.png

What is cause of the difference in time? 
The server time is equal of SYSDATE value

Different CURRENT_TIMESTAMP and SYSDATE in oracle

How can I do in **one select** with multiple columns and put each column in a variable?

Something like this:



    --code here
    V_DATE1 T1.DATE1%TYPE;
    V_DATE2 T1.DATE2%TYPE;
    V_DATE3 T1.DATE3%TYPE;
    
    SELECT T1.DATE1 INTO V_DATE1, T1.DATE2 INTO V_DATE2, T1.DATE3 INTO V_DATE3
    FROM T1
    WHERE ID=&#39;X&#39;;

    --code here


Select multiple columns into multiple variables

can anybody explain what is the main difference between functions and procedures in Oracle? Why must I use procedures if I can do everything with functions? 

 
 1. If I cannot call procedure in sql statement, ok, I&#39;ll write a function to do the same work.
 2. Procedures don&#39;t return values, ok, I&#39;ll return only sql%rowcount or 1(success), 0(exception) after any dml operation
 3. Both procedures and functions can pass variables to calling environment via OUT/IN OUT parameters

I heard that the main difference is in performance, &#39;procedures are faster than functions&#39;. But without any detail. 

Thanks in advance.

Functions vs procedures in Oracle

I&#39;m comparing three Oracle schemas.

I want to get a list of all the functions and procedures used in each database. Is this possible via a query? (preferably including a flag as to whether they compile or not)

Ideally it would be great to have a single query with a flag that states whether the function/procedure is in each schema. But even just the first bit would be better than manually inspecting each schema.


Get a list of all functions and procedures in an Oracle database

Using the DUAL table, how can I get a list of numbers from 1 to 100?

SQL to generate a list of numbers from 1 to 100

I have a PL/SQL function with BOOLEAN in parameter:

    function get_something(name in varchar2, ignore_notfound in boolean);

This function is a part of 3rd party tool, I cannot change this.

I would like to use this function inside a SELECT statement like this:

     select get_something(&#39;NAME&#39;, TRUE) from dual;

This does not work, I get this exception:

&gt; ORA-00904: &quot;TRUE&quot;: invalid identifier


As I understand it, keyword `TRUE` is not recognized.

How can I make this work?

How to use BOOLEAN type in SELECT statement

This information should be easy to find, but I haven&#39;t had any luck.

When I have a `BEGIN - END` block in a PL/SQL, does it behave as an atomic transaction, that will try to commit on hitting the `END` block and if anything goes wrong rolls back the changes?

If not, how do I make sure that the code inside the BEGIN - END block behaves like an atomic transaction and how does the block behave &quot;by default&quot;?

EDIT: I am running from a stored procedure and I am using an implicit block, I think.

BEGIN - END block atomic transactions in PL/SQL

    SET SERVEROUTPUT ON
    DECLARE
		v_student_id NUMBER := &amp;sv_student_id;
		v_section_id NUMBER := 89;
		v_final_grade NUMBER;
		v_letter_grade CHAR(1);
    BEGIN
		SELECT final_grade
		INTO v_final_grade
		FROM enrollment
		WHERE student_id = v_student_id
		AND section_id = v_section_id;
		
		CASE -- outer CASE
			WHEN v_final_grade IS NULL THEN
				DBMS_OUTPUT.PUT_LINE (&#39;There is no final grade.&#39;);
			ELSE
				CASE -- inner CASE
					WHEN v_final_grade &gt;= 90 THEN v_letter_grade := &#39;A&#39;;
					WHEN v_final_grade &gt;= 80 THEN v_letter_grade := &#39;B&#39;;
					WHEN v_final_grade &gt;= 70 THEN v_letter_grade := &#39;C&#39;;
					WHEN v_final_grade &gt;= 60 THEN v_letter_grade := &#39;D&#39;;
					ELSE v_letter_grade := &#39;F&#39;;
				END CASE;
				
				-- control resumes here after inner CASE terminates
				DBMS_OUTPUT.PUT_LINE (&#39;Letter grade is: &#39;||v_letter_grade);
		END CASE;
		-- control resumes here after outer CASE terminates
    END;

the above code I have taken from the book *&quot;Oracle PL/SQL by Example, 4th Edition 2009&quot;* my problem is when I enter a `student_id` not present in the table it returns me the following error

&lt;pre&gt;
Error report: ORA-01403: no data found
ORA-06512: at line 7
01403. 00000 -  &quot;no data found&quot;
*Cause:    
*Action:
&lt;/pre&gt;

but according to the book it should have returned a null value and then follow the case flow.

PL/SQL block problem: No data found error

I have been trying to find out what the difference is between the `IS` and `AS` keywords in PL/SQL when creating an Oracle function or procedure. 

I have searched and have been unable to find any information on this. Does anyone know the difference?

IS vs AS keywords for PL/SQL Oracle Function or Procedure Creation

When I run the following in an Oracle shell it works fine

    truncate table table_name

But when I try to put it in a stored procedure

    CREATE OR REPLACE PROCEDURE test IS
    BEGIN
        truncate table table_name;
    END test;
    /

it fails with

    ERROR line 3, col 14, ending_line 3, ending_col 18, Found &#39;table&#39;, Expecting:  @   ROW  or   (   or   .   or   ;   :=

Why?

Truncating a table in a stored procedure

What is the easiest way to INSERT a row if it doesn&#39;t exist, in PL/SQL (oracle)?


I want something like:

    IF NOT EXISTS (SELECT * FROM table WHERE name = &#39;jonny&#39;) THEN
      INSERT INTO table VALUES (&quot;jonny&quot;, null);
    END IF;

But it&#39;s not working.

Note: this table has 2 fields, say, **name** and **age**. But only **name** is PK.

All Plsql Solutions on Gang of Coders

Total of 43 Plsql Solutions