I actively maintain virtual test instances for my writing and teaching on Windows 7, Red Hat Enterprise Linux, Fedora, and Solaris. Perl on Windows is always interesting. I use ActivePerl on Windows 7 rather than Strawberry Perl, and it was interesting to see this note after I upgraded to the most current Community Edition of ActivePerl (5.16.3).
I thought it might be kind to post ActiveState’s Release Note because you should really read it before you try to install ActivePerl on Windows 7/8. Just make sure you’ve removed any earlier version of ActivePerl before trying the install. That’s what I did, and surprise, there weren’t any problems.
While the installation doesn’t tell you that you need to restart the Windows 7 operating system, you do. If you don’t restart Windows 7 after the ActivePerl install, you’ll get the following error message:
install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC
You can grab the DBD::MySQL bundle with this command:
perl -MCPAN -e install Bundle::DBD::mysql
It downloads the MinGW (Minimalist GNU for Windows) C/C++ compiler, but it’ll be nested and not conflict with one if you have installed it independently. Absence of the compiler means you can’t attempt to build the library.
Proceeding further, I discovered a number of incompatibilities. The basic commands require you to load CPAN (Comprehensive Perl Archive Network) shell:
perl -MCPAN -e shell
Inside the Perl
cpan> force install DBD:mysql --mysql_config C:\ProgramData\MySQL\MySQL Server 5.6
You also need to create a MySQL anonymous user in the database to support the default user during compilation, and if you dropped the test database you need to recreate it. I also ended up creating an ODBC user. Here are the commands to run as the root superuser:
CREATE USER 'ODBC'@'localhost'; CREATE USER ''@'localhost' IDENTIFIED BY 's3krit'; GRANT ALL ON test.* TO ''@'localhost';
I also took the time to update the MySQL
test.pl to Perl 5.16; unfortunately, at the end of the day I didn’t get it working. Maybe I’ll try back in a few weeks. You need to change line 57 from port 5050 to port 3306, and “
SharedSecret” to “
s3krit“, as shown:
Here’s are the edits required
test.pl program on lines 189 and 194:
The only release note provided is below, which I found inadequate. If you find the trick before me, please post it.
fork()emulation has known limitations. See perlfork for a detailed summary. In particular,
fork()emulation will not work correctly with extensions that are either not thread-safe, or maintain internal state that cannot be cloned in the pseudo-child process. This caveat currently applies to extensions such as Tk and Storable.
- It seems that some people are having problems with the ActivePerl MSI installer. The first thing to note is that you CANNOT install ActivePerl 5.16 over an older version of ActivePerl based on 5.14, 5.12, 5.10, 5.8, 5.6 or 5.005, such as build 1204 or any other 1200, 1000, 800, 600 or 500 series build. We have determined that some of these problems are due to other installations of Perl that may be pointed at by something in the environment.
The sure-fire solution is to make absolutely certain that no other installations of Perl are on the target machine. Realizing that this is not always possible, you can follow these steps to ensure the other installations will not interfere.
- Stop the “Windows Installer” service. This can be accomplished from the command prompt using the following command:
C:\> net stop "Windows Installer"
- Temporarily remove or rename
PERL5LIBenvironment variables in the system environment.
- Temporarily remove or rename the following registry values:
[\\HKEY_LOCAL_MACHINE\Software\Perl] lib = <directory> (REG_SV) [\\HKEY_LOCAL_MACHINE\Software\Perl] sitelib = <directory> (REG_SV) [\\HKEY_LOCAL_MACHINE\Software\Perl] lib-<PerlVersion> = <directory> (REG_SV) [\\HKEY_LOCAL_MACHINE\Software\Perl] sitelib-<PerlVersion> = <directory>(REG_SV)
- Proceed with the installation.
Once the installation has completed successfully, the above actions may be undone although restoring the environment variables or the registry values may interfere with the proper operation of your new ActivePerl installation. In order to perform all of the above steps, you will need to have Administrative privileges on the target machine. If you do not have the required privileges you should contact your Administrator.
- Stop the “Windows Installer” service. This can be accomplished from the command prompt using the following command:
Somebody asked why you can’t implement MySQL triggers that write information when you want to stop the DML statement, like autonomous procedures in Oracle. The question was a surprise but I didn’t find anything on it, so here’s how you can do it. This is more or less like an autonomous process by leveraging both the InnoDB and MyISAM engine’s behaviors. This post leverages an earlier explanation of MySQL Triggers.
- First you create a MyISAM table, which is a persistent store that auto commits when you’re other InnoDB tables can be transactionally dependent. Here’s a simple MyISAM
CREATE TABLE logger ( logger_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY , logger_event VARCHAR(50) , logger_table VARCHAR(50) , logger_instring VARCHAR(100) , logger_outstring VARCHAR(100) , created_by INT UNSIGNED , creation_date DATE , last_updated_by INT UNSIGNED , last_update_date DATE) ENGINE=MyISAM;
- Next, you create an on-insert trigger that changes an input but doesn’t stop the transaction. It also writes to the logger MyISAM table in the scope of the transaction.
CREATE TRIGGER contact_insert BEFORE INSERT ON contact FOR EACH ROW BEGIN /* Check if last name contains a white space. */ IF new.last_name REGEXP '^.* .*$' THEN /* Insert into an MyISAM table, which auto commits in the scope of a transaction. */ INSERT INTO logger VALUES ( null ,'insert' ,'contact' , new.last_name , REPLACE(new.last_name,' ','-') , new.created_by , new.creation_date , new.last_updated_by , new.last_update_date ); /* Replace the name for the INSERT INTO the CONTACT table. */ SET new.last_name := REPLACE(new.last_name,' ','-'); END IF; END; $$
- Next, you create an on-update trigger that changes an update while aborting the transaction. It also writes to the logger MyISAM table because its outside the InnoDB scope of a transaction and auto committed on insert.
CREATE TRIGGER contact_update BEFORE UPDATE ON contact FOR EACH ROW BEGIN /* Check if last name contains a white space. */ IF new.last_name REGEXP '^.* .*$' THEN /* Insert into an MyISAM table, which auto commits in the scope of a transaction. */ INSERT INTO logger VALUES ( null ,'update' ,'contact' , new.last_name , null , old.created_by , old.creation_date , new.last_updated_by , new.last_update_date ); /* Throw an exception to force the business user to see they can't update a last name with a white space. */ SIGNAL SQLSTATE '42000'; END IF; END; $$
- Next, you create a test case with an
UPDATEstatement that meets the condition of the triggers.
/* Insert a row meeting the trigger condition. */ INSERT INTO contact VALUES ( null, 1001, 1003,'Catherine', null,'Zeta Jones', 1001, UTC_DATE(), 1001, UTC_DATE()); /* Update a row meeting the trigger condition. */ UPDATE contact SET last_name = 'Zeta Jones' , last_updated_by = 1003 , last_update_date = UTC_DATE() WHERE last_name = 'Zeta-Jones';
- Last, query the logger table. You have a record inserted for both the allowed behavior and the aborted behavior. This means you have the ability to capture material that should never be inserted or updated into a table and who did it by leveraging the who-audit columns of the table.
SELECT * FROM logger;
+-----------+--------------+--------------+-----------------+------------------+------------+---------------+-----------------+------------------+ | logger_id | logger_event | logger_table | logger_instring | logger_outstring | created_by | creation_date | last_updated_by | last_update_date | +-----------+--------------+--------------+-----------------+------------------+------------+---------------+-----------------+------------------+ | 1 | insert | contact | Zeta Jones | Zeta-Jones | 1001 | 2013-04-26 | 1001 | 2013-04-26 | | 2 | update | contact | Zeta Jones | NULL | 1001 | 2013-04-26 | 1003 | 2013-04-26 | +-----------+--------------+--------------+-----------------+------------------+------------+---------------+-----------------+------------------+ 2 rows in set (0.00 sec)
This effectively delivers in MySQL the equivalent of an autonomous transaction in Oracle. The result from the non-critical trigger records the before and after value, while the results from the critical update trigger only record the before values because the event is aborted by raising an error in the trigger. As always, I hope this helps somebody looking for a solution.
My install instructions on the web site were old, somebody wanted me to publish another set of screen capture for the MySQL 5.6 install and configuration. This is it for Windows 7 using the downloadable MSI file.
The installation from MySQL’s perspective is actually the installation and configuration of MySQL. For your convenience and reference, I’ve already installed the pre-requisites for MySQL. They’re:
- Visual Studio Tools for Office 20120 Runtime
- Microsoft .NET Framework 4 Client Profile
- Microsoft Excel 2007 or greater
- Microsoft .NET Framework 4 Client Profile
- Microsoft Visual C++ 2010 32-bit runtime
- Microsoft .NET Framework 4 Client Profile
Below are the installation steps after you download the current release
.msi file. The icon should look like the one to the right. For this example, I”m using the
mysql-installer-community-220.127.116.11.msi. Double-click the icon on your desktop or from your
C:\Users\username\Downloads folder. While working through the steps, you can launch any of the small images to the left if you’d like to see what your screen should look like (generally with a right click to open in a new window).
- The first screen is a Windows 7 dialog box. Click the Run button to install launch the MySQL 5.6 Installer.
- The second screen is a Windows 7 dialog box. It advises you that the MySQL Installer is working and lets you cancel that operation. Don’t click the Cancel button unless you want to stop the MySQL 5.6 Installer.
- The third screen is a MySQL Installer message box. It closes when ready to proceed. Ignore it, unless it’s there for more than a couple minutes. If that occurs you’ve got something wrong with your Windows installation or a very slow computer. If the former, kill the installation process; if the latter, wait patiently.
- The fourth screen in the process is the first MySQL Installer screen. Here you choose what you want to do. You can install MySQL products, inquire about MySQL, or check physical resource components. Provided you installed the prerequisites listed above, you should be prepared to install MySQL Products. Click the Install MySQL Products link to proceed or one of the others to explore.
- The second MySQL Installer screen is the license form. You must check the I accept the license terms checkbox to enable the Next button. Once the Next button is enabled, click it to proceed.
- The third MySQL Installer screen connects to the Internet and finds the latest product update. You can check the Skip the check for updates (not recommended) checkbox to skip this but for the example we’ll check anyway. Click the Execute button to proceed.
- The fourth MySQL Installer screen connects to the Internet and finds the latest product update. You can check the Skip the check for updates (not recommended) checkbox to skip this but for the example we’ll check anyway. Click the Execute button to proceed.
- The fifth MySQL Installer screen acknowledges the latest update is what you’re installing. Click the Next button to proceed.
The sixth MySQL Installer screen gives you five choices for the installation, which are listed below. You most likely want to install the Developer Default, so click the Developer Default radio button and then, click the Next button to proceed.
- MySQL Server
- Both the client and server software for the MySQL Server
- MySQL Workbench
- The GUI application to develop for and manage the server.
- MySQL Visual Studio Plugin
- To work with the MySQL Server from VS.
- MySQL Connectors
- Connector/Net, Java, C/C++, OBDC and others.
- Examples and tutorials
- To help you get started with your development.
- Allows you to read the documentation offline.
- The seventh MySQL Installer screen performs a system check for the pre-requisites, which I listed before the installation. Assuming you installed them, you should see a screen that confirms your system configuration is ready for installation. Click the Next button to proceed.
The eighth MySQL Installer screen performs displays the products that it’ll install, which are listed below and available in the full image to the left. Click the Execute button to install the products.
- MySQL Server 5.6.11
- MySQL Workbench CE 5.2.47
- MySQL Notifier 1.0.3
- MySQL for Excel 1.1.1
- Connector/ODBC 5.2.4
- Connector/C++ 1.1.2
- Connector/C++ 1.1.2
- Connector/J 5.1.24
- Connector/NET 6.6.5
- MySQL Documentation 5.6.11
- Samples and Examples 5.6.11
- The nineth MySQL Installer screen shows the installation by product and it can take a couple minutes. The screen to the left displays progress more than halfway complete. You don’t need to do anything in this step until all products are complete.
- The tenth MySQL Installer screen shows the completed installation. Everything should install successfully, as shown in the image. Click the Next button to proceed.
This concludes the installation of the MySQL products. The next section shows you how to configure MySQL.
You have two basic options, the simple one and the advanced one. These steps will show you how to perform an advanced configuration. I’ve opted to maintain the step numbering from the beginning of the installation. Here are the steps:
- The eleventh MySQL Installer screen is the first MySQL Configuration screen. You can click the Show Details button or begin the configuration. Click the Next button to proceed.
- The second MySQL Configuration screen sets the server configuration type, enables TCP/IP networking (as opposed to a socket model), and lets you enable Advanced Configuration. For this installation, we enable the Show Advanced Options checkbox before you click the Next button.
The third MySQL Configuration screen sets the password and lets you create MySQL User Accounts. It’s much easier to let the install proceed and use MySQL Workbench to create databases, users, and roles; plus grant permissions through the GUI environment. Enter the
rootpassword twice, a trivial and unsecure password
cangetinis what I recommend to my students who won’t have any meaningful information in the database. Make sure you can remember the password you enter. Clearly, a better password is required for real environments. After entering the password twice, click the Next button to proceed.
The fourth MySQL Configuration screen sets the Windows Service Name, and you should probably make sure there isn’t another MySQL56 service on the machine before you proceed. You have the choice of running the Windows Service using the Standard System Account or a Customer User account. Unless you’re an expert at Windows 7 administration, you should probably choose the Standard System Account as the one running the Windows 7 service. Click the Next button.
The fifth MySQL Configuration screen sets the logging options. You only need Show Query Log typically, but the Error Log is helpful. Make the choices and click the Next button.
- The sixth MySQL Configuration screen explains the next step. It installs the sample files and example databases. You can see what you’ve installed when you click the Show Details button, which is what I did to get the image at the left. The default choice installs the samples and example databases, which can’t hurt. You’ll need the test database if you install DBD::mysql for Perl. If you don’t want them, you can drop them from the database.
The seventh MySQL Configuration screen explains you’ve completed configuring the MySQL Server. Click the Next button to proceed.
The eighth MySQL Configuration screen explains you’ve finished everything. You can copy the log file to clipboard, which allows you to see everything that was done. Click the Finish button to complete the installation and configuration.
Just one caveat (that’s a warning), this installation doesn’t put the MySQL executable into your System
%PATH% variable. You’ll need to do that, and if I get a chance I’ll put a post together for that. I know one or two of my students may need it later.
I hope this helps those you are using the new installer for the first time. It’s a superior tool to the old one, which was also a good tool.
Somebody asked me why they can’t query the
DBMS_TYPES.TYPECODE_OBJECT value because they get an
ORA-06553 error. Their query attempt is:
SELECT dbms_types.typecode_object FROM dual;
Naturally, it raises the following exception:
SELECT dbms_types.typecode_object * ERROR at line 1: ORA-06553: PLS-221: 'TYPECODE_OBJECT' IS NOT a PROCEDURE OR IS undefined
The explanation is very simple. It’s a package scoped variable and in Oracle 11g only accessible in a PL/SQL block. Here’s an anonymous block that would print the value to the console:
BEGIN dbms_output.put_line(dbms_types.typecode_object); END; /
Hope that helps those trying to discover what a package variable’s value is.
While I tried to deflect how you perform SQL Injection attacks against a MySQL procedure, my students requested that I post examples of what to do to avoid SQL injection, and what not to do to invite attacks. The best practice to avoid SQL injection attacks is too always bind inputs to data types, and avoid providing completely dynamic
Here’s the correct way to dynamically generate a result from a MySQL Stored Procedure:
CREATE PROCEDURE hello (IN pv_input VARCHAR(50)) BEGIN SELECT sample_id , sample_name FROM sample WHERE sample_name = pv_input; END; $$
A call to this
hello procedure will only return the row or rows where the
pv_input value matches the
sample_name column value. Any attempt to exploit it like the one below fails.
CALL hello('\'Harriet\' OR 1 = 1');
It fails because there’s no matching
'Harriet' OR 1 = 1 in the table’s
sample_name column. However, it works well when we submit
'Harriet' by herself, without the intended SQL inject phrase “
OR 1 = 1“, as you can see:
+-----------+-------------+ | sample_id | sample_name | +-----------+-------------+ | 2 | Harriet | +-----------+-------------+ 1 row in set (0.00 sec)
There are two approaches that you should never put in your code because they can be readily exploited unless you carefully parse the incoming string parameter. The problem in both cases is causes by concatenation rather than binding. The first example is extremely unlikely as an error but possible.
CREATE PROCEDURE hello (IN pv_input VARCHAR(50)) BEGIN SET @sql := CONCAT('SELECT sample_id, sample_name FROM sample WHERE sample_name = ',pv_input); PREPARE stmt FROM @sql; EXECUTE stmt; END; $$
hello procedure using concatenation and a prepared statement is vulnerable to SQL injection. A call like the following would return all two rows in the
CALL hello('\'Juliette\' OR 1 = 1');
It would display:
+-----------+-------------+ | sample_id | sample_name | +-----------+-------------+ | 1 | Hank | | 2 | Harriet | +-----------+-------------+ 2 rows in set (0.00 sec)
While there’s no
sample_name value of
'Juliette', the “
OR 1 = 1” is true. Therefore, the
SELECT statement filters out nothing and returns all the data from the table. It’s probably clear you’d never do this type of prepared statement inside a stored procedure, but most SQL Injection attacks exploit your scripting language implementation. Unfortunately, bad coding practices can infrequently expose this type of vulnerability; and they typically occur when a junior programmers is following a bad coding example.
A solution with the
WHERE clause as part of the parameter would look like this:
CREATE PROCEDURE hello (IN pv_where VARCHAR(50)) BEGIN SELECT sample_id , sample_name FROM sample pv_where; END; $$
The modified call:
CALL hello('WHERE sample_name = \'Juliette\' OR 1 = 1');
returns all rows from the table.
A solution without the
WHERE clause as part of the parameter would look like the following but it fails during runtime and returns no rows [Updated in response to comment]. The failure has nothing to do with the comment’s dialog on the
CONCAT function, which also added nothing to the example once I tested it.
CREATE PROCEDURE hello (IN pv_where VARCHAR(50)) BEGIN SELECT sample_id , sample_name FROM sample WHERE pv_where; END; $$
It’s simply works only when you provide a “1 = 1″ or other comparison without embedded apostrophes (‘) but fails with embedded apostrophes. That means the following statement fails:
CALL hello('sample_name = \'Juliette\' OR 1 = 1');
but this SQL injection statement works:
CALL hello('1 = 1');
returns all rows from the table.
This example, when you omit the white space also works with embedded strings or numeric operands and an operator:
CREATE PROCEDURE hello (IN pv_where VARCHAR(50)) BEGIN SELECT sample_id , sample_name FROM sample WHEREpv_where; END; $$
It returns all rows with a call like this:
CALL hello('sample_name = \'Juliette\' OR 1 = 1');
My take initially was that it might be a bug, and I logged one (Bug 68903). That’s was a dumb thing to do because
WHEREpv_where simply becomes a table alias in the query.
In conclusion, the first example is a good practice. The other two should never exist! Well, they shouldn’t exist unless you’re parsing the web form inputs vigilantly.
Hope this helps those trying to understand how to avoid SQL injection attacks. Always try to solve dynamic SQL statement problems by binding variables into statements.
Sometimes interesting problems lead to shock or dismay at the suppositions of why they occur. Why an
ORA-22979 is raised is one of those, and the error is typically:
ERROR at line 1: ORA-22979: cannot INSERT object VIEW REF OR user-defined REF
This error occurs on an
INSERT statement if you follow the example from the Oracle 11gR2 Object-Relational Developer’s Guide, which also has various slightly modified examples in a couple PL/SQL books. It also happens on an
UPDATE statement to populate
The conflict is typically between the uniqueness of the reference and an attempt to make a non-reference column of the object type a primary key constrained column and embedded object view. The source of the conflict is the
OBJECT IDENTIFIER IS PRIMARY KEY associated with a primary key in the Oracle documentation. The two goals are mutually exclusive; only the reference or non-reference column can be the object identifier. Unfortunately, Oracle documentation contains both examples in different places without making any effective cross reference.
If you want to make a column of an object type a primary key for an object table (that is a table that uses an object type to define its structure) and the object view (the content of the embedded object type), you can’t include the
OBJECT IDENTIFIER IS PRIMARY KEY clause when you want to populate the
REF column of the object type. Here’s an example that uses a column of the object type as a primary key and leaves the
REF column empty:
-- Create the BASE_T type, or specification for IDL. CREATE OR REPLACE TYPE base_t IS OBJECT ( obj_id NUMBER , obj_name VARCHAR2(30) , obj_ref REF base_t) NOT FINAL; /
You can then create a table like the following:
CREATE TABLE base OF base_t ( obj_id CONSTRAINT base_pk PRIMARY KEY ) OBJECT IDENTIFIER IS PRIMARY KEY;
Let’s insert some rows to test for ourselves that this fails when you try to assign references:
INSERT INTO base VALUES (base_t(1, 'Dwalin',NULL)); INSERT INTO base VALUES (base_t(2, 'Borfur',NULL)); INSERT INTO base VALUES (base_t(3, 'Gloin',NULL)); INSERT INTO base VALUES (base_t(4, 'Kili',NULL)); INSERT INTO base VALUES (base_t(5, 'Fili',NULL));
UPDATE statement attempts to assign references, but fails as shown below:
UPDATE base b SET obj_ref = REF(b);
UPDATE fails as shown:
UPDATE base b * ERROR at line 1: ORA-22979: cannot INSERT object VIEW REF OR user-defined REF
The simple fix redefines the object table by removing the
OBJ_ID column as an object identifier and primary key value. You do that by removing the
OBJECT IDENTIFIER IS PRIMARY KEY clause because the column of the object type can be a primary key for the table without being an object view identifier. After you make the change, you can successfully update the table with object references. Object identifiers or references are unique and serve the same purpose of a primary key for the object view, and at the same time they can’t both exist.
CREATE TABLE base OF base_t
Inserting the same rows, you can now update the table to provide valid object references. Let’s experiment with how they work because that’s also not as clear as I’d like in the Oracle documentation.
The next statement creates a
CHILD table that holds a reference to the
BASE (or parent) table and another instance of the same
BASE_T object type:
CREATE TABLE child ( child_id NUMBER CONSTRAINT child_pk PRIMARY KEY , base_ref REF base_t SCOPE IS base , child base_t);
INSERT statement can’t use a
VALUES clause because we MUST capture the reference (or in this case primary key) from the
BASE (or parent) table. An
INSERT statement with a query does the trick:
INSERT INTO child SELECT 1, obj_ref, base_t(1, 'Gimli',NULL) FROM base b WHERE b.obj_name = 'Gloin';
You should note that the reference for the
CHILD column isn’t set but is likewise not required for the example to work.
Now, lets perform an standard
INNER JOIN (equijoin) between the two tables by using the references as primary and foreign keys. Please note the trick is referring to the table and column of the
BASE (or parent) table and the table, column, and embedded
OBJ_REF of the CHILD table.
COLUMN father FORMAT A10 COLUMN son FORMAT A10 SELECT b.obj_name AS "Father" , c.child.obj_name AS "Son" FROM base b INNER JOIN child c ON b.obj_ref = c.base_ref.obj_ref;
You get the following results:
Father Son ---------- ---------- Gloin Gimli
You can make a view of this table with either of these syntaxes:
CREATE OR REPLACE VIEW base_v OF base_t WITH OBJECT OID DEFAULT AS SELECT * FROM base;
CREATE OR REPLACE VIEW base_v OF base_t WITH OBJECT OID (obj_id) AS SELECT * FROM base;
Hope it helps anybody trying it. Personally, I think it’s better to use collections of object types, but that’s much bigger discussion that I’ll save for the Oracle Database 12c PL/SQL Programming book that I’m writing.
Somebody was trying to create a striped view based on a table’s
end_date temporal columns. They asked for some help, so here are the steps.
Basically, you create a user-defined data type, or structure:
1 2 3 4
CREATE OR REPLACE TYPE item_structure IS OBJECT ( id NUMBER , lookup VARCHAR2(30)); /
Then, you create a list (an Oracle table) of the structure, like:
CREATE OR REPLACE TYPE item_lookup IS TABLE OF item_structure; /
Lastly, you create an object table function, like:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
CREATE OR REPLACE FUNCTION get_item_types RETURN item_lookup IS -- Declare a variable that uses the record structure. lv_counter PLS_INTEGER := 1; -- Declare a variable that uses the record structure. lv_lookup_table ITEM_LOOKUP := item_lookup(); -- Declare static cursor structure. CURSOR c IS SELECT cl.common_lookup_id AS lookup_id , SUBSTR(cl.common_lookup_meaning,1,60) AS lookup_meaning FROM common_lookup cl WHERE cl.common_lookup_table = 'ITEM' AND cl.common_lookup_column = 'ITEM_TYPE' AND SYSDATE BETWEEN cl.start_date AND NVL(cl.end_date,TRUNC(SYSDATE) + 1) ORDER BY cl.common_lookup_meaning; BEGIN FOR i IN c LOOP lv_lookup_table.EXTEND; /* The assignment pattern for a SQL collection is incompatible with the cursor return type, and you must construct an instance of the object type before assigning it to a collection. */ lv_lookup_table(lv_counter) := item_structure( i.lookup_id , i.lookup_meaning ); lv_counter := lv_counter + 1; END LOOP; /* Call an autonomous function or procedure here! It would allow you to capture who queried what and when; and acts like a pseudo trigger for queries. */ RETURN lv_lookup_table; END; /
Now you can embed the object table function in a view, like this:
1 2 3
CREATE OR REPLACE VIEW item_lookup_view AS SELECT * FROM TABLE(get_item_types);
Why not simply use an embedded query in the view, like the following?
SQL> CREATE OR REPLACE VIEW normal_view AS 2 SELECT cl.common_lookup_id AS lookup_id 3 , SUBSTR(cl.common_lookup_meaning,1,60) AS lookup_meaning 4 FROM common_lookup cl 5 WHERE cl.common_lookup_table = 'ITEM' 6 AND cl.common_lookup_column = 'ITEM_TYPE' 7 AND SYSDATE BETWEEN cl.start_date AND NVL(cl.end_date,TRUNC(SYSDATE) + 1) 8 ORDER BY cl.common_lookup_meaning;
My guess is that it was too easy but who knows, maybe they found a real need. The only need that I see occurs when you’re enforcing something like HIPPA and you want to capture unauthorized queries along with who performed them.
Naturally, I hope this helps those looking to resolve syntax errors when they have a need to do the more complex solution.
It’s funny but Oracle doesn’t want you to enter a trivial password, and about every week I get asked what the standards are for Oracle Database passwords. That’s funny too because it’s in the documentation, the one most experienced and new users never read – Oracle Database Installation Guide (available by platform, the link is for the Windows platform).
Anyway, let me quote the rules:
Oracle recommends that the password you specify:
- Contains at least one lowercase letter.
- Contains at least one uppercase letter.
- Contains at least one digit.
- Is at least 8 characters in length.
- Uses the database character set which can include the underscore (_), dollar ($), and pound sign (#) character.
- If (the password) contains special characters, including beginning the password with a number or symbol, then enclose the password with double-quotation marks.
- Should not be an actual word.
Likewise, you can’t use the old educational passwords:
SYSaccount password cannot be
SYSTEMaccount password cannot be
SYSMANaccount password cannot be
DBSNMPaccount password cannot be
- If you choose to use the same password for all the accounts, then that password cannot be
Hope this helps, and by the way reading the documentation never hurts too much!
I’m off to speak at the Utah Oracle User’s Group Training Days 2013 tomorrow and Thursday. I’m presenting on Oracle Database Triggers and on techniques leveraging PHP and MySQL Striped Views. If you’re not there, you can check this older (but recently updated) post on PHP and MySQL Striped Views.
Hope to see a few folks who use the blog. BTW, I won’t be at Collaborate 2013 this year. Travel budgets are always tight, and this year they’re more than tight.
My take on the good, the bad, and the ugly of our Winter season, beside all the snow I’ve shoveled and blown to get in and out of my driveway, is:
February brought us MySQL 5.6 GA (General Availability) and Ruby 2.0.0-p0. Mega thanks to the MySQL and Ruby teams! By the way, if you’ve always wanted to know a little about Ruby without a great deal of effort, you should check this awesome little 20 minute Ruby tutorial. Naturally, the best Ruby Programming book is a bit dated, and maybe there will be a new one later in the year. If anybody knows whether there’s a new edition forthcoming, I’d appreciate hearing about it! Another more current, Ruby 1.9, book is Programming Ruby 1.9: The Pragmatic Programmers’ Guide.
It seems like a lot of folks who implemented Microsoft SharePoint are rapidly preparing to migrate from SharePoint 2010 to 2013, but what’s unclear is whether they’re upgrading for the features or bug fixes. It does seem many are opting for the Microsoft cloud services, and rumor has it that’s because a little collaboration requires a lot of hardware in the data center. It’s probably a good idea to do some research before implementing Microsoft SharePoint.
If NBC got the quote right today, President Obama disappointed me by saying, “… I should somehow do a Jedi mind meld with these (republican) folks …” How can the President not know that Star Trek Vulcan’s aren’t in the Star Wars universe? Worse yet, my disappointment with Peter Jackson returns because he is releasing the theatrical version The Hobbit: An Unexpected Journey Bluray and DVD on March 19th and then an extended version later in the year. Ouch!!! He’s getting into my wallet again.