No, you can&#39;t.

There&#39;s no reason why you would need to. This is a one-time operation and so takes only an additional second or two to actually type and execute.

If you&#39;re adding columns in your web application this is more indicative of a flaw in your data-model as you shouldn&#39;t need to be doing it.

----

In response to your comment that a comment is a column attribute; it may _seem_ so but behind the scenes Oracle stores this as an attribute of an _object_.


    SQL&gt; desc sys.com$
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     OBJ#                                      NOT NULL NUMBER
     COL#                                               NUMBER
     COMMENT$                                           VARCHAR2(4000)
    
    SQL&gt;

The column is optional and `sys.col$` does not contain comment information.

I assume, I have no knowledge, that this was done in order to only have one system of dealing with comments rather than multiple.


You can use below query to update or create comment on already created table.

SYNTAX:

    COMMENT ON COLUMN TableName.ColumnName IS &#39;comment text&#39;;

Example:

    COMMENT ON COLUMN TAB_SAMBANGI.MY_COLUMN IS &#39;This is a comment on my column...&#39;;

Query to add column with comment are :

    alter table table_name 
	add( &quot;NISFLAG&quot;    NUMBER(1,0) )

    comment on column &quot;ELIXIR&quot;.&quot;PRD_INFO_1&quot;.&quot;NISPRODGSTAPPL&quot; is &#39;comment here&#39;

    commit;

Add comments for two different columns of the EMPLOYEE table :

    COMMENT ON EMPLOYEE
         (WORKDEPT IS &#39;see DEPARTMENT table for names&#39;,
         EDLEVEL IS &#39;highest grade level passed in school&#39; )

Here are my attempts with error messages. What am I doing wrong?

    string.decode(&quot;ascii&quot;, &quot;ignore&quot;)


&gt; UnicodeEncodeError: &#39;ascii&#39; codec can&#39;t encode character u&#39;\xa0&#39; in
&gt; position 37: ordinal not in range(128)


    string.encode(&#39;utf-8&#39;, &quot;ignore&quot;)

&gt; UnicodeDecodeError: &#39;ascii&#39; codec can&#39;t decode byte 0xc2 in position
&gt; 37: ordinal not in range(128)

string encoding and decoding?

I am trying to update the value of a column where it matches a certain `userid`, but it keeps giving a syntax error.

    UPDATE user 
       SET balance = 15000.000000000 
     WHERE id = 11203;

The table called user has many rows with two columns, `balance` and `id`. I am trying to edit the balance of the user id in the code.

Update column value PostgreSQL

I am using Oracle 11g for my web application. I want to add a column and a comment to an existing table. I can do that easily with the below commands

    ALTER TABLE product ADD product_description VARCHAR2(20)
and 

    COMMENT ON COLUMN product.product_description 
         IS &#39;This is comment for the column&#39;;

But I want to do above task in single command. I searched on internet for a command to add a column and comment in a single command but I couldn&#39;t find. I wonder if this is possible. Any suggestions would be highly appreciated.

SQL to add column and comment in table in single command

I am using Oracle 11g for my web application. I want to add a column and a comment to an existing table. I can do that easily with the below commands
<pre><code class="hljs language-sql">ALTER TABLE product ADD product_description VARCHAR2(20)
</code></pre>
and
<pre><code class="hljs language-vbnet">COMMENT ON COLUMN product.product_description 
 IS 'This is comment for the column';
</code></pre>
But I want to do above task in single command. I searched on internet for a command to add a column and comment in a single command but I couldn't find. I wonder if this is possible. Any suggestions would be highly appreciated.

I am using a java PreparedStatment object to construct a series of batched INSERT queries.  The query statement is of the format...

    String strQuery = &quot;INSERT INTO ? (col1, col2, col3, col4, col5) VALUES (?,?,?,?,?,?);&quot;;

...so both field values and the tablename are variables (ie. I have multiple tables with the same column format of which each insert will be directed to a different one).  I can get the executes to work if I remove the &quot;?&quot; tablename variable and hard code but each prepared statement will be inserted into a different table so needs to remain a variable I populate immediately prior to executing the batch query using...

    stmt.setString(1, &quot;tableName1&quot;);

How can I let this be a dynamic variable please?


How to use a tablename variable for a java prepared statement insert

I got the following error while trying to alter a column&#39;s data type and setting a new default value:

    ALTER TABLE foobar_data ALTER COLUMN col VARCHAR(255) NOT NULL SET DEFAULT &#39;{}&#39;;

&gt; ERROR 1064 (42000): You have an error in your SQL syntax; check the
&gt; manual that corresponds to your MySQL server version for the right
&gt; syntax to use near &#39;VARCHAR(255) NOT NULL SET DEFAULT &#39;{}&#39;&#39; at line 1

How to alter a column and change the default value?

I&#39;ve used the mysqli_stmt_bind_param function several times. However, if I separate variables that I&#39;m trying to protect against SQL injection I run into errors.

Here&#39;s some code sample:

    function insertRow( $db, $mysqli, $new_table, $Partner, $Merchant, $ips, $score, $category, $overall, $protocol )
    {
        $statement = $mysqli-&gt;prepare(&quot;INSERT INTO &quot; .$new_table . &quot; VALUES (?,?,?,?,?,?,?);&quot;);
        mysqli_stmt_bind_param( $statment, &#39;sssisss&#39;, $Partner, $Merchant, $ips, $score, $category, $overall, $protocol );
        $statement-&gt;execute();
    }

Is it possible to somehow replace the `.$new_table.` concatenation with another question mark statement, make another bind parameter statement, or add onto the existing one to protect against SQL injection?

Like this or some form of this:

    function insertRow( $db, $mysqli, $new_table, $Partner, $Merchant, $ips, $score, $category, $overall, $protocol )
    {    
        $statement = $mysqli-&gt;prepare(&quot;INSERT INTO (?) VALUES (?,?,?,?,?,?,?);&quot;);
        mysqli_stmt_bind_param( $statment, &#39;ssssisss&#39;, $new_table, $Partner, $Merchant, $ips, $score, $category, $overall, $protocol );
        $statement-&gt;execute();
    }




Can I parameterize the table name in a prepared statement?

I am trying to learn the best way to write queries. I also understand the importance of being consistent. Until now, I have randomly used single quotes, double quotes, and backticks without any real thought.

Example:

    $query = &#39;INSERT INTO table (id, col1, col2) VALUES (NULL, val1, val2)&#39;;

Also, in the above example, consider that `table`, `col1`, `val1`, etc. may be variables.

What is the standard for this? What do you do?

&lt;sub&gt;I&#39;ve been reading answers to similar questions on here for about 20 minutes, but it seems like there is no definitive answer to this question.&lt;/sub&gt;

When to use single quotes, double quotes, and backticks in MySQL

From time to time I see questions regarding connecting to database.  
Most answers is not the way I do it, or I might just not get the answers correctly. Anyway; I&#39;ve never thought about it because the way I do it works for me.

But here&#39;s a crazy thought; Maybe I&#39;m doing this all wrong, and if that&#39;s the case; I would really like to know how to properly connect to a MySQL database using PHP and PDO and make it easy accessible.

**Here&#39;s how I&#39;m doing it:**

First off, here&#39;s my file structure *(stripped down)*:

    public_html/

    * index.php  

    * initialize/  
      -- load.initialize.php  
      -- configure.php  
      -- sessions.php   

**index.php**  
At the very top, I have `require(&#39;initialize/load.initialize.php&#39;);`.  

**load.initialize.php**  

    #   site configurations
        require(&#39;configure.php&#39;);
    #   connect to database
        require(&#39;root/somewhere/connect.php&#39;);  //  this file is placed outside of public_html for better security.
    #   include classes
        foreach (glob(&#39;assets/classes/*.class.php&#39;) as $class_filename){
            include($class_filename);
        }
    #	include functions
        foreach (glob(&#39;assets/functions/*.func.php&#39;) as $func_filename){
            include($func_filename);
        }
    #	handle sessions
        require(&#39;sessions.php&#39;);

*I know there&#39;s a better, or more correct, way to include classes, but can&#39;t remember what it was. Haven&#39;t gotten the time to look into it yet, but I think it was something with `autoload`. something like that...*

**configure.php**  
Here I basically just override some *php.ini*-properties and do some other global configuration for the site

**connect.php**  
I&#39;ve put the connection onto a class so other classes can *extends* this one...

    class connect_pdo
    {
        protected $dbh;

        public function __construct()
        {
            try {
                $db_host = &#39;  &#39;;  //  hostname
                $db_name = &#39;  &#39;;  //  databasename
                $db_user = &#39;  &#39;;  //  username
                $user_pw = &#39;  &#39;;  //  password

                $con = new PDO(&#39;mysql:host=&#39;.$db_host.&#39;; dbname=&#39;.$db_name, $db_user, $user_pw);  
                $con-&gt;setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
                $con-&gt;exec(&quot;SET CHARACTER SET utf8&quot;);  //  return all sql requests as UTF-8  
            }
            catch (PDOException $err) {  
                echo &quot;harmless error message if the connection fails&quot;;
                $err-&gt;getMessage() . &quot;&lt;br/&gt;&quot;;
                file_put_contents(&#39;PDOErrors.txt&#39;,$err, FILE_APPEND);  // write some details to an error-log outside public_html  
                die();  //  terminate connection
            }
        }

        public function dbh()
        {
            return $this-&gt;dbh;
        }
    }
    #   put database handler into a var for easier access
        $con = new connect_pdo();
        $con = $con-&gt;dbh();
    //



*Here I do believe there&#39;s room for massive improvement since I recently started learning OOP, and using PDO instead of mysql.  
So I&#39;ve just followed a couple of beginners tutorials and tried out different stuff...*

**sessions.php**  
Beside handling regular sessions, I also initialize some classes into a session like this:  

    if (!isset($_SESSION[&#39;sqlQuery&#39;])){
        session_start();
        $_SESSION[&#39;sqlQuery&#39;] = new sqlQuery();
    }

This way this class is available all over the place. This might not be good practice(?)...  
Anyway, this is what this approach allows me to do from everywhere:

    echo $_SESSION[&#39;sqlQuery&#39;]-&gt;getAreaName(&#39;county&#39;,9);  // outputs: Aust-Agder (the county name with that id in the database)

Inside my `sqlQuery`-*class*, which `extends` my `connect_pdo`-*class*, I have a public function called `getAreaName` which handles the request to my database.  
Pretty neat I think.


**Works like a charm**  
So that&#39;s basically how I&#39;m doing it.  
Also, whenever I need to fetch something from my DB from not within a class, I just do something similar to this:

    $id = 123;
    
    $sql = &#39;SELECT whatever FROM MyTable WHERE id = :id&#39;;
    $qry = $con-&gt;prepare($sql);
    $qry -&gt; bindParam(&#39;:id&#39;, $id, PDO::PARAM_INT);
    $qry -&gt; execute();
    $get = $qry-&gt;fetch(PDO::FETCH_ASSOC);
 
Since I put the connection into a variable inside *connect_pdo.php*, I just have referring to it and I&#39;m good to go. It works. I get my expected results...

But regardless of that; I would really appreciate if you guys could tell me if I&#39;m way off here. What I should do instead, areas I could or should change for improvement etc... 

I&#39;m eager to learn...

How to properly set up a PDO connection

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 want to add a new auto increment primary column to a existing table which has data. How do I do that?

I first added a column and then try to add a sequence after that, I lost how to insert and make that column as primary key.

Add a auto increment primary key to existing table in oracle

I am trying to use the `LISTAGG` function in Oracle. I would like to get only the distinct values for that column. Is there a way in which I can get only the distinct values without creating a function or a procedure?

&lt;pre&gt;
  col1  col2 Created_by
   1     2     Smith 
   1     2     John 
   1     3     Ajay 
   1     4     Ram 
   1     5     Jack 
&lt;/pre&gt;

I need to select col1 and the `LISTAGG` of col2 (column 3 is not considered). When I do that, I get something like this as the result of `LISTAGG`: *[2,2,3,4,5]* 

I need to remove the duplicate &#39;2&#39; here; I need only the distinct values of col2 against col1.

LISTAGG in Oracle to return distinct values

I am using Oracle SQL Developer.
I essentially have a table of pictures that holds the columns:

[DATE_CREATED(date), NUM_of_PICTURES(int)]

and if I do a select *, I would get an output similar to:

    01-May-12    12
    02-May-12    15
    03-May-12    09
    ...
    ...
    01-Jun-12    20
    ...
    etc.

I am trying to aggregate these sums of pictures into MONTHLY numbers instead of DAILY.

I&#39;ve tried doing something like:

    select Month(DATE_CREATED), sum(Num_of_Pictures))
    from pictures_table
    group by Month(DATE_CREATED);

This outputs an error:

    ORA-00904: &quot;MONTH&quot;: invalid identifier
    00904. 00000 -  &quot;%s: invalid identifier&quot;
    *Cause:    
    *Action:
    Error at Line: 5 Column: 9

Do I have the Month function wrong?

How to query GROUP BY Month in a Year

Iam trying to refresh the materialized view by using:

    DBMS_MVIEW.REFRESH(&#39;v_materialized_foo_tbl&#39;)

But it&#39;s throwing invalid sql statement. 

Then I have created a stored procedure like this:

    CREATE OR REPLACE 
    PROCEDURE MAT_VIEW_FOO_TBL 
    IS
    BEGIN
       DBMS_MVIEW.REFRESH(&#39;v_materialized_foo_tbl&#39;)
    END MAT_VIEW_FOO_TBL IS;

This procedure has been created successfully but when i am calling this procedure with 

    MAT_VIEW_FOO_TBL;

it&#39;s throwing an error again.

Kindly suggest a solution for this issue.

Thanks,
Srinivas



How to refresh materialized view in oracle

I have defined a name for each of the constraint for the multiple tables that I have created in Oracle SQL. 

The problem is that to drop a constraint for the column of a particular table I need to know the name that I have supplied for each constraints, which I have forgotten. 

How do I list out all the names of constraints that I have specified for each column of a table? 

Is there any SQL statement for doing so? 

Display names of all constraints for a table in Oracle SQL

I&#39;m trying to connect to a schema on 11g (v11.2.0.1.0) from a PC with 9i (v9.2.0.1) client. It seems to connect fine to some schemas, but not this one - it comes back with a `ORA-01017 Invalid Username/Password` error every time.

The username and password are DEFINITELY correct - can anyone think of a reason why this wouldn&#39;t work? 

Are there any fundamental incompatibilities between 9i and 11g?


ORA-01017 Invalid Username/Password when connecting to 11g database from 9i client

I have a query that looks something like this:
    
    SELECT DISTINCT share.rooms
    FROM Shares share
      left join share.rooms.buildingAdditions.buildings.buildingInfoses as bi
	... //where clause omitted
	ORDER BY share.rooms.floors.floorOrder, share.rooms.roomNumber,
             share.rooms.firstEffectiveAt, share.shareNumber, share.sharePercent

Which results in the following exception:

    Caused by: org.hibernate.exception.SQLGrammarException: ORA-01791: not a SELECTed expression

If I remove the DISTINCT keyword, the query runs without issue.  If I remove the order by clause, the query runs without issue.  Unfortunately, I can&#39;t seem to get the ordered result set without duplicates.  

Using the DISTINCT keyword causes this error: not a SELECTed expression

I have applied for an internship in a company and as a question they have asked me to create a schema for their company with certain requirements and mail them the *DDL* file. I have installed Oracle database 11g Express edition, but how do I create a new schema in Oracle database 11g? I have searched in the net for a solution but I could not understand what to do. And after creating a schema, which file should I mail them?

How to create a new schema/new user in Oracle Database 11g?

What are the difference between Oracle DATE and TIMESTAMP type?
Both have date and time component?
Also what is corresponding type in Java for these date types?

Content Type	Original Author	Original Content on Stackoverflow
Question	Sunil Chavan	View Question on Stackoverflow
Solution 1 - Sql	Ben	View Answer on Stackoverflow
Solution 2 - Sql	Ravi Sambangi	View Answer on Stackoverflow
Solution 3 - Sql	Suresh	View Answer on Stackoverflow
Solution 4 - Sql	ratm	View Answer on Stackoverflow

SQL to add column and comment in table in single command

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

Solution 3 - Sql

Solution 4 - Sql

Update column value PostgreSQL

string encoding and decoding?

Attributions