Since Oracle 9i there are two ways or declaring a directory for use with UTL_FILE. The older way is to set the INIT.ORA parameter UTL_FILE_DIR. We have to restart the database for a change to take affect. The value can like any other PATH variable; it accepts wildcards. Using this approach means passing the directory … Read more
There’s a strong chance that the privileges to select from table1 have been granted to a role, and the role has been granted to you. Privileges granted to a role are not available to PL/SQL written by a user, even if the user has been granted the role. You see this a lot for users … Read more
I know this is an old question, but it hasn’t been correctly addressed, so I’m answering it for others who may run into this problem. By default Oracle’s ODP.net binds variables by position, and treats each position as a new variable. Treating each copy as a different variable and setting it’s value multiple times is … Read more
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN (‘FUNCTION’,’PROCEDURE’,’PACKAGE’) The column STATUS tells you whether the object is VALID or INVALID. If it is invalid, you have to try a recompile, ORACLE can’t tell you if it will work before.
The user and password are DEFINITELY incorrect. Oracle 11g credentials are case sensitive. Try ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE; and alter password. http://oracle-base.com/articles/11g/case-sensitive-passwords-11gr1.php
Go to the windows machine that hosts the Oracle database server Go to Start -> Run -> Services.msc in Windows. Locate OracleService < SID > (here OracleServiceORCL) and click on Start to start the oracle database service (if not already running) Once it is up and running, from the command prompt run the following: tnsping … Read more
You can use VARRAY for a fixed-size array: declare type array_t is varray(3) of varchar2(10); array array_t := array_t(‘Matt’, ‘Joanne’, ‘Robert’); begin for i in 1..array.count loop dbms_output.put_line(array(i)); end loop; end; Or TABLE for an unbounded array: … type array_t is table of varchar2(10); … The word “table” here has nothing to do with database … Read more
You use ROWNUM. ie. SELECT user FROM Dual WHERE ROWNUM = 1 http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm
In Oracle, to insert multiple rows into table t with columns col1, col2 and col3 you can use the following syntax: INSERT ALL INTO t (col1, col2, col3) VALUES (‘val1_1’, ‘val1_2’, ‘val1_3’) INTO t (col1, col2, col3) VALUES (‘val2_1’, ‘val2_2’, ‘val2_3’) INTO t (col1, col2, col3) VALUES (‘val3_1’, ‘val3_2’, ‘val3_3’) . . . SELECT 1 … Read more