http://qs1969.pair.com?node_id=247219


in reply to DBD::Oracle help for portable DB handle code/module!

Wow! A lot of questions. Let's see...

INFORMATIONS THAT I NEED: (Please, reply with this part)

Q1) How I make this 2 REGEX querys, like in MySQL:

select * from foo where(bar REGEX "^[aeiou]+$") ; select * from foo where(bar REGEX "^[^aeiou]+$") ;

A: You don't. Oracle has no concept of REGEX queries. The best you can do is use the LIKE operator, which gives you the _ and % wildcards.

Underscore (_) matches any one character. Percent (%) matches zero or more of any character. You use it like this:

select * from foo where bar like '%xyz%';

Also note, in Oracle, strings must be delimited with single quotes.

Q2) How I LOCK and UNLOCK a table? When I unlock will unlock all the tables (like in MySQL) or just one?

A: Execute:

LOCK TABLE <table> IN <lockmode> MODE [NOWAIT];

IN <lockmode> MODE: lockmode can be ROW SHARE (allows concurrent access but prohibits exclusive locking by other users), ROW EXCLUSIVE (as row share but also prohibits ROW SHARE locking by other users), SHARE (prevents updates), SHARE ROW EXCLUSIVE (prohibits ROW SHARE locking and updates) or EXCLUSIVE (only allows queries to other users). NOWAIT means the command will return immediately whether or not the table can be locked; normally, Oracle blocks until a lock is obtained.

To unlock, you either COMMIT or ROLLBACK.

Q3) What types are suported? Based in MySQL, what is the similar:

VARCHAR(100) = ? VARCHAR(150) = ? TEXT = ? MEDIUMTEXT = ? SMALLINT = ? FLOAT = ? DOUBLE = ? FLOAT(10) = ? FLOAT(10,4) UNSIGNED = ? BOOLEAN = ?

Character types up to 4000 bytes are probably best defined with a VARCHAR2({size}).

Numbers - integer or floating point - are defined using NUMBER({precision}, {scale}) - {precision} = total number of digits from 1 to 38, scale = number of digits after the decimal point from -84 to 127. A negative scale rounds e.g. -2 rounds to the nearest hundred.

A floating point number is defined as NUMBER(38).

There is not a BOOLEAN datatype. You'd have to use a NUMBER(1) I guess.

There is a DATE datatype, and a range of LONG datatypes for large blocks of data.

Q4) How I make an AUTO_INCREMENT column? For example, and column called ID where when a row is inserted in the table, this col will be improved automatically with the next ID. And need to be an PRIMARY KEY.

A: You can't have Oracle do this automatically, you'd have to create a sequence and a trigger and have the trigger update the column in the table from the sequence on insert.

Q5) How to set a columns as PRIMARY KEY?

A: At CREATE TABLE time:

CREATE TABLE <table> (<field> <type> [UNIQUE] [NOT NULL], ... CONSTRAINT <constraint_name> PRIMARY KEY (<field name>));

Q6) Is the LIKE resource enable? It works like that?: select * from foo where(bar LIKE "%text_in_the_midle%") ;

A: The same.

Q7) How to get the list of tables?

A: The easiest way is use DBI! The tables method works with DBD::Oracle.

Q8) How to get columns of a table and the types of them?

A: Again, I would think the table_info method of DBI should work.

Q9) How to get the maximal value of a integer column?:

select max(ID) as ID from foo ;

A: The same.