Recent content by stefanhei

But it wont work." is not the best error description. What error message do you see? If the parameters P_Start_Date and P_End_Date are of datatype DATE I do not see why the condition you mention should not work, unless the string that variable timestamp1 holds does not match the date format...

Hi, the error number of the most recent error can be found in SQLCODE and the message in SQLERRM. The complete error stack is stored in ora_server_error_msg.

Accessing objects in another schema is pretty straightforward. First you need the SELECT privilege on the table or MV you wish to access - the DBA or the other application team has to grant it. This privilege can be granted either directly to the user or via a database role. If you wish to...

My suggestion was not to use bash, but a mechanism that disables variable substitution in your here-doc. After reading up a ksh manual online I found out that quoting the delimiter turns off parsing with this shell too. So either use SamBones suggestion, which should be the most portable one...

Hi, in your here-document v$tablespace is evaluated as a shell variable. I do not have a box with korn shell at hand, but in bash you can turn of variable subsitution in here-docs by quoting the token that is used as delimiter: #!/bin/bash ... sqlplus -s "/ as sysdba" <<-"EOF" >>gname.csv set...

Hi, Oracle allows "as" to define a table alias but does not allow the SQL server style column alias using the equal sign ( IN_amt = (e.sql * .1) ). Another way to run the query that works on Oracle and SQL Server, but with different syntax is the with clause. This is the Oracle syntax: WITH cte...

The password expiration is managed by profiles. A new database comes with a profile named DEFAULT, which is assigned to all users. In this profile the password life time is set. The quick fix is to change the DEFAULT profile (note that this will affect all database users that have this profile...

Hi, the whole heredoc is evaluated before it is passed to dbaccess, which will be fed with the results of the command substitution of the date command, not the date command itself. Quote the heredoc delimiter to turn of all substitutions and expansions: ... dbaccess -e $DB_NAME <<-"!" >>...

You can specify how to sort: ls -1d java* |sort -nk1.5 |tail -1

Hi, as long as the number is only one digit: ls -1d java* |sort |tail -1

Nice, but it will fail in 13 years. Put a tail between cal and grep to get rid of the year in the header that may cause problems.

Hi, you can define the plus sign as your group sperator and put it in the desired position: select to_char(55566.75,'99990G00D00','NLS_NUMERIC_CHARACTERS= ''.+'' ') from dual

If monitoring is set for a table all DML actions on it are counted and can be found in ALL_TAB_MODIFICATIONS and it does not have anything to do with auditing. Actually I'm not sure if there is a view to find out if a specific table is audited or not.

The '-' does not matter. to_char always right-justifies numbers unless told otherwise. TRIM is easier to understand, but fm000 is better performance-wise (a context switch less per call), so the preferred way depends on your goal. If you give this code to someone who will maintain and modify it...

The fill mode modifier of the to_char function eliminates the space: Select MY_NAME || '-' || TO_CHAR(MY_NUMBER, 'fm000') As MyField