pdf split and merge mac downloadnetop school 6 12 free downloadsound forge 8 crack free downloadpagemaker 7 o free download
September 12, 2002 - 11:31 am UTC
Hi, Tom, I want to write the file into client machine by TEXTIO. You told us you can change from UTLFILE to TEXTIO? And I make the package owasylk inside the program unit within form, and as well change every one of the UTLFILE to TEXTIO type. But when I compiled it, there's an error:, native - - error. How can I solve that? Thanks
Followup September 12, 2002 - 3:44 pm UTC
in forms, you almost certainly want to work with EXECSQL, you may use DBMSSQL nevertheless, you cannot in most releases of forms reference a packaged variable that way. Just use the phone number - - CONSTANTS - - v6 constant integer: 0; native constant integer: 1; v7 constant integer: 2; - -
September 12, 2002 - 12:12 pm UTC
Hi, Tom I solved this dilemma on the client side. So, please ignore that question. Thanks and also a nice day.
September 12, 2002 - 12:26 pm UTC
Hi, Tom, Thanks for you excellent works. My question for you is: we have got data write in to the excel file, but a great in general cell format. What i wish to accomplish is generate your data in TEXT format. In you printrow codes, could we implement that? Could you shed a lightweight on this lease? Thanks,
Followup September 12, 2002 - 4:11 pm UTC
Youll should dig up the SYLK file format for the web - - google it, this can be a documented standard.
September 15, 2002 - 11:43 pm UTC
Hi, Tom, Appreciate for your solution. There are basically 3 sorts of data inside database: VARCHAR2, NUMBER and Date. I want to dynamically write them into excel datasheet. So I must dynamically have the data style of each column after which wirte them into your columnrow based for the derived data type. Could you shed some light with this topic please? Also to the Number, how do I be aware of it is just NUMBER or NUMBER6, 2? Are there any difference to procedure that? At last, we desire to use JAVA implement precisely the same function write data into excel, by way of example, SQLJ or JDBC. Could you compare your solution and JAVA solution inside aspect of performance and straightforward of use and also security please? Thanks a million
Followup September 16, 2002 - 7:17 am UTC
No you dont. A number may be fetched in to a varchar2. A date might be fetched in to a varchar2. When you print - - they are going to all be converted into varchar2 regardless. Hence, I just fetch them into varchar2. This utility handles dates/numbers along with. A comparision with this to java o this can be written, the java the first is not o it is easy to code, performant, and can never throw a Null pointer exception o you must setup utlfiledir to produce this plsql version operate in releases before 9iR2, in 9iR2 you may use a directory object instead. You need to use permission to produce this work with java - - more or less exactly the same. Java is not going to make this any benefit, count on me on that you. Why do you care what language something is printed in.
September 16, 2002 - 10:57 am UTC
Hi, Tom, Please bear when camping, Im kinda insist my estimation. Because what I might like to do is pre-format each column when write those to the excelsheet. The person who receives the excelsheet probably doesnt have in mind the datatype for every columnbeacuse it is usually GENERAL format when he/she opens the columncell format property. So with this pre-format, each column will be inside the right format if the guy open the columncell format and know very well what the original format in the data. That means discussing only write data into excel and also prepare the fileformat column for your person with no/little DB knowledge. We are aware of we could do this by utilizing PRO/C or JAVA because we can easily write them into excel by correct format- %s, %i, %d and it also is from the right format and able to be checked by opening the format property inside the excelsheet. I remember there are a few lines of codes which handle finding datatypes on the outcome at a query dynamically, but I cannot find it, do you give me that pointer please? Thanks a million
JDBC KPRB driver default connection for Java Stored Procedures and Database JSPs.
Oracle s JDBC Thin driver uses Java sockets in order to connect directly to Oracle. It provides a unique TCP/IP version of Oracle s Net8 SQLNet protocol. Because it's 100% Java, this driver is platform independent which enable it to also run at a Web Browser applets.
Oracle s JDBC OCI drivers uses Oracle OCI Oracle Call Interface to have interaction with an Oracle database. You must employ a JDBC OCI driver appropriate for a Oracle client installation. The OCI driver works most likely through SQLNet or Net8.
JDBC OCI7 works having an Oracle7 client.
JDBC OCI8 works with the Oracle8 client.
Either of such client versions can access Oracle7 or Oracle8 servers.
The JDBC OCI drivers assist you to call the OCI right from Java, thereby providing a higher degree of compatibility using a specific Version of Oracle. Because they use native methods, they can be platform specific.
Oracle s JDBC KBPR driver is especially used for writing Java stored procedures, triggers and database JSPs. It uses the default/current database session thereby requires no additional database username, password or URL.
All three drivers support exactly the same syntax and APIs. Oracle needs three drivers to back up different deployment options. Looking at source code, they are going to only differ inside way you hook up to the database. Remember, you must make use of a JDBC version which fits the version within your Java Development Kit.
How can you connect using the JDBC Thin Driver?
The the JDBC thin driver provides only way gain access to Oracle on the Web applets. It is smaller and faster compared to the OCI drivers, and doesnt have to have a pre-installed version with the JDBC drivers.
machine:port:SID, userid, password
Statement stmt ;
select BANNER from SYS.VVERSION
1;//Print col 1
How would you connect with all the JDBC OCI Driver?
One have to have Net8 SQLNet installed and before trying to use one from the OCI drivers.
or oci7 Service, userid, password
name tablespacename,
from dbadatafiles a, select fileid, sumbytes free
Consider a sequence named MYSEQ which is created to generate automatic numbers. Somebody, in error, has tried to gain access to the sequence from SQLPlus and therefore the value is increased to 32. Our objective is actually to reset the worthiness to 10 in order that next created number should have a price of 11.
WHERE sequencename MYSEQ ;
This will return 1. It just isn't always essential to run the above mentined query to uncover the INCREMENT BY value. You can think it is out by checking out the column data for the purpose the sequence is utilized.
ALTER SEQUENCE myseq INCREMENT BY - 1;
LINESQLERRM;
ALTER SEQUENCE myseq INCREMENT BY 1;
Date and time format settings are based for the users locale and native language. For example, SCOTT needs default NLSDATEFORMAT MM/DD/YYYY, and ZAHN needs default DD-MON-YYYY., etc. The idea is always to create an AFTER LOGON trigger at database level, which reads LOGON session UserName and arranges NLSDATEFORMAT by executing ALTER SESSION dynamically.
IF SYSCONTEXTuserenv, sessionuser SCOTT THEN
c, alter session set nlsdateformat
chr39MM/DD/YYYYchr39, ;
ELSIF SYSCONTEXTuserenv, sessionuser ZAHN THEN
c, alter session set nlsdateformat
chr39DD-MON-YYYYchr39, ;
There is obviously some ambiguity in connection with datafile size limit using a given platform and version in the database. Here is a simple method of identifying the most size which a datafile can grow before switching to another location datafile.
Suppose you've got a locally managed tablespace NOL with AUTOEXTEND on with no MAXEXTEND value specified Note: Dont perform the reason you just read this tip.
select fileid, filename, autoextensible
where tablespacename like NOL;
Querying the filext table, we are able to get the value on the maximum size in database blocks this agreement the datafile can grow before switching to a higher file. Technically this must be the ideal filesize that Oracle can understand to get a database datafile.
select from filext where file 9;
select value from vparameter where name dbblocksize;
Setting the default will give you a maximum importance of 4194302 blocks with increments of a block once the datafile extends. On a database of 4K block size this might work out being:
Is Oracle functional?
One in the first tasks required associated with an interface would be to check that the objective Oracle database is definitely ready for business. Ive seen and used many methods to try this, but my current favorite would be to run a small predictable query and view the result. If the query performs as you expected, then it's likely that Oracle is OK. The following function Is Oracle OK does the job
For SUN Solaris, grep in /usr/xpg4/bin have to be used, hence the PATH variable is setup.
select dummyOK from dual;
This script queries the dual table and scans the output to the string that is certainly expected if it is all totally working well XOK. Note that care is taken to make sure if Oracle were to reject the submitted SQL, any fragment of the Oracle - generated error report won't meet the acceptance criteria set from the grep command.
Now we have established which the database is operational utilizing the Is Oracle OK function, we should insert some on the contents of theetc/passwd file right into a table. In this situation, we would like to load the fifth fields delimited by of each and every line that starts with the character r. To add a little bit more spice, the next example code creates, populates, queries and drops the example table. We issue a commit whenever 10 records are inserted and following last insert. Note the application of UNIX environment variables in the SQL statements.
print commit; Issue a commit as soon as the last insert
In shell scripts, usually turn autocommit off and take charge of issuing the commits self. Note which the shell variables Item1 and Item5 are single-quoted inside the insert SQL because they can be strings. Dont quote numbers!
Watch out for single-quote characters contained inside data source, theyll result in the SQL statements to fail should you not escape them or take them out.
A neat trick to use in certain situations like debugging is usually to save the many SQLPlus input and output with a file. Try replacing
command around the last line with the previous example code, with all on the single line
While this will work nicely, its better than replace the many sqlplus - s scott/tiger strings from the body from the code by having an alias, simply because this allows to provide a debug oracle facility, which often can activate to record the Oracle IO. To do that, the beginning with the Oracle S hell-script code includes this again all over a single line
Once the alias is defined, utilize alias To Oracle instead on the string sqlplus - s scott/tiger from the body on the code; for instance:
If SQLPlus encounters a mistake, it generally reports the problem having an error code prefixed with either ORA, ERROR. Armed with this information, test that some Oracle interactions been employed correctly by just scanning the output with an occurrence of the error prefix, for example within the following code snippet the domain name of TABLE is missining to produce a blunder:
Once again, we like to define an alias with this call through SQLPlus, but a function that can take the SQL as a possible argument could also be used for making things even prettier:
When a SQLPlus session terminates, all uncommitted transactions are generally lost or committed according to your setup. Using the method just outlined, this may cause things a tad difficult if you wish to check how the situation is going without ending the actual SQLPlus session. To counter this issue, set a SQLPlus session as being a co-process and contact it while using the print - p and browse - p shell commands. For example, initiate a SQLPlus session as being a co-process with these command:
Note: UNIX also reports how the co-process has completed.
Unfortunately, the read - p command will hang if you have no output to accumulate or the output stream is not terminated. This can even happen if you're just too quick in attempting to grab the output while Oracle continues to be cogitating. This method, however, offers the programming prize to become able to fully contact Oracle with a single, unbroken SQLPlus session.
To stop the read command from hanging programs, place a marker within the output stream for instance, utilizing the SQLPlus prompt command after requesting some work. T hen make sure the code isn't going to read beyond that marker. Placing the marker also provides the system the instant required to avoid the too quick hang mentioned earlier.
Newer versions from the Korn shell give a time-out argument to the read command.
When while using methods outlined on this page, PL/SQL might be used in just the identical way as SQL. For example:
P L/SQL procedure successfully completed.
The benefit of Oracle shell scripts is that you may apply the entire range of UNIX tools towards the Oracle data. Given the Oracle interface within reason simple, the productivity boost gained by the shell scripter using UNIX to govern the data may be significant.
If you wish to retrieve TABLENAMES from ALLTABLES the spot that the table name is a lot like ADD with all the following query, you could possibly notice how the query is returning ADDRESS and ADDCODES:
create table address p1 number;
create table addcodes p1 number;
where tablename like ADD% ;
If you are trying to escape the with these query, you will still find the same result.
Therefore the real question is: How do you use LIKE to find data made up of an underscore or percent sign? The answer would be to escape the underscore and/or percent sign from the LIKE template. You must designate what character you would like to utilize via the ESCAPE keyword. A slash is often used, but any character would actually work:
If you wish to return a code from SQLPlus towards the UNIX shell script, next the WHENEVER SQLERROR directive may help. We can return a status from 255 on the UNIX shell. This is usually inside? environment variable with the Korn or Bash Shell once execution of the command. Here is a KSH script that shows how this may work:
which you intend to send from SQLPlus towards the shell, when
The 1 is the thing that you are looking for. The key should be to
raiseapplicationerror - 20000-224 - :rc, Error! ;
to boost the error. We can raise errors in the given range, nevertheless the shell will simply keep an unsigned byte from the status return value values 255. It takes our exit file and looks at that last byte. By using - 20000-224 and subtracting from a return code, we find yourself exiting together with the value of the return code provided that:rc is inside 255!.
When SQLPlus starts up, it seems for two files - and. The must be inside directory ORACLE HOME/sqlplus/admin and may be run when the conn e ction to your database is complete. Once the file is executed, the file will be
executed, though the location on the file is quite more flexible. It depends about the current working directory that you started SQLPlus plus the operating system environment variable SQLPATH.
If there is a from the local directory, it'll be executed. If there is no local file, then Oracle will examine the planet variable SQLPATH and walk the trail until it finds the 1st directory containing data called and definately will execute that script and after that stop walking the way.
If you need to copy this, make certain that every user has got the privilege available the vinstance dynamic performance view through the SYS schema. You may find on some versions and platforms you will get an otherwise inexplicable hang since you connect from SQLPlus.
O ne major deficiency with this approach is when a user does a connect user/password then a script is just not re-executed. Depending whatever you have put in to the prompt this will leave the user using a completely misleading prompt.
GRANT SELECT ON VINSTANCE TO PUBLIC;
SQLPlus: Release 8.1.7.0.0 - Production
c Copyright 2000 Oracle Corporation.
Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production
JServer Release 8.1.7.1.0 - Production
How can I ensure the end user only has one session within the database?
This could be a requirement of your application, w ell, we have not found reasonable for limiting people in this particular way. However, people wish to accomplish it then when they do, many of them do it the difficult way. For example, they're going to have a load job run by the operating-system that will think about the VSESSION table and arbitrarily kill sessions of users that have more then 1 session. Alternatively, they may create their particular tables and enjoy the application insert a row when an end user logs in, and take the row after they log out. This implementation invariably contributes to lots of calls for the help desk because if the application crashes, the row never gets removed. There are many other creative ways to try this, but none is just as easy as:
SQL create profile onesession limit sessionsperuser 1;
SQL alter user scott profile onesession;
SQL alter system set resourcelimittrue;
Thats it now any user using the ONESESSION profile can login only once.
Most people are familiar together with the NVL function which checks for your existence of NULL values. Oracle offers a new function called NVL2 which checks for your existence of NOT NULL. The syntax because of this function can be as follows.
If expr1 just isn't null then a function will return expr2. Otherwise, the function will return expr3. The expr1 may have any datatype and arguments expr2 and expr3 may be of any datatype besides LONG. The datatype in the return value is the one about expr2.
SQL select empno, ename, sal, comm, nvl2comm, 1, 0 commpre
The above query checks for that presence of NOT NULL from the COMM field, and returns 1 where COMM is NOT NULL and 0 where it truly is NULL.
NVL takes 2 arguments while NVL2 takes 3.
NVL returns the initial argument if the initial argument will not be null, whereas NVL2 returns your second argument if the initial argument just isn't null and returns the 3rd argument if the initial argument is null.
In NVL, the datatype on the return is that in the first argument, in NVL2 the datatype in the return is that on the second argument.
Suppose you are asked about certain activities on your own database, on this example using a certain table. You need to keep a record who has inserted, updated, or perhaps deleted which record with this table.
For inserting and updating records you could possibly include additional columns for example last inserted/updated by and last inserted/updated timestamp, but whats around the deleted records?
Using an action log table provides the possibility to keep a count for all DML statements as needed. Follow the simple example below and you should participate inside the benefits of action logging:
SESUSERNAME VARCHAR230,
SESTERMINAL VARCHAR230,
Create trigger to log inserting, updating,
LACTION %TYPE;
IF INSERTING THEN LACTION: INSERT; END IF;
IF UPDATING THEN LACTION: UPDATE; END IF;
IF DELETING THEN LACTION: DELETE; END IF;
NAME, LACTION,
SYSDATE, , , ,
S. CLIENT INFO
WHERE USERENVSESSIONID;
In the 1st step the action log table is produced. You may enlarge it by your own needs, by table name to log different tables within the identical action table.
In the next step the trigger is created inside the table that you simply wish to find the logging information. In this case it will be the ABCUSER table. According to the present DML statement the neighborhood action variable is determined to either INSERT, UPDATE, or DELETE. The insert statement on the logging table gathers the existing information around the manipulated user data USERID, USERNAME and the actual session settings. The current session settings are selected from VSESSION utilizing the general purpose function USERENV to match the actual session identifier.
If you are attempting to truncate a table using a remote database, you will obtain the following error:
You use a database link for the remote database so you're able to see objects there and execute them procedures, functions, packages, triggers, etc. T he option would be to make the following procedure within the remote database, then execute it on the local one.
vsqlerrorcode PLSINTEGER;
vsqlerrormessage VARCHAR2512;
EXECUTE IMMEDIATE TRUNCATE TABLE ptablename;
vsqlerrormessage: SQLERRMvsqlerrorcode;
LINEOTHER ERROR;
LINEvsqlerrormessage;
END TruncateRemoteTable;
To execute the task, use the next from a nearby database:
TruncateRemoteTabledblink remotetablename ;
I n release 8.1, a fresh SQL function, TOLOB, copies data from your LONG column within a table to your LOB column. The datatype on the LONG and LOB must correspond for just a successful copy. For example, LONG RAW data has to be copied to BLOB data, and LONG data has to be copied to CLOB data. In the next example we show the way to migrate a table with one LONG with a CLOB datatype.
Create a short lived table with converted BLOB field.
AS SELECT id, TOLOBbdata bdata FROM document;
RENAME lobtmp TO document;
If you're planning to store LOB data, combined with other data inside same table, then you definately should keep the LOB data within a separate tablespace.
You are applying an old browser. It might not exactly display this and other websites correctly.
The Forum continues to be upgraded to some modern and advanced system. Please see the announcement relating to this update.
Get eliminate all advertisements and obtain unlimited use of documents by upgrading to Premium Membership. Upgrade to Premium Now plus get a Premium Badge!
hello all, perhaps there is any good free or open source option to software tools like TOAD or PL/SQL developer.
Basically i will be looking at some SQL and PLSQL development and in addition some administrative options like browsing schemas and tablespaces etc. Google search threw up something called dbSaint on the other hand don t define anything abt it. Thanks for your time
i believe additionally there is a free version of TOAD with many restrictions. Why don t you are attempting that out. I never really about heard dbSaint so can t supply you with any pointers for that particular.
You can try Toad trial versions. Doesn t have much restriction for normal development activities. Doesn t even must install Toad earlier versions. Just must copy and paste.
surprise surprise. doesn't have one have you heard of Tora? Open source could be the world s best movement in case you ask my humble opinion. Yes this from somebody who s neck-deep irnto oracle,
that reminds me of mySQL, another brilliant open source product now Sun has got it over. Btw this excellent website also has mySQL with the backend! Coming bacj to your point. Tora is a great option to TOAD, it s Open Source also it s free
I heard it before, and not as free Product. One from the contributor vendor is Quest Software, Inc Toad can be by exactly the same vendor.
I never tried. I use a concern about the following part of their documentation.
All distributions call for a working Oracle client installation. This includes the statically linked Oracle binary for Linux. Oracle 8i will be the only supported client side version At least Oracle 8.1.5, it will work partially with earlier or later versions of Oracle but it can be as of the version not supported Please make an effort to send me patches.
Btw, I assume 90% of Oracle Forum s backend is MySQL rather than Oracle!!!
hmmm yes, i did so read this point but am undecided whether they signify for windows executable installer or source files which have to be compiled. I possess a feeling the windows executable setup work fine even with no oracle client.
However on second thoughts, going even when installing TOAD you choose to do require an oracle client installed. because TOAD searches to the Oracle Home along with the tnsnsames file. So it s hardly surprising that Tora may necessitate an oracle client too.
Btw, I assume 90% of Oracle Forum s backend is MySQL but not Oracle!!!
Actually that s as you wouldn t get a web host which will offer oracle in the backend. We can t afford it! I presume OTN operates on oracle because oracle would have his or her servers i do think
TOAD works even lacking any oracle client, a minimum of the latest version 9 i believe works although u don t come with an oracle home set. You can directly offer the IP and SID of the database server and yes it connects you through. Though within my machine oracle database 10g is installed, on the other hand never bother to update my when i hook up with a new database server because i just supply the IP and SID.
Oracle 8i may be the only supported client side version At least Oracle 8.1.5, it may work partially with earlier or later versions of Oracle but it's as with this version not supported
ie. As per the documentation Oracle client of 8.0.3 Or 9.2.0.110.2.0.4 will never work rather would possibly not work.
hi everyone thanks for every one of the replies. I installed oracle client 8i and installed Tora. It s everything i want to and even more and flawlessly. Thanks for your support
hey simona, you can even consider Oracle SQL Developer. It s also free as well as a great utility. you'll be able to download it from oracle s site:
I liked DreamCoder for Oracle Free, you may you are able to download it from site.
Sql Developer - oracle prefered good much better than toad on execution navigation.,
Do you already produce an account?
Develop complex object oriented applications in 50% much less time with TierDeveloper. Generate Code without compromising on quality. TierDeveloper is definitely an object to relational mapping O/R mapping code generation tool that allows map and generate well business and data objects. TierDeveloper also generates custom and Windows Forms apps.
Tier Developer fulfills all Code Generation needs a single package. The leading NET Code Generation and Object Relational Mapping tool was recently declared to get entirely free software TierDeveloper can generate code in and C for, Windows Forms, Remoting and Web services. It would be the Code Generator that allows you to work with its powerful code generation templates within the simplest manner possible. Generate code for SQL Server, MS Access, DB2, Oracle, and OLEDB compliant databases.
Map objects and embed SQL, stored procedure calls, and business rules
Code Generator for, Windows Forms, Remoting client
Customize GUI layout. Use combo-boxes and more
Copyright Alachisoft 2002,.
Develop complex object oriented applications in 50% much less time with TierDeveloper. Generate Code without compromising on quality. TierDeveloper can be an object to relational mapping O/R mapping code generation tool that allows map and generate well business and data objects. TierDeveloper also generates custom and Windows Forms apps.
Tier Developer fulfills all Code Generation needs within a package. The leading NET Code Generation and Object Relational Mapping tool was recently declared to become entirely free software TierDeveloper can generate code in and C for, Windows Forms, Remoting and Web services. It would be the Code Generator that permits you to work with its powerful code generation templates from the simplest manner possible. Generate code for SQL Server, MS Access, DB2, Oracle, and OLEDB compliant databases.
Map objects and embed SQL, stored procedure calls, and business rules
Code Generator for, Windows Forms, Remoting client
Customize GUI layout. Use combo-boxes and more
Copyright Alachisoft 2002,.