MARISTREAM.COM

oracletips.guru

Pseudo-IF in SQL SELECT

http://www.oracletips.guru/Pseudo_IF_in_SQL_SELECT.htm

Steve Rea's Oracle and SunGardHE Banner Tips, Tricks, and Scripts. Pseudo-IF in SQL SELECT. I had an app that needed to produce a grouping value of 5, 10, 15, or 20, based on date ranges. For the following date ranges, I needed that field to be as shown:. 20 for earlier dates. Decode(sign(trunc(hire date)-to date('30-JUN-88') ,1,5,. Decode(sign(trunc(hire date)-to date('30-JUN-83') ,1,10,. Decode(sign(trunc(hire date)-to date('30-JUN-78') ,1,15,20) ). You Are Visitor Number. Visit our other web sites:.

oracletips.guru

Defines Using Select

http://www.oracletips.guru/Defines_Using_Select.htm

Steve Rea's Oracle and SunGardHE Banner Tips, Tricks, and Scripts. Instead of creating a one-line SQL script containing a define statement to set variables at runtime, you can use the new value option of the column command to set the variable's value generated by a select statement. The snippet below is taken from my Y2K Date Input. Select 'define fieldval = ' to char(decode(sign(9-length('&fieldval') ,-1,. To date('&fieldval','DD-MON-YYYY'),to date('&fieldval','DD-MON-RR') ,. DD-MON-YYYY') from dual;.

oracletips.guru

Adding Line Feeds To Select Statement Output

http://www.oracletips.guru/Adding_Line_Feeds_To_Select_Statement_Output.htm

Steve Rea's Oracle and SunGardHE Banner Tips, Tricks, and Scripts. Adding Line Feeds To Select Statement Output. To put line feeds in the results from a select statement, so that you can produce several lines of text split where you want them, use chr(10) where you want the line split to occur, such as:. Select 'first line contents' chr(10) 'second line contents' from dual;. If you need an exact number of characters per line, padded on the right with blanks, use rpad:. You Are Visitor Number. Of your dat...

oracletips.guru

Huge Strings Using LOB's

http://www.oracletips.guru/Huge_Strings_Using_LOBs.htm

Steve Rea's Oracle and SunGardHE Banner Tips, Tricks, and Scripts. Huge Strings Using LOB's. The VARCHAR2 type in PL/SQL is limited to 32K in length. What do you do if you need to work with strings longer than 32K? Below shows how to create a Temporary CLOB and concatenate strings to it resulting in a string longer than 32K (the result is a 96000 byte string). You can do a Describe on DBMS LOB (desc dbms lob) in sqlplus to see what other functions are available to you to work with LOB's. A resource for f...

oracletips.guru

Handling Y2K Date Input With Pseudo-IF

http://www.oracletips.guru/Handling_Y2K_Date_Input_With_Pseudo_IF.htm

Steve Rea's Oracle and SunGardHE Banner Tips, Tricks, and Scripts. Handling Y2K Date Input With Pseudo-IF. Set showmode off echo off. Set heading off pagesize 0 timing off feedback off termout on. Accept fieldval char prompt 'Enter Date (dd-MON-yy or dd-MON-yyyy): '. Set termout off verify off. Select 'define fieldval = ' to char(decode(sign(9-length('&fieldval') ,-1,. To date('&fieldval','DD-MON-YYYY'),to date('&fieldval','DD-MON-RR') ,. DD-MON-YYYY') from dual;. Rem your other custom code goes here.

oracletips.guru

Accessing Two Databases in SQL*Plus

http://www.oracletips.guru/Accessing_Two_Databases_in_SQL_Plus.htm

Steve Rea's Oracle and SunGardHE Banner Tips, Tricks, and Scripts. Accessing Two Databases in SQL*Plus. To create the database link in sqlplus, such as for pprd db (the database connect string name you use to log into Banner, which is defined in tnsnames.ora), with a name of pprd for user ID myuserid with a password of mypswd, enter the following command:. Create database link pprd connect to myuserid identified by mypswd using 'pprd db';. Drop database link pprd;. You Are Visitor Number. CAKID - See A K...

oracletips.guru

Recreating Data Guard Standby Database

http://www.oracletips.guru/Recreating_Data_Guard_Standby_Database.htm

Steve Rea's Oracle and SunGardHE Banner Tips, Tricks, and Scripts. Recreating Data Guard Standby Database. Here, we will recreate the Data Guard standby database on the standby server. (The standby had gotten out of sync when one of the archive logs on the primary was deleted, and the standby stopped applying any changes later than that missing archive log file.). THESE STEPS ARE DONE ON THE PRIMARY SERVER:. Rmanbackup.shl PROD full. Get the list of backup files from today to FTP to the standby server:.

oracletips.guru

User ID/Database Name Prompt in SQL*Plus

http://www.oracletips.guru/User_ID_Database_Name_Prompt_in_SQL_Plus.htm

Steve Rea's Oracle and SunGardHE Banner Tips, Tricks, and Scripts. User ID/Database Name Prompt in SQL*Plus. Column new prompt new value new prompt. Select username ': ' substr(global name,1,instr(global name '.','.')-1). New prompt from user users, global name;. Set sqlprompt &new prompt. On Oracle8, some of our users were getting a buffer overflow on the above code, so, you might want to try the following instead:. Column new prompt new value new prompt. Set sqlprompt &new prompt. You Are Visitor Number.

oracletips.guru

Accessing Remote Databases

http://www.oracletips.guru/Accessing_Remote_Databases.htm

Steve Rea's Oracle and SunGardHE Banner Tips, Tricks, and Scripts. If you just want to access a database on a different machine than sqlplus is running on (instead of having to append the link name to each SQL statement), you can create an sqlplus session directly to that other machine's database by appending the database connect string to the optional user ID/password parameters to sqlplus (along with setting up tnsnames.ora), such as if your test db is on another machine:. Export TWO TASK=test db.