Re: PL/SQL help needed

Farjaad

PLSQL 201. The long but still incomplete answer.

1. What is the difference between PL/SQL Tables and SQL Tables? -- SQL tables:
2. database structures stored in the database (on system files)
3. persistent - permanent unless table is drooped
4. manipulated and queried with SQL: SELECT FROM, DELETE FROM, INSERT INTO, UPDATE ...etc. accessible by many users - assuming appropriate privileges are granted
5. may have indexes, may have views based on them, may be replicated in a snapshot etc.................. -- PLSQL tables
6. programmatic structures, like a plsql variable
7. stored in memory of the users plsql process space
8. persist only for the scope they are defined in. If defined in one block or one procedure -they only last while the block or the procedure execute. If plsql table is defined in a package, the table persists from package invocation invocation until SESSION termination. Not accessible to other users or sessions.
9. comparable to single dimension arrays of records in other languages. Table records accessed by index subscript. Record fields accessed by dot notation.
10. manipulated by programmatic operators and attributes. Assignment by := operator, of elements, or of same typed records. Attributes like .first .next .last .delete .exists are used to locate, delete and test.
11. most operations are record at a time or field at a time. (delete may delete one record if indexed, or all records if not indexed)
12. no set DML operations (other then delete)
13. no SQL operations, no where clause, no order by, no joins etc
14. write you own loops to access, manipulate plsql table data.
15. plsql table may be passed as a parameter in a plsql procedure or function, may be returned by a function (NOTE: procedures with table parameter can be used as data source by FORMS 5.0 and 6.0) --
16. What is Dynamic SQL?
17. Most SQL used in PLSQL or in embedded SQL (pro*C etc.) is static. The static SQL code or commands are hard coded and written into the plsql at the time of PLSQL compilation. The static code does not change.
18. Dynamic SQL refers to SQL that is generated at execute time. The SQL code can be generated programmatically or possibly input by users. The code is stored in a char variable, then it is passed to the database by a number of functions for parsing and execution. The number of functions required differ for different types of SQL statements - with queries taking the most function calls, and DDL taking the least. The functions vary slightly by environment (plsql, PRO*C/COBAL/ADA etc., or OCI) but follow the same pattern. NOTE: SQL writing/executing SQL is a kind of dynamic SQL available in SQLPLUS. NOTE: Plsql can also be dynamic, that is: a plsql block, procedure or executable statement can be generated and compiled and executed dynamically. --
19. Can we use DDL statements in PL/SQL? PLSQL by itself - without extensions cannot do DDL. With earlier versions of PLSQL this was a significant programmatic limitation. With later and current versions of Oracle, the DBMS_SQL package has been provided for dynamic SQL/plsql. This package executes on the server side. There are also Oracle client side packages that can execute client side plsql (forms package extensions) as well as SQL. With the DBMS_SQL package, dynamic SQL can be done - and that dynamic SQL can include DDL. -- Here is a procedure that I use to execute DDL. Store it under a schema with correct privileges to execute the DDL. -- CREATE OR REPLACE procedure do_ddl (in_ddl in varchar2) is /* Author Mark Fredericks PROSOFT (Professional Software Engineering Inc, Virginia Beach VA) execute a SQL ddl command example: do_ddl('create table scott.new_tab(col_1 VARCHAR2(20) NOT NULL)'); do_ddl('create unique index new_indx on new_tab(col_1)'); */ cursor_handle integer := dbms_sql.open_cursor ; ddl_str varchar2(32000) := in_ddl; begin dbms_sql.parse(cursor_handle,ddl_str,dbms_sql.native); dbms_sql.close_cursor(cursor_handle); end;

There are a number of PLSQL books that cover this topic including:

Oracle Pl/SQL Programming
by Steven Feuerstein, Bill Pribyl, Debby Russell O'Reilly & Associates; ISBN: 1565923359

Oracle Pl/SQL Programming (Oracle Series) by Scott Urman, Tim Smith
Oracle Press; ISBN: 0078821762

I strongly recommend both.

This should get you started, Good luck. HTH

Mark